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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s