Database Standards

These standards apply to all database objects. Most of the standards are present to improve code readability and maximize runtime performance. Where there is an additional reason, the reason is noted with the standard.

Structure Standards

The following are the standards that should be used to structure a Stored Procedure or Function. Differences between the two will be noted.

1. All Stored Procedures and Functions should be saved as “CREATE OR ALTER PROCEDURE/FUNCTION [SCHEMA].[StoredProcedureName/FunctionName]”. 
   This allows them to be run as a script against an existing database without throwing errors. 
2. All Stored Procedures and Functions with parameters should list those parameters vertically.
   1. The opening bracket should remain on the line above the first parameter and below the procedure name.
   2. The closing bracket should remain on the line below the last parameter.
   3. Commas separating parameters should be placed on the line with the preceding parameter.
3. All Stored Procedures and Functions should be saved with the following AS declaration. This allows them to be encrypted or decrypted in a large script with a single batch command.  

   CREATE OR ALTER PROCEDURE [dbo].[GI_Procedure] 
   (
       @ParameterA AS UNIQUEIDENTIFIER, 
       @ParameterB AS UNIQUEIDENTIFIER, 
       @ParameterC AS UNIQUEIDENTIFIER
   )
   AS -- DECRYPT
   -- WITH ENCRYPTION AS
   BEGIN
   

4. The first statement in every Stored Procedure following the initial BEGIN should be BEGIN TRY with the final block of every Stored Procedure being the CATCH block related to that TRY block. This is done to ensure that every error in the stored procedure is caught in the stored procedure and to ensure meaningful error messages are returned.
5. The first statement in the primary TRY block should be a single DECLARE statement that includes all required variables for the Stored Procedure.

   DECLARE
       @VarA as TYPE,
       @VarB as TYPE,
       @VarC as TYPE

6. After declaring all variables, any applicable validation should be done. Validation helps to secure the application by preventing unauthorized data access and data changes.
   1. If the stored procedure is called from Rest-Logic, validate the user’s session. If the stored procedure is only called from other TSQL, do not validate the user session.
   2. By default, read access should be validated if the session is also validated. However there are cases where read access is not applicable or required. These should be evaluated on a case-by-case basis with the reason for not validating read access given in a comment.

      EXECUTE [dbo].[GI_ValidateSession] @SessionGUID, @UserGUID
      EXECUTE [dbo].[GI_ValidateReadAccess] @SessionGUID, @UserGUID, @InstanceGUID

7. At the end of the stored procedure should be a CATCH block.
   1. The CATCH block should always begin by concatenating the error message as below.
   2. Next, if there are transactions in the Stored Procedure, check for active transactions. If there is an active transaction, roll back the transaction.
   3. Finally, the exception should be logged and a new exception should be thrown.

BEGIN CATCH
    
        -- Log and raise the error...
        DECLARE @ErrorMessage AS VARCHAR(1000) = @SPName + ' - ' + ERROR_MESSAGE()
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION
        END
        EXECUTE [dbo].[GI_LogEvent] @UserGUID, @SPName, 1, @ErrorMessage
        ;THROW 50000, @ErrorMessage, 1
        
    END CATCH

The final structure of a Stored Procedure or Function should be the following

CREATE OR ALTER PROCEDURE [dbo].[GI_Procedure] 
(
    @ParameterA AS UNIQUEIDENTIFIER, 
    @ParameterB AS UNIQUEIDENTIFIER, 
    @ParameterC AS UNIQUEIDENTIFIER
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

    BEGIN TRY

        DECLARE
            @SPNAME AS VARCHAR(100) = OBJECT_NAME(@@PROCID),
            @VarB AS TYPE,
            @VarC AS TYPE

        EXECUTE [dbo].[GI_ValidateSession] @SessionGUID, @UserGUID 
        EXECUTE [dbo].[GI_ValidateReadAccess] @SessionGUID, @UserGUID, @InstanceGUID

        TSQL FOR THE PROCEDURE

    END TRY
    BEGIN CATCH

    -- Log and raise the error...
    DECLARE @ErrorMessage AS VARCHAR(1000) = @SPName + ' - ' + ERROR_MESSAGE()
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END
    EXECUTE [dbo].[GI_LogEvent] @UserGUID, @SPName, 1, @ErrorMessage
    ;THROW 50000, @ErrorMessage, 1
        
    END CATCH
END

Formatting Standards

• Database Objects
  • All database objects references should be contained within square brackets.
  • All database objects should be referenced with their schema. ex. [dbo].[GI_LogEvent].
• Keywords
  • All Keywords should be fully capitalized. ex. SELECT, DELETE, AND, FROM.
• Commenting
  • Avoid commenting unless necessary. It is necessary if someone has to ask what the code does or why it is written in any particular way.
  • Always comment immediately above the code that caused the comment.
    • In the case of the comment applying to a condition in a SELECT statement, the comment should be the line above the SELECT keyword.
• Conditions
  • IF and WHILE statements should use full BEGIN and END keywords to denote their code blocks.

IF condition
BEGIN
    Block
END

• • Conditions should not be placed in brackets.
  • There should be one line per condition.
  • Conditions should be on the same line as the preceding keyword. (IF, WHERE, AND, etc.)
  • Additional conditions connected by the AND or OR keywords should be on new, indented lines.
  • Two or more conditions combined into a single condition should be placed in ( and ) brackets.

IF condition
    AND condition
    OR (Subcondition1 AND Subcondition2)

• • The WHERE keyword and first condition of a SELECT, DELETE, or UPDATE statement should be together on a new line.
  • Avoid using the LIKE Keyword where possible.
    • When unavoidable, avoid using wildcard characters at the beginning of the string.
  • If a WHERE condition uses the LIKE keyword, the FORCESEEK table hint should be used on any searched tables.

• SELECT
  • SELECT statements should not select * unless every column is required. This is to prevent unnecessary data from being passed to the application.
  • Columns in SELECT statements should be listed vertically with separating commas on the line with the preceding column name.
  • When assigning names to columns in a SELECT statement, the AS Name remains on the same line with the column name.
  • The FROM keyword and table should be together on a new line.
  • All SELECT statements should be done with the NOLOCK table hint.
  • SELECT statements should be made against lookup tables or views by preference. This reduces procedure running time by running against smaller tables.
  • Always use column names when using the ORDER BY keyword.
  • Any JOIN statements should be made on the following line and indented.
    • Any JOIN statement should be a specific JOIN type (ex. LEFT OUTER JOIN).

SELECT
    TA.ColumnA AS NameA,
    TB.ColumnB AS NameB
FROM TableA TA WITH (NOLOCK)
LEFT INNER JOIN TableB TB
    ON Condition
WHERE Condition
    AND Condition2
    OR (Subcondition1 AND Subcondition2)

• UPDATE
  • UPDATE and the table to update should be on the same line.
  • SET should be on a new line.
  • Each column assignation should be on a new line and indented with separating commas on the line with the preceding column assignation.

UPDATE Table
SET
    ColumnA = valueA,
    ColumnB = valueB
WHERE Condition 
    AND Condition2 
    OR (Subcondition1 AND Subcondition2)

• INSERT
  • INSERT INTO and the Table name should all be on a single line.
  • INSERT statements should not be run against lookup tables or views as those changes will be overwritten.
  • Columns should be listed on individual lines with separating commas on the line with the preceding column.
  • As brackets are required, the opening bracket should be on the preceding line with the table name. The closing bracket should be on the line with the last column.
  • The VALUES keyword should be placed on a new line.
  • VALUEs should be listed on individual lines with separating commas on the line with the preceding column.
  • As brackets are required, the opening bracket should be on the preceding line with the table name. The closing bracket should be on the line with the last column.

INSERT INTO Table(
    ColumnA,
    ColumnB,
    ColumnC)
SELECT 
    ColumnA,
    ColumnB,
    ColumnC

INSERT INTO table(
    ColumnA,
    ColumnB)
VALUES(
    ValueA,
    ValueB)

INSERT INTO table( 
    ColumnA, 
    ColumnB) 
VALUES
( 
    ValueA 
    ValueB
),
(
    ValueC,
    ValueD
)

•  DELETE
  • DELETE statements should not be run against lookup tables.
  • The table name should be on the same line as the DELETE statement.
  • DELETE statements should always have a condition.
  • DELETE statements should always have all applicable validation to ensure no user can delete a record that they should not be able to.
    • If the user will always be signed in for this procedure, validate the session.
    • If the user will always have read access to the instance, validate the user’s read access.
    • For example, if a configuration parameter is supposed to be deleted, the instance to be deleted should be validated as a configuration parameter before the delete takes place.

DELETE FROM Table
WHERE Condition

•  CURSORs
  • All CURSORs should be given the LOCAL and FAST_FORWARD hints. This will increase their speed by allowing the server to choose an optimal execution plan.
  • All code within an open CURSOR should be indented.
  • The CURSOR should be surrounded immediately by a TRY/CATCH block that closes and deallocates the cursor.
  • The CURSOR should be closed and deallocated when it’s operation is finished.
  • CURSORs should always be contained in a TRANSACTION which can be rolled back if the CURSOR throws an exception.

BEGIN TRY
    BEGIN TRANSACTION
        DECLARE CursorName CURSOR LOCAL FAST_FORWARD FOR
        SELECT 
            ColumnA,
            ColumnB
        FROM Table
        WHERE Condition
        OPEN CursorName 

        FETCH NEXT FROM CursorName INTO
            @VariableA,
            @VariableB

        WHILE @@FETCH_STATUS = 0
        BEGIN

            Code Block

            FETCH NEXT FROM CursorName INTO 
                @VariableA, 
                @VariableB
        END

        CLOSE CursorName
        DEALLOCATE CursorName
    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    CLOSE CursorName
    DEALLOCATE CursorName
    -- Log and raise the error...
    DECLARE @ErrorMessage AS VARCHAR(1000) = @SPName + ' - ' + ERROR_MESSAGE()
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END
    EXECUTE dbo.GI_LogEvent @UserGUID, @SPName, 1, @ErrorMessage
    ;THROW 50000, @ErrorMessage, 1
        
END CATCH

• COUNT
  • The COUNT function is used to count the number of rows in a table.
  • COUNT (*) counts every row in a table. You can count every word in every dictionary by counting every row in the Dictionaries table.
  • COUNT (Column) counts every row in the table with a non-null value in the given column. You can count every word in a single dictionary by counting Word values in the Dictionaries table with a given dictionary GUID.
  • COUNT DISTINCT (Column) counts every unique value in the given column that is not null. You can count every Dictionary by counting every unique DictionaryGUID value.

XML

• Avoid manipulating XML in SQL unless necessary. SQL XML processing is inefficient compared to C# so it should be performed in C# whenever possible.
  • This does not mean that procedures should pass XML manipulation during procedures to C#.
• When unavoidable, XML should be formatted with tags on separate lines and indented.
  • The indentation should show their level within the XML. A value within an element within an element should be indented 2 times.

SET @XML = '<ROOT>' +
    '<ElementA AttributeA="' + @AttributeAValue + '">' + @ElementAValue + '</ElementA>' +
    '<ElementB AttributeB="' + @AttributeBValue + '">' + @ElementBValue + '</ElementB>' +
'</ROOT>'

• When selecting or setting XML values from XML, the XML.value function should be used.
  • Each value function should be on a single line with any related variables or AS declarations.
  • The output variable type must always be the same type as any variable being set by the function.
  • List GI functions

SELECT 
    @VarA = Column.value('/ROOT[1]/ElementA[1]/@AttributeA[1]', 'VARCHAR(300)'),
    @VarB = Column.value('/ROOT[1]/ElementA[1]', 'VARCHAR(300)')
FROM TableA
WHERE Condition

SELECT
    Column.value('/ROOT[1]/ElementA[1]/@AttributeA[1]', 'VARCHAR(300)') AS ResultA
    Column.value('/ROOT[1]/ElementA[1]', 'VARCHAR(300)') AS ResultB

Exception Handling

Proper exception handling is vital to developers. These standards mean that any exception should be caught and logged with the location and an error message that immediately inform any developer where the application failed.

• Functions should not have TRY/CATCH blocks. Instead, if they reach a known failure state, they should throw an exception to be caught in caller’s CATCH block.
• Every Stored Procedure should include a TRY/CATCH block that contains all code within the procedure.
• Whenever an error can be identified within the code of the TRY block, the THROW statement should be used.
  • The THROW statement should be immediately preceded by it’s required semicolon.
  • The error value for each THROW statement should be 50000.
  • When throwing a new exception outside of a CATCH block, the error message should be a meaningful string that explains the error.
  • The severity value for each THROW statement should be 1.
• In each CATCH block, the error message should be thrown to the next level.
• In the final CATCH block, the given error message should be combined with the name of the Stored Procedure and logged, before being thrown to the next level.
• If the Stored Procedure contains a transaction, the final CATCH block should check for an open transaction and roll it back if one exists.
  • If any TRY block contains a transaction, the following CATCH block should check for an open transaction in the same manner.

Naming Conventions

Tables

• Table names should be singular
  • Use Client instead of Clients
• Use PascalCase when naming tables
• Avoid using numbers in table names, except in lookup tables
  • See Lookup tables section
• Avoid using abbreviations if possible
  • Use Account instead of Acct
• Use only alphanumeric characters and the underscore character (_) for table names.
• Table names should not start with a digit.
• Avoid using reserved words as table names
  • A list of reserved words can be found here

Columns

• Boolean field names should be phrased as a question
  • Use IsActive instead of Active
  • This is so that the meaning of the data in the field is not ambiguous.
• Fields that contain a date OR time, the word Date or Time should appear somewhere in the column name.
• Datetime fields all use the datetime2 type as UTC (Universal Time Coordinate)
• Use Pascal case when naming columns and use only alphanumeric characters and the underscore character (_) for column names.
• Column names should not start with a digit.
  • This can interfere with XML data storage.
• Avoid using reserved words as column names.
  • A list of reserved words can be found here
• No need to reference table name in column names.
• Do not prefix a column with a data type, such as IntCustomerId

Lookup tables

Lookup tables can’t exist on their own and they are dependent on the underlying base tables. So, it makes sense to include the name of the table in the lookup table name.

Use functionality prefixes to group related lookup tables.

• LU_ prefix_ConceptName if it is a level one lookup table
  • This table is automatically generated by trellispark if specified in the concept when performing SQL generation
• L2_prefix_TableName if it is a level two lookup table
  • Can only contain data from level one lookup tables.
• L3_prefix_TableName if it is a level three lookup table
  • Can only contain data from level two and below lookup tables.
• L4_prefix_TableName if it is a level four lookup table
  • Can only contain data from level three and below lookup tables.

Note: The lookup insert and delete stored procedures should be modified to add the functionality prefix when the functionality first begins to use lookup tables.

Indexes

The indexes should use the following format:

IDX_TableName_ColumnName

For example:

• IDX_AccountExchange_AccountID (Non-Cluster Index)
• CIDX_AccountExchange_AccountID (Cluster Index)

Views

• Use PascalCase when naming views
• Only use alphanumeric characters and the underscore character (_) for view names
• View names should not start with a digit
• Avoid using reserved words as view names
  • A list of reserved words can be found here
• trellispark will automatically generate views for all form definitions for concepts that are instantiated in the data model when the Generate SQL command is executed.
  • The name format of the generated view is vw_prefix_ConceptName
• All other views should use the format vw_prefix_ViewName
  • Where ViewName is a meaningful description of the view

Stored procedures

• Do not prefix your stored procedures with something that will cause the system to think it is a system procedure
  • For example, in SQL Server, if you start a procedure with sp_, xp_ or dt_ it will cause SQL Server to check the master database for this procedure first, causing a performance hit.
• Prefix stored procedures with functionality designation.
  • For example: ZSO_ which represents the Server Owner functionality.
• Standard stored procedure name format: FunctionalityDesignation_Action
  • For example: ZSO_LogEvent
• trellispark will automatically generate GET procedures during the generate SQL command. It is best practice to preface your procedures with intent.
  • An example of proper name convention would be dbo.Get_prefix_ConceptName
• trellispark will automatically generate paged GET procedures during the generate SQL command. It is best practice to preface your procedures with intent.
  • An example of proper name convention would be dbo.GetPaged_prefix_ConceptName
• If your stored procedure returns a scalar value, or performs an operation like validation, you should use the verb and noun combination.
  • For example: ValidateUser

Functions

• Functions should follow the same naming convention as stored procedures

Triggers

Triggers have many things in common with stored procedures. However, triggers are different than stored procedures in two important ways.

1. Triggers don’t exist on their own. They are depending upon a table.
2. Triggers can only execute when an Insert, Update, or Delete happens on one or more of the records in the table.

Triggers

• Use PascalCase when naming triggers
• Only alphanumeric characters and the underscore character (_) for trigger names
• Trigger names should not start with a digit
• Avoid using reserved words as trigger names
  • A list of reserved words can be found here
• Avoid using abbreviations
  • Use Account instead of Acct

Variables

• Cursor names should begin “cur” to make it instantly apparent that the variable refers to a cursor.
• GUID variables should end GUID to make it instantly apparent that the variable refers to a GUID

Additional Standards

• Always use spaces between words, operators, and values to improve readability.
• When determining the date, always use SYSUTCDATETIME. This ensures consistent database timing across multiple time zones.
• Where possible, perform XML manipulation, string concatenation, and conversions in C#. C# processes these better than SQL, making the whole application run faster.
• Always use VARCHAR instead of CHAR. CHAR always uses the same amount of storage where a VARCHAR uses storage based on the number of characters being stored. 
  • Always use a value for VARCHAR that reflects the expected size of the variable. (GUIDs are always 36 characters long. If a VARCHAR variable will store a GUID, use a length of 36 for it).
    • InstanceName should always be VARCHAR(300).
    • Hierarchy should always be VARCHAR(500).
• TEXT, NTEXT, and IMAGE are deprecated forms of storage, so should not be used.
• Each table must have a primary key. This improves the speed of any statements that include select logic when the condition includes the primary key.
• If there are common selects run against any column(s) that are not the table’s primary key, generate an index on the column(s). This improves the speed of any statements that include select logic when the condition includes the column(s).
• Columns with default values should not allow nulls.
• Do not use the GOTO or WAITFOR keywords. SQL operates most efficiently when running from top to bottom. If it is jumping around, it becomes possible to create unintended loops or have hanging stored procedures waiting timers.