A common pattern in SQL Server development is the use of “staging tables”. These are tables, usually temporary, that contain rows that are manipulated in several ways before being inserted into the “real” table, usually at the end of the entire process and in a transaction.

A very common way to create a staging table is to use SELECT INTO to create a temp table that is an exact replica of the target table, like so:

select top 0 * 
into #TargetTable 
from SourceTable

From there, you can insert new rows into the staging table, manipulate them, and eventually move them into the target table at one time.

However….

If the SourceTable used above happens to have non-nullable columns in it (and corresponding default constraints), the non-nullable attribute of the column will be replicated into the staging table, but the defaults won’t.

This can end up forcing your code to explicitly specify default values for those non-nullable columns. Not a particularly difficult task, but tedious, and error prone. Additionally, it introduces duplicate logic in your SQL (you’ve already set those default values in the definition of SourceTable, so replicating them in your stored proc code is not the greatest idea).

What you need is a way to copy the default constraints to the new staging table after you’ve created it.

And that’s exactly what CopyDefaults is for:

/****************************************************************

Used to copy defaults from a specified source table to a target 
table, usually a temp table created to replicate a particular
table, as in:

    select top 0 *
    into #NewTempTable
    from SourceTable

If the source table contains non-nullable columns that have defaults
the above will replicate the null-nullable attribute of the columns
but won't replicate the defaults, so you won't be able to insert
records into the new temp table without specifying values for all the
non-nullable columns.

Usage:

    exec CopyDefault 'SourceTable', 'TargetTable'
    
You may specify a full name for either source or target

    exec CopyDefaults 'db.dbo.SourceTable', 'otherdb.dbo.TargetTable'
 
****************************************************************/
ALTER PROCEDURE CopyDefaults
    @SourceTable varchar(256), 
    @TargetTable varchar(256)

AS

BEGIN


SET NOCOUNT ON

-- Parse the Database names and provide reasonable
-- defaults
declare @SourceDB varchar(128)
declare @TargetDB varchar(128)
select @SourceDB = parsename(@SourceTable, 3)
select @TargetDB = parsename(@TargetTable, 3)
if @SourceDB is null and left(@SourceTable, 1) = '#' begin
    select @SourceDB = 'tempdb' 
    select @SourceTable = 'tempdb..' + @SourceTable
end    
if @TargetDB is null and left(@TargetTable, 1) = '#' select @TargetDB = 'tempdb' 
if @SourceDB is null select @SourceDB = db_name()
if @TargetDB is null select @TargetDB = db_name()


declare @sql varchar(max)
select @sql=''

-- use a double indirected dynamic sql snippet to
-- create the defaults for the target table, based on those
-- from the Source table.
-- NOTE: the target should NOT already have any of the defaults 
-- established!
select @sql = 
'
declare @sql varchar(max)
select @sql=''''
select @sql=@sql + 
    ''IF  EXISTS (SELECT * FROM ' + @TargetDB + '.dbo.sysobjects WHERE name = '''''' + dc.name + ''xx'''' AND type = ''''D'''')
        ALTER TABLE ' + @TargetTable + ' DROP CONSTRAINT '' + dc.name + ''xx
    ALTER TABLE ' + @TargetTable + 
    ' ADD CONSTRAINT '' + dc.name + ''xx DEFAULT '' +
    replace(replace(definition, ''(('', ''(''), ''))'','')'') 
    + '' FOR '' + c.name + char(13) + char(10) 
FROM ' + @SourceDB + '.sys.default_constraints dc
INNER JOIN ' + @SourceDB + '.sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
where dc.type = ''D'' and OBJECT_ID(''' + @SourceTable + ''') = dc.parent_object_id
exec (@sql)' 

-- and execute the SQL 
exec (@sql)

END
GO

Usage is simple. Just create your Staging Table as described above:

select top 0 * 
into #TargetTable 
from SourceTable

Then execute CopyDefaults:

exec CopyDefaults 'SourceTable', '#TargetTable'

Now your defaults should be in place, so newly inserted rows in #TargetTable will automatically get the appropriate default values.

Finally, here’s a test script to exercise and verify the functionality.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON


/****************************************************************
Create a test Source Table 
****************************************************************/
if OBJECT_ID('tempdb..#SourceTable') is not null drop table #SourceTable
CREATE TABLE #SourceTable(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [Bit1] [bit] NOT NULL,
    [Bit2] [bit] NOT NULL,
    [Address] [varchar](50) NOT NULL, CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED 
    (ID ASC)
)

ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Bit1  DEFAULT 1 FOR Bit1
ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Bit2  DEFAULT 0 FOR Bit2
ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Address  DEFAULT '' FOR Address



/****************************************************************
Insert a sample record
****************************************************************/
insert into #SourceTable (Name) values ('TestName')


/****************************************************************
Select to prove the defaults were filled in
****************************************************************/
select * from #SourceTable


/****************************************************************
Replicate the source table
****************************************************************/
if OBJECT_ID('tempdb..#TargetTable') is not null drop table #TargetTable
select top 0 * into #TargetTable from #SourceTable


/****************************************************************
Try to insert and it will fail
****************************************************************/
BEGIN TRY
    insert into #TargetTable (Name) values ('TestName')
    print 'Insert into target succeeded (it shouldn''t have)'
END TRY
BEGIN CATCH
    print 'Insert into target failed (which is correct)'
END CATCH



/****************************************************************
Copy the default constraints over
****************************************************************/
exec CopyDefaults '#SourceTable', '#TargetTable'



/****************************************************************
And retry to insert and it should succeed now
****************************************************************/
BEGIN TRY
    insert into #TargetTable (Name) values ('TestName')
    print 'Insert into target succeeded (it should)'
END TRY
BEGIN CATCH
    print 'Insert into target failed (which should not have happened)'
END CATCH
select * from #TargetTable

Had another interesting SQL challenge come up today. Basically, I had two tables. I needed to delete all the records from Table A that did not have any related records in Table B.

Simple enough. The existing code was like so:

delete x
from #tmp x
left join #tmp as y on x.ID=y.ID and y.Status<>'dismissed'
where x.Status='dismissed'
    and y.ID is null

Now, in this case, Table A and Table B happen to be the same table, just interrelated, but I had similar situations with distinct tables.

Since the table in question was a temporary table, there weren’t any indexes defined, so that would account for some of the slowdown, but this query was taking forever to finish.

Just looking at the query, though, it struck me that the LEFT JOIN was likely doing way more work that necessary, which can be confirmed via a quick check of the execution plan.

The problem is that the join has to go ahead and connect all the related records that are actually related, even though, in this case, those are the very records we don’t care anything about (notice the y.Person is null clause).

I’m guessing that the optimizer can’t or doesn’t realize that we don’t actually care about those rows in this case. Not surprising, but definitely interesting.

So, a quick rewrite is in order. I chose a correlated subquery:

delete x
from #tmp x
where x.Status='dismissed'
    and not exists(
        select top 1 1 from #tmp y where y.ID = x.ID and y.Status<>'dismissed'
    )

Believe it or not, this version executed in under a second against the exact same million+ count rowset. The difference was surprising, and I’m not often surprised anymore by SQL optimization tricks.

Ok, a few things to note:

• The table still doesn’t have any indexes. Things will likely be even faster with some good indexes on the table. But this is a temp table, so you’ll have to weigh the cost of creating the index vs the savings you get for additional queries that might use it while the table exists.
• Since I don’t actually care about any of the related records, I select the scalar value “1” for the result of the subquery. Technically, this is not actually necessary since the NOT EXISTS clause is used, which usually causes the SQL optimizer to automatically forgo returning any actual data. I just like to be explicit about things.
• Further, since I only want to know whether any related records exist or not, I can select only the TOP 1 related record, and get it or nothing. This allows some additional SQL optimizations that center around use of the TOP clause, which is a good thing.
• And finally, use of the NOT EXISTS clause allows even more internal SQL optimizations.

Bottom line

Definitely keep correlated subqueries in your SQL toolbelt. This one example shaved more than 20 minutes off the execution of a particular stored proc.

Are correlated subqueries always the answer? Oh, hell no. Often, they’re the worst approach.

Are there other ways to do this? Certainly. A CROSS JOIN is the first thing that comes to mind. Establishing indexes on the temp table would certainly help as well.

But as fast as this solution is, I didn’t take things any farther.

I generally can appreciate what Joe Celko brings to the table in terms of SQL programming and theory, but a recent article of his had me scratching my head, even considering his sometimes Ivory Tower approaches.

The article is at Intelligent Enterprise here.

What caught my eye was the following paragraph. He’s discussing his own classification system of keys in databases and one of the types is described thusly:

3. An “exposed physical locator” is not based on attributes in the data model and is exposed to the user. There is no reasonable way to predict it or verify it, since it usually comes from the physical state of the hardware at the time of data insertion. The system obtains a value thru some physical process in the storage hardware totally unrelated to the logical data model ” for example, IDENTITY columns or other proprietary, non-relational auto-numbering devices.

Technically, these are not really keys at all, since they are attributes of the physical storage and are not even part of the logical data model. But they are handy for lazy, non-RDBMS programmers who do not want to research or think! This is the worst way to program in SQL

So, to paraphrase, he’s saying that if you use IDENTITY columns in your database schemas, you’re “lazy” and “do not want to research or think”

Uhuh.

So how about it, you lazy, slovenly, heathen SQL programmers out there that dare to use Identity columns?

And in case it’s not obvious, I can’t disagree more with Celko on this point<g>