While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll discuss the details more in the fragmentation series I'm starting). As part of the demo, we wanted to change the column default for the leading key of a table from NEWID() to NEWSEQUENTIALID() – problem was that none of us could remember the exact syntax, so we worked it out together. I thought it would make an interesting post, so here it is.
First off, here's my table with a poor clustered index key:
CREATE TABLE BadKeyTable (
c1 UNIQUEIDENTIFIER DEFAULT NEWID () ROWGUIDCOL,
c2 SMALLDATETIME DEFAULT GETDATE (),
c3 CHAR (400) DEFAULT 'a',
c4 VARCHAR(MAX) DEFAULT 'b');
GO
CREATE CLUSTERED INDEX BadKeyTable_CL ON BadKeyTable (c1);
GOINSERT INTO BadKeyTable DEFAULT VALUES;
GO
(And you'll notice that I've given up doing nice colors in the T-SQL I post – it's too time consuming). The default we're interested in is in bold above. To change the default, we first need to find the constraint name so we can drop it. There are two queries you can use:
SELECT [name] FROM sys.objects
WHERE [parent_object_id] = OBJECT_ID ('BadKeyTable');
GO
or
SELECT [name] FROM sys.default_constraints
WHERE [parent_object_id] = OBJECT_ID ('BadKeyTable');
GO
The second is obviously the more supported way, as the first will return the names of all sub-objects of this table – all constraints, and all internal tables, such as XML indexes. The second query returns:
name
——————————-
DF__BadKeyTable__c1__7C8480AE
DF__BadKeyTable__c2__7D78A4E7
DF__BadKeyTable__c3__7E6CC920
DF__BadKeyTable__c4__7F60ED59
The constraint we're interested in is the one for the first column – DF__BadKeyTable__c1__7C8480AE. Now we need to drop the constraint and then add the new one as there's no way to simply alter the constraint in-place. We do that using:
ALTER TABLE BadKeyTable DROP CONSTRAINT DF__BadKeyTable__c1__7C8480AE;
GO
ALTER TABLE BadKeyTable ADD CONSTRAINT DF__BadKeyTable__c1
DEFAULT NEWSEQUENTIALID() FOR c1;
GO
And we're done.
3 thoughts on “How to change a default constraint”
Hi Paul
Here is my quick and dirty solution for renaming constraints.
What do you think about it?
declare @sql varchar(8000)
set @sql = ”
select @sql = @sql + ‘exec sp_rename ”’ + d.name + ”’, ”DF_’ + o.name + ‘_’ + c.name + ”’,”object”’
from sysobjects o
inner join dbo.syscolumns c on o.id = c.id
inner join dbo.sysobjects d on c.cdefault = d.id
where d.name <> ‘DF_’ + o.name + ‘_’ + c.name
select @sql
exec (@sql)
For SQL 2000, looks like that will work.
works as well in 2005 and 2008 :-)