Comprehensive Guide to Creating Tables, Constraints, and User-Defined Types in SQL Server 2005

1. SQL Server 2005 Ch 3. Creating Tables, Constraints, and User-Define Types

2. Creating Tables • Data Types • Define what type of data will be held in a column • Seven Categories: • Exact Numeric • Approximate Numeric • Monetary • Date and Time • Character • Binary • Special Purpose

3. Creating Tables Cont. • Exact Numeric Types • bigint 8 Bytes -2E63 to 2E63 - 1 • int 4 Bytes -2E31 to 2E31 – 1 • smallint 2 Bytes -32,768 to 32,767 • tinyint 1 byte 0 to 255 • decimal(p,s) 5-7 Bytes -10E38+1 to 10E38 - 1 • numeric(p,s) 5-17 Bytes -10E38+1 to 10E38 - 1

4. Creating Tables Cont. • Approximate Numeric Types • float(p) 4 or 8 bytes -2.28E308 to 2.23E308 • real 4 bytes -3.4E38 to 3.4E38 • Monetary Data Types • money 8 Bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807 • smallmoney 4 bytes -214,748.3648 to 214,748.3647 • Date and Time Data Types • Datetime 8 bytes Jan. 1, 1753 – Dec. 31, 9999 • Smalldatetime 4 bytes Jan. 1, 1900 – Jun. 6, 2079

5. Creating Tables Cont. • Character Data Types • char(n) 1-8,000 bytes Max 8,000 ANSI • nchar(n) 2-8,000 bytes Max 4,000 Unicode • varchar(n) 1-8,000 bytes Max 8,000 ANSI • varchar(max) up to 2GB up to 1,073,741,824 ANSI • nvarchar(n) 1-8,000 bytes Max 4,000 Unicode • varchar(max) up to 2GB up to 536,870,912 Unicode • text up to 2GB up to 1,073,741,824 ANSI • ntext up to 2GB up to 536,870,912 Unicode • Binary Data Types • binary(n) 1-8,000 bytes fixed-sized binary data • varbinary(n) 1-8,000 bytes variable-sized binary data • varbinary(max) up to 2GB variable-sized binary data • image up to 2GB variable-sized binary data

6. Creating Tables Cont. • Specialized Data Types • bit • timestamp • uniuqeidentifier • sql_variant • cursor • table • Xml

7. Creating Tables Cont. • Nullability (null or not null) • Null – no value in column (not represented by space or 0) • Identity • Automatically generated incremented value • Used with exact numeric data types • Has seed and incremented values • Table Types • Permanent • Temporary local or global – use # or ## when creating tables • Table variable – used in stored procedures – use Declare @varTableName TABLE in t-sql • Computed Columns • data not stored physically in column • Can be stored physically by using PERSISTED keyword

8. Creating Tables Cont. • Create Table statement • CREATE TABLE     [ database_name . [ schema_name ] . | schema_name . ] table_name         ( { <column_definition> | <computed_column_definition> }      [ <table_constraint> ] [ ,...n ] ) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ] [ ; ]

9. Creating Tables Cont. • Table Permissions • Create Table • Alter Table • Select • Insert • Update • Delete • References • Use Grant command to give permissions • GRANT <permission> [ ,...n ] TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ] [ AS <database_principal> ]

10. Implementing Constraints • Constraint – restricting values entered into a column • Check Constraint – limits range of values in a column or enforces specific patterns • Column level or table level • Can create rule to have check constraint available to all tables, columns in database • Default Constraint – If no value is given in column then default value is given

11. Implementing Constraints Cont. Unique Constraints – insures that no duplicate value is in column Primary Key Constraints – Primary key acts as unique identifier for row Foreign key constraints – forces value to match value in another table

12. Creating User-Defined Types • User-Defined Types (UDT) • Allows DBA’s to create different data types to match the application • Transact-SQL UDT • Allows creates an ‘alias’ to a defined data type • CLR (common language runtime) UDT • Allows programmers to create a data-type utilizing a .NET language. This UDT will be compiled into a .DLL file for addition into the database.