Visual Basic Programmer

All about SQL Server

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.