SQL Server - What coding/development rules does your team have in place?

Question

I asked a similar question last week and didn't get a very good response, so perhaps I didn't phrase the question in the right way.

I'd like to know what processes/policies/rules your team has in place for writing T-SQL code and database schema. Here are a couple examples:

1) All foreign key columns should be indexed
2) All primary key columns should be integer Identity
3) All stored procedures/user defined functions need comments
4) No underscores in T-SQL variable names

These are the sorts of things I'm curious about.

Thanks!

Answer 1

I disagree with all foreign keys being indexed. If you have a foreign key to a master table of only 3 options, in a table with millions of rows, you have a massive overhead of pointless indexes. A reverse lookup from the key to the data is unlikely.

Answer 2

It seems like #1, #2 and #3 are all pretty poor rules. It is not always necessary to index a foreign key. It doesn't make sense to create a surrogate key when a good natural key exists. Comments don't compile so they get out of sync with code quite easily and can fool the reader. They should only be used when absolutely necessary. Commenting to fulfill a hard and fast rule is worse than a waste of time.

Item #4 is pretty good. Teams I have worked on have been more concerned with things that go to the quality of the implementation (e.g. good error handling in all stored procedures, and Stored procedures do only one thing)