Module 3: Creating Data Types and Tables

1. Module 3: Creating Data Types and Tables

2. Overview • Working with Data Types • Working with Tables • Generating Column Values • Generating Scripts

3. Lesson: Working with Data Types • System-Supplied Data Types • User-Defined Data Types • Guidelines for Specifying Data Types

4. System-Supplied Data Types • Numeric • Integer • Exact numeric • Approximate numeric • Monetary • Date and Time • Character and Unicode Character • Binary • Other

5. User-Defined Data Types Creating EXEC sp_addtype city, 'nvarchar(15)', NULL EXEC sp_addtype region, 'nvarchar(15)', NULL EXEC sp_addtype country, 'nvarchar(15)', NULL Dropping EXEC sp_droptype city

6. Guidelines for Specifying Data Types • Specify Narrowest Column Possible • If Column Length Varies, Use a Variable Data Type • Use tinyint Appropriately • In General, Use decimal For Numeric Data Types • If Storage Is Greater Than 8000 Bytes, Use text or image • Use money for Currency • Do Not Use float or real as Primary Keys

7. Lesson: Working with Tables • How SQL Server Organizes Data in Rows • How SQL Server Organizes Variable-Length Data • Table Creation and Deletion Process • Column Addition and Removal Process

8. How SQL Server Organizes Data in Rows Data Header Fixed Data NB VB Variable Data 4 bytes Null Block Variable Block

9. How SQL Server Organizes Variable-Length Data Data row Text Pointer Root Structure Intermediate Node Intermediate Node block 1 block 2 block 1 block 2

10. Table Creation and Deletion Process • Creating a Table • Column Collation • Specifying NULL or NOT NULL • Computed Columns • Dropping a Table

11. Column Addition and Removal Process Customer_name Sales_amount Sales_date Customer ID Commission ADD ALTER TABLE CategoriesNew ADD Commission money null DROP ALTER TABLE CategoriesNew DROP COLUMN Sales_date

12. Lesson: Generating Column Values • The Identity Property • The NEWID Function and the uniqueidentifier Data Type

13. The Identity Property • Requirements for Using the Identity Property • Only one identity column is allowed per table • Use with integer, numeric, and decimal data types • Retrieving Information About the Identity Property • Use IDENT_SEED and IDENT_INCR for definition information • Use SCOPE_IDENTITY to determine most recent value • Managing the Identity Property

14. The NEWID Function and the uniqueidentifier Data Type • These Features Are Used Together • Ensure Globally Unique Values • Use with the DEFAULT Constraint • Use with distributed and replicated databases • May degrade performance if used as primary key CREATE TABLE Customer (CustID uniqueidentifier NOT NULL DEFAULT NEWID(), CustName char(30) NOT NULL)

15. Lesson: Generating Scripts • Uses of Generated Database Schema Scripts • Script Generation Process

16. Uses of Generated Database Schema Scripts • Maintain backup script • Create or update a database development script • Create a test or development environment • Train new employees

17. Script Generation Process • SQL Enterprise Manager • Entire database into single script file • Table-only schema • Table and index schema • SQL Analyzer • One object at a time

18. Lab A: Creating Data Types and Tables • Exercise 1: Creating User-Defined Data Types • Exercise 2: Creating Tables in the ClassNorthwind Database • Exercise 3: Adding and Dropping Columns • Exercise 4: Generating Transact-SQL Scripts • Exercise 5: Loading the ClassNorthwind Database with Data