Skip to main content

T-SQL

Overview​

We use the Repository pattern with the MSSQL repositories being written using Dapper. Each repository method in turn calls a Stored Procedure, which primarily fetches data from Views.

  1. Views define explicit column lists, selecting specific columns from tables
  2. Stored Procedures select * from views
  3. C# objects returned by repository methods match the columns defined in the corresponding view

This separation of concerns means:

  • The view definition is where you specify which columns are needed
  • Stored procedures simply SELECT * FROM [dbo].[ViewName]
  • The view acts as a contract between the database and application layer

Best practices​

  1. Consistency: Follow established patterns throughout the codebase
  2. Readability: Prioritize code readability and maintainability
  3. Performance: Consider index usage and query optimization
  4. Security: Use parameterized queries and proper data type validation
  5. Modularity: Break complex operations into smaller, reusable procedures

File organization​

Directory structure​

  • Schema-based organization: Files are organized by domain/schema (Auth, Billing, Secrets Manager, Vault, etc.)
  • Object type grouping: Within each domain, files are grouped by type:
    • Tables/ - Table definitions
    • Views/ - View definitions
    • Stored Procedures/ - Stored procedure definitions
    • Functions/ - User-defined functions
  • Root-level objects: Common objects are placed directly in dbo/:
    • Stored Procedures/ - General stored procedures
    • Tables/ - Core tables
    • Views/ - General views
    • User Defined Types/ - Custom data types

File naming conventions​

  • Stored Procedures: {EntityName}_{Action}.sql
    • e.g. User_Create.sql, Organization_ReadById.sql
  • Tables: {EntityName}.sql
    • e.g. User.sql, Organization.sql
  • Views: {EntityName}View.sql or {EntityName}{Purpose}View.sql
    • e.g. UserView.sql, ApiKeyDetailsView.sql
  • Functions: {EntityName}{Purpose}.sql
    • e.g. UserCollectionDetails.sql
  • User Defined Types: {TypeName}.sql
    • e.g. GuidIdArray.sql
Versioning

When a new version of an entity is introduced and needs to be maintained next to the existing one during deployment, use versioned names for the different scripts, so that the relationship is clear - e.g. a _V2 suffix.

Code style​

General standards​

These standards should be applied across any T-SQL scripts that you write.

  • Indentation: Use 4 spaces (not tabs) for all SQL code files
  • Keywords: Use UPPERCASE for all SQL keywords (CREATE, SELECT, FROM, WHERE, GROUP BY, ORDER BY, JOIN, ON, INTO, TOP, etc.)
  • Object names: Always use square brackets [dbo].[TableName]
  • Schema: Use [dbo] prefix for all objects
  • Line endings: Use consistent line breaks with proper indentation
  • Vertical lists: Vertically list items as much as feasibly possible, and use consistent indentation to make vertical listing quick and easy. A vertical list is much easier to compare and makes code changes easily detectable
  • Blank lines: Separate sections of code with at least one blank line
  • Commas: Commas should be placed at the right end of the line
  • Parentheses: Parentheses should be vertically aligned with spanning multiple lines
  • Data type modifiers: When a data type has a size or precision modifier, the preferred style is to omit the space between the type name and the opening parenthesis (e.g., NVARCHAR(50) not NVARCHAR (50), DATETIME2(7) not DATETIME2 (7))
  • ID generation: Do not use NEWID() to generate IDs in the database. New IDs are generated in application code using CoreHelpers.GenerateComb(). See GUID generation for more information.

SELECT statements​

  • SELECT keyword on its own line
  • Column names indented (4 spaces)
  • One column per line for multi-column selects
  • Call out the specific table/alias for where a column is from when joining to other tables
  • FROM keyword on separate line, aligned with SELECT
  • FROM clause indented (4 spaces)
    • Use aliases for table names when joining to other tables
  • JOIN keywords on separate line, aligned with FROM
    • Use full join specifications (INNER JOIN vs JOIN, LEFT OUTER JOIN vs LEFT JOIN, etc)
  • JOIN clauses indented to align with table/column name(s)
  • WHERE keyword on separate line, aligned with FROM/JOIN
  • WHERE clause on separate lines, indented to align with table/column name(s)
SELECT
    U.[Id],
    U.[Name],
    U.[Email],
    OU.[OrganizationId]
FROM
    [dbo].[User] U
INNER JOIN
    [dbo].[OrganizationUser] OU ON U.[Id] = OU.[UserId]
WHERE
    U.[Enabled] = 1

WHERE clause conditions​

  • AND/OR keywords go at the start of the next line, indented to align with the condition above it
  • Wrap grouped OR conditions in parentheses, with the opening ( on the same line as AND and the closing ) on its own line aligned with AND
  • Use inline comments to explain non-obvious conditions, such as status code meanings
WHERE
    O.[Enabled] = 1
    AND O.[UsePolicies] = 1
    AND (
        -- Active users linked by UserId
        (OU.[Status] != 0 AND OU.[UserId] = @UserId)
        -- Invited users matched by email (Status = 0)
        OR EXISTS (
            SELECT
                1
            FROM
                [dbo].[UserView] U
            WHERE
                U.[Id] = @UserId
                AND OU.[Email] = U.[Email]
                AND OU.[Status] = 0
        )
    )

IN clauses​

  • For bulk operations, prefer table-valued parameters (TVPs) using the IN (SELECT [Id] FROM @Ids) pattern
  • For direct value lists, use no spaces after commas: IN (0,1,2)
WHERE
    [Id] IN (SELECT [Id] FROM @Ids)

Subqueries​

  • Use EXISTS (not IN) for correlated subqueries — EXISTS short-circuits on the first match, whereas IN evaluates all matching values first
  • Use IN for non-correlated subqueries (where the inner query does not reference the outer query) — the optimizer typically produces equivalent plans, and IN reads more naturally in this context
  • Use SELECT 1 inside EXISTS checks, not SELECT *
  • Indent the subquery body 4 spaces within the parentheses; align the closing ) with the opening context

Correlated subquery (references outer query — use EXISTS):

CASE WHEN EXISTS (
        SELECT
            1
        FROM
            [dbo].[ProviderUserView] PU
        INNER JOIN
            [dbo].[ProviderOrganizationView] PO ON PO.[ProviderId] = PU.[ProviderId]
        WHERE
            PU.[UserId] = OU.[UserId]
            AND PO.[OrganizationId] = P.[OrganizationId]
    ) THEN 1 ELSE 0 END AS [IsProvider]

Non-correlated subquery (self-contained — use IN):

SELECT
    [Id],
    [Name]
FROM
    [dbo].[OrganizationView]
WHERE
    [Id] IN (
        SELECT
            [OrganizationId]
        FROM
            [dbo].[CollectionView]
        WHERE
            [ExternalId] IS NOT NULL
    )

Common table expressions (CTEs)​

  • Prefix the WITH keyword with a semicolon: ;WITH
  • Place the CTE name and AS followed by the opening ( on the same line
  • Put the closing ) on its own line; follow it with a comma and the next CTE name when chaining
  • Each CTE SELECT follows the same formatting rules as a regular SELECT statement
  • Put UNION ALL on its own line, with a blank line above and below it
;WITH OrgUsers AS
(
    -- Active users: direct UserId match
    SELECT
        OU.[Id],
        OU.[OrganizationId],
        OU.[Status]
    FROM
        [dbo].[OrganizationUserView] OU
    WHERE
        OU.[Status] <> 0
        AND OU.[UserId] = @UserId

    UNION ALL

    -- Invited users: matched by email
    SELECT
        OU.[Id],
        OU.[OrganizationId],
        OU.[Status]
    FROM
        [dbo].[OrganizationUserView] OU
    WHERE
        OU.[Status] = 0
        AND OU.[Email] = @UserEmail
),
Providers AS
(
    SELECT
        [OrganizationId]
    FROM
        [dbo].[UserProviderAccessView]
    WHERE
        [UserId] = @UserId
)
SELECT
    OU.[Id],
    OU.[OrganizationId],
    CASE WHEN PR.[OrganizationId] IS NULL THEN 0 ELSE 1 END AS [IsProvider]
FROM
    OrgUsers OU
LEFT JOIN
    Providers PR ON PR.[OrganizationId] = OU.[OrganizationId]

Stored procedures​

  • Stored Procedure Name: {EntityName}_{Action} format (e.g., [dbo].[User_ReadById])
    • EntityName: The main table or concept (e.g. User, Organization, Cipher)
    • Action: What the procedure does (e.g. Create, ReadById, DeleteMany)
  • Parameters: Start with @ and use PascalCase (e.g., @UserId, @OrganizationId)
  • OUTPUT parameters: Explicitly declare with OUTPUT keyword
Example of common CRUD operations
  • Create: {EntityName}_Create procedures
  • Read: {EntityName}_ReadById, {EntityName}_ReadBy{Criteria} procedures
  • Read Many: {EntityName}_ReadManyByIds, {EntityName}_ReadManyBy{Criteria} procedures
  • Update: {EntityName}_Update procedures
  • Delete: {EntityName}_DeleteById, {EntityName}_Delete procedures
Avoid using Get in procedure names

Some procedures in the codebase use Get instead of Read in the name (e.g., CipherOrganizationPermissions_GetManyByOrganizationId, OrganizationReport_GetSummaryDataById). These are incorrect and should not be used as a reference. Always use Read or ReadMany for SELECT operations.

Basic structure​

  • Wrap the entire procedure body in BEGIN/END statements
CREATE PROCEDURE [dbo].[EntityName_Action]
    @Parameter1 DATATYPE,
    @Parameter2 DATATYPE = NULL,
    @Parameter3 DATATYPE OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    -- Procedure logic here

END

Common examples​

Read by ID — select a single record from a view:

CREATE PROCEDURE [dbo].[EntityName_ReadById]
    @Id UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON

    SELECT
        *
    FROM
        [dbo].[EntityNameView]
    WHERE
        [Id] = @Id
END

Read many by IDs — bulk read using a table-valued parameter:

CREATE PROCEDURE [dbo].[EntityName_ReadManyByIds]
    @Ids AS [dbo].[GuidIdArray] READONLY
AS
BEGIN
    SET NOCOUNT ON

    SELECT
        *
    FROM
        [dbo].[EntityNameView]
    WHERE
        [Id] IN (SELECT [Id] FROM @Ids)
END

Read many with filter — multiple AND conditions with an inline status code comment:

CREATE PROCEDURE [dbo].[EntityName_ReadManyByOrganizationIdAndRole]
    @OrganizationId UNIQUEIDENTIFIER,
    @Role TINYINT
AS
BEGIN
    SET NOCOUNT ON

    SELECT
        *
    FROM
        [dbo].[EntityNameDetailsView]
    WHERE
        [OrganizationId] = @OrganizationId
        AND [Status] = 2 -- 2 = Confirmed
        AND [Type] = @Role
END

Parameter declaration​

  • One parameter per line
  • Align parameters with consistent indentation (4 spaces)
  • Default values on same line as parameter
  • OUTPUT parameters clearly marked
Default parameter values

When adding parameters to an existing stored procedure, a default value must be specified to ensure backward compatibility and ensure existing code can be called without modification.

Use SET NOCOUNT ON to prevent the automatic return of row count messages, which improves performance and ensures consistent behavior across different client applications that might handle these messages differently.

INSERT statements​

  • Column list in parentheses, one column per line
  • VALUES clause with parameters aligned
  • Proper indentation for readability
INSERT INTO [dbo].[TableName]
(   [Column1],
    [Column2],
    [Column3]
)
VALUES
(   @Parameter1,
    @Parameter2,
    @Parameter3
)

UPDATE statements​

  • UPDATE and table name on different lines
  • SET clause with each column assignment on separate line
  • WHERE clause clearly separated
UPDATE
    [dbo].[TableName]
SET
    [Column1] = @Parameter1,
    [Column2] = @Parameter2,
    [Column3] = @Parameter3
WHERE
    [Id] = @Id

Tables​

  • Table Name: Singular form of the object name, PascalCase (e.g., [dbo].[User] not [dbo].[Users], [dbo].[AuthRequest] not [dbo].[AuthRequests])
  • Column Names: PascalCase (e.g., [Id], [CreationDate], [MasterPasswordHash])
  • Primary Key: PK_{TableName} (e.g., [PK_User], [PK_Organization])
  • Foreign Keys: FK_{TableName}_{ReferencedTable} (e.g., FK_Device_User)
  • Default Constraints: DF_{TableName}_{ColumnName} (e.g., [DF_Organization_UseScim])

Column definitions​

  • Alignment: Column names, data types, and nullability vertically aligned using spaces
  • Data Types: Use consistent type patterns:
    • UNIQUEIDENTIFIER for IDs
    • DATETIME2(7) for timestamps
    • NVARCHAR(n) for Unicode text
    • VARCHAR(n) for ASCII text
    • BIT for boolean values
    • TINYINT, SMALLINT, INT, BIGINT for integers
  • Data type modifiers: No space between the type name and its size or precision modifier (e.g., NVARCHAR(50) not NVARCHAR (50), DATETIME2(7) not DATETIME2 (7))
  • Nullability: Explicitly specify NOT NULL or NULL
  • Standard Columns: Most tables include:
    • [Id] UNIQUEIDENTIFIER NOT NULL - Primary key
    • [CreationDate] DATETIME2(7) NOT NULL - Record creation timestamp
    • [RevisionDate] DATETIME2(7) NOT NULL - Last modification timestamp
CREATE TABLE [dbo].[TableName]
(
    [Id]            UNIQUEIDENTIFIER    NOT NULL,
    [Column2]       NVARCHAR(100)       NOT NULL,
    [Column3]       NVARCHAR(255)       NULL,
    [CreationDate]  DATETIME2(7)        NOT NULL,
    [RevisionDate]  DATETIME2(7)        NOT NULL,
    [Column6]       BIT                 NOT NULL CONSTRAINT [DF_TableName_Column6] DEFAULT (1),
    CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Views​

  • View Name:
    • {EntityName}View
      • Used when the view maps closely to a single table, with little or no joins. (e.g., [dbo].[ApiKeyView] from ApiKey)
    • {EntityName}DetailsView for complex views
      • Used for views that combine multiple tables or add logic beyond a basic table select. These usually serve a specific display or reporting use case and are named to reflect the context (e.g., [dbo].[OrganizationUserDetailsView])
    • For views that combine entities, create a view with a clear name tied to the main entity (e.g., [dbo].[OrganizationUser_MemberAccessDetailsView]) and a stored procedure that reads from it (e.g., [dbo].[MemberAccessDetails_ReadByUserId]).

Simple views​

CREATE VIEW [dbo].[ViewName]
AS
SELECT
    *
FROM
    [dbo].[TableName]

Complex views​

CREATE VIEW [dbo].[ComplexViewName]
AS
SELECT
    T1.[Column1],
    T1.[Column2],
    T2.[Column3],
    CASE
        WHEN T2.[Column4] IS NOT NULL
        THEN 1
        ELSE 0
    END AS ColumnAlias
FROM
    [dbo].[Table1] T1
LEFT JOIN
    [dbo].[Table2] T2 ON T1.[Id] = T2.[ForeignId]
WHERE
    T1.[Enabled] = 1

Functions​

  • Function Name: [Schema].[FunctionName] (e.g., [dbo].[UserCollectionDetails])
    • The name should describe what the function returns
CREATE FUNCTION [dbo].[FunctionName](@Parameter DATATYPE)
RETURNS TABLE
AS RETURN
SELECT
    [Column1],
    [Column2],
    CASE
        WHEN Condition
        THEN Value1
        ELSE Value2
    END [ComputedColumn]
FROM
    [dbo].[TableName]
WHERE
    [FilterColumn] = @Parameter

User defined types​

  • User Defined Type Name: [Schema].[TypeName] (e.g., [dbo].[GuidIdArray])
    • The name should describe the type
CREATE TYPE [dbo].[TypeName] AS TABLE
(   [Column1] DATATYPE NOT NULL,
    [Column2] DATATYPE NOT NULL
);

Indexes​

  • Index Name: IX_{TableName}_{ColumnName(s)} (e.g., [IX_User_Email])
    • The name should clearly indicate the table and the columns being indexed
CREATE NONCLUSTERED INDEX [IX_OrganizationUser_UserIdOrganizationIdStatus]
   ON [dbo].[OrganizationUser]([UserId] ASC, [OrganizationId] ASC, [Status] ASC)
   INCLUDE ([AccessAll])

Error handling​

  • Use SET NOCOUNT ON in stored procedures
  • Implement appropriate transaction handling where needed
  • Follow consistent error reporting patterns
    • Business logic should not be in stored procedures, but there may be times when it makes sense to do error handling in other scripts (migrations, one-off data scrubs)
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE
        [dbo].[TableName]
    SET
        [Column1] = 'NewValue'
    WHERE
        [Id] = 'IdValue'


    UPDATE
        [dbo].[TableName2]
    SET
        [Column1] = 'NewValue'
    WHERE
        [Id] = 'IdValue'

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;

END CATCH;

Comments and documentation​

  • Use -- for single-line comments
  • Add comments for complex business logic and the reason for a command or block of code
  • Document magic numbers and status codes (e.g., -- 2 = Confirmed)
  • Provide brief explanations for complex CASE statements or calculations
  • Don't comment unnecessarily, such as commenting that an insert statement is about to be executed

Deployment scripts​

There are specific ways migration scripts should be structured. We do so to adhere to the following guiding principles:

  • It must be idempotent: Always ensure a migration can be run multiple times without causing errors or duplicating data.

  • It must avoid breaking changes: Migrations should never delete or rename columns that are still in use by deployed code.

  • It must maintain schema integrity: The schema of the database defined in code should map exactly to the schema of the database in all deployed environments.

  • It must be backwards compatible: Code should be able to work with both the old and new schema during a rolling deployment.

Tables​

Creating a table​

When creating a table, you must first check if the table exists:

IF OBJECT_ID('[dbo].[{table_name}]') IS NULL
BEGIN
    CREATE TABLE [dbo].[{table_name}]
    (   [Id]                UNIQUEIDENTIFIER NOT NULL,
        [Column1]           DATATYPE         NOT NULL,
        [Column2]           DATATYPE         NULL,
        [CreationDate]      DATETIME2(7)     NOT NULL,
        [RevisionDate]      DATETIME2(7)     NOT NULL,
        CONSTRAINT [PK_{table_name}] PRIMARY KEY CLUSTERED ([Id] ASC),
        CONSTRAINT [FK_{table_name}_{referenced_table}] FOREIGN KEY ([ForeignKeyColumn]) REFERENCES [dbo].[ReferencedTable] ([Id])
    );
END
GO

Deleting a table​

When deleting a table, use IF EXISTS to avoid an error if the table doesn't exist.

DROP IF EXISTS [dbo].[{table_name}]
GO

Adding a column to a table​

You must first check to see if the column exists before adding it to the table.

IF COL_LENGTH('[dbo].[{table_name}]', '{column_name}') IS NULL
BEGIN
    ALTER TABLE [dbo].[{table_name}]
        ADD [{column_name}] {DATATYPE} {NULL|NOT NULL};
END
GO

When adding a new NOT NULL column to an existing table, please re-evaluate the need for it to truly be required. Do not be afraid of using Nullable<T> primitives in C# and in the application layer, which is almost always going to be better than taking up unnecessary space in the DB per row with a default value, especially for new functionality or features where it will take a very long time to be useful for most row-level data, if at all.

If you do decide to add a NOT NULL column, use a DEFAULT constraint instead of creating the column, updating rows and changing the column. This is especially important for the largest tables like dbo.User and dbo.Cipher. Our version of SQL Server in Azure uses metadata for default constraints. This means we can update the default column value without updating every row in the table (which will use a lot of DB I/O).

This is slow:

IF COL_LENGTH('[dbo].[Table]', 'Column') IS NULL
BEGIN
    ALTER TABLE
        [dbo].[Table]
    ADD
        [Column] INT NULL
END
GO

UPDATE
    [dbo].[Table]
SET
    [Column] = 0
WHERE
    [Column] IS NULL
GO

ALTER TABLE [dbo].[Table]
    ALTER COLUMN [Column] INT NOT NULL
GO

This is better:

IF COL_LENGTH('[dbo].[Table]', 'Column' IS NULL
BEGIN
    ALTER TABLE [dbo].[Table]
        ADD [Column] INT NOT NULL CONSTRAINT DF_Table_Column DEFAULT 0
END
GO
Do not use defaults for string columns

Default values should only be used for integral types (BIT, TINYINT, SMALLINT, INT, BIGINT). Do not provide default values for string columns (VARCHAR, NVARCHAR, or their MAX variants), as this can lead to unnecessary storage overhead and performance issues.

Changing a column data type​

You must wrap the ALTER TABLE statement in a conditional block, so that subsequent runs of the script will not modify the data type again.

IF EXISTS (
    SELECT
        *
    FROM
        INFORMATION_SCHEMA.COLUMNS
    WHERE
        COLUMN_NAME = '{column_name}'
        AND DATA_TYPE = '{datatype}'
        AND TABLE_NAME = '{table_name}'
)
BEGIN
    ALTER TABLE [dbo].[{table_name}]
    ALTER COLUMN [{column_name}] {NEW_TYPE} {NULL|NOT NULL}
END
GO

Adjusting metadata​

When adjusting a table, you should also check to see if that table is referenced in any views. If the underlying table in a view has been modified, you should run sp_refreshview to re-generate the view metadata.

EXECUTE sp_refreshview N'[dbo].[{view_name}]'
GO

Views​

Creating or modifying a view​

We recommend using the CREATE OR ALTER syntax for adding or modifying a view.

CREATE OR ALTER VIEW [dbo].[{view_name}]
AS
SELECT
    *
FROM
    [dbo].[{table_name}]
GO

Deleting a view​

When deleting a view, use IF EXISTS to avoid an error if the table doesn't exist.

DROP IF EXISTS [dbo].[{view_name}]
GO

Adjusting metadata​

When altering views, you may also need to refresh modules (stored procedures or functions) that reference that view so that SQL Server can update its cached metadata and compiled references to it.

IF OBJECT_ID('[dbo].[{procedure_or_function}]') IS NOT NULL
BEGIN
    EXECUTE sp_refreshsqlmodule N'[dbo].[{procedure_or_function}]';
END
GO

Functions and stored procedures​

Creating or modifying a function or stored procedure​

We recommend using the CREATE OR ALTER syntax for adding or modifying a function or stored procedure.

CREATE OR ALTER {PROCEDURE|FUNCTION} [dbo].[{sproc_or_func_name}]
...
GO

Deleting a function or stored procedure​

When deleting a function or stored procedure, use IF EXISTS to avoid an error if it doesn't exist.

DROP IF EXISTS [dbo].[{sproc_or_func_name}]
GO

Indexes​

When creating indexes, especially on heavily used tables, our production database can easily become offline, unusable, hit 100% CPU and many other bad behaviors. Our production database is configured to do online index builds by default, (so as not to lock the underlying table), so you should not specify ONLINE = ON, as this may cause failures on some SQL Server editions that do not support online index rebuilds. Online index creation may cause the index operation to take longer, but it will not create an underlying schema table lock which prevents all reads and connections to the table and instead only locks the table of updates during the operation.

A good example is when creating an index on dbo.Cipher or dbo.OrganizationUser, those are heavy-read tables and the locks can cause exceptionally high CPU, wait times and worker exhaustion in Azure SQL.

CREATE NONCLUSTERED INDEX [IX_OrganizationUser_UserIdOrganizationIdStatus]
   ON [dbo].[OrganizationUser]([UserId] ASC, [OrganizationId] ASC, [Status] ASC)
   INCLUDE ([AccessAll])

Modifying Existing Indexes​

To add columns to an existing index, recreate the index with the same name using DROP_EXISTING = ON. SQL Server will build the new index from the existing one while keeping the old index available for queries during the rebuild.

IF EXISTS (
    SELECT
        *
    FROM
        sys.indexes
    WHERE
        name = 'IX_Organization_Enabled'
        AND object_id = OBJECT_ID('[dbo].[Organization]')
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Organization_Enabled]
    ON [dbo].[Organization]([Id] ASC, [Enabled] ASC)
    INCLUDE ([UseTotp], [UsersGetPremium])
    WITH (DROP_EXISTING = ON);
END
ELSE
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Organization_Enabled]
    ON [dbo].[Organization]([Id] ASC, [Enabled] ASC)
    INCLUDE ([UseTotp], [UsersGetPremium]);
END
GO

Index best practices​

  • Create indexes after table definition with GO separator
  • Include INCLUDE clause when beneficial for covering indexes
  • Use filtered indexes with WHERE clause when appropriate