Database CreationDuring the physical implementation discussed in Chapter 3, important issues with regard to appropriate use of the CREATE DATABASE statement are as follows :
Shrinking FilesYou 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 PropertiesA complete list of potential column properties (see Chapter 3) is as follows:
Check ConstraintsA 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 IndexingThe 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 IndexingNonclustered 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 DefinitionsData 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 BindingSchema 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 ViewsTo 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:
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:
Data Integrity OptionsTable 1 lists the data integrity options. Table 1. Maintaining Data Integrity
|