SQL Scales: 2 - T-SQL Programming

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 = "flower"

    • "Set @x = (select something)" will set @x = null if (select something) returns nothing

    • "Select @x = something" will leave @x = "flower" if (select something) returns nothing


  • Common Abbreviations / Naming:


    • Amt, Nbr, Pct

    • List names should end with "List".


  • Solution Tension:


    • Do the simplest thing that will work

    • Solve the problem fully (don't settle for shallow/lazy/half-baked)

    • Avoid Premature Optimization

    • If you've got an 80% solution in your head, go with it. Don't wait for the "perfect" idea.


  • Remember, future-you will either bless or curse current-you based on what you create today. Let's make future-you happy :^)


 

Comments

Popular posts from this blog

SOPA and the NDAA

Hiding an ASPXGridView Delete button with HTMLRowCreated vs. CommandButtonInitialize

Specify the Server Name in your OutputFileDirectory for Hallengren SQL Server Backups