Physical Database Design and Implementation

Database Creation

During the physical implementation discussed in Chapter 3, important issues with regard to appropriate use of the CREATE DATABASE statement are as follows :

• The default growth increment measure is MB but can also be specified with a KB or % suffix. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs.

• A maximum of 32,767 databases can be defined on a server.

• The minimum size for a log file is 512K.

• Each database has an owner. The owner is the user who creates the database. The database owner can be changed through sp_changedbowner .

• The Master database should be backed up after a user database is created.

• The default unit of measure for the size and maxsize settings is MB if you supply a number but no measure is provided. If options are not supplied, maxsize will default to unlimited and file growth will be 10 percent.

Shrinking Files

You can shrink each file within a database to remove unused pages; this applies to both data and log files. It is possible to shrink a database file manually, as a group , or individually. You use the DBCC statement with the SHRINKDATABASE or SHRINKFILE parameters (DBCC parameters will be shown in the Fast Facts section at the end of this text). Use DBCC SHRINKDATABASE to shrink the size of the data files in the specified database, or you can selectively choose a specific file and shrink its size using DBCC SHRINKFILE .

You can set the database to automatically shrink at periodic intervals by right-clicking on the database and selecting the database properties page from within the Enterprise Manager.

Column Properties

A complete list of potential column properties (see Chapter 3) is as follows:

• Column Name . This should be meaningful so as to describe the column content.

• Data Type. Any one of 25 possible definitions provides the basis for the data a column will contain. Choices include several possibilities for each data type.

• Length. For many data types, the length is predetermined. You must, however, specify a length for character, Unicode ( nCHAR ), and binary data. A length must also be specified for variable length data columns . If a char or nCHAR data type is only a single character, then no length has to be defined.

• Allow Nulls. You can provide an indicator for allowing NULL content for any variable except those assigned as primary keys.

• Primary Key. This enforces unique content for a column and can be used to relate other tables. Must contain a unique non- NULL value.

• Description. Provides an explanation of the column for documentation purposes. (This is an extended table property).

• Default Value. Provides a value for a column when not explicitly given during data entry. A default object must be created and then bound to a column, but the preferred technique is to provide the default definition, directly attached to the column in the create/alter table definition. It is defined at the database level and can be utilized by any number of columns in a database.

• Precision. The number of digits in a numeric column.

• Scale. The number of digits to the right of a decimal point in a numeric column.

• Identity. Inserts a value automatically into a column based on seed and increment definitions.

• Identity Seed. Provides the starting value for an Identity column.

• Identity Increment. Defines how an Identity will increase/decrease with each new row added to a table.

• Is RowGuid. Identifies a column that has been defined with the "Unique Identifier" data type as being the column to be used in conjunction with the ROWGUIDCOL function in a SELECT list.

• Formula. Provides a means of obtaining the column content through the use of a function or calculation.

• Collation. Can provide for a different character set/ sort order than other data. (Use with extreme caution, if at all, as it will impair development ability.)

Check Constraints

A Check constraint may be desired to ensure that a value entered meets given criteria based on another value entered. In working with a physical structure, real data constraints are one of the main points of focus in Chapter 3. A table level Check constraint is defined at the bottom of the Alter/Create Table statement, unlike a column Check constraint, which is defined as part of the column definition.

Clustered Indexing

The selection of the appropriate column(s) to base a clustered index on is important for a number of reasons. As previously mentioned, a clustered index represents the order in which the data is physically stored on disk. For this reason, you can define only a single clustered index for any table. If you choose not to use a clustered index in a table, then the data on disk will be stored in a heap. If present, a clustered index will be used by all nonclustered indexes to determine the physical location of the data. Additionally, clustered indexes are particularly useful when a range of data will be queried, such as with the BETWEEN statement.

Nonclustered Indexing

Nonclustered indexes provide a means of retrieving the data from the database in an order other than that in which the data is physically stored. Nonclustered indexes are often used as covering indexes where all the columns specified in the query are contained within the same index.

Indexing is dealt with in a number of chapters in the book and will have equal importance on the exam. The basics of an indexing strategy were introduced in Chapters 2 and 3. Considerable attention was also given to their interaction with views and their affect on performance in Chapters 10 and 12, respectively.

Encryption Can Secure Definitions

Data encryption is a mechanism that can be used to secure data, communications, procedures, and other sensitive information. When encryption techniques are applied, sensitive information is transformed into a non-readable form that must be decrypted to be viewed .

Encryption will slow performance regardless of the method implemented because extra processing cycles are required whenever encryption or decryption occurs.

Schema Binding

Schema binding involves attaching an underlying table definition to a view or user-defined function. With binding, a view or function is connected to the underlying objects. Any attempt to change or remove the objects will fail unless the binding has first been removed. Normally you can create a view, and the underlying table might be changed so that the view no longer works. To prevent the underlying table from being changed, the view can be "schema bound" to the table. Any table changes, which would break the view, are not allowed. Schema binding is a technique that can be used with user-defined functions (see Chapter 9) and views, including indexed views (see Chapter 7).

Indexed Views

To allow for the use of indexed views, a number of session-level options must be set "on" when you create the index. You will need to set NUMERIC_ROUNDABORT "off." Options that need to be set are as follows:

• ANSI_NULLS

• ANSI_PADDING

• ANSI_WARNINGS

• ARITHABORT

• CONCAT_NULL_YIELDS_NULL

• QUOTED_IDENTIFIERS

Other than the specific set of options, nothing more needs to be done for the optimizer to utilize an Index with a query on a view. Essentially, the optimizer handles the View query in the same manner that it would a standard query against a table. The view cannot reference another view, only underlying tables are permitted, and you must create the view with the SCEMABINDING option. The creation of an indexed view is supported only by the Enterprise and Developer Editions.

There are a series of limitations to the content of the SELECT statement for the view definition. They are as follows:

• No use of *

• A column name used as a simple expression cannot be specified in more than one view column.

• No derived tables.

• Rowset functions are not permitted.

• UNION , outer joins, subqueries, or self-joins cannot be used only simple joins.

• No TOP , ORDER BY , COMPUTE , or COMPUTE BY clause.

• DISTINCT is not permitted.

• COUNT(*) cannot be used but COUNT_BIG(*) is allowed.

• Aggregate functions: AVG , MAX , MIN , STDEV , STDEVP , VAR , or VARP .

• A SUM function cannot reference a nullable expression.

• No use of full-text predicates CONTAINS or FREETEXT .

Data Integrity Options

Table 1 lists the data integrity options.

Table 1. Maintaining Data Integrity

Technique	Integrity Achieved	Usage	Timing(Log)
Primary Key	Entity	Identify each row	Before
Foreign Key	Referential/Domain	Ensure no orphan child elements	Before
Unique Index	Entity	Ensure KEY entries are exclusive	Before
Unique Constraint	Entity	No duplicate column values	Before
Identity	Entity	Auto incremented values	Before
Check Constraint	Domain	Ensure correct column entry	Before
Not NULL	Domain	A value must be present	Before
Default	Domain	Provides initial value	Before
Rule	Domain	Ensure correct column entry	Before
Trigger	Referential/Domain	Respond to add, change, delete	After
Stored Procedures	Referential/Domain/Entity	Process controlled operations	Before