Percentage Of NULLS And Values In A SQL Server Table

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

CREATE TABLE #perc ( Field1 INT,Field2 INT,Field3 INT)
INSERT INTO #perc
SELECT NULL,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT NULL,NULL,1
UNION ALL
SELECT NULL,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 1,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 2,1,2
UNION ALL
SELECT 3,1,1


--Get the percentage of nulls in all the fields in my table
SELECT 100.0 * SUM(CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field1Percent,
100.0 * SUM(CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field2Percent,
100.0 * SUM(CASE WHEN Field3 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field3Percent
FROM #perc


--Get the values and the percentage of all values in a field
SELECT Field3 AS Value,COUNT(Field3) AS ValueCount,
100.0 * COUNT(coalesce(Field3,0))/(SELECT COUNT(*) FROM #perc ) AS Percentage
FROM #perc
GROUP BY Field3
ORDER BY Percentage DESC



DROP TABLE #perc

Microsoft SQL Server Developer Edition 2005 CD/DVD For Sale On Amazon

For all of you people who don't have a MSDN subscription, Amazon is selling the developer edition of SQL Server 2005 for $59.99 with free shipping

Microsoft SQL Server Developer Edition 2005 CD/DVD

SQL Server 2005 and Visual Studio 2005 are released!!!

SQL Server 2005 and Visual Studio 2005 are available for download at the MSDN site
For people who have a subscription go here
http://www.msdn.microsoft.com/subscriptions/

Enjoy

SQL Server 2005 Certification Links

Below are the links for the SQL Server Certification exams

Microsoft Certified Technology Specialist: SQL Server 2005

Microsoft Certified IT Professional: Database Administrator

Microsoft Certified IT Professional: Database Developer

Microsoft Certified IT Professional: Business Intelligence Developer

Microsoft Certified Database Administrator

My Top 3 SQL Server Books

This is my top 3 SQL server books.
They are not in any order. I use all 3 of them frequently

The Guru's Guide to Transact-SQL
by Ken Henderson
This is a fantastic book, Ken has done such a great job. I have bought this book in December 2001 after reading some reviews on Amazon'
I must tell you that it is much better than I would ever have expected.
There are so many cool things that I didn't know you could do in SQL Server, the chapter about undocumented things is a gem.
Ken will show you many ways to accomplish something and of course every example is better than the one before it
If you are a SQL Server Programmer then this book belongs in your library, it doesn't matter if you are a beginner or a MVP you will definitely learn something from this book
Another bonus is that all the source code is on a CD


Inside Microsoft SQL Server 2000
by Kalen Delaney
If you need to know about locking/blocking/deadlocks/performance then this is the book for you It goes into such detail and with great examples on how to find out what causes deadlocks and how to prevent them. For example using sp_lock2
The SQL server architecture part is a must read as well the chapter about cursors and large objects


Microsoft SQL Server 2000 Unleashed (2nd Edition)
by Ray Rankins, Paul Jensen, Paul Bertucci
This is a very good reference book about SQL Server it covers everything from programming to administration to OLAP. I keep it on my desk when I need to look up things (with the help of post-it notes)

SQL Server 2005 Books On Line on MSDN

For those of you who don't want to download the books on line for SQL Server 2005 but would like to see it online here is the link (http://msdn2.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx)

Here are the links for the main categories

Database Engine(link)
The Database Engine is the core service for storing, processing and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. The Database Engine also provides rich support for sustaining high availability.

Analysis Services(link)
Analysis Services delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by allowing you to design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services enables you to design, create, and visualize data mining models. These mining models can be constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

Integration Services(link)
Integration Services is a platform for building high performance data integration solutions, including packages that provide extract, transform, and load (ETL) processing for data warehousing.

Replication(link)
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency. By using replication, you can distribute data to different locations and to remote or mobile users by means of local and wide area networks, dial-up connections, wireless connections, and the Internet.

Reporting Services(link)
Reporting Services delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.

Notification Services(link)
Notification Services is an environment for developing and deploying applications that generate and send notifications. You can use Notification Services to generate and send timely, personalized messages to thousands or millions of subscribers, and can deliver the messages to a variety of devices.

Service Broker(link)
Service Broker helps developers build scalable, secure database applications. This new Database Engine technology provides a message-based communication platform that enables independent application components to perform as a functioning whole. Service Broker includes infrastructure for asynchronous programming that can be used for applications within a single database or a single instance, and also for distributed applications.

Full-Text Search(link)
Full-Text Search contains the functionality you can use to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

Some Undocumented DBCC Commands

DBCC MEMORYSTATUS
This DBCC command provides detailed info about SQL Server memory usage

DBCC TRACEON(3604)
DBCC Resource
DBCC TRACEOFF(3604)
This DBCC command list SQL Server resource utilization

DBCC DBREINDEXALL('pubs')
This DBCC command will rebuild all the indexes for a user Database, the DB has to exclusively locked or you will get the error message “The database could not be exclusively locked to perform the operation.”

DBCC FLUSHPROCINDB
This will force a recompile of all the stored procedures in a database

DECLARE @ID INT
SET @id =DB_ID('pubs')
DBCC FLUSHPROCINDB(@ID)

Find Out How Many Occurrences Of A Substring Are In A String

If you want to know how many occurrences of a substring you have in a string play around with the code below. The trick here is to take the total length of the string, subtract the same string minus the occurrences and divide by the length of the substring

For example
String is ’ABABABABZZZZZ’, length is 13
Substring is ’AB’ length is 2
Length of string minus all occurrences of substring is 5
So (13 -5) /2 =4 occurrences

DECLARE @chvString VARCHAR(500)
SELECT @chvString ='ababababajdfgrhgjrhgierghierabababaaaaaaaabbbbbbbaaaa'
DECLARE @chvSearchString VARCHAR(50)
SELECT @chvSearchString = 'ab'
SELECT LEN(@chvString) AS StringLength,
LEN(@chvSearchString) AS SearchForStringLength,(LEN(@chvString)-
(LEN(REPLACE(@chvString,@chvSearchString,''))))/LEN(@chvSearchString) AS HowManyOccurances

Do Not Drop And Create Indexes On Your Tables

When you do this the nonclustered indexes are dropped and recreated twice, once when you drop the clustered index and then again when you create the clustered index.

Use the DROP_EXISTING clause of the CREATE INDEX statement, this recreates the clustered indexes in one atomic step, avoiding recreating the nonclustered indexes since the clustered index key values used by the row locators remain the same.

Here is an example:

CREATE UNIQUE CLUSTERED INDEX pkmyIndex ON MyTable(MyColumn)
WITH DROP_EXISTING

Which Service Pack Is Installed On My SQL Server

To get the answer to this question run the code below

SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion,
SERVERPROPERTY( 'ProductLevel') AS ServicePack

There are 3 possible values for ProductLevel

'RTM' = shipping version.
'SPn' = service pack version
'Bn' = beta version.

List All The Indexes In My Database

The query below will list all the indexes on all user tables in the database

SELECT OBJECT_NAME ( si.id ) AS TableName ,
CASE indid WHEN 1 THEN 'Clustered'
ELSE 'NonClustered'
END TypeOfIndex,
si.[name] AS IndexName
FROM sysindexes si
JOIN sysobjects so ON si.id =so.id
WHERE xtype ='U'
AND indid < 255
ORDER BY TableName,indid

Concatenate Data Into One Comma Delimited String

To concatenate data from several rows you can use the code below
I have declared the variable as varchar 1000, adjust the size if you think the size doesn't fit your needs

USE pubs

DECLARE @chvCommaDelimitedString VARCHAR(1000)

SELECT @chvCommaDelimitedString = COALESCE(@chvCommaDelimitedString + ',', '') + REPLACE(au_lname,'''','''''')
FROM dbo.authors

SELECT @chvCommaDelimitedString

Find Out Server Roles For a SQL Server Login

This is a quick way to find out what roles a user has on the SQL Server
Just change the value of @chvLoginName to the login you want

DECLARE @chvLoginName VARCHAR(50)
SELECT @chvLoginName='BUILTIN\Administrators'

SELECT [name],sysadmin,securityadmin,serveradmin,
setupadmin,processadmin,diskadmin,
dbcreator,bulkadmin,loginname
FROM master..syslogins
WHERE loginname =@chvLoginName

-------------------------------------------------------------------------------
The query below returns all logins whose role is a certain role.
The available roles are:

sysadmin
Can perform any activity in SQL Server.

serveradmin
Can set serverwide configuration options, shut down the server.

setupadmin
Can manage linked servers and startup procedures.

securityadmin
Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.

processadmin
Can manage processes running in SQL Server.

dbcreator
Can create, alter, and drop databases.

diskadmin
Can manage disk files.

bulkadmin
Can execute BULK INSERT statements.

SELECT [name],sysadmin,bulkadmin
FROM master..syslogins
WHERE sysadmin =1 or bulkadmin =1

How to find all the tables and views in a database

Use the code below

USE PUBS
GO

--Get All Tables
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

--Get All Views
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
GO

That's all, plain and simple

Split a comma delimited string fast!

-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO

--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO

--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO

--order the string
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO

--Get distinct values
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2'
SELECT DISTINCT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO

Fast Date Ranges Without Loops In SQL Server 2000

The trick to create date ranges without loops is to use a pivot table.
How does this work? Run the code below and you will see, only create the pivot table once and run all the other code seperately

-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END
GO

--Last 10 years from today
SELECT DATEADD(yy,-numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10

--Next 10 years from today
SELECT DATEADD(yy,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10

--Next 100 months from today
SELECT DATEADD(mm,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100

-- next 100 weeks from 2000-01-01
DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2000-01-01 00:00:00.000'
SELECT DATEADD(wk,numberID,@dtmDate)
FROM dbo.NumberPivot
WHERE NumberID < 100

-- next 100 quarters from today
SELECT DATEADD(qq,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100

That's it, come back tomorrow and I will show you how to use the Pivot table to split strings

Put Tables Into Memory

If you have lookup tables (or other small tables) that are frequently accessed and you don’t want to reduce I/O use the command DBCC PINTABLE. What this does is it keeps the table in the data cache all the time so that you reduce I/O which in turn will boost SQL Server performance
Once you pin a table it is not in memory until it’s first requested and then only the data pages requested are in memory not the whole table

How to pin a table?

To pin a table use the script below

DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC PINTABLE (@intDBID, @intTableID)


Be careful not to pin large tables since they will stay in memory and SQL Server will have less memory available for other task
To unpin a table use the same script but replace PINTABLE with UNPINTABLE (see below)

DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC UNPINTABLE (@intDBID, @intTableID)

Test it out of you staging/development environment first before doing this on a production box

SQL Server MVP's

Here is a link to a list of all the MS SQL Server MVP's
All of them have website links next to their name
Now you can see who all those people answering your questions in the newsgroups are

http://www.mvps.org/links.html#SqlServer

Query Analyzer Trick

In Query Analyzer you can save a lot of time by using this trick instead of typing all the column names of a table
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window

Date formatting in SQL Server

According to Joe Celko this should always happen on the client side, but in case you ever need it (for example in DTS when you have to output to a file) here is the SQL code.

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,112),112,'yymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'