THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Piotr Rodak

if datepart(dw, getdate()) in (6, 7)
    use pubs;



DEFAULT constraints and sp_bindefault

When you deal with old databases, with origins somewhere around second half of nineteen nineties, you can expect all kinds of oddities, like funny naming conventions, (lack of) referential integrity, absence of clustered indexes and things like rules and defaults created separately from tables DDL.

I just happen to work with such database. I see many tables which some time ago were migrated from a Sybase system that have their DEFAULT constraints defined with sp_bindefault stored procedure. You don’t have to memorize exact syntax of the invocation of this procedure because it is marked as obsolete and should not be used anymore:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you create default definitions by using the DEFAULT keyword of the ALTER TABLE or CREATE TABLE statements instead. For more information, see Creating and Modifying DEFAULT Definitions.

Now, when I read the documentation, the sp_bindefault has some interesting capability. If you create default and user defined type (alias), and you bind this default to the type with sp_bindefault, you can inadvertently affect all columns of that type in all tables in the database if they don’t have any default or are using existing default of the alias type:

   1: create table dbo.t1
   2: (
   3: col1 int not null,
   4: col2 int not null constraint DF_t1col2 default(0)
   5: )
   6: go
   7: ---create default for the column
   8: create default dbo.DFValue_1 as 1
   9: go
  10: ---bind default to the column
  11: exec sp_bindefault 'dbo.DFValue_1', 'dbo.t1.col1'
  12: go
  13: ---create udt
  14: create type ssn
  15: from varchar(15) not null
  16: go
  17: --create default - empty string
  18: create default dbo.DF_UDTEmpty as ''
  19: go
  20: --bind default to the udt
  21: exec sp_bindefault 'dbo.DF_UDTEmpty', 'dbo.ssn'
  22: go
  23: --add new column to the table
  24: alter table dbo.t1 add secnumber1 ssn
  25: go
  26: ---insert into the table and select results
  27: insert dbo.t1(col1, col2, secnumber1) default values
  28:  
  29: ---secnumber1 is empty string
  30: select * from dbo.t1
  31: go
  32: ---create second table
  33: create table dbo.t2
  34: (
  35:     col1 int constraint DF_t2_col1 default(10),
  36:     secnumber ssn
  37: )
  38: go
  39: create default dbo.DF_SSNNotEmpty as '<unknown ssn>'
  40: go
  41: ---note here - no @futureonly param provided
  42: exec sp_bindefault 'dbo.DF_SSNNotEmpty', 'dbo.ssn'
  43: go
  44: --insert into t1 and t2
  45: insert dbo.t1(col1, col2, secnumber1) default values
  46: insert dbo.t2(col1, secnumber) default values
  47: go
  48: ---check contents of the tables
  49: select * from dbo.t1 --what?
  50: select * from dbo.t2
  51: go

If you run the above script you will see that the default value of the column secnumber1 in table dbo.t1 has changed to ‘<unknown ssn>’! You can really hurt yourself if you make a mistake and omit the parameter @futureonly in the call to sp_bindefault. If you use @futureonly parameter, the above behavior will not occur. It’s pretty easy to make changes you don’t know about until user calls you at 3 am on Sunday.

It is good idea to change tables to use standard column DEFAULT constraints. This should be of course made with care, probably only when you change the tables anyway. But, if you were curious how many tables you have in your database that use the DEFAULT objects, you can run this script:

   1: ---all default objects with names of tables they are assigned to.
   2: select b.default_object_id, a.name default_object_name, 
   3: b.object_id table_id, object_schema_name(b.object_id) table_schema, 
   4: object_name(b.object_id) table_name, b.name column_name, b.column_id
   5: from sys.objects a inner join sys.all_columns b
   6: on a.object_id = b.default_object_id
   7: where 1=1
   8: and a.object_id not in (select object_id from sys.default_constraints)

Full testing script is attached to the post as usual, enjoy :).

Published Tuesday, September 21, 2010 12:22 AM by Piotr Rodak

Attachment(s): default testing.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Naomi said:

Do you know how can I script such constraints for an existing table? SSMS CREATE script doesn't script these constraints.

Thanks a lot in advance

November 21, 2011 5:50 PM
 

Piotr Rodak said:

Hi Naomi,

You can make SSMS to script what you want. Go to Tools/Options, then select SQL Server Object Explorer/Scripting. I have all constraints, indexes and triggers enabled so I get full picture of what table is when I script it. I most often script the table to investigate its structure rather than open columns, indexes, triggers nodes in object explorer.

Regards

Piotr

November 21, 2011 6:35 PM
 

Naomi said:

Hi Piotr,

You're right, I did get them scripted - I set to true practically every option including the very last one - although it doesn't say what is it for.

Thanks.

November 21, 2011 8:27 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement