Sometimes you want to know what the percentage is of null values in a table for a Column
Or you might want to know what the percentage of all values in a Column is grouped by value
You can get these answers by running the code below

First create this table

Get the percentage of nulls in all the Columns in my table

Output

Column1Percent	Column2Percent	Column3Percent
55.555555555555	11.111111111111	22.222222222222

Get the values and the percentage of all values for a Column

Output

Value	ValueCount	Percentage
1	6	66.666666666666
NULL	2	22.222222222222
2	1	11.111111111111

You have only $50 left and you can buy two DVDs or one SQL book, what do you do? I would buy the book but not every person has the same idea of a fun time. This is the reason why I present you with a bunch of links to articles which will give you very good info. some of this you won’t be able to find in a book anyway.

The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should - and when you should not.

Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. Two versions are available, one for SQL 2005 and one for SQL 2000 and earlier.

Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background. Two articles on error handling in SQL Server.

The ultimate guide to the datetime datatypes
The purpose of this article is to explain how the datetime datatypes work in SQL Server, including common pitfalls and general recommendations.

Stored procedure recompiles and SET options
Using stored procedures is generally considered a good thing. One advantage of stored procedures is that they are precompiled. This means that at execution time, SQL Server will fetch the precompiled procedure plan from cache memory (if exists) and execute it. This is generally faster than optimizing and compiling the code for each execution. However, under some circumstances, a procedure needs to be recompiled during execution.

Do You Know How Between Works With Dates?
article explaining why it can be dangerous to use between with datetime data types

How Are Dates Stored Internally In SQL Server?
Article explaining how datetimes are actually stored internally

Three part deadlock troubleshooting post, a must read if you want to understand how to resolve deadlocks.
Deadlock Troubleshooting, Part 1
Deadlock Troubleshooting, Part 2
Deadlock Troubleshooting, Part 3

SQL Server 2005 Whitepapers List
A list of 29 different SQL Server 2005 Whitepapers

Keep a check on your IDENTITY columns in SQL ServerThis article shows you how to keep an eye on your IDENTITY columns and find out before they run out of values, and fail with an arithmetic overflow error.

Character replacements in T-SQL
Quite often SQL programmers are left with the dirty job of working with badly formatted strings mostly generated from external sources. Typical examples are badly structured date values, social security numbers with misplaced hyphens, badly formatted phone numbers etc. When the data set if small, in many cases, one can easily fix by a one time cleanup code snippet, but for larger sets one will need more generalized routines.

The selectivity of an index is extremely important. If your index is not selective enough then the optimizer will simply have to do a scan. This is also a reason why creating an index on a gender column does not make a lot of sense.

First create this table

USE tempdb
go

CREATE TABLE TestCompositeIndex (State char(2),Zip Char(5))
INSERT TestCompositeIndex VALUES('NJ','08540')
INSERT TestCompositeIndex VALUES('NJ','08540')
INSERT TestCompositeIndex VALUES('NY','10028')
INSERT TestCompositeIndex VALUES('NY','10021')
INSERT TestCompositeIndex VALUES('NY','10021')
INSERT TestCompositeIndex VALUES('NY','10021')
INSERT TestCompositeIndex VALUES('NY','10001')
INSERT TestCompositeIndex VALUES('NJ','08536')
INSERT TestCompositeIndex VALUES('NJ','08540')

If you have a composite index (composite means the index contains more than one column) you need to run this code.

DECLARE @Count int

SELECT DISTINCT State, Zip
FROM TestCompositeIndex;

SET @Count = @@ROWCOUNT;



SELECT (@Count*1.0) / COUNT(*) AS IndexSelectivity,
COUNT(*)AS TotalCount,
@Count AS DistinctCount
FROM TestCompositeIndex;

Result
——–
IndexSelectivity TotalCount DistinctCount
.555555555555 9 5

If you have a one column index you can use this code

SELECT (COUNT(DISTINCT State)* 1.0) / COUNT(*) AS IndexSelectivity,
COUNT(*) AS TotalCount,
COUNT(DISTINCT State) AS DistinctCount
FROM TestCompositeIndex;

Result
——–
IndexSelectivity TotalCount DistinctCount
.222222222222 9 2