SQLServerCentral Article

Reverse Engineering Alias Data Types in SQL Server 2000

,

Introduction.

In SQL Server 2005 Microsoft introduced 'alias data types'. In reality, what we knew as 'user-defined data types' in SQL Server 2000, became 'alias data types' in SQL Server 2005. I understand that some DBA or developers work with SQL

Server 2005 and do not have any interest in SQL Server 2000, but many bugs described in this article were successfully implemented in SQL Server 2005.

'Alias data types'(ADT) is much better then 'user-defined data types' (UDT) because the former name was misleading and confusing. In this article we are going to use Alias Data Types (ADT) name and explore ADTs in MS SQL Server 2000. I firmly believe this knowledge will help you many times.

Most shops do not use user-defined data types and most likely will not use alias data types, but we should know both!

You can create aliases for column or table names in a query for

readability and simplicity.

Alias is an alternate (short or long) form of a column name.

For example, in this query we use column and table names

Select
  dbo.tblEmployee.EmpName
  ,dbo.tblEmployee.Salary
  ,dbo.Department.[Name]
 from dbo.tblEmployee inner join dbo.Department
 on dbo.tblEmployee.DepartmentIdentificationNumber = dbo.Department.DepartmentIdentificationNumber

We can rewrite this query using column and table aliases

Select
  E.EmpName as 'Employee Name'
  ,E.Salary  as 'Annual Salary'
  ,D.[Name]  as 'Department Name'
 from dbo.tblEmployee E inner join dbo.Department D
 on E.DepartmentIdentificationNumber = D.DepartmentIdentificationNumber

Where letters E and D are table aliases and 'Employee Name', 'Annual Salary', 'Department Name' are column aliases.

Table and Column aliases are not sharable and their life last only for

one query execution.

Each table column has a data type. A data type is an attribute that defines the type of data

that column can hold. SQL Server supplies system data types - character, integer, datetime and many others. To get a list of system-supplied data types and their properties run this query:

select * from systypes where xusertype < 256 order by [name]
-- or
select * from master.dbo.spt_datatype_info order by data_type asc
-- or to get the list of all data types defined in the database run system stored procedure:
sp_datatype_info

to get properties of one specific data type, for example bit: sp_datatype_info -7 -- (not user friendly)

Alias data types(ADT) derive from the system-supplied data types and their purpose to be reusable, to be sharable, to enforce the same data type, length, and nullability across multiple tables in a database. Alias data types is a convenient way to name instances of a system data type.

For example, we can call varchar(100) as adt_EmployeeLastName and use alias data type adt_EmployeeLastName in table definition instead of varchar(100):

create table dbo.Employee
( EmployeeId int
 ,EmployeeLastName adt_EmployeeLastName )

Sysname is the first Alias data type. Sysname is used to reference database object names.

select * from systypes where [name] = 'sysname' and xusertype = 256

Here are Help pages describing ADT in MS SQL Server 2000:

  • Creating User-Defined Data Types
  • How to create user-defined data types (Enterprise Manager)
  • How to delete user-defined data types (Enterprise Manager)
  • Data Types
  • Using Data Types
  • sp_addtype
  • sp_droptype
  • sp_rename
  • systypes
  • search for string 'User-Defined Data Types'

Why use ADTs?

  • to pass Certification Exam
  • easy way to define columns that contain the same data type, length, and nullability across multiple tables
  • consistency
  • readability
  • safe time
  • sharing
  • simplicity
  • domain integrity
  • avoid implicit conversions
  • simplify application development
  • enforce standards

Change data type or size of a column in 1000 tables:

- without ADT means changing the source code for all the tables

- without ADT means a lot of tedious work

- with ADT change one ADT definition

Sounds great, but MS SQL Server does not provide tools to perform these tasks.

In Enterprise Manager there is design table tool but there is not design ADT tool.

Application Development

  • without ADT remember all data types or constantly check them in references sources
  • with ADT, self explanatory, easy to use

When to use ADTs?

Most DBAs and developers say 'N E V E R!'. But... SQL GURUs say if you know how to use - use it.

There are three ADT schools:

  • First School - We hate and do not use ADT.
  • Brave School - We like and do use ADT.
  • Wisdom School - We wait until the development of ADT is finished in MS SQL Server 3025.

Why Not Use?

First School - difficult to change ADT

- special data type 'table variables' do not support ADT

- requires extra work to use ADT in temporary table

- scripting ADT is a nightmare

- no cast or convert with ADT

- absence of tools to handle ADT

By the way some of these issues fixed in MS SQL 2005.

What if you need to change only one ADT in 2500 tables and keep it the same in 200 tables?

MS SQL Server does not provide tools to perform these tasks.

People say - you do not do it every day but when you need to do - there is no tool.

You can modify database objects using Alter command. You can only create and drop ADT. You can attach (link) or detach ADT to a column of a table. ADT cannot be defined using the MS SQL Server timestamp data type. This means that all other system-supplied data types can be used with ADT. You may notice that bigint, money, smallmoney system data types are missing from the list in Help but this typo is fixed in MS SQL Server 2005 help.

From Help:

Note: If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.

You may create ADT EmployeeId in Model table and you will automatically have the same ADT EmployeeId in all new databases. This is good with new development but if you want to add new ADT to multiple databases it will not help. It would be nice to have a tool to create ADT in selected databases with two, three clicks.

We are going to study ADT behavior in the following examples:

Example 1: How to use ADT?

Create a user table

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        decimal(18,7) NOT NULL
        ,EmployeeFirstName char(5)       NOT NULL
)

Create an alias data type

You can create an alias data type using system stored procedure sp_addtype

exec sp_addtype
   @typename = 'EmployeeFirstName' -- name of Adt, required parameter
  ,@phystype = 'varchar(5)'        -- system or physical data type on which the Adt is based, required parameter
  ,@nulltype = 'NULL'              -- nullability (allow or not allow null), optional parameter
  ,@owner    = 'dbo'               -- domain schema, optional parameter

You may notice that column name and alias data type have the same name. You will not find the ADT in the sysobjects table.

Check on alias data type properties

You may see ADT properties using system stored procedure sp_help.

exec sp_help EmployeeFirstName

Compare the results with properties of system data type varchar

exec sp_help varchar

Another way to see ADT properties is to use INFORMATION_SCHEMA view Domains

select *
from INFORMATION_SCHEMA.DOMAINS
where Domain_Name = 'EmployeeFirstName'

Link an alias data type to a table column

alter table dbo.tblTestEmployee
alter column EmployeeFirstName EmployeeFirstName

Create new user alias data type

There was a request to change length of EmployeeFirstName column from varchar(5) to varchar(60) and as part of this request EmployeeFirstName column should not contain nulls.We are going to create a new ADT - adt_EmployeeFirstName and replace existing ADT EmployeeFirstName with new ADT.

exec sp_addtype
@typename = 'adt_EmployeeFirstName'
,@phystype = 'varchar(60)'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'

Override existing alias data type with new alias data type

alter table dbo.tblTestEmployee
alter column EmployeeFirstName adt_EmployeeFirstName

Check an alias data type usage

select *
from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
where Table_Name = 'tblTestEmployee' and Domain_Name = 'adt_EmployeeFirstName'

Drop not linked (not used) alias data type

Since ADT EmployeeFirstName is not used anymore we are going to drop it using system stored procedure sp_droptype

exec sp_droptype @typename = 'EmployeeFirstName'

You can not drop ADT that is linked to a table column. This statement:

exec sp_droptype @typename = 'adt_EmployeeFirstName'

will produce an error message:

Server: Msg 15180, Level 16, State 1, Procedure sp_droptype, Line 32
Cannot drop. The data type is being used.

Detach an alias data type

In one step above we saw how to drop link between table column and ADT. Here is an alternate way of doing it. We are going to use this approach every time when we want to get rid of all ADT linked to a table

alter table dbo.tblTestEmployee
alter column EmployeeFirstName char(60)

Check the ADT usage, no rows should be found

select *
from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
where Table_Name = 'tblTestEmployee'

Now you can drop not used ADT using system stored procedure sp_droptype

exec sp_droptype @typename = 'adt_EmployeeFirstName'

Create new alias data types

Here we are going to create new ADT to use them in create table statement.

exec sp_addtype
   @typename = 'adt_BigInteger'  -- name of ADT
  ,@phystype = 'BigInt'          -- system or physical data type on which the Adt is based
  ,@nulltype = 'NOT NULL'        -- nullability ( allow or not allow null)
  ,@owner    = 'dbo'             -- domain schema
exec sp_addtype
   @typename = 'adt_EmployeeLastName'
  ,@phystype = 'varchar(100)'
  ,@nulltype = 'NONULL'
  ,@owner    = 'dbo'
exec sp_addtype
   @typename = 'adt_EmployeeFirstName'
  ,@phystype = 'char(10)'
  ,@nulltype = 'NULL'
  ,@owner    = 'dbo'

Create table using alias data types instead of using system datatypes

An alternate way to link ADT to a table column is to link ADT to a column in the create table statement

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        adt_BigInteger
        ,EmployeeFirstName adt_EmployeeFirstName
        ,EmployeeLastName  adt_EmployeeLastName
)

Summary: We learned:

  • how to create an ADT using the system stored procedure sp_addtype and link the ADT to a table column
  • how to link an ADT and table column using alter table and alter column commands
  • how to remove link between ADT and column
  • only one ADT can be linked to a column, by assigning a new data type or a new ADT to a

    column we force MS SQL Server to remove the link between table column and ADT.

  • how to drop not used (detached) ADT using system stored procedure sp_droptype

Example 2: Change ADT using first algorithm in order to change ADT definition (length, datatype or nullability or all three attributes) we will use this algorithm:

Please notice that in case 1 we used different ADT name to change properties of a table column. In this study we are going to use the same ADT name. There is no way to change ADT! We should drop it and create again.

Alter table column with predetermined system data type.

We are going to query INFORMATION_SCHEMA.COLUMNS view to find system data type used in EmployeeFirstName column definition - char(10)

SELECT TABLE_NAME               as TableName
      ,COLUMN_NAME              as ColumnName
      ,ORDINAL_POSITION         as ColumnIdentificationNumber
      ,DATA_TYPE                as DataType
      ,CHARACTER_MAXIMUM_LENGTH as MaximumLengthInCharacters
      ,IS_NULLABLE              as Nullability
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE Table_Name = 'tblTestEmployee'
 and Column_Name = 'EmployeeFirstName'

Alter table column with corresponding system data type found in MaximumLengthInCharacters column. This statement will detach ADT adt_EmployeeFirstName from table column EmployeeFirstName

alter table dbo.tblTestEmployee
alter column EmployeeFirstName char(10)

Drop ADT

Check that ADT is linked only to our table, if it was linked to other table - we should first detach ADT. This query should return zero rows:

select *
 from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
 where Domain_Name = 'adt_EmployeeFirstName'

Now we can drop ADT adt_EmployeeFirstName

exec sp_droptype @typename = 'adt_EmployeeFirstName'

Create ADT according to new requirements

We are going to change all parameters of alias data type adt_EmployeeFirstName

exec sp_addtype
  @typename = 'adt_EmployeeFirstName'
 ,@phystype = 'nvarchar(100)'
 ,@nulltype = 'NULL'
 ,@owner    = 'Yakov.Shlafman'

Link new ADT to a column in a table

alter table dbo.tblTestEmployee
alter column EmployeeFirstName adt_EmployeeFirstName

Check the result:

select *
 from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
 where Table_Name = 'tblTestEmployee'
setuser N'yakov.shlafman'
exec sp_help adt_EmployeeFirstName

Example 2. Change ADT using second algorithm.

Rename ADT that is attached to a table column

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        adt_BigInteger
        ,EmployeeFirstName adt_EmployeeFirstName
        ,EmployeeLastName  adt_EmployeeLastName
)
sp_rename adt_EmployeeFirstName, adt_UsingTemporaryADT

check the name of ADT attached to a table column EmployeeFirstName

select *
 from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
 where Table_Name = 'tblTestEmployee'

Create new ADT according to requirements using used name

exec sp_addtype
  @typename = 'adt_EmployeeFirstName'
 ,@phystype = 'char(100)'
 ,@nulltype = 'NOT NULL'
 ,@owner    = 'dbo'

Attach new ADT to a table column

alter table dbo.tblTestEmployee
alter column EmployeeFirstName adt_EmployeeFirstName
select *
from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
where Table_Name = 'tblTestEmployee'

Drop detached ADT

exec sp_droptype @typename = 'adt_UsingTemporaryADT'

Example 3. Does Nullability in create table statement takes precedence of ADT nullability?

Yes! We are going to create an ADT that has property 'NOT NULL', attach it to a table column that is defined as nullable (allows null values) and check the result.

exec sp_addtype
  @typename = 'adt_NotNullableEmployeeFirstName'
 ,@phystype = 'nvarchar(100)'
 ,@nulltype = 'NOT NULL'
 ,@owner    = 'Yakov.Shlafman'
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        decimal(18,7) NULL
        ,EmployeeFirstName adt_NotNullableEmployeeFirstName      NULL
)

To check nullability run this query:

SELECT
  TABLE_NAME               as TableName
 ,COLUMN_NAME              as ColumnName
 ,ORDINAL_POSITION         as ColumnIdentificationNumber
 ,DATA_TYPE                as DataType
 ,CHARACTER_MAXIMUM_LENGTH as MaximumLengthInCharacters
 ,IS_NULLABLE              as Nullability
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE Table_Name = 'tblTestEmployee' and IS_NULLABLE = 'YES'

To override nullability in EmployeeFirstName column we are going to create a new ADT and link it to EmployeeFirstName. This will make EmployeeFirstName column nullable.

exec sp_droptype @typename = 'adt_EmployeeFirstName'
exec sp_addtype
  @typename = 'adt_EmployeeFirstName'
 ,@phystype = 'char(100)'
 ,@nulltype = 'NOT NULL'
 ,@owner    = 'dbo'
alter table dbo.tblTestEmployee
alter column EmployeeFirstName adt_EmployeeFirstName

To check nullability run this query:

SELECT
TABLE_NAME               as TableName
,COLUMN_NAME              as ColumnName
,ORDINAL_POSITION         as ColumnIdentificationNumber
,DATA_TYPE                as DataType
,CHARACTER_MAXIMUM_LENGTH as MaximumLengthInCharacters
,IS_NULLABLE              as Nullability
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
Table_Name = 'tblTestEmployee'  and IS_NULLABLE = 'NO'

Try it:

insert into dbo.tblTestEmployee (EmployeeId) values (234)

Here is the message generated by MS SQL Server

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EmployeeFirstName', table 'Flowers.dbo.tblTestEmployee';
column does not allow nulls. INSERT fails.
The statement has been terminated.

Example 4: Reverse engineering alias data types in MS SQL Server 2000 for beginners.

The purpose of this exercise is to analyze system tables after all ADT created with available system data types. This will allow us to write queries and generate 'create ADT' statements based on information in system tables.

To get create ADT statement using Enterprise Manager:

- click on database

- click on User Defined Data Types

- click on alias data type

- press CTR + C keys

- open Query Analyzer

- press CTR + V keys to paste create alias data type statement into your editor pane

(using this technique you can get source code of most database objects)

Drop all not used, not linked and detached ADT.

This exercise will teach you how to backup information about not used ADT

and drop them all. First we will generate create ADT statement based on system tables or backup information. Then you will learn how to reverse engineer alias data types. Next we will create a test table to store all system datatypes that can be

used with ADT.

Here are two scripts to create and populate dbo.tblDataTypeForAdt

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblDataTypeForAdt')
              and ObjectProperty(id, N'IsUserTable') = 1)
    drop table  dbo.tblDataTypeForAdt
CREATE TABLE dbo.tblDataTypeForAdt (
  DataTypeId          smallint   identity(1,1) NOT NULL PRIMARY KEY CLUSTERED
 ,TestDataType        varchar(60)              NOT NULL
 ,TestDataProperties  varchar(60)                  NULL
)
set nocount on
truncate table dbo.tblDataTypeForAdt
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('binary', '(6387)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('bit', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('char', '(239)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('datetime', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('decimal','(18,11)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('float', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('image', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('int', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('nchar', '(1235)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('ntext', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('numeric', '(38,17)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('nvarchar','(3582)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('real', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('smalldatetime', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('smallint', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('text', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('tinyint', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('uniqueidentifier', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('varbinary', '(7841)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('varchar','(3578)')
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('bigint', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('money', null)
insert into dbo.tblDataTypeForAdt (TestDataType, TestDataProperties) values ('smallmoney', null)
UPDATE STATISTICS dbo.tblDataTypeForAdt
set nocount off

Please notice that we used 'odd' numbers for length of data types to easy recognize them in system tables.

TestDataProperties column of table dbo.tblDataTypeForAdt contains:

precision and scale for Numeric data types

the maximum length for the character based data types

Now we create and run a stored procedure dbo.up_CreateAdtForTesting that will create 46 ADT. Run this procedure:

exec dbo.up_CreateAdtForTesting

To check the results of this run execute this query:

select *
 from INFORMATION_SCHEMA.DOMAINS
 where Domain_Name like 'adt_%'

All created ADT are not linked to any table in the database.

We can check this:

select *
 from INFORMATION_SCHEMA.DOMAINS D
 where not exists
   ( select *
      from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE CDU
      where D.Domain_Name = CDU.Domain_Name
   )

Drop all not used, not linked and detached ADT.

There is no need to store not used ADT in system tables. We are going to backup all information about ADT in a user defined table dbo.tblNotUsedAdt and then drop all not linked ADT.

if exists (select *
             from dbo.sysobjects
            where id = object_id(N'dbo.tblNotUsedAdt')
              and ObjectProperty(id, N'IsUserTable') = 1)
    drop table  dbo.tblNotUsedAdt
go
create table dbo.tblNotUsedAdt (
AdtIdentifier  int identity(1,1) NOT NULL primary key clustered
,AdtName      sysname NOT NULL
,xtype      tinyint NOT NULL
,[status]      tinyint NOT NULL
,xusertype    smallint NOT NULL
,[length]     smallint NOT NULL
,xprec     tinyint NOT NULL
,xscale     tinyint NOT NULL
,tdefault     int NOT NULL
,[domain]     int NOT NULL
,[uid]     smallint NOT NULL
,reserved     smallint NOT NULL
,collationid   int          NULL
,usertype     smallint                 NULL
,[variable]     bit                      NULL
,allownulls    bit                      NULL
,[type]           tinyint                  NULL
,printfmt       varchar(255)             NULL
,prec             smallint                 NULL
,[scale]          tinyint                  NULL
,[collation]     sysname                  NULL
,dropDate     smalldatetime   NOT NULL
,[comments]  varchar(100)  NOT NULL
)
go

Here is a stored procedure that will remove all not used ADT and backup all information about them in table dbo.tblNotUsedAdt. up_NotUsedAdtMaintenance

exec dbo.up_NotUsedAdtMaintenance 'Monthly cleansing job '

check that:

select *
  from INFORMATION_SCHEMA.DOMAINS D
  where not exists
   ( select *
      from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE CDU
      where D.Domain_Name = CDU.Domain_Name
   )

All information about dropped ADT recorded in the table dbo.tblNotUsedAdt

select * from dbo.tblNotUsedAdt where AdtName   = 'adt_numeric_NotNull'

Generate create ADT statement based on system tables or backup information.

To reverse engineer create ADT statement we are going to use stored procedure dbo.up_ReverseEngineeringAdt. This stored procedure allows to generate create ADT script from system table systypes or from our backup table dbo.tblNotUsedAdt. Here are the examples:

To reverse engineer dropped ADT from our backup table dbo.tblNotUsedAdt

declare @AdtName   sysname
declare @SourceInd bit
set @AdtName   = 'adt_numeric_NotNull'
set @SourceInd = 0
exec dbo.up_ReverseEngineeringAdt  @AdtName, @SourceInd

To reverse engineer existing ADT from system table systypes

exec sp_addtype
@typename = 'adt_TestADT'
,@phystype = 'nvarchar(100)'
,@nulltype = 'NOT NULL'
,@owner    = 'Yakov.Shlafman'
declare @AdtName   sysname
declare @SourceInd bit
set @AdtName   = 'adt_TestADT'
set @SourceInd = 1
exec dbo.up_ReverseEngineeringAdt  @AdtName, @SourceInd

Obvious things:

When you change nullability of a column from NULL to NOT NULL using ADT all rows in this table should have values in the column. First, let's create a user table

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        decimal(18,7) NOT NULL
        ,EmployeeFirstName char(30)           NULL
)
insert into dbo.tblTestEmployee(EmployeeId) values(1)
insert into dbo.tblTestEmployee(EmployeeId) values(2)
insert into dbo.tblTestEmployee(EmployeeId) values(3)

Next we create an alias data type using the system stored procedure sp_addtype

exec sp_droptype adt_EmployeeFirstName
exec sp_addtype
  @typename = 'adt_EmployeeFirstName' -- name of Adt, required parameter
 ,@phystype = 'varchar(100)'          -- system or physical data type on which the Adt is based, required parameter
 ,@nulltype = 'NOT NULL'              -- nullability (allow or not allow null),                  optional parameter
 ,@owner    = 'dbo'                   -- domain schema, optional parameter

We can change the column nullability from NULL to NOT NUll using an ADT. First attempt:

alter table dbo.tblTestEmployee
alter column EmployeeFirstName  adt_EmployeeFirstName
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EmployeeFirstName',
table 'Flowers.dbo.tblTestEmployee'; column does not allow nulls. UPDATE 
fails.
The statement has been terminated.

Our second attempt:

update dbo.tblTestEmployee
set EmployeeFirstName = 'To be determined'
alter table dbo.tblTestEmployee
alter column EmployeeFirstName  adt_EmployeeFirstName

This works and we can check the results like this:

SELECT  TABLE_NAME               as TableName
       ,COLUMN_NAME              as ColumnName
       ,ORDINAL_POSITION         as ColumnIdentificationNumber
       ,DATA_TYPE                as DataType
       ,CHARACTER_MAXIMUM_LENGTH as MaximumLengthInCharacters
       ,IS_NULLABLE                                     as Nullability
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE Table_Name = 'tblTestEmployee'  and IS_NULLABLE = 'NO'

Summary

We just touched the tip of an iceberg of a very simple topic named Alias Data Types for beginners. We did find answers to some questions we had. We got understanding of how ADTs work.

References:

http://msdn2.microsoft.com/en-us/library/ms189283.aspx

http://msdn2.microsoft.com/fr-fr/library/ms131120.aspx

http://msdn2.microsoft.com/en-us/library/ms186366.aspx

http://msdn2.microsoft.com/en-us/library/ms187752.aspx

http://www.thescripts.com/forum/thread144805.html

Credits:

my wife, my kids, my teacher Ilya Petrenko

my coworkers from Viatech Inc.

SQLServerCentral Forums

Links to source code of stored procedure:

dbo.up_CreateAdtForTesting

dbo.up_NotUsedAdtMaintenance

dbo.up_ReverseEngineeringADT

Files:

TestStoredProcedure_up_ReverseEngineeringAdt_Part1.txt

TestStoredProcedure_up_ReverseEngineeringAdt_Part2.txt

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

Unique Identifier: Usage and Limitations

The Unique Identifier datatype in SQL Server has tremendous potential to solve many of the issues one finds with Identity fields, other primary keys, etc. However it also has some drawbacks and limitations. Author Sharad Nandwani looks at some of the pros and cons of this datatype.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

2004-06-24

14,018 reads