SQL Server Coding Standards: Best practices for INSERT Query

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Good Indentation makes queries more readable and easy to debug. Try to break the queries into different lines by different query clauses like INSERT and VALUES or INSERT… SELECT.

Wherever possible, place targeted column names in separate lines one level indented to the right side
enclosed with the left parenthesis and right parenthesis.

If a column is not the first column in the list, place comma (,) separator before the name of a column. Apply same formatting convention to VALUES clause also.

This helps queries easier to read, debug and maintain. While debugging a query, anytime any line containing a column name can be easily commented out or uncommented as per the need.

Following is an example of properly indented code for INSERT statement:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

INSERT INTO [dbo].[tbl_Customers] ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) VALUES ( 'CUST001' ,'SomeFirstName' ,'SomeLastName' ,'19820101' ,'SomeEmailAddress@domain.com' ,'M' )

While writing INSERT statements, always specify explicitly the list of targeted columns for which the values are supplied with the VALUES clause. Do not rely on implicit order of the columns in table.

This can break a query if some field is added to or deleted from the table. Thus, instead of writing an insert query without specifying the list of targeted columns as shown below:

1 2 3 4 5 6 7 8 9 10

INSERT INTO [dbo].[tbl_Customers] VALUES ( 'CUST001' ,'SomeFirstName' ,'SomeLastName' ,'19820101' ,'SomeEmailAddress@domain.com' ,'M' )

Write the above query by explicitly specifying the list of column names within a pair of parenthesis in INSERT statement as shown below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

INSERT INTO [dbo].[tbl_Customers] ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) VALUES ( 'CUST001' ,'SomeFirstName' ,'SomeLastName' ,'19820101' ,'SomeEmailAddress@domain.com' ,'M' )

When inserting multiple records with multiple INSERT statements as shown below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

INSERT INTO [dbo].[tbl_Customers] ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) VALUES ( 'CUST001' ,'SomeFirstName' ,'SomeLastName' ,'19820101' ,'SomeEmailAddress@domain.com' ,'M' ) INSERT INTO [dbo].[tbl_Customers] ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) VALUES ( 'CUST002' ,'SomeAnotherFirstName' ,'SomeAnotherLastName' ,'19820101' ,'SomeAnotherEmailAddress@domain.com' ,'F' )

Instead re-write the same query by replacing multiple INSERT statements with single INSERT statement and by specifying values for multiple rows with VALUES clause as shown below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

INSERT INTO [dbo].[tblCustomers] ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) VALUES ( 'CUST001' ,'SomeFirstName1' ,'SomeLastName1' ,'19820101' ,'SomeEmailAddress1@domain.com' ,'M' ) ,( 'CUST002' ,'SomeFirstName2' ,'SomeLastName2' ,'19820101' ,'SomeEmailAddress2@domain.com' ,'F' ) ,( 'CUST003' ,'SomeFirstName3' ,'SomeLastName3' ,'19820101' ,'SomeEmailAddress3@domain.com' ,'M' )

This is the new feature of SQL Server 2008 called row constructor that allows us to specify values for multiple records in a single INSERT statement which increases code readability and manageability.

When it is required to return newly inserted records to the calling application, consider using OUTPUT clause in the INSERT statement rather than using two separate INSERT and SELECT statements to retrieve ‘just-inserted’ records as shown below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

INSERT INTO [dbo].[tbl_Customers] ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) OUTPUT inserted.CustomerID ,inserted.FirstName ,inserted.LastName ,inserted.BirthDate ,inserted.EmailAddress ,inserted.Gender VALUES ( 'CUST006' ,'SomeFirstName1' ,'SomeLastName1' ,'19820101' ,'SomeEmailAddress1@domain.com' ,'M' )