How to name DB Objects – Best Practices

• Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.

Example: UserDetails and Emails

• If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.

Example: Page_UserDetails, Page_ Emails

• Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb

Example: spApplicationName_GetUserDetails , spApplicationName_UpdateEmails

• Use following Naming pattern for triggers: TR_<TableName>_<action><description>

Example: TR_Emails_LogEmailChanges, TR_UserDetails_UpdateUserName

• Indexes : IX_<tablename>_<columns separated by_>

Example: IX_UserDetails_UserID

• Primary Key : PK_<tablename>

Example: PK_UserDetails, PK_ Emails

• Foreign Key : FK_<tablename_1>_<tablename_2>

Example: FK_UserDetails_Emails

• Default: DF_<table name>_<column name>

Example: DF_ UserDetails _UserName

Precedence order for data types

SQL Server uses the following precedence order for data types:

Be careful while you define a DATA TYPE in sql. a little knowledge can make your query bit fast and optimized  

• user-defined data types (highest)
• sql_variant
• xml
• datetimeoffset
• datetime2
• datetime
• smalldatetime
• date
• time
• float
• real
• decimal
• money
• smallmoney
• bigint
• int
• smallint
• tinyint
• bit
• ntext
• text
• image
• timestamp
• uniqueidentifier
• nvarchar (including nvarchar(max) )
• nchar
• varchar (including varchar(max) )
• char
• varbinary (including varbinary(max) )
• binary (lowest)

Welcome to the world of SQL

Hi folks,

This blog dedicated to help my friends who are new to SQL and want to know the facts about SQL. So if you have any query/question on SQL server please post the same on the blog or mail me on sqlfacts@gmail.com. I will post the answer on the blog.

Thanks,