SQL Server Programming Hacks

[SQLDenis]

This is a collection of SQL hacks available on the Wiki, right now we have 8 sections and between 70 and 80 hacks. Ideally we will have more hacks and we will also have a SQL admin hacks page in the future.

Sections
* 1 NULLS
* 2 Dates
* 3 Sorting, Limiting Ranking, Transposing and Pivoting
* 4 Handy tricks
* 5 Pitfalls
* 6 Query Optimization
* 7 Undocumented but handy
* 8 Usefull Admin stuff For The Developer


Below are direct links to all the hacks on the wiki, you can also get a list of all the hacks on the wiki itself here: SQL Server Programming Hacks


NULLS

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

Dates

First and last days of month, quarter, week
Epoch date
Get Datetime Without Time
Date Ranges Without Loops (numbers table)
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
How To Use ROW_NUMBER() In A WHERE Clause
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
Rank
Dense rank
Rownumber
Sort certain values last
Returning The Maximum Value For A Row
Using Common Table Expressions for Parent-Child Relationships

Handy tricks

Five ways to return all rows from one table which are 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
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
Functions on left side of the operator
Query Optimizations With Dates
Optimization: Set Nocount On
Don’t use arithmetic operators on a column in the where clause
Don’t use * but list the 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

[SQLSister]

Nice Dennis, You guys have been busy haven't you?

[chrissie1]

Nah, not really

[hakus]

cool collection ... more more more

[Remou]

It is a great achievement.

[SQLDenis]

We had 80 hacks when we started and now we have 84. I have been concentrating on the SQL Server Admin Hacks lately
Right now we have 24 admin 'hacks' and these are not categorized yet

[dodo666]

Gr8 stuff guyz,

Thnx.

[Emtucifor]

Do you want to put "Date Range WHERE Clause Simplification" under the dates section?

[SQLDenis]

Will do (l8er 2nite)

[SQLDenis]

I moved it

[JeffModen]

Since it's a Wiki, can anyone add to it? If so, how to add to existing Wiki posts and how to create a new one, please?

[JeffModen]

To give an example of why I wanted to get in on the Wiki, let's look at the undocumented hack called xp_DIRTREE. The Wiki has code in it like this...

tsqlLine number Off | Hide | Select all

1. EXEC master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL'

If you run something like that, you get a listing of directories and all subdirectories along with what the depth of the directory is. While that's nice, there are other uses for xp_DIRTREE if you understand that there's not just one operand for DIR, but 3

The first operand you've already seen and guessed. It's the "base" directory that you want to work down from.

The second operand is numeric. 0 (the default when not included), says to list all subdirectories. Any other number will be the limit to the depth of subdirectories found. If you GOOGLE xp_DIRTREE, you can find many articles about this second operand.

Not so true with the third operand. I think there might only be two articles on all of GOOGLE that describes it. 0 (the default value when not included), says to list only directories. Any non-zero value tells it to also list file names along with the directories and an extra column that shows up called "file"... if that column has a 0 in it, its a directory. If that column has a 1 in it, it's a FILE!

Try running this and see...

tsqlLine number Off | Hide | Select all

1. EXEC master..xp_dirtree 'C:\Program Files',2,1

I use the 3 operand version in my BULK INSERT ETL code all the time to fill a file with file names. xp_DirTree is available in 2k, 2k5, and 2k8. Dunno about 2010.

tsqlLine number Off | Hide | Select all

1. CREATE TABLE #MyDir
2.         (
3.         RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
4.         Name   VARCHAR(256),
5.         DEPTH  SMALLINT,
6.         IsFile TINYINT
7.         )
8.  
9.  INSERT INTO #MyDir
10.         (Name, DEPTH, IsFile)
11.    EXEC Master..xp_DirTree '<somedirectoryhere>',1,1
12.  
13.  SELECT * FROM #MyDir WHERE IsFile = 1

[chrissie1]

Since it's a Wiki, can anyone add to it? If so, how to add to existing Wiki posts and how to create a new one, please?

Editing is simple, you just go to the article and then click on the edit link in the sidebarmenu.

Adding an article is also simple (most of the time). YOu just go to the desired category and then fill in some title in the create article in this category textbox.

[Naomi]

BTW, thanks to both you guys I finally read it - good read. Otherwise I was just skiping this thread...

[onpnt]

Added to the admin hacks today
Restore DB and Replication will not start
Fix Orphaned Database Users
Finding Forwarded Records SQL Server 2008
Transfer all tables to different schema