https://blog.sqlauthority.com/2008/09/23/sql-server-coding-standards-guidelines-part-1/
SQL SERVER – Guidelines and Coding Standards Part – 1
- Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.
Example:
UserDetails
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
- Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)
- Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECTstatement.
Example:
1
2
| SELECT Username, PasswordFROM UserDetails |
- Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)
- Properly format SQL queries using indents.
Example: Wrong Format
1
| SELECT Username, Password FROM UserDetails ud INNER JOIN Employee e ON e.EmpID = ud.UserID |
Example: Correct Format
1
2
3
| SELECT Username, PasswordFROM UserDetails udINNER JOIN Employee e ON e.EmpID = ud.UserID |
- Practice writing Upper Case for all SQL keywords.
Example:
SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.
- It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.
- If “One Table” references “Another Table” than the column name used in reference should use the following rule :
Column of Another Table : <OneTableName> ID
Example:
If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.
- Columns with Default value constraint should not allow NULLs.
- Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
- Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
- Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it next suggestion.
- Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERTstatement. (Read More Here)
1
2
3
4
| INSERT INTO TABLE A (column1, column2)SELECT column1, column2FROM TABLE BWHERE .... |
- Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.
Example:
[Order Details]- Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)
- Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.
- Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
- Indent code for better readability. (Example)
- While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)
- Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)
- Avoid using rules of database objects instead use constraints.
- Do not use the RECOMPILE option for Stored Procedure unless there is specific requirements.
- Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)
- Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure. (Example)
https://blog.sqlauthority.com/2008/09/24/sql-server-coding-standards-guidelines-part-2/
SQL SERVER – Guidelines and Coding Standards Part – 2
- To express apostrophe within a string, nest single quotes (two single quotes).
Example:
SET @sExample = 'SQL''s Authority'- When working with branch conditions or complicated expressions, use parenthesis to increase readability.
IF ((SELECT 1FROM TableNameWHERE 1=2) ISNULL)- To mark single line as comment use (–) before statement. To mark section of code as comment use (/*…*/).
- If there is no need of resultset then use syntax that doesn’t return a resultset.
IF EXISTS (SELECT 1FROM UserDetailsWHERE UserID = 50)- Rather than,
IF EXISTS (SELECT COUNT (UserID)FROM UserDetailsWHERE UserID = 50)- Use graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze SQL queries. Your queries should do an “Index Seek” instead of an “Index Scan” or a “Table Scan”. (Read More Here)
- Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures.
Example:
SP<App Name>_ [<Group Name >_] <Action><table/logical instance> - Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to repeat those joins/calculations in all your queries. Instead, just select from the view. (Read More Here)
- Do not query / manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure.
- Avoid using ntext, text, and image data types in new development work. Use nvarchar (max), varchar (max), and varbinary (max) instead.
- Do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual file stored on a server.
- Use the CHAR datatype for a non-nullable column, as it will be the fixed length column, NULL value will also block the defined bytes.
- Avoid using dynamic SQL statements if you can write T-SQL code without using them.
- Minimize the use of Nulls. Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values
- Use Unicode datatypes, like NCHAR, NVARCHAR or NTEXT if it needed, as they use twice as much space as non-Unicode datatypes.
- Always use column list in INSERT statements of SQL queries. This will avoid problem when table structure changes.
- Perform all referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validations that cannot be performed using constraints.
- Always access tables in the same order in all stored procedure and triggers consistently. This will avoid deadlocks. (Read More Here)
- Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
- With Begin and End Transaction always use global variable @@ERROR, immediately after data manipulation statements (INSERT/UPDATE/DELETE), so that if there is an Error the transaction can be rollback.
- Excessive usage of GOTO can lead to hard-to-read and understand code.
- Do not use column numbers in the ORDER BY clause; it will reduce the readability of SQL query.
Example: Wrong StatementSELECT UserID, UserName, PasswordFROM UserDetailsORDER BY 2
Example: Correct Statement
SELECT UserID, UserName, PasswordFROM UserDetailsORDER BY UserName- The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.
- If stored procedure always returns single row resultset, then consider returning the resultset using OUTPUTparameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than resultsets returned by SELECT statements.
- Effective indexes are one of the best ways to improve performance in a database application.
- BULK INSERT command helps to import a data file into a database table or view in a user‐specified format.
- Use Policy Management to make or define and enforce your own policies fro configuring and managing SQL Server across the enterprise, eg. Policy that Prefixes for stored procedures should be sp.
- Use sparse columns to reduce the space requirements for null values. (Read More Here)
- Use MERGE Statement to implement multiple DML operations instead of writing separate INSERT, UPDATE, DELETE statements.
- When some particular records are retrieved frequently, apply Filtered Index to improve query performace, faster retrieval and reduce index maintenance costs.
- EXCEPT or NOT EXIST clause can be used in place of LEFT JOIN or NOT IN for better peformance.
Example:
SELECT EmpNo, EmpNameFROM EmployeeRecordWHERE Salary > 1000 AND SalaryNOT IN (SELECT SalaryFROM EmployeeRecordWHERE Salary > 2000);- (Recomended)
SELECT EmpNo, EmpNameFROM EmployeeRecordWHERE Salery > 1000EXCEPT
SELECT EmpNo, EmpNameFROM EmployeeRecordWHERE Salery > 2000ORDER BY EmpName;
https://blog.sqlauthority.com/2007/06/05/sql-server-database-coding-standards-and-guidelines-part-2/SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL Server Database Coding Standards and Guidelines – Part 2
Coding Standards
- Optimize queries using the tools provided by SQL Server5
- Do not use SELECT *
- Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server
- Avoid unnecessary use of temporary tables
- Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better6
- Avoid using <> as a comparison operator
- Use ID IN(1,3,4,5) instead of ID <> 2
- Use SET NOCOUNT ON at the beginning of stored procedures7
- Do not use cursors or application loops to do inserts8
- Instead, use INSERT INTO
- Fully qualify tables and column names in JOINs
- Fully qualify all stored procedure and table references in stored procedures.
- Do not define default values for parameters.
- If a default is needed, the front end will supply the value.
- Do not use the RECOMPILE option for stored procedures.
- Place all DECLARE statements before any other code in the procedure.
- Do not use column numbers in the ORDER BY clause.
- Do not use GOTO.
- Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs
- Or use TRY/CATCH
- Do basic validations in the front-end itself during data entry
- Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server
- Always use a column list in your INSERT statements.
- This helps avoid problems when the table structure changes (like adding or dropping a column).
- Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.
- Any expression that deals with NULL results in a NULL output.
- The ISNULL and COALESCE functions are helpful in dealing with NULL values.
- Do not use the identitycol or rowguidcol.
- Avoid the use of cross joins, if possible.
- When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
- Avoid using TEXT or NTEXT datatypes for storing large textual data.9
- Use the maximum allowed characters of VARCHAR instead
- Avoid dynamic SQL statements as much as possible.10
- Access tables in the same order in your stored procedures and triggers consistently.11
- Do not call functions repeatedly within your stored procedures, triggers, functions and batches.12
- Default constraints must be defined at the column level.
- Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.
- Define all constraints, other than defaults, at the table level.
- When a result set is not needed, use syntax that does not return a result set.13
- Avoid rules, database level defaults that must be bound or user-defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.
- Constraints that apply to more than one column must be defined at the table level.
- Use the CHAR data type for a column only when the column is non-nullable.14
- Do not use white space in identifiers.
- The RETURN statement is meant for returning the execution status only, but not data.
Reference:
5) Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do an “Index seek” instead of an “Index scan” or a “Table scan.” A table scan or an index scan is a highly undesirable and should be avoided where possible.
6) Consider the following query to find the second highest offer price from the Items table:
1
2
3
4
5
6
7
8
| SELECT MAX(Price)FROM ProductsWHERE ID IN(SELECT TOP 2 IDFROM ProductsORDER BY Price DESC) |
The same query can be re-written using a derived table, as shown below, and it performs generally twice as fast as the above query:
1
2
3
4
5
6
7
| SELECT MAX(Price)FROM(SELECT TOP 2 PriceFROM ProductsORDER BY Price DESC) |
7) This suppresses messages like ‘(1 row(s) affected)’ after executing INSERT, UPDATE, DELETE and SELECT statements. Performance is improved due to the reduction of network traffic.
8) Try to avoid server side cursors as much as possible. Always stick to a ‘set-based approach’ instead of a ‘procedural approach’ for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead. If a cursor is unavoidable, use a WHILE loop instead. For a WHILE loop to replace a cursor, however, you need a column (primary key or unique key) to identify each row uniquely.
9) You cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. So, if you don’t have to store more than 8KB of text, use the CHAR(8000) or VARCHAR(8000) datatype instead.
10) Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan at runtime. IF and CASE statements come in handy to avoid dynamic SQL.
11) This helps to avoid deadlocks. Other things to keep in mind to avoid deadlocks are:
- Keep transactions as short as possible.
- Touch the minimum amount of data possible during a transaction.
- Never wait for user input in the middle of a transaction.
- Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed.
12) You might need the length of a string variable in many places of your procedure, but don’t call the LEN function whenever it’s needed. Instead, call the LEN function once and store the result in a variable for later use.
13)
1
2
3
4
| IF EXISTS ( SELECT 1 FROM Products WHERE ID = 50) |
Instead Of:
1
2
3
4
| IF EXISTS ( SELECT COUNT(ID) FROM Products WHERE ID = 50) |
14) CHAR(100), when NULL, will consume 100 bytes, resulting in space wastage. Preferably, use VARCHAR(100) in this situation. Variable-length columns have very little processing overhead compared with fixed-length columns.
Complete Series of Database Coding Standards and Guidelines
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download
