Professional SQL Server™ 2005 Programming

5.9. Choosing What to Use

Wow. Here you are with all these choices. How do you figure out which is the right one to use? Some of the constraints are fairly independent (PRIMARY and FOREIGN KEYs, UNIQUE constraints). You are using either them or nothing. The rest have some level of overlap with each other and it can be confusing to decide which to use. You got some hints from me while going through this chapter about what some of the strengths and weaknesses are of each of the options, but it will probably make a lot more sense if you look at them all together in the following table.

Restriction	Pros	Cons
Constraints	Fast.	Must be redefined for each table.
	Can reference other columns.	Can't reference other tables.
	Happens before the command occurs.	Can't be bound to data types.
	ANSI compliant.
Rules, Defaults	Independent objects.	Slightly slower.
	Reusable.	Can't reference across columns.
	Can be bound to data types.	Can't reference other tables.
	Happens before the command occurs.	Really meant only for backward compatibility.
Triggers	Ultimate flexibility.	Happens after the command occurs.
	Can reference other columns and other tables.	High overhead.
	Can even use .NET to reference information that is external to your SQL Server.

The main time to use rules and defaults is when you are implementing a rather robust logical model and are making extensive use of user-defined data types. In this instance, rules and defaults can provide a lot of functionality and ease of management without much programmatic ...