NULLS
Testing for Null Values
How To Check If Any, ALL Or No Parameters Have A NULL Value
Return The First Non Null Value
Return Null If A Value Is A Certain Value
Concatenation with NULL values
NOT IN and NULLs
Dates
How to find the first and last days in years, months etc
Epoch Date
Get Datetime Without Time
Date Ranges Without Loops
Calculate Birthday In Years
Formatting Dates
Calling Stored Procedures With Datetime Parameters
Trouble With ISDATE And Converting To SMALLDATETIME
Sorting, Limiting Ranking, Transposing and Pivoting
Return Top N Rows
Dynamic TOP
Sorting Numbers Stored In A Varchar Column
Row To Column (PIVOT)
Column To Row (UNPIVOT)
Split A String By Using A Number Table
Concatenate Values From Multiple Rows Into One Column
Concatenate Values From Multiple Rows Into One Column Ordered
How To Use ROW_NUMBER() In A WHERE Clause
Rank
Dense Rank
Row Number
Sort certain values last
Returning The Maximum Value For A Row
Handy tricks
5 ways to return rows from one table not in another table
Order IP Addresses
Data formatting dates
Data formatting SSN
6 Different Ways To Get The Current Identity Value
Use XACT_ABORT to roll back non trapable error transactions
Random Sorting
Sort Values Ascending But NULLS Last
Adding Leading Zeros To Integer Values
How do I format money/decimal data with commas?
Find Out How Many Occurrences Of A Substring Are In A String
Ten SQL Server Functions That You Have Ignored Until Now
Use the *1 trick to do math with two varchar values
Store The Output Of A Stored Procedure In A Table Without Creating A Table
Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both
Three Ways To Display Two Counts From a Table Side By Side
Finding duplicates across columns
Use REPLACE To eliminate unwanted characters
Pitfalls
Update triggers
Integer math
Identity Values And Triggers
Use XACT_STATE() To Check For Doomed Transactions
Three differences between COALESCE and ISNULL
Non deterministic functions and nullif
Subquery typo with using in
ISNUMERIC Trouble
Case Without Else
Query Optimization
Case Sensitive Search
No Functions on Left Side of Operator
Query Optimizations With Dates
Optimization: Set Nocount On
No Math In Where Clause
Don't Use (select *), but List Columns
Undocumented but handy
xp_getnetname
xp_fileexist
xp_dirtree
xp_subdirs
xp_getfiledetails
xp_fixeddrives
Sp_tempdbspace
xp_enumdsn
xp_enumerrorlogs
Some Undocumented DBCC Commands
sp_MSForEachtable
sp_MSforeachDB
Usefull Admin stuff For The Developer
sys.dm_exec_sessions
Find all tables that contain a certain column
Find All Tables Without Triggers In SQL Server
Find all Primary and Foreign Keys In A Database
Find Out If A Table Has An Identity Column
Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used