Some SQL Server Programming Hacks

Some SQL Server Programming Hacks

NULLS

1. Testing for NULL values
2. Use COALESCE To Return First Non Null Value
   How To Check If Any, ALL Or No Parameters Have A NULL Value
3. Return first non null value
4. Return Null If A Value Is A Certain Value
5. Concatenation with nulls
6. NOT IN and nulls

Dates

1. First and last days of month, quarter, week
2. Epoch date
3. Get Datetime Without Time
4. Date Ranges Without Loops (numbers table)
5. Calculate birthday in years
6. Formatting Dates
7. Calling Stored Procedures With Datetime Parameters
8. Trouble With ISDATE And Converting To SMALLDATETIME

Sorting, Limiting,Ranking, Transposing and Pivoting

1. Return Top N Rows
2. Dynamic top
3. Sorting Numbers Stored In A Varchar Column
4. How To Use ROW_NUMBER() In A WHERE Clause
5. Row To Column (PIVOT)
6. Column To Row (UNPIVOT)
7. Split A String By Using A Number Table
8. Concatenate Values From Multiple Rows Into One Column
9. Concatenate Values From Multiple Rows Into One Column Ordered
10. Rank
11. Dense rank
12. Rownumber
13. Sort certain values last
14. Returning The Maximum Value For A Row

Handy tricks

1. Five ways to return all rows from one table which are not in another table
2. Order IP Addresses
3. Data formatting dates
4. Data formatting SSN
5. 6 Different Ways To Get The Current Identity Value
6. Use XACT_ABORT to roll back non trapable error transactions
7. Random Sorting
8. Sort Values Ascending But NULLS Last
9. Adding Leading Zeros To Integer Values
10. How do I format money/decimal data with commas?
11. Find Out How Many Occurrences Of A Substring Are In A String
12. Ten SQL Server Functions That You Have Ignored Until Now
13. Use the *1 trick to do math with two varchar values
14. Store The Output Of A Stored Procedure In A Table Without Creating A Table
15. Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both
16. Three Ways To Display Two Counts From a Table Side By Side
17. Finding duplicates across columns
18. Use REPLACE To eliminate unwanted characters

Pitfalls

1. Update triggers
2. Integer math
3. Identity Values And Triggers
4. Use XACT_STATE() To Check For Doomed Transactions
5. Three differences between COALESCE and ISNULL
6. Non deterministic functions and nullif
7. Subquery typo with using in
8. ISNUMERIC Trouble
9. Case Without Else

Query Optimization

1. Case sensitive search
2. Functions on left side of the operator
3. Query Optimizations With Dates
4. Optimization: Set Nocount On
5. Don’t use arithmetic operators on a column in the where clause
6. Don’t use * but list the columns

Undocumented but handy

1. xp_getnetname
2. xp_fileexist
3. xp_dirtree
4. xp_subdirs
5. xp_getfiledetails
6. xp_fixeddrives
7. Sp_tempdbspace
8. xp_enumdsn
9. xp_enumerrorlogs
10. Some Undocumented DBCC Commands
11. sp_MSforeachtable
12. sp_MSforeachDB

Usefull Admin stuff For The Developer

1. sys.dm_exec_sessions
2. Find all tables that contain a certain column
3. Find All Tables Without Triggers In SQL Server
4. Find all Primary and Foreign Keys In A Database
5. Find Out If A Table Has An Identity Column
6. Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used
7. SQLDenis