Show Menu
Cheatography

Sol T-SQL Coding Standards Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

General

Always use lower case
Use consistent and descri­ptive identi­fiers and names.
first_­name, address_1, contact_id
Include comments in SQL code where necessary. Use the C style opening /* and closing */
/*this is a comment*/
Names must begin with a letter and may not end with an unders­core.
Avoid the use of multiple consec­utive unders­cor­es—­these can be hard to read
Use unders­cores where you would naturally include a space in the name
ie first_name not firstName, contact_id not contactId

Column Naming Conven­tions

Always use the singular name
first_name not firstNames
Always use lowercase
Do not add a column with the same name as its table and vice versa
Always add a identity column to the table and make it a primary key
order_id int identi­ty(1,1)

Common Column Names

part_no
first_­name, last_name
address_1, address_2
postal­_code
order_id, cart_id

Query Syntax

Always use uppercase for the reserved keywords like SELECT and WHERE
To make the code easier to read it is important that the correct compliment of spacing is used. Do not crowd code or remove natural language spaces.
Spaces should be used to line up the code so that the root keywords all end on the same character boundary
Although not exhaustive always include spaces before and after equals (=), after commas (,), surrou­nding apostr­ophes (') where not within parent­heses or with a trailing comma or semicolon.
 

Table Naming Conver­tions

Always use the singlar
st_contact not st_con­tacts,
Use unders­cores where you would naturally include a space
ex_ord­er_­detail not exOrde­rDetail
A two or three character table identifier maybe used, if the database has idente­fiers stick with the naming convention
Never give a table the same name as one of its columns and vice versa.
When concat­enating two tables, use a table name that approp­riately reflects the relati­onship
orders to users relati­onship would involve order, user, and user_order tables

Table Prefixes

ex_
extra - This table contains specific data to the customer of the database is it's functi­onality is not repeated in other databases
hst_
history - This table is contains historic data that is used for reporting
ld_
load - This table is used with TDI to load data into the system. It's data is temporary and supplied by a customer
sb_
smartbuy - This table contains infomr­ation related to accounts, branches or carts or transa­ction
sc_
smartcat - This table contains data related to the smartcat catalog
st_
smartsite - This table contains inform­ation related to logins or contact details
tmp_
temporary - This table contains temporary data that can be deleted when done
Some database's may have table prefixes, a two or three character code to identify the tables function in the database.

Stored Procedures

The name must contain a verb.
Follow the format of usp_Ac­tio­n_B­usi­nes­sEntity
usp_SE­L_User, usp_IN­S_User, usp_DE­L_User, usp_UP­D_User
Use the prefix USP
usp_ not sp_