module 3 designing a physical database model. module overview selecting data types designing...
TRANSCRIPT
Module 3
Designing a Physical Database Model
Module Overview
• Selecting Data Types
• Designing Database Tables
• Designing Data Integrity
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
Considerations for Selecting Standard Column Data Types
Integer versus GUID primary keysüü
Fixed versus variable length columnsüü
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üü
Considerations for Selecting New SQL Server 2008Data Types
• Same date precision as date• Same time precision as time • DateTimeOffset is time zone aware
Datetime2 / Datetimeoffset
• 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
FILESTREAM
• 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
Date / Time
• To create tables with a hierarchical structure
• To query and perform work with hierarchical data by using T-SQL
Hierarchyid
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 risks of tightly coupling a CLR user-defined data types and the databaseConsider the risks of tightly coupling a CLR user-defined data types and the database
Consider the overhead of row-by-row processing
Considerations for Using Spatial Data Types
Spatial Data: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
• 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
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 >
Establishing Naming Standards for Database Objects
Use only standard abbreviations in namesUse only standard abbreviations in namesüü
Use names that comply with the rules for forming SQL Server 2008 identifiersUse names that comply with the rules for forming SQL Server 2008 identifiersüü
Use descriptive termsUse descriptive termsüü
Use models for naming standardsUse models for naming standardsüü
Be consistent across all objectsüü
Record and communicate naming standardsüü
Use prefixes ONLY when it provides valueüü
Use policy-based managementüü
Name intersection tables consistentlyüü
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?
• 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
Lesson 2: Designing Database Tables
Guidelines for Determining Table Width
Large Object Types Data Overflow
Capacity Planning
Properties of Sparse Columns
What Are 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.
• 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.
Sparse columns are ordinary columns that have an optimized storage for NULL values. They can be used with column sets and filtered index.Sparse columns are ordinary columns that have an optimized storage for NULL values. They can be used with column sets and filtered index.
Data types that you cannot specify as sparse are geography, geometry, image, ntext, text, timestamp, and user-defined data types.Data types that you cannot specify as sparse are geography, geometry, image, ntext, text, timestamp, and user-defined data types.
Demonstration: How To Create a Table By UsingSparse Columns
In this demonstration, you will see how to:
Create a table by using sparse 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
Use persisted computed columns for performance
Guidelines for Using Computed Columns
Use computed columns to derive results from other columns
Usage of Computed Columns
Exceptions in Column
Data
Usage of Persisted Computed Columns
Usage of Persisted Computed Columns
Usage of Computed Columns
Exceptions in Column
Data
Computed Columns
Discussion: Using Computed Columns
• What kind of problems do computed columns solve?
• When is a computed column actually computed?
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
Guidelines for Designing Column Constraints
Use column CHECK Constraints
CONSTRAINT chkQty CHECK (Amount > 0)...)
Declare columns as NOT NULL
(HireDate int NOT NULL...)
Use ANSI default constraints
(CONSTRAINT Qty DEFAULT 0...)
Use CHECK constraints instead of bound rules
Check Constraint
Bound Rules
Guidelines for Designing Table Constraints
Use DRI for data integrity in a database
Use triggers to enforce referential integrity
Use ANSI-standard options
Use table-level CHECK constraints Check Constraint
ON DELETE
Specify cascading levels and optionsSpecify cascading levels and options
Guidelines for Designing Database Constraints byUsing DDL Triggers
Use DDL triggers for auditing Use DDL triggers for auditing
Use DDL triggers to prevent database changes
Use DDL triggers to support security
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üü
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.
1.Time of the day for a 24 hour clock
2.Company name
3.E-mail address
4.Postal code
5.Product information
6.Product descriptive brochure
7.Telephone number
Question:
What are the best options for column data types and data constraints?
Lab 3: Designing a Physical Database Model
• Exercise 1: Specifying Database Object Naming Standards
• Exercise 2: Converting a Logical Database Model into a Physical Database Model
Estimated time: 40 minutes
Logon Information
Virtual machine
User name
Password
NYC-SQL1
Administrator
Pa$$w0rd
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.
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?
Module Review and Takeaways
• Review Questions
• Real-world Issues and Scenarios