Software Development
SQL Server Naming Standards
 The
goal of a naming standard for database objects is to easily identify the type and
purpose of the objects in the database. The naming convention should enable practical,
legible, concise, unambiguous and consistent names to be created for database objects.
Whatever rules are adopted, consistency in applying the conventions is important.
This will save time and effort.
General Naming Rules
- The name should be short, easy to read and not more than 30 characters.
- The name should be unambiguous, using no more than one or two words
- Use only letters. Avoid numbers and spaces.
- Minimise the use of underscores – use only if it is easier to read. Do not start
with an underscore.
- Use Camel Case to separate words.
- Use only well accepted abbreviations and acronyms.
- SQL keywords should be in upper case.
Tables
A prefix of "tbl" should be used for each Table. The Tables used in procedures,
triggers, views, etc will be easily recognisable. This also makes for consistency
between SQL Server and Microsoft Access databases.
Table names should always be singular – use "tblCustomer" instead of "tblCustomers".
Where tables share a schema/database with unrelated tables, the table names should
have an additional prefix. For example, a Client table of "My Trading Company" could
have a prefix of "tblMtcClient". All the tables for that application will appear
together in listings.
Junction tables of many to many relationships should concatenate the names of the
tables. "tblDoctor" and "tblPatient" tables should be named tblDoctorPatient".
Columns
The primary key Column should include the name of the Table and "ID", for example
"CustomerID". Foreign key Columns should have the same name, as the primary key
of the parent table. For example, a foreign key of "ClientID" will be the same as
the name of primary key for the tblClient table. Multiple Foreign keys may need
a descriptor.
- Column names do not need a prefix.
- Boolean Columns should be given names like "IsDeleted", "HasPermission", or "IsValid".
The meaning should be unambiguous.
- Date/ Time Column names should have the word "Date" appended.
- Avoid Generic Column Names like Status, Group and Type. These should have an identifying
noun – for example ProductName, ProductCode, ProductDescription.
- Many tables need a "LoadDate" Column. Use the same name in all tables.
Indexes
The index name should have a prefix of "idx", followed by the Table name and Column
Name. The index name could be followed by "U" (unique) or "C" (clustered). For example
an index on ProductID of the Product Table could be called, "idx_Product_ProductID_U".
Constraints
A two letter prefix identifies the constraint type:
- Primary Key: pk
- Foreign Key: fk
- Check: ck
- Unique: un
This is followed by the Table and Column names, for example fkProductState.
Views
The view should have a prefix of "view". This should be followed by the Table name
or purpose of the View.
Stored Procedures
Use a prefix of "usp" (User Stored Procedure) or three letters to describe the application.
Do not use a prefix of "sp_". The prefix should be followed by the name of the Table
and a verb describing the operation performed – Create, Read, Update, and Delete.
Triggers
Use a prefix of "trg" or three letters to describe the application. The prefix should
be followed by the name of the Table and a verb describing the operation executed
– Insert, Update, or Delete. If the trigger handles more than one operation, include
both operations in the name.
|