Constraints
Good practise to have RI in dev but turned off in prod (for perf reason in olap projects)
FK - helps the optimizer
EXEC sp_helpconstraint tablename
FK cascading - take this into account when thinking about deletes and updates.
Unique constraint is NOT a key but could is enforced on a col which could be one. Simply makes sure data is unique.
CHECK constraint - checks the values going in:
ALTER TABLE X ADD CONSTRAINT CN_X CHECK (AGE > 0)
WITH NOCHECK can be used when creating a constraint on a table that already has data you dont care about.
DEFAULT value will be populated into a newly created column (table already has data and new col is added) *ONLY* if NOT NULL is specified.
VIEWS
Views never run as fast as the underlying select query as no optimization is done for them.
UPDATE/INSERT and DELETE statements can be run on views as long as they are single table views. Anything more complex and INSTEAD OF triggers must be used.
If the view is created with a WHERE statement and the WITH CHECK option is on - any data updated or inserted in to the view will be limited to adhere to the where statement.
WITH ENCRYPTION option of the view will hide the underlying select statement. - You WONT be able to get it back so make sure source code is backed up.
WITH SCHEMABINDING option will NOT allow underlying objects to be modified until the view is dropped.
Partition views - Just a view that joins across multiple tables UNION style.
Indexed views 'materialize' the view by creating a clustered index for them After the clustered index is created additional non clustered indexes can be created.
DDL TRIGGERS
New in 2005 - Will fire upon meta data changes on either a server or database wide level.
Good for auditting changes - possibly on the quite.