Database Standards:- Refered (CMS SQL Server Standards and Guidelines)

1.0. Databases, File and File Path

• The Database name should accurately reflect the database content and its function. 
• All database name must be prefixed with originating component`s acronym .
• Example:-
• CBC_Medicare_Compare
• EDG_Database_Tracking
• Do not use space in the database name.
• File names must match the database name
• If the database is being developed off site from CMS's central office, please create your databases in the default Microsoft directory structure.
• Example:-
• C:\Program Files\Microsoft SQL Server\ MSSQL\data\OIS_Personnel_Master_Data.MDF

2.0. Tables and Views

• Table names should accurately reflect the table's content and function.
•  Do not use spaces in the name.
• View names follow the same conventions as table names, but should be prefixed with the literal 'VW'.
• Example:-
• vw1999NewRegulations
• It is better to have a Database prefix at the beginning of table.Then developer can easily recognize the table from which database. This will apply if and only is one application deal with many databases.
• Example:-
• CMB_Employee(This is the Employee table in CBC_Medicare_Compare database) 

3.0. Columns

• Each column name must be unique within its table.
• Each column name must be derived from the business name identified during the business/data analysis process. For more information on deriving column names from business names, see Creating Physical Names for Elements and Columns in the Data Administration standards. If the column was not identified during the analysis of business data, it must still be given a spelled-out logical name and an abbreviated physical name.
• Do not use reserved or key words as object names.
• The name can have a maximum of 18 characters.
• When naming of the column also better to have first 3 letters of the table as prefix. this will avoid long column names as well as increase readability of the columns also.
• Example:- 
• "Emp_Id" instead of "EmployeeId"  

4.0. Indexes

• Primary keys have a suffix of '_PK'.
• Foreign keys have a suffix of '_FKx' where x is a number that is incrementally assigned.
• Clustered indexes have a suffix of '_IDX'.
• All other indexes have a suffix of '_NDXx' where x is incrementally assigned.

Only one suffix per index may be appended. The application of the appropriate suffix should follow the following hierarchy: primary key, clustered index, foreign key, other index. E.g., an index that is both a primary key and clustered should have a suffix of '_PK'. It is good practice to index columns that are frequently used in a query's selection criteria.

• But Unique Key used suffix of '_UK'. this will increased the readability.

5.0. Stored Procedures

• System level stored procedures are named using a prefix 'SP__' (two underscores) and a description of what the stored procedure does.
• All application level and user defined stored procedures are prefixed with the constant 'Usp' with a description of what the stored procedure does.
• Example:-
•  UspGetLastModifiedDate
• I would like to suggest, when naming stored procedure, better to have a table prefix just after the 'Usp' then developer can identify the correct stored procedure without opening it.
• Example:-
• UspEmp_GetLastModifiedDate(this procedure is belongs to Employee table)
• UspPro_GetLastModifiedDate(this procedure is belongs to Project table) 

6.0. Triggers

• Triggers are named to indicate the table they are for and the type of trigger. The purpose of the trigger is identified in the prefix to the name.
• All triggers should be prefixed with the letter 'T', a letter(s) designating the type, an underscore, and the table name. The type should be designated as 'I' = insert, 'U' = update, 'D' = delete.
• Example:- 
• ti_Orders (Insert trigger)

7.0. Variables

• The base, which describes the content of the variable.
• The prefix, which describes the datatype of the variable.

Datatype	Prefix	Example
Char	chr	@chrFirstName
Varchar	chv	@chvActivity
Nchar	chrn	@chrnLastName
Nvarchar	chvn	@chvnLastName
Text	txt	@txtNote
Ntext	txtn	@txtnComment
Datetime	dtm	@dtmTargetDate
Smalldatetime	dts	@dtsCompletedDate
Tinyint	iny	@inyActivityID
Smallint	ins	@insEquipmentTypeID
Integer	int	@intAsset
Bigint	inb	@inbGTIN
Numeric or Decimal	dec	@decProfit
Real	rea	@reaVelocity
Float	flt	@fltLength
Smallmoney	mns	@mnsCost
Money	mny	@mnyPrice
Binary	bin	@binPath
Varbinary	biv	@bivContract
Image	img	@imgLogo
Bit	bit	@bitOperational
Timestamp	tsp	@tspOrderID
Uniqueidentifier	guid	@guidPrice
sql_variant	var	@varInventory
Cursor	cur	@curInventory
Table	tbl	@tblLease