"Give a man a fish, feed him for a day; teach a man to fish, feed him for a lifetime; teach a man to teach others to fish, feed a generation" - Author unknown.

In part I of this series we learned all about Alias Data Types(ADT). It looks like it is very simple to manage ADTs, but complexity comes when an ADT is linked to column that is tied with a constraint, a rule, or a default. In this case changes to the ADT become complicated.

In this article, we are going to review the following:

1. Create the dbo.tblBillPlanVersion table
2. Create the first ADT adt_PlanId and link it to the PlanId column
3. Create a unique constraint on PlanId column
4. Create a second ADT adt_BigPlanId and try to replace the adt_PlanId with adt_BigPlanId
5. Drop the unique constraint and replace adt_PlanId with adt_BigPlanId.
6. Restore the unique constraint on PlanId column.

First we create dbo.tblBillPlanVersion table

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblBillPlanVersion')
              and ObjectProperty(id, N'IsUserTable') = 1)
    drop table dbo.tblBillPlanVersion
create table dbo.tblBillPlanVersion (
PlanVersionId    char(12)     not null primary key nonclustered,
PlanId           int          not null,
PlanVersionDescription varchar(250) not null,
PlanVersionStatus      char(1)      not null,
CreatedDate            timestamp    not null )

Next we want to create the first ADT adt_PlanId and link it to PlanId column

if exists( select * from systypes where [name] = 'adt_PlanId')
     exec sp_droptype 'adt_PlanId'
exec sp_addtype
@typename = 'adt_PlanId'
,@phystype = 'int'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'
alter table dbo.tblBillPlanVersion
alter column PlanId adt_PlanId

Step 3 is to create the unique constraint on PlanId column

alter table dbo.tblBillPlanVersion
add constraint UC_tblBillPlanVersion_PlanId unique clustered (PlanId)

Step 4 - Create the second ADT adt_BigPlanId and try to replace adt_PlanId with adt_BigPlanId

if exists( select * from systypes where [name] = 'adt_BigPlanId')
     exec sp_droptype 'adt_BigPlanId'
exec sp_addtype
@typename = 'adt_BigPlanId'
,@phystype = 'bigint'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'
alter table dbo.tblBillPlanVersion
alter column PlanId adt_BigPlanId

Here is a message that we receive:

Server: Msg 5074, Level 16, State 8, Line 1
The object 'UC_tblBillPlanVersion_PlanId' is dependent on column 'PlanId'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN PlanId failed because one or more objects access 
this column.

We will get the same message if we try to change data type of PlanId column with a system data type.

alter table dbo.tblBillPlanVersion
alter column PlanId bigint

Step 5 is to drop the unique constraint and replace adt_PlanId with adt_BigPlanId. First, we drop unique constraint:

alter table dbo.tblBillPlanVersion
drop constraint UC_tblBillPlanVersion_PlanId

Second, we replace adt_PlanId with adt_BigPlanId

alter table dbo.tblBillPlanVersion
alter column PlanId adt_BigPlanId

The last step is to restore the unique constraint on PlanId column. We replaced existing ADT with new one and now we will restore Primary Key Constraint

alter table dbo.tblBillPlanVersion
add constraint UC_tblBillPlanVersion_PlanId unique clustered (PlanId)

We did it manually for one table with one ADT. But what if we have adt_PlanId linked to multiple tables? Can we automate this process? If we want to automate this process we need to know how to reverse engineer any constraints. Based on our experience with example above and many others(that we are going to review in this article) we will develop a stored procedure to change or drop ADT for multiple tables.

If the ADT is linked to a table column that is not tied to a constraint, the algorithm of changing the ADT will be the same as we developed in part I of this article:

1. alter table column with corresponding system data type
2. drop ADT
3. create ADT according to new requirements
4. link new ADT to a column in a table

If the ADT is linked to a table column that is tied to a constraint(s), the algorithm for changing the ADT should be:

1. Reverse engineer all table constraints that are tied with ADT and save their source code
2. drop all table constraints identified in step 1
3. alter table column with corresponding system data type
4. drop ADT
5. create ADT according to new requirements
6. link new ADT to a column in a table
7. recreate all dropped constraints

We will concentrate our efforts on analyzing and reverse engineering constraints. Here is a list of table constraints available in Microsoft SQL Server 2000.

1. PRIMARY KEY constraints.(A table can have only one PRIMARY KEY constraint.)
2. FOREIGN KEY constraints
3. UNIQUE constraints
4. CHECK constraints
5. DEFAULT definitions
6. Nullability

Now we will walk through each step of the process.

1.1 Create a PRIMARY KEY constraint when creating a table. In this example MS SQL Server names the primary key constraint

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblTestEmployee')
              and ObjectProperty(id,N'IsUserTable') = 1)
    drop table dbo.tblTestEmployee
GO
create table dbo.tblTestEmployee (
         EmployeeId        bigint       NOT NULL Primary key clustered
        ,EmployeeFirstName varchar(100) NOT NULL
        ,EmployeeLastName  varchar(100) NOT NULL
        ,CreatedDate       datetime     NOT NULL
)

Here is how to view the information about created primary key constraint:

select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where Table_Name = 'tblTestEmployee'
and Constraint_Type = 'PRIMARY KEY'

In 1.2 in this example, we give a name to the primary key constraint. If we follow some standards, we assume that all primary key constraints easier to recognize and manage.

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblTestEmployee')
              and ObjectProperty(id,N'IsUserTable') = 1)
    drop table dbo.tblTestEmployee
GO
create table dbo.tblTestEmployee (
         EmployeeId        bigint       NOT NULL
CONSTRAINT PK_tblTestEmployee_EmployeeId PRIMARY KEY NONCLUSTERED
        ,EmployeeFirstName varchar(100) NOT NULL
        ,EmployeeLastName  varchar(100) NOT NULL
        ,CreatedDate       datetime     NOT NULL
)

Here is how to view the information about created primary key constraint:

select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where Table_Name = 'tblTestEmployee'
and Constraint_Type = 'PRIMARY KEY'

What is missing? From the result set we can not tell primary key is clustered or not. Here is a new version of the same query:

select
CONSTRAINT_CATALOG
,CONSTRAINT_SCHEMA
,CONSTRAINT_NAME
,TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,CONSTRAINT_TYPE
-- ,IS_DEFERRABLE       this column always returns NO
-- ,INITIALLY_DEFERRED  this column always returns NO
,case when objectproperty(object_id(CONSTRAINT_NAME), 'CnstIsClustKey') = 0
      then 'Primary key has nonclustered index' else
           'Primary key has clustered index'
  end as PrimaryKeyProperty
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where Table_Name = 'tblTestEmployee'
and Constraint_Type = 'PRIMARY KEY'

1.3 in this example PK is based on two columns

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblTestEmployee')
              and ObjectProperty(id,N'IsUserTable') = 1)
    drop table dbo.tblTestEmployee
GO
create table dbo.tblTestEmployee (
         EmployeeId        bigint       NOT NULL
        ,EmployeeFirstName varchar(100) NOT NULL
        ,EmployeeLastName  varchar(100) NOT NULL
        ,CreatedDate       datetime     NOT NULL
PRIMARY KEY NONCLUSTERED (EmployeeLastName desc,EmployeeFirstName desc)
)

We can view primary key constraint information using system procedure

sp_helpconstraint. Here are two modes we can run this procedure:

sp_helpconstraint  @objname = 'tblTestEmployee', @nomsg = 'nomsg'  
  -- only one result set, we can save it only
  -- in temporary table
sp_helpconstraint  @objname = 'tblTestEmployee', @nomsg = 'msg'    
  -- two results sets
  -- there is no way to save two result sets
  -- from this system stored procedure

1.4 in this example PK is combined from two columns

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblTestEmployee')
              and ObjectProperty(id,N'IsUserTable') = 1)
    drop table dbo.tblTestEmployee
GO
create table dbo.tblTestEmployee (
         EmployeeId        bigint       NOT NULL
        ,EmployeeFirstName varchar(100) NOT NULL
        ,EmployeeLastName  varchar(100) NOT NULL
        ,CreatedDate       datetime     NOT NULL
CONSTRAINT pk_tblTestEmployee_EmpLastName_EmpFirstName
PRIMARY KEY CLUSTERED (EmployeeLastName asc,EmployeeFirstName desc)
WITH  FILLFACTOR = 64
)
go

You can review the constraints with:

sp_helpconstraint  'tblTestEmployee'

What is missing from the output? We added a fillfactor and it is not shown by system stored procedure sp_helpconstraint.

1.5 Add PRIMARY KEY constraint to an existing table, specify fillfactor and key order

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblTestEmployee')
              and ObjectProperty(id,N'IsUserTable') = 1)
    drop table dbo.tblTestEmployee
GO
create table dbo.tblTestEmployee (
         EmployeeId        bigint       NOT NULL
        ,EmployeeFirstName varchar(100) NOT NULL
        ,EmployeeLastName  varchar(100) NOT NULL
        ,CreatedDate       datetime     NOT NULL
)
go
alter table dbo.tblTestEmployee
add constraint PK_tblTestEmployee_EmployeeId primary key nonclustered
(EmployeeId desc ) WITH  FILLFACTOR = 94

1.6 Modifying PRIMARY KEY constraint

From the Help topic 'Creating and Modifying PRIMARY KEY Constraints': Note. To modify a PRIMARY KEY constraint using Transact-SQL or SQL-DMO, you must first delete the existing PRIMARY KEY constraint and then re-create it with the new definition. It is not possible to change the length of a column defined with a PRIMARY KEY constraint.

!!! We are going to change the length of a column defined with a Primary key constraint !!!

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblTestEmployee')
              and ObjectProperty(id,N'IsUserTable') = 1)
    drop table dbo.tblTestEmployee
GO
create table dbo.tblTestEmployee (
         EmployeeId        bigint       NOT NULL
        ,EmployeeFirstName varchar(2)   NOT NULL
        ,EmployeeLastName  varchar(4)   NOT NULL
        ,CreatedDate       datetime     NOT NULL
CONSTRAINT pk_tblTestEmployee_EmpLastName_EmpFirstName
PRIMARY KEY CLUSTERED (EmployeeLastName asc,EmployeeFirstName desc)  WITH  
FILLFACTOR = 94
)

Here are different ways to view information about Primary key constraint

select
CONSTRAINT_CATALOG
,CONSTRAINT_SCHEMA
,CONSTRAINT_NAME
,TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,CONSTRAINT_TYPE
-- ,IS_DEFERRABLE       this column always returns NO
-- ,INITIALLY_DEFERRED  this column always returns NO
,case when objectproperty(object_id(CONSTRAINT_NAME), 'CnstIsClustKey') = 0
      then 'Primary key has nonclustered index' else
           'Primary key has clustered index'
  end as PrimaryKeyProperty
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where Table_Name = 'tblTestEmployee'
and Constraint_Type = 'PRIMARY KEY'
exec sp_helpconstraint  'tblTestEmployee'
exec sp_help 'pk_tblTestEmployee_EmpLastName_EmpFirstName'
exec sp_helpindex @objname = 'tblTestEmployee'

The first attempt to change the length of the column that defined with Primary Key Constraint

alter table dbo.tblTestEmployee
alter column EmployeeLastName varchar(200)

This attempt failed. Here is an error message:

Server: Msg 5074,Level 16,State 8,Line 1
The object 'pk_tblTestEmployee_EmpLastName_EmpFirstName' is dependent on 
column 'EmployeeLastName'.
Server: Msg 4922,Level 16,State 1,Line 1
ALTER TABLE ALTER COLUMN EmployeeLastName failed because one or more objects 
access this column.

But if we create an ADT we will be able to replace existing ADP with the new one.

if exists( select * from systypes where [name] = 
'adt_ExtendedEmployeeLastName')
     exec sp_droptype 'adt_ExtendedEmployeeLastName'
exec sp_addtype
@typename = 'adt_ExtendedEmployeeLastName'
,@phystype = 'varchar(8000)'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'
alter table dbo.tblTestEmployee
alter column EmployeeLastName adt_ExtendedEmployeeLastName

As a result, this query will show us that EmployeeLastName column is varchar(8000)

select *
from INFORMATION_SCHEMA.Columns
where Table_Name = 'tblTestEmployee'
and Domain_Name = 'adt_ExtendedEmployeeLastName'

What a change! We did change EmployeeLastName column data type from varchar(4) to varchar(8000). We are going to do it second time!!!

if exists( select * from systypes where [name] = 
'adt_MinimumEmployeeLastName')
     exec sp_droptype 'adt_MinimumEmployeeLastName'
exec sp_addtype
@typename = 'adt_MinimumEmployeeLastName'
,@phystype = 'varchar(8000)'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'
alter table dbo.tblTestEmployee
alter column EmployeeFirstName adt_MinimumEmployeeLastName

We got a warning message:

Warning: The table 'tblTestEmployee' has been created but its maximum row 
size (10041)
exceeds the maximum number of bytes per row (16041). INSERT or
UPDATE of a row in this table will fail if the resulting row length exceeds 
8060 bytes.

But we did change the length of the column that is defined with Primary Key Constraint.

select *
from INFORMATION_SCHEMA.Columns
where Table_Name = 'tblTestEmployee'
and Domain_Name = 'adt_MinimumEmployeeLastName'

Reverse engineering a primary key constraint.

After analyzing the system stored procedure sp_helpconstraint, I decided to modify it to a function and use the new function to reverse engineering constraints. First run the script 'Create table tblTestEmployee with PK and ADT'.

Here is an example of how to get information about Primary Key Constraint using ufn_helpconstraint function:

declare @TableName nvarchar(776)
declare @ConstraintType   char(2)
set @TableName = 'tblTestEmployee'
set @ConstraintType  = 'PK'
select *
from dbo.ufn_helpconstraint(@TableName, @ConstraintType)

Why is a function is better then a stored procedure? The result set of this function we can store in a table variable (not in a temporary table) and use for reverse engineering Primary Key Constraint.

declare @TableName nvarchar(776)
declare @ConstraintType   char(2)
-- declare table variable
declare  @ResultSet table (
ConstraintType    nvarchar(146)   null
,ConstraintName sysname         null
,DeleteAction         varchar(20)     null
,UpdateAction         varchar(20)     null
,StatusEnabled       varchar(20)     null
,StatusForReplication varchar(20)     null
,ConstraintKeys    nvarchar(2126)  null
,FkReferences       nvarchar(1000)  null
)
set @TableName = 'tblTestEmployee'
set @ConstraintType   = 'PK'
insert into @ResultSet
select
ConstraintType
,ConstraintName
,DeleteAction
,UpdateAction
,StatusEnabled
,StatusForReplication
,ConstraintKeys
,FkReferences
from dbo.ufn_helpconstraint(@TableName, @ConstraintType)
select *
from @ResultSet

Why a function and not a stored procedure? Here is an example of how easy to reverse engineer the Primary Key Constraint with this function:

declare @TableName nvarchar(776)
declare @ConstraintType   char(2)
set @TableName = 'tblTestEmployee'
set @ConstraintType   = 'PK'
select
'alter table ' +  @TableName + '  add constraint ' + ConstraintName + ' ' + 
ConstraintType
+ ' (' + ConstraintKeys + ')'
from dbo.ufn_helpconstraint(@TableName, @ConstraintType)

Here is how we will use this function to generate a "drop Primary Key Constraint" statement:

declare @TableName nvarchar(776)
declare @ConstraintType   char(2)
set @TableName = 'tblTestEmployee'
set @ConstraintType   = 'PK'
select
'alter table ' +  @TableName + '  drop constraint ' + ConstraintName
from dbo.ufn_helpconstraint(@TableName, @ConstraintType)

The result:

alter table tblTestEmployee  drop constraint pk_tblTestEmployee_EmpLastName_EmpFirstName

Now, let's run the above statement. We get this message:

Warning: The table 'tblTestEmployee' has been created but its maximum row size (16041)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

What? Another misleading message. And if you run this statement a second time the message is not any better.

Run the script 'Create table tblTestEmployee with PK and ADT'. Here is how to determine if ADT is linked to a column that is part of Primary Key Constraint and the name of Primary Key Constraint:

declare @TableName      sysname
declare @AdtName        sysname
declare @ConstraintType sysname
set @AdtName        = 'adt_MinimumEmployeeLastName'
set @TableName      = 'tblTestEmployee'
set @ConstraintType = 'PRIMARY KEY'
select CU.Constraint_Name
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU inner join
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
     on
         CU.Constraint_Catalog = TC.Constraint_Catalog
     and CU.Constraint_Schema  = TC.Constraint_Schema
     and CU.Constraint_Name    = TC.Constraint_Name
     and CU.Table_Catalog      = TC.Table_Catalog
     and CU.Table_Schema       = TC.Table_Schema
     and CU.Table_Name         = TC.Table_Name
     and CU.Constraint_Name    = TC.Constraint_Name
     and TC.Constraint_Type    = @ConstraintType
     and TC.Table_Name         = @TableName
     inner join INFORMATION_SCHEMA.Columns C
     on CU.Column_Name         = C.Column_Name
     and CU.Table_Catalog      = C.Table_Catalog
     and CU.Table_Schema       = C.Table_Schema
     and CU.Table_Name         = C.Table_Name
     and Domain_Name           = @AdtName

Clean up after yourself

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblTestEmployee')
              and ObjectProperty(id,N'IsUserTable') = 1)
    drop table dbo.tblTestEmployee
GO
exec sp_droptype 'adt_ExtendedEmployeeLastName'
exec sp_droptype 'adt_MinimumEmployeeLastName'

Credits:

My wife, my kids, my teacher Ilya Petrenko

My coworkers from Viatech Inc.

SQLServerCentral Forums

Files:

ufn_helpconstraint.txt

Create table tblTestEmployee with PK and ADT.sql