Tuesday, March 11, 2008

CHECKSUM as a computed column

Last year I showed using CHECKSUM to compare row differences in a table synchronization script.

If the application is your own however, i.e. you are controlling the schema design, you can add the CHECKSUM as a computed column.

USE Adventureworks
go
ALTER TABLE Person.Contact
ADD RSVersion  AS 
(CHECKSUM(ContactID
,NameStyle
,Title
,FirstName
,MiddleName
,LastName
,Suffix
,EmailAddress
,EmailPromotion
,Phone
,PasswordHash
,PasswordSalt) ) PERSISTED NOT NULL

go

NB :
1) You cannot include a computed column in the defintion for a further computed column.
2) You cannot do CHECKSUM(*) in a computed column definition.
3) You CAN however, add indexes to computed columns :)

Links :
Indexes on Computed Columns: Speed Up Queries, Add Business Rules
SQL Server Computed Columns

No comments: