Examples

Insert statement

In this example we make sure to

Qualify table names with the schema name.
Specify INSERT columns.

Practices to avoid

    INSERT INTO SubscribingConsultant
    VALUES ('7A5', 'C1234567');

Examples of good practice

    [INSERT INTO dbo.SubscribingConsultant(Organisation, ConsultantId)]
    [VALUES('7A5', 'C1234567\');]

Select statement

In this example we make sure to

Qualify table names with the schema name.
Add a meaningful table alias name.
Use the field name in the ORDER BY clause rather than a constant.

Practices to avoid

    SELECT Report.Id, Report.SubjectGivenName AS FirstName, Report.SubjectFamilyName AS LastName
    FROM Report
    ORDER BY 1 ASC;

Examples of good practice

    SELECT Report.Id, Report.SubjectGivenName AS FirstName, Report.SubjectFamilyName AS LastName
    FROM dbo.Report AS Report
    ORDER BY Report.Id ASC;

Queries

In this next example, we will step through a query making sure to: -

Expand wildcards searches.
Qualify table names with the schema name.
Add a meaningful table alias name.
Qualify column names with table alias.
Add a meaningful column alias name¹.
Alias the column name using the AS convention.
Add the semi colon terminator.

Practices to avoid

    SELECT * FROM Report

    SELECT Report.Id, SubjectGivenName f, Report.SubjectFamilyName
    FROM Report

    SELECT Report.Id, SubjectGivenName f, Report.SubjectFamilyName
    FROM dbo.Report

    SELECT Report.Id, SubjectGivenName f, Report.SubjectFamilyName
    FROM dbo.Report AS Report

    SELECT Report.Id, Report.SubjectGivenName f, Report.SubjectFamilyName
    FROM dbo.Report AS Report

    SELECT Report.Id, Report.SubjectGivenName FirstName, Report.SubjectFamilyName
    FROM dbo.Report AS Report

    SELECT Report.Id, Report.SubjectGivenName AS FirstName, Report.SubjectFamilyName
    FROM dbo.Report AS Report

Examples of good practice

    SELECT Report.Id, Report.SubjectGivenName AS FirstName, Report.SubjectFamilyName
    FROM dbo.Report AS Report;

Dates #1

In this example, we ensure that a query against a date field can use the index.

Practices to avoid

    SELECT ObservationRequest.ReportId FROM dbo.ObservationRequest ObservationRequest
    WHERE CONVERT(VARCHAR(10), ObservationRequest.AuthorisedDateTime, 112) = '2018-12-12';

Examples of good practice

    SELECT ObservationRequest.ReportId
    FROM dbo.ObservationRequest ObservationRequest
    WHERE CAST(ObservationRequest.AuthorisedDateTime AS DATE) = '2018-12-12';

Dates #2

We step through the next example ensuring to

Eliminate the DATEDIFF operator.
Eliminate arithmetic operators against date fields.

Practices to avoid

    SELECT ObservationRequest.ReportId
    FROM dbo.ObservationRequest ObservationRequest
    WHERE > DATEDIFF(DAY,ObservationRequest.AuthorisedDateTime,GETDATE())<=30;

    SELECT ObservationRequest.ReportId
    FROM dbo.ObservationRequest > ObservationRequest
    WHERE CAST(ObservationRequest.AuthorisedDateTime AS DATE) > GETDATE()-30;

Examples of good practice

    SELECT COUNT(DISTINCT ObservationRequest.ReportId)
    FROM dbo.ObservationRequest ObservationRequest
    WHERE CAST(ObservationRequest.AuthorisedDateTime AS DATE) >= DATEADD(DAY,-30,CAST(GETDATE() AS > DATE));

Stored procedures #1

In this next example, we will step through a stored procedure making sure to:

Qualify the procedure name with the schema.
Add BEGIN and END statements.
SET NOCOUNT ON
Return a value.

Practices to avoid

    CREATE PROCEDURE prGetReportMasterId @Id BIGINT
    AS
        SELECT
            Report.Id
        , Report.MasterReportId
        FROM
            dbo.Report AS Report
            WHERE Report.Id = @Id;

    CREATE PROCEDURE dbo.prGetReportMasterId @Id BIGINT
    AS
        SELECT
            Report.Id
        , Report.MasterReportId
        FROM
            dbo.Report AS Report
            WHERE Report.Id = @Id;

    CREATE PROCEDURE dbo.prGetReportMasterId @Id BIGINT
    AS
    BEGIN
        SELECT
            Report.Id
        , Report.MasterReportId
        FROM
            dbo.Report AS Report
            WHERE Report.Id = @Id;
    END;

    CREATE PROCEDURE dbo.prGetReportMasterId @Id BIGINT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT
            Report.Id
        , Report.MasterReportId
        FROM
            dbo.Report AS Report
            WHERE Report.Id = @Id;
    END;

Examples of good practice

    CREATE PROCEDURE dbo.prGetReportMasterId @Id BIGINT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT
            Report.Id
        , Report.MasterReportId
        FROM
            dbo.Report AS Report
            WHERE Report.Id = @Id;
        RETURN 0;
    END;

Stored procedures #2

In the following example, we will step through a stored procedure making sure to:

Specify length for VARCHAR parameters.
Specify columns for INSERT statements.
SET NOCOUNT ON, SET XACT_ABORT ON
Add implicit transaction using BEGIN TRANSACTION and COMMIT
Add Error Handling using TRY ... CATCH blocks.

Practices to avoid

    CREATE PROCEDURE dbo.InsertReferenceDataValue
          @ReferenceDataDomainId INT
        , @Code                  VARCHAR
        , @Rubric                VARCHAR
        , @Active                BIT
        , @ParentId              INT = NULL
    AS
    BEGIN
        INSERT dbo.ReferenceDataValue
        VALUES
            (
                @ReferenceDataDomainId
                , @Code
                , @Rubric
                , GETDATE()
                , @Active
                , @ParentId
            );
    RETURN 0;
    END;

    CREATE PROCEDURE dbo.InsertReferenceDataValue
          @ReferenceDataDomainId INT
        , @Code                  VARCHAR(50)
        , @Rubric                VARCHAR(200)
        , @Active                BIT
        , @ParentId              INT = NULL
    AS
    BEGIN
        INSERT dbo.ReferenceDataValue
        VALUES
            (
                @ReferenceDataDomainId
                , @Code
                , @Rubric
                , GETDATE()
                , @Active
                , @ParentId
            );
    RETURN 0;
    END;

    CREATE PROCEDURE dbo.InsertReferenceDataValue
          @ReferenceDataDomainId INT
        , @Code                  VARCHAR(50)
        , @Rubric                VARCHAR(200)
        , @Active                BIT
        , @ParentId              INT = NULL
    AS
    BEGIN
        INSERT dbo.ReferenceDataValue
            (
                Id
            , ReferenceDataDomainId
            , Code
            , Rubric
            , DateTimeCreated
            , Active
            , ParentId
            )
        VALUES
            (
                @ReferenceDataDomainId
                , @Code
                , @Rubric
                , GETDATE()
                , @Active
                , @ParentId
            );
    RETURN 0;
    END;

    CREATE PROCEDURE dbo.InsertReferenceDataValue
          @ReferenceDataDomainId INT
        , @Code                  VARCHAR(50)
        , @Rubric                VARCHAR(200)
        , @Active                BIT
        , @ParentId              INT = NULL
    AS
    BEGIN
        SET NOCOUNT ON;
        SET XACT_ABORT ON;
        INSERT dbo.ReferenceDataValue
            (
                Id
            , ReferenceDataDomainId
            , Code
            , Rubric
            , DateTimeCreated
            , Active
            , ParentId
            )
        VALUES
            (
                @ReferenceDataDomainId
                , @Code
                , @Rubric
                , GETDATE()
                , @Active
                , @ParentId
            );
    RETURN 0;
    END;

    CREATE PROCEDURE dbo.InsertReferenceDataValue
          @ReferenceDataDomainId INT
        , @Code                  VARCHAR(50)
        , @Rubric                VARCHAR(200)
        , @Active                BIT
        , @ParentId              INT = NULL
    AS
    BEGIN
        SET NOCOUNT ON;
        SET XACT_ABORT ON;
        BEGIN TRANSACTION
        INSERT dbo.ReferenceDataValue
            (
                Id
            , ReferenceDataDomainId
            , Code
            , Rubric
            , DateTimeCreated
            , Active
            , ParentId
            )
        VALUES
            (
                @ReferenceDataDomainId
                , @Code
                , @Rubric
                , GETDATE()
                , @Active
                , @ParentId
            );
        COMMIT
    RETURN 0;
    END;

Examples of good practice

    CREATE PROCEDURE dbo.InsertReferenceDataValue
          @ReferenceDataDomainId INT
        , @Code                  VARCHAR(50)
        , @Rubric                VARCHAR(200)
        , @Active                BIT
        , @ParentId              INT = NULL
    AS
    BEGIN
        SET NOCOUNT ON;
        SET XACT_ABORT ON;
        BEGIN TRY
            BEGIN TRANSACTION
            INSERT dbo.ReferenceDataValue
                (
                    Id
                , ReferenceDataDomainId
                , Code
                , Rubric
                , DateTimeCreated
                , Active
                , ParentId
                )
            VALUES
                (
                    @ReferenceDataDomainId
                    , @Code
                    , @Rubric
                    , GETDATE()
                    , @Active
                    , @ParentId
                );
            COMMIT
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMsg VARCHAR(4000);
            SET @ErrorMsg = ERROR_MESSAGE();
            -- Rollback our transaction
            IF @@TRANCOUNT>0
                ROLLBACK;
            --Re-raise the error to our application;
            RAISERROR(@ErrorMsg, 16, 1);
        END CATCH;
    RETURN 0;
    END;

Naming

In our last example, we contrast a simple database query that does not conform to our rules with one that does. The conformant query assumes there is a constraint preventing any changes to the underlying database schema.

The conformant query implements the following rules: -

Names are descriptive and singular.
Pascal Casing applied.
Prefixes and underscores removed.
All Keywords are uppercase.
Meaningful table alias name applied.
Columns qualified with Table alias and meaningful column alias applied.
Meaningful column alias and name applied.
SQL Prompt formatting style rule applied.

Practices to avoid

    SELECT SubjectGivenName, SubjectFamilyName, [Hospital Ward]
    FROM tblPatients WHERE ID = @int_ID
    AND SubjectGivenName =  @str_param2;

Examples of good practice

    SELECT
        Patient.SubjectGivenName AS FirstName
        , Patient.SubjectFamilyName AS LastName
        , Patient.[Hospital Ward] AS HospitalWard
    FROM
        dbo.tblPatients AS Patient
    WHERE
        Patient.ID = @PatientId
        AND Patient.SubjectGivenName = @FirstName;

https://documentation.red-gate.com/codeanalysis/code-analysis-for-sql-server/style-rules/st010 ↩