Module 3 Designing a Physical Database Model

1. Module 3 Designing a Physical Database Model

2. Module Overview • Selecting Data Types • Designing Database Tables • Designing Data Integrity

3. Lesson 1: Selecting Data Types • Considerations for Selecting Standard Column Data Types • Considerations for Selecting New SQL Server 2008 Data Types • Considerations for Using CLR User-Defined Data Types • Considerations for Using Spatial Data Types • Guidelines for Using the XML Data Type • Establishing Naming Standards for Database Objects • Discussion: Working with Data Tables

4. Considerations for Selecting Standard Column Data Types Integer versus GUID primary keys ü VARCHAR (MAX), NVARCHAR (MAX), and VARBINARY (MAX) data types ü Character column collations and Unicode and non-Unicode data types ü Transact-SQL user-defined data types ü Fixed versus variable length columns ü

5. Considerations for Selecting New SQL Server 2008Data Types Date / Time Datetime2 / Datetimeoffset • Date: From 01/01/0001 to 12/31/9999 • Date: No time component • Time: Based on 24 hour clock, NO date component • Time: Accurate to 100 ns • Same date precision as date • Same time precision as time • DateTimeOffset is time zone aware Hierarchyid FILESTREAM • To create tables with a hierarchical structure • To query and perform work with hierarchical data by using T-SQL • When a table contains a FILESTREAM column • When objects are larger than 1 MB • Where fast read access is important • Where you use a middle tier

6. Considerations for Using CLR User-Defined Data Types Use common language runtime (CLR) user-defined data types for nonstandard or proprietary data types Avoid excessively complex data types Consider the overhead of row-by-row processing Consider the risks of tightly coupling a CLR user-defined data types and the database

7. Considerations for Using Spatial Data Types Spatial Data: • Represents information on the location and shape of geometric objects • Can be of two types including geometry and geography data types • Implemented as .NET common language runtime data types in SQL Server • Supports eleven spatial data objects, or instance types Spatial Data Geometry Data Type Geography Data Type

8. Guidelines for Using the XML Data Type Use the XML data type for data that is not frequently updated ü Use typed XML columns ü Use the XML data type for data that is not relationally structured ü Use the XML data type for configuration information ü Use the XML data type for data with recursive structures ü < xml >

9. Establishing Naming Standards for Database Objects Use names that comply with the rules for forming SQL Server 2008 identifiers Use descriptive terms Be consistent across all objects Use models for naming standards Use only standard abbreviations in names Record and communicate naming standards Use prefixes ONLY when it provides value Use policy-based management Name intersection tables consistently ü ü ü ü ü ü ü ü ü

10. Discussion: Working with Data Tables • Scenario: • Some developers argue to use a simple table structure containing only four columns—a unique row identifier, a field identifier (analogous to a column name), a datatype indicator, and the data itself stored as a sqlvariant. • Some say that the application could reconstitute all data fields, and pivot them into a virtual table. • The argument often goes further to whether developers should be involved in working with the database schema, since they can create new data properties (fields) as needed. • Question: • What is the fallacy in these arguments?

11. Lesson 2: Designing Database Tables • Guidelines for Determining Table Width • What Are Sparse Columns? • Demonstration: How To Create a Table By Using Sparse Columns • Guidelines for Using Computed Columns • Discussion: Using Computed Columns

12. Guidelines for Determining Table Width Large Object Types Data Overflow Capacity Planning

13. What Are Sparse Columns? Sparse columns are ordinary columns that have an optimized storage for NULL values. They can be used with column sets and filtered index. Properties of Sparse Columns • The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. • Catalog views for a table that has sparse columns are the same as for a typical table. • The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. Data types that you cannot specify as sparse are geography, geometry, image, ntext, text, timestamp, and user-defined data types.

14. Demonstration: How To Create a Table By UsingSparse Columns • In this demonstration, you will see how to: • Create a table by using sparse columns

15. Guidelines for Using Computed Columns Usage of Persisted Computed Columns Usage of Computed Columns Usage of Computed Columns Usage of Persisted Computed Columns Use computed columns to derive results from other columns Use persisted computed columns for performance Computed Columns • Avoid the overhead of complex functions in computed columns • Avoid persisted computed columns on active data • Protect against numeric overflow and divide by zero errors Exceptions in Column Data Exceptions in Column Data

16. Discussion: Using Computed Columns What kind of problems do computed columns solve? When is a computed column actually computed?

17. Lesson 3: Designing Data Integrity • Guidelines for Designing Column Constraints • Guidelines for Designing Table Constraints • Guidelines When Implementing DDL Triggers • Discussion: Identifying the Best Options for Column Data Types and Data Constraints

18. Guidelines for Designing Column Constraints (CONSTRAINT Qty DEFAULT 0...) (HireDate int NOT NULL...) Use ANSI default constraints Declare columns as NOT NULL CONSTRAINT chkQty CHECK (Amount > 0)...) Check Constraint Bound Rules Use CHECK constraints instead of bound rules Use column CHECK Constraints

19. Guidelines for Designing Table Constraints Specify cascading levels and options ON DELETE Use triggers to enforce referential integrity Use table-level CHECK constraints Check Constraint Use DRI for data integrity in a database Use ANSI-standard options

20. Guidelines for Designing Database Constraints byUsing DDL Triggers Use DDL triggers for auditing Use DDL triggers to support security Use DDL triggers to prevent database changes

21. Guidelines When Implementing DDL Triggers Use DDL triggers with transactions ü Use DDL triggers scope to control database operations or objects that activates the trigger ü Avoid creating DDL Trigger on both CREATE_SCHEMA and CREATE_TABLE event ü Use value() instead of query() when querying data returned from EVENTDATA ü

22. Discussion: Identifying the Best Options for Column Data Types and Data Constraints Scenario: QuantamCorp is a local company conducting business only in USA and Canada. You need to create a single table to contain the following information. Time of the day for a 24 hour clock Company name E-mail address Postal code Product information Product descriptive brochure Telephone number Question: What are the best options for column data types and data constraints?

23. Lab 3: Designing a Physical Database Model Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd • Exercise 1: Specifying Database Object Naming Standards • Exercise 2: Converting a Logical Database Model into a Physical Database Model Logon Information Estimated time: 40 minutes

24. Lab Scenario The main goals of the HR VASE project are as follows: • Provide managers with current and historical information about employee vacation and sick-leave data. • Provide permission to individual employees to view their vacation and sick-leave balances. • Provide permission to selected employees in the HR department to view and update employee vacation and sick-leave data. • Provide permission to the HR manager to view and update all the data. • Standardize employee job titles. • In this lab, you will build a physical database model based on the logical model created earlier. You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp2008 sample database in SQL Server 2008.

25. Lab Review • Can you explain the purpose of creating naming standards and having a Naming Standards policy? • What kind of issues may arise if a Naming Standards policy does not exist?

26. Module Review and Takeaways • Review Questions • Real-world Issues and Scenarios