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

Post a Comment

Popular posts from this blog

SOPA and the NDAA

-
During the Internet blackout today against SOPA, I was complaining on VoiceOfTheDBA that I wish we could get a blackout on the recent "indefinite detention of US citizens" fiasco. I said: Unfortunately (as you know) we have bigger issues. I’d love to see a blackout for basic liberty:   http://www.washingtonpost.com/opinions/is-the-united-states-still-the-land-of-the-free/2012/01/04/gIQAvcD1wP_story.html   http://www.huffingtonpost.com/2011/11/29/senate-votes-to-let-military-detain-americans-indefinitely_n_1119473.html   http://abcnews.go.com/blogs/politics/2011/12/with-reservations-obama-signs-act-to-allow-detention-of-citizens/ But now I've read the actual final section of the law under question, and couldn't figure out what the big deal is: http://thomas.loc.gov/cgi-bin/cpquery/?&dbname=cp112&sid=cp112MdQpb&refer=&r_n=hr329p1.112&item=&&&sel=TOC_1073854& Upon further research, I see that mostly the NDAA refused to clarify our sta...
Read more

Hiding an ASPXGridView Delete button with HTMLRowCreated vs. CommandButtonInitialize

-
It's pretty typical in Administration forms to need to hide buttons on certain rows for various reasons. Particularly it is nice to hide the Delete button if in fact the row in question is being used in other places in the app. This is a nice feature to let the user know up front that the row in question is in use, rather than them trying to delete it and getting a foreign key related error after the fact. For years now in DevExpress ASPxGridView's I have been handling this in the HtmlRowCreated event. And I have been doing it wrong. Before: protected void gridGiftCodes_HtmlRowCreated ( object sender, DevExpress.Web.ASPxGridViewTableRowEventArgs e) { if (e.RowType == GridViewRowType.Data){ int commandColumnIndex = - 1 ; //Find the command column commandColumnIndex = e.Row.Cells.Count- 1 ; //assume it's the last visible colum //Get the command cell for this row GridViewTableCommandCell cell = e.Row.Cells[commandColumnIndex] as Gri...
Read more

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

-
I've been playing with SQL Agent tokens more, researching how Ola Hallengren uses them in his famous Maintenance Solution, and how you can use them. Particularly I was looking for a way to specify the "ServerName" in the Output File path. You can do this using a token instead of having to customize the MaintenanceSolution.sql script for each server. This is helpful when you're doing environment takeovers and trying to get consistent backups going across several servers. Here is an example of our solution, which is working well. You would modify line 29 of Ola's script:   DECLARE @OutputFileDirectory nvarchar ( max ) = '\\MyBackupServer\DBBACKUPS\ $(ESCAPE_SQUOTE(SRVR)) \OUTPUT' Then for each job step, Ola will setup the following output file. This can be seen in the "Advanced" properties of a particular Job Step. '\\MyBackupServer\DBBACKUPS\$(ESCAPE_SQUOTE(SRVR))\OUTPUT\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DAT...
Read more