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)'

Average length of all rows in a table

Average length of all rows in a table

Sometimes you want to know how much space the average row is in your table
Instead of writing tedious AVG(LEN(field1) + LEN(field2) + LEN(field3)) from tableA
You could use the INFORMATION_SCHEMA.COLUMNS system view and create a stored procedure that loops through the table, creates the script and executes/prints this script

Well lucky for you I have created this stored procedure and the code is posted below
Before anyone says what if you just run
dbcc showcontig (‘TableName’) with tableresults you will get a field AverageRecordSize use that instead, well that’s fine but what if I only want 3 out of 4 fields? This stored procedure will let me do that; I will run it with 0 instead of 1 for the last parameter.
Once the procedure runs I copy the SQL statement from the messages tab in Query Analyzer take out the fields I don’t need and we are done

This is how you run it
@chvTableName this is your table name
@bitExecutionType 1= select,0=print


exec AverageColumnLength 'authors',1 --select
exec AverageColumnLength 'authors',0 --print

--------------------------------------------------
create procedure dbo.AverageColumnLength
@chvTableName varchar(255),
@bitExecutionType bit
as
set nocount on

create table #TempColumnNames (
tID int identity not null,ColumnName varchar(50) not null)

insert into #TempColumnNames
select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @chvTableName

declare @chvSQLString varchar(2000)
select @chvSQLString = 'SELECT AVG('

declare @chvColumnName varchar(50)

declare @intLoopID int,@intLoopMaxID int
select @intLoopID = 1, @intLoopMaxID =Max(tID) from #TempColumnNames

while @intLoopID <= @intLoopMaxID
begin
select @chvColumnName =ColumnName from #TempColumnNames where tID =@intLoopID
select @chvSQLString =@chvSQLString + 'LEN(' + @chvColumnName + ')+' + char(10)
select @intLoopID = @intLoopID + 1
end


select @chvSQLString =left(@chvSQLString,len(@chvSQLString) -2) + ') ' + char(10)
select @chvSQLString =@chvSQLString + ' AS AverageColumnLength FROM ' + @chvTableName

if @bitExecutionType =0
print @chvSQLString
else
exec( @chvSQLString)--


drop table #TempColumnNames

set nocount off

How to return random results from SQL SERVER

Sometimes you want to display 4 random articles/pics/you name it on your web page and you don’t want to write a lot of code.
SQL SERVER 2000 has a neat little function called NEWID() that can help you out.
Run the code below from Query Analyzer and keep hitting F5.
You will see that the results will be different every time.

USE pubs

SELECT top 4 * FROM dbo.authors
ORDER BY NEWID()

Note: this does not work in SQL SERVER 7

@@IDENTITY returns wrong identity field

When using @@IDENTITY I get the wrong results
I have seen this problem posted many times on newsgroups and It actually happened to a co-worker not too long ago
This ‘problem’ can occur when you do an insert into Table A and there is at trigger defined that fires when the insert happens and this trigger does an insert into table B.
What @@IDENTITY returns is actually the identity from Table B
In order to get the identity back from table A you should use SCOPE_IDENTITY() instead