SpurData Blog

Little disciplines to remember when programming T-SQL. Structured programming practices: Find what varies and encapsulate it Keep your procedures small; let each do one thing and do it well Favor Clarity over Cleverness Clever solutions are being scrapped in IT shops all over the world every day and replaced with less "clever" solutions that are understandable and maintainable. Usually Consistent is better than Better. (Sad but true.) Simply formatted header comments are maintained; complex formats are not. Example of a simple header: /* Note on what the proc does. usage: exec usp_MyProc created YYYYMMDD by username updated YYYYMMDD by username - chg# xxxx: description */ In Reporting queries, prefer #Temp Tables over @Table Variables. Table variables are easier to pass around, but the optimizer always assumes there is only one row. For larger temp storage (>100 rows), use #temp tables which produce real statistics. Set vs. Select - prefer Set Assume @x = &q

Some time ago I read a blog that I've lost track of, but it suggested that in the same way that practicing piano scales builds muscle memory and proficiency that then allows you to more easily attain higher level skills, so in other disciplines we should practice our scales in order that our daily work could achieve a higher level with less effort. It's similar to how having memorized your times tables makes algebra more accessible. So for myself I want to put together some SQL Scales, little mantras that help me stay on track as I'm writing SQL or administrating SQL Server.