• Tweet

• Tweet

SQL Database Coding Standards with C++

1. Data Types to be used:

We should use only following data types:

1. INT
2. BIGINT
3. SMALLINT
4. VARCHAR
5. BIT
6. DATETIME
7. Give default value for BIT datatype. It should not be nullable
8. Table and Column names should never be in lower case. It should describe its purpose. Avoid using short forms.
9. While creating a table FK and PK to be well thought and defined.
10. Variables names should start with one/two letter indicating its data type in lower case.

For e.g. INT variable should begin with i.  Name should be descriptive and short forms should be avoided. It each word should start with capital letter followed by all small letters.

E.g.

Correct way: – iTotalCount

Incorrect way: – xyz

6. Table columns used with “WHERE” clause inside stored procedures should be indexed/keyed. This will increase the speed of data processing.
7. Ordering of parameters in WHERE clause should be done properly. Primary key/index should precede bit variables.

E.g.:- An index is created on combination of columns (REF_ID, T_TYPE_STR, CNUMBER, TLOG_ID)

— Correct way where Indexed keys are used in sequence in ‘WHERE’ clause

SELECT REF_ID,T_TYPE_STR,C_NUMBER,TLOG_ID

FROM T_T_DATA_tbl

WHERE REF_ID = 1

AND LOG_ID = ‘4042654’

AND T_TYPE_STR = ‘SA’

AND CNUMBER = ‘10702’

–Incorrect way

SELECT REF_ID, T_TYPE_STR, CNUMBER, LOG_ID

FROM T_T_DATA_tbl

WHERE LOG_ID = ‘4042654’

AND T_TYPE_STR = ‘SA’

8. While writing a stored procedure we should have description section at the beginning which will contain

Author:

Creation date:

Description:

If any sp is modified this section should be appended with

Modified by:

Modified on:

Description:

9. ROW_INSERTION_DATE_TIME AND ROW_UPDATION_DATE_TIME column should have default values as GETDATE().

10. Ensure ROW_UPDATION_DATE_TIME is updated if any update is done on the given row.

11. ROW_ACTION_COUNT should have default value as 1.

12. ROW_ACTION_COUNT can be modified in update query itself. We don’t need to select its value in separate query.

13. IF EXISTS should be used instead of SELECT COUNT to find if a row is already present. Because IF EXISTS is specially designed for checking if something exists or not. So, in this case it’s better than COUNT (*).

E.g.:-

IF EXISTS (SELECT 1 FROM Products WHERE id = ”)

BEGIN

–do what you need if exists

END

• Do not use COUNT (1)/ COUNT (*) in SELECT statement insideif exists/not exists condition :

–Incorrect way

IF EXISTS   (

SELECT COUNT (1)

FROM FILE_RECORD_DETAILS_TBL

WHERE SEQUENCE_NUMBER = 0

)

BEGIN

SELECT ‘1’

END

ELSE

BEGIN

SELECT ‘2’

END

–Correct way

IF EXISTS (SELECT 1 FROM RECORD_DETAILS_TBL WHERE SEQUENCE_NUMBER = 0)

BEGIN

SELECT ‘1’

END

ELSE

BEGIN

SELECT ‘2’

END

14. ISNULL should be used while selecting aNULLABLE column and default value to be selected needs to be provided in case column is NULL.

SELECT  ISNULL(XYZ_ID,0) AS XYZ_ID

15. While writing any sp for fetching record set proper ALIAS should be used i.e.

Instead of writing    SELECT  M_ID, S_ID FROM ……

–correct way

SELECT  PMT.M_ID AS M_ID,

PST.S_ID AS STORE_ID

FROM M_TBL PMT INNER JOIN S_TBL PST

on PMT.M_ID = PST.M_ID

–incorrect way

SELECT  A.M_ID AS Column1 ,

A1.S_ID AS Column2

FROM M_TBL A INNER JOIN S_TBL A1

on A.M_ID = A1.M_ID

16. Use of database functions such as LTRIM, RTRIM, CAST should be avoided. Using any type of function (system or user defined) in WHERE or JOIN clause can decrease query performance because this practice create hurdles in query optimizer work of proper index selection.

For better understanding on this point, check the execution plan

—-Incorrect way

SELECT CNUMBER,B_ID FROM T_T_DATA_tbl

WHERE LTRIM(CNUMBER) = ‘10702’

AND B_ID  = ‘9002’

—-Correct way

SELECT CNUMBER,B_ID FROM T_T_DATA_tbl

WHERE CNUMBER = 10702

AND B_ID  = 9002

17. SET NOCOUNT ON should be used.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

18. @@identity should not be used as it gives ID of last inserted row and may cause problem if some trigger is present on the table. Instead use suitable select statement.
19. Multiple update queries within same stored procedure should be with a TRANSACTION. This should be done to avoid consequences in case of failure of one of the queries.

—- Correct way

BEGIN

BEGIN TRY

BEGIN TRANSACTION

DECLARE @lOldID BIGINT = 0

SELECT TOP 1  @lOldID = VELOCITY_CHECK_SCHEME_ROW_ID

FROM V_CHECK_S_DEFINITION_TBL

WHERE S_DISPLAY_NAME = ”

UPDATE V_R_S_MAPPING_TBL

SET IS_DELETED = 1,

ROW_UPDATION_DATE_TIME = GETDATE()

WHERE R_ID = ” AND S_ROW_ID = ”;

UPDATE T_T_DATA_tbl

SET ROW_UPDATION_DATE_TIME = GETDATE()

WHERE CNUMBER = ”;

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

END

—- Incorrect way

BEGIN

DECLARE @lOldID BIGINT = 0

SELECT TOP 1  @lOldID = V_CHECK_S_ROW_ID FROM V_CHECK_S_DEFINITION_TBL

WHERE S_DISPLAY_NAME = ”

UPDATE V_R_S_MAPPING_TBL

SET IS_DELETED = 1,

ROW_UPDATION_DATE_TIME = GETDATE(),

WHERE R_ID = ” AND S_ROW_ID = ”;

UPDATE T_T_DATA_tbl

SET ROW_UPDATION_DATE_TIME = GETDATE()

WHERE CNUMBER = ”;

END

20. JOINS should be on KEYS/INDEXES. (Same as #Point 7.)
21. RFU column should be avoidedin server application and columns can be added.
22. SELECT query shouldnot use WITH NOLOCK.

Benefit of the WITH NOLOCK is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance. The downside is that, because the statement does not issue any locks against the tables being read, some “dirty,” uncommitted data could be read.

23. No objectsshould be accessed using DB instance name. (P_HDB or AUDB).
24. Do not use ‘select all columns (SELECT *)’ option; use only specific columns to query the result. It returned more columns than you actually needed. This is especially true when new columns are added to underlying tables that didn’t exist and weren’t needed when the original code was written. It may hamper query performance.
25. Avoid the nested IF statements and use CASE statement. It will execute matching part immediately. ‘CASE’ statement will make the code more readable.
26. Avoid dynamic SQL statements as much as possible. Avoiding dynamic SQL may provide an abstract layer of security to database. E.g.:-

DECLARE @Table_Name VARCHAR(500)

SET @Table_name = ‘temp; Drop table temp;’

–Incorrect value may be passed. This is called SQL INJECTION.

EXEC( ‘SELECT top 10 * FROM ‘ + @table_name )

27. Avoid using <> as a comparison operator. Ex: Use ID IN (1,3,4,5) instead of ID<> 2.
28. Use BEGIN-END code block: Try to bind your whole SP code within BEGIN-END block. Although it is optional but it looks good and gives a better picture.
29. PARAMETER SNIFFING: Do not use SP parameters directly within the WHERE clause of SQL statements. This may cause the case of Parameter Sniffing. To avoid this, assign the parameter values to local variables and then uses them within SQL queries.

–Correct way

CREATE PROC [dbo].[DisplayBInfo] @BeginDate DATETIME, @EndDate DATETIME

AS

BEGIN

DECLARE @Begin_Date DATETIME,@End_Date DATETIME;

SET @Begin_Date = @BeginDate;

SET @End_Date = @EndDate;

SELECT BDate, BAmt

FROM BInfo

WHERE BDate between @Begin_Date AND @End_Date;

END

–Incorrect way

CREATE PROC [dbo].[DisplayBInfo] @BeginDate DATETIME, @EndDate DATETIME

AS

BEGIN

SELECT BDate, BAmt

FROM BInfo WHERE BDate between @BeginDate AND @EndDate;

END

Use below link for better clarification: –

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

30. Aliasing objects: Do not use SQL Server reserve keywords while naming SQL Server objects, like for: tables, columns, views, etc. Although it allows most of the time, but it’s better to use other and better naming conventions.

31. Avoid ‘sp_’ prefix : Do not prefix the SP name with ‘sp_’. If it begins with ‘sp_’ then the compiler will first search it in master database and then in the current one, thus delaying the execution.

32. Use fully qualified objects name: In your SQL queries, this helps in quickly finding the cached/compiled plan. Also execute the SP by using its fully qualified name, like: EXEC dbo.SPname.
33. Use table variables: Try to use table variables instead of temporary tables inside the SP to cache small record sets whenever possible. Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better
34. Use of Temp Tables: If you think that the temporary record set may exceed up to millions of rows, then try using temporary table instead of table variables. Also try creating indexes on temporary tables if required.

1. The major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table-variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution.

1. Logs are not maintained for temporary variables/tables, these are session specific. So, no/less memory is required.

• TRUNCATE TABLE is not to be used in Stored procedures as production DB has replication set up. Ref:

https://msdn.microsoft.com/en-IN/library/ms177570.aspx

You cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
• Participate in an indexed view.
• Are published by using transactional replication or merge replication.