Microsoft Transact-SQL: The Definitive Guide is coming off the presses shortly

subtitled: How to rewrite bad t-sql code...

Here's the link:

http://www.jbpub.com/catalog/9780763784164/

If I get a discount code, I'll put it here...

This is a great book for folks looking to teach application developers how to rewrite their code more efficiently; you should buy a case of these to hand out to your development teams :-)

Jeff

Load a 1Tb database in 30 minutes!

Some links deserver their own blog post. This one was passed on to me by a client, it's teriffic, you should read it immediately.

http://technet.microsoft.com/en-us/library/dd537533.aspx

Concurrency issues during massive updates / archiving

Mass updates / mass deltes often cause contention issue with locking / blocking. One technique I suggest to my students is to update / delete parts of the table at a time, where feasible, so as to avoid embarassing issues like table locks.

The following code could be improved, but it has the right general idea: For each involved table, delete 1000 rows at a time until we're done.

CREATE PROCEDURE [dbo].[DeleteSnapshotData]
@I_lNumDays int
AS

SET NOCOUNT ON
-- get cutoff date string
declare @tablename varchar(50)
declare @dtCutoff smalldatetime
declare @strCutoff varchar(50)
declare @command nvarchar(2000)
set @dtCutoff = getdate() - @I_lNumDays
set @strCutoff = cast(year(@dtCutoff) as varchar) + '-' + right('00' + cast(month(@dtCutoff) as varchar), 2) + '-' + right('00' + cast(day(@dtCutoff) as varchar), 2)

-- build temp table
set rowcount 0
select sysobjects.name into #snapshots from sysobjects join syscolumns on sysobjects.id = syscolumns.id where sysobjects.name like '%snapshot%' and (OBJECTPROPERTY(sysobjects.id, N'IsTable') = 1) and (syscolumns.name = 'tdate')

set rowcount 1
select @tablename = name from #snapshots

while @@rowcount > 0
begin

delete from #snapshots where name = @tablename
if ((@tablename != 'Closing_Charges_Snapshot') and (@tablename != 'Closing_Credits_Snapshot'))
BEGIN
print 'deleting expired data from ' + @tablename + '...'

SET ROWCOUNT 1000
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
SET NOCOUNT OFF
select @command = N'delete from ' + @tablename + ' where tdate < ''' + @strCutoff + ''''
exec (@command)

IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END

COMMIT TRANSACTION
END
END
SET NOCOUNT ON
set rowcount 1
select @tablename = name from #snapshots
end
drop table #snapshots

Common Table Expressions (CTE) in SQL Server 2005 & 2008

From 2008 Books Online:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

The really cool part of this is the self-referencing part.

I'm not going to try to teach you CTE here. Honestly, it's been done too well already. Here's a link to a favorite:

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

It does reference a few other documents (like I did with books online, above), again so as not to reinvent the wheel.

I wanted to share a couple of limitations in CTEs that I encountered this past week.

Requirement: Unlimited self-referencing / recursion in object definitions.

With the self-referencing objects, the problem that you have is the circular reference. With a CTE, you can limit this by specifying a recursion limit (i.e. "circle no more than X times").

Is this a good idea? Does it make sense to circle multiple passes through a set of objects once you've already collected all of the objects?

We ended up (in SQL 2005) using a temp table implementation (well, really, memory table not temp table). The reason: we needed to be able to say, "grab the next set of child relationships, but ONLY if we haven't already grabbed those relationships." The CTE limitation in sql 2005 doesn't allow mulitple recursive references.

Imagine my delight when I read the above: "...can be referenced multiple times in the same query,,," ... this in and of itself is enough to talk this particular client into going to SQL Server 2008.

Happy CTEs...