tailieunhanh - Microsoft SQL Server 2008 R2 Unleashed- P126

Microsoft SQL Server 2008 R2 Unleashed- P126:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1194 CHAPTER 34 Data Structures Indexes and Performance In addition the functions in the computed column must be deterministic. A deterministic function is one that returns the same result every time it is called with the same set of input parameters. When you create a clustered index on a computed column it is no longer a virtual column in the table. The computed value for the column is stored in the data rows of the table. If you create a nonclustered index on a computed column the computed value is stored in the nonclustered index rows but not in the data rows unless you also have a clustered index on the computed column. Be aware of the overhead involved with indexes on computed columns. Updates to the columns that the computed columns are based on result in updates to the index on the computed column as well. Indexes on computed columns can be useful when you need an index on large character fields. As discussed earlier the smaller an index the more efficient it is. You could create a computed column on the large character field by using the CHECKSUM function. CHECKSUM generates a 4-byte integer that is relatively unique for character strings but not absolutely unique. Different character strings can generate the same checksum so when searching against the checksum you need to include the character string as an additional search argument to ensure that you are matching the right row. The benefit is that you can create an index on the 4-byte integer generated by the CHECKSUM that can be used to search against the character string instead of having to create an index on the large character column itself. Listing shows an example of applying this solution. LISTING Using an Index on a Computed Checksum Column --The first statement is used to disable any previously created --DDL triggers in the database which would prevent creating a new constraint. DISABLE TRIGGER ALL ON DATABASE go -- First add the computed column to the table alter table titles add .