Using a ‘zero’ row in SQL Server tables

At first I rebelled against the very idea of doing this but I was eventually convinced and now all my tables have zero rows. This doesn’t mean there are no rows in the tables, this means that whenever I create a new table I insert a special row with a primary key of zero and default values in the other fields. So my table create scripts look something like this:

create table dbo.tblmyvalue (
myvalueid_pk int identity (0, 1) not null ,
strmyvalue nvarchar (200) COLLATE SQL_Latin1_General_CP1_CS_AS not null constraint def_tblmyvalue_strmyvalue default ‘~na~’ ,
constraint tblmyvalue_myvalueid_pk primary key clustered(myvalueid_pk)
) on primary
go
set identity_insert tblmyvalue on
insert into tblmyvalue (myvalueid_pk, strmyvalue) values (0, ‘~na~’)
set identity_insert tblmyvalue off

Why?
The simple answer is “it’s easy”. Whenever I have another table refer to this new table, I can always have zero values in the foreign keys for it. I don’t have to worry about relational integrity: I can use 0 since there will always be a zero row that can be referred to. This may seem the shortcut of someone who likes to avoid referential integrity but it actually has many more benefits and efficiencies than a precisely crafted database that takes forever to set up without this in place. In fact, having lived with it for a while I consider this approach very ‘relational’, perhaps even ‘reverential’ (praise Cobb) since everything is linked! Here is a list of some of these benefits:

  1. all data rows from 1 onward have meaningful data that isn’t there merely as a placeholder for another table
  2. a table with its own ordering system (an internal integer field with a foreign key constraint pointing to its own primary key) can be easily created and maintained i.e.: those that point to zero are the first in the list and those that point to it, follow
  3. default constraints on a table field can be used for error trapping instead of imposing referential integrity; all inserts simply require 0 for foreign key fields
  4. even tables that aren’t pointed to by foreign keys may eventually be and Holy Relational Tables, Batman, me and my zero rows will be ready

I modified a script (since updated) by Tatsu I found on SQLServerCentral to automate the data entry of the zero rows. I find it also useful to use if you forget the fields in a particular table. My version follows:

/*
**modified 2005/07/26 for SQL Server 2005 by Terry Grignon
**change row “set @TableName = ‘tblyourtable'” to whatever table you want to ‘zero’
Credits:
Tatsu & Bob Wiechman
*/
declare @TableName sysname
declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @DebugMode bit
declare @ColList nvarchar(4000)
declare @ValList nvarchar(4000)
declare @NewList nvarchar(4000)
declare @SQL1 nvarchar(4000)
declare @SQL2 nchar(10)
declare @SQL3 nchar(1000)

set @TableName = ‘tblyourtable’
set @IdentityInsert = 1 — set to 0 if you DON’T want IDENTITY_INSERT turned on
set @DebugMode = 1 — set to 1 if you only want a script
set @ColList = ”
set @ValList = ”
set @SQL1 = ‘insert into ‘ + @TableName + ‘ (‘
set @SQL2 = ‘) values (‘
set @SQL3 = ‘)’
–set @SQL3 = ‘)”, ”””null”””, ”null”) from ‘ + @TableName
–print @sql1 + @sql2 + ‘)’
print ‘cut below’

declare csrColumns cursor local fast_forward for
select c.name, c.system_type_id –xtype
from sys.columns c –select * from sys.columns where name = ‘vbarpassword’
inner join sys.objects o –select * from sys.objects
on o.object_id = c.object_id
where o.name = @TableName
and o.type in (‘U’, ‘S’)
order by Column_id

open csrColumns
fetch next from csrColumns into @ColName, @ColType

while @@fetch_status = 0
begin
— uncomment line below to see the numeric types
— print @ColName + ‘ ‘ + convert (varchar (10), @ColType)
set @ColList = @ColList + ‘ ‘ + @ColName
if @ColType in (173, 62, 60, 108, 59, 122)
set @ValList = @ValList + ‘~’
if @ColType in (165)
set @ValList = @ValList + ‘convert(varbinary,0)~’
— set @ValList = @ValList + ‘ ”+convert(varchar(200),’ + @ColName + ‘)+”’
— uid and string types
else if @ColType in (175, 239, 48, 52, 56, 36, 167)
set @ValList = @ValList + ‘0~’
else if @ColType in (106)
set @ValList = @ValList + ‘0.0~’
else if @ColType in (36, 61, 104, 231)
set @ValList = @ValList + ‘default~’
— set @ValList = @ValList + ‘ ”””+isnull(‘ + @ColName + ‘,”null”)+”””’
— dates (does not support nulls yet)
else if @ColType in (58)
set @ValList = @ValList + ‘~’
— set @ValList = @ValList + ‘ ”””+convert(varchar(200),’ + @ColName + ‘)+”””’
fetch next from csrColumns into @ColName, @ColType
end

close csrColumns
deallocate csrColumns

set @ValList =@ValList + ‘~’

set @ColList = replace(ltrim(@ColList), ‘ ‘, ‘, ‘)
set @ValList = replace(ltrim(@ValList), ‘~~’, ”)
set @ValList = replace(ltrim(@ValList), ‘~’, ‘, ‘)

if @IdentityInsert = 1
print ‘set identity_insert ‘ + @TableName + ‘ on’

if @DebugMode = 1
Begin
set @NewList = @SQL1 + @ColList + @SQL2 + @ValList + @SQL3
set @NewList = rtrim(@NewList)
print @NewList
end
else
exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3)

if @IdentityInsert = 1
print ‘set identity_insert ‘ + @TableName + ‘ off’

print ‘cut before’

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s