Doug Lane’s 11-Part T-SQL Level-Up

The best practices for writing T-SQL

• Cursor:
  • It works in the logic of For each row in the table, do this with col1, do this with col2, do this with col3
  • Instead of this we need: for the table do….
• Number table (table having only numbers)
  • Joining a simple number table with a table will help you get output for all the values even for which you don’t have anything in the main table
  • FROM numbers n Left JOIN main on main.num = n.num Group By main.clm Order By main.clm
• Date Table (Table having only dates)
  • Establish a single source of data
  • Easily look up date names without DATENAME()
  • No need to cast and concatenate the date to get the formatted form of date
  • Store custom periods specific to your needs
• CASE:
  • It is an expression and not a statement
  • It can evaluate multiple condition without nesting
  • It can be placed inside other expression and functions
  • It is a simple way to Pivot
  • It can be used with Insert, update, delete
• Computed Columns
  • Deterministic/Non-deterministic question
  • Persisted: Computed value is stored into the table
• Windowing functions
  • ROW_NUMBER() Over (Partition by clm order by clm1)
  • SUM(Clm) Over (Partition By clm2, clm3)
  • AVG(clm) Over (Partition By clm2, clm3)
  • LAG/LEAD(clm,1) Over (Order By clm2)
  • First_value/Last_value(clm1) Over (Order by clm2)
  • SUM(clm1) OVER (ORDER BY clm2 Rows Between Unbounded/3 Preceding AND Current ROW/Unbounded following)
• Cursors
  • T SQL Level Up Chapter 6 Replacing Cursors Part 1
  • Steps for changing from cursor to set based
    • Remove the cursor language
    • Remove the variable declaration