SQL Server Programming Hacks - 100+ List
From Wiki
Contents |
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 How To Calculate How Many Minutes Have Passed Since Midnight With Datetime In SQL Date Range WHERE Clause Simplification ISO Week In SQL Server
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 Using Common Table Expressions for Parent-Child Relationships
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 How To Check For A Certain Time With SQL CSharp IsNullOrEmpty Function In SQL Server SQL Compare Without The Price Tag Use RAISERROR within a Try/Catch Use sp_executesql Or EXEC To Get The Count For Dynamic Table Names Converting IP Addresses Between Bigint and Varchar
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 Syntax Error Converting The Varchar Value To A Column Of Data Type Int Check If Temporary Table Exists SQL Server Quirks - sp rename
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
Contributed by: SQLDenis
