More on SQL

User Defined Types

User defined types are always based on system-supplied data type. They can enforce data integrity and they allow nulls.

To create a user-defined data type in SQL Server, choose types under "Programmability" in your database. Next, right click and choose 'New' –>'User-defined data type' or execute the sp_addtype system stored procedure. After this, type:

sp_addtype ssn, 'varchar(11)', 'NOT NULL'

This will add a new user-defined data type called SIN with nine characters.

In this example, the field EmployeeSIN uses the user-defined data type SIN.

CREATE TABLE SINTable
(
EmployeeID        INT Primary Key,
EmployeeSIN    SIN,
CONSTRAINT CheckSIN
CHECK (EmployeeSIN LIKE
' [0-9][0-9][0-9] – [0-9][0-9] [0-9] – [0-9][0-9][0-9] ')
)

ALTER TABLE

You can use ALTER TABLE statements to add and drop constraints.

• ALTER TABLE allows columns to be removed.
• When a constraint is added, all existing data are verified for violations.

In this example, we use the ALTER TABLE statement to the IDENTITY property to a ColumnName field.

USE HOTEL
GO
ALTER TABLE  tblHotel
ADD CONSTRAINT unqName UNIQUE (Name)

Use the ALTER TABLE statement to add a column with the IDENTITY property such as ALTER TABLE TableName.

ADD
ColumnName         int    IDENTITY(seed, increment)

DROP TABLE

The DROP TABLE will remove a table from the database. Make sure you have the correct database selected.

DROP TABLE tblHotel

Executing the above SQL DROP TABLE statement will remove the table tblHotel from the database.