Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

SQL Server Programming Hacks - 100+ List

From Wiki

Jump to: navigation, search

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
Military Time Format
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
Using the STUFF function to format the time from a datetime or time datatype
Converting Unix time to SQL Server datetime

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

XML

Creating A XSD Schema From A Table In SQL Server With FOR XML Syntax


Handy tricks

5 ways to return rows from one table not in another table
Finding Record with Last Modified date in all tables
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
Rename column in many tables with one script
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
How to reset an identity value after a table has been emptied
How to insert a row into a table with only an identity column
Generating a MD2, MD4, MD5, SHA, or SHA1 hash by using HashBytes

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
WHERE conditions on a LEFT JOIN
Union All
IntelliSense does not see new objects in SQL Server Management Studio

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

SQL Server Integration Services

How to debug 64 bit SSIS packages

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

Useful 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
How to enable the CLR on SQL Server 2005 and higher


Contributed by: SQLDenis


ROLLBACK

265 Rating: 3.5/5 (137 votes cast)