Naming conventions for database, table, column, star schema, indexes, Azure, and PowerBI.

Following the AdventureWorks database sample from Microsoft:

Database naming

  • databasename
  • Lower case
  • No spaces/dashes/underscores

Tables, columns, schema

  • TableName or ColumnName
    • Use column [Name] if they are reserved keywords
    • Or use friendlier columns like ProductName
  • UpperCamelCase
  • No spaces
  • Singular

Views

  • Same as Tables and
  • Use the prefix lower v followed by ViewName like vGetAllCategories

Stored Procedures

  • Same as Tables and
  • Use the prefix lower usp (user stored procedure) followed by a Name, like uspLogError

Keys

  • Primary/Foregin key: TableName + ID such as CustomerID

Indexes

  • Clustered: PK_TableName_ColumnName
  • Non-Clustered: IX_TableName_ColumnName

T-SQL Cheat Sheet (ChatGPT generated)

ChatGPT can be contradictory. The examples generated for tables used plural as in Customers. However, when I asked if I should use singular or plural when creating tables, it responded to use singular Customer.

Most common DDL and DML

CREATE TABLE Customer (
    CustomerID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

ALTER TABLE Customer
ADD PhoneNumber VARCHAR(20);

INSERT INTO Customer (CustomerID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'johndoe@example.com');

UPDATE Customer
SET Email = 'johndoe123@example.com'
WHERE CustomerID = 1;

DELETE FROM Customer
WHERE CustomerID = 1;

Clustered/Non-Clustered indexes

ChatGPT’s naming convention here was “whatever” :)

This will create a clustered index called “idx_CustomerID” for Customers

CREATE CLUSTERED INDEX IX_Customer_CustomerID ON Customer (CustomerID);

This will create a non-clustered index called “idx_LastName” on the “LastName” column of the “Customers” table. Note that a table can have multiple non-clustered indexes.

CREATE NONCLUSTERED INDEX IX_Customer_LastName ON Customer (LastName);

MERGE

Suppose you have two tables, “Sales” and “SalesStaging”, that contain information about sales transactions. You want to update the “Sales” table with any new or updated transactions from the “SalesStaging” table. You can use the MERGE statement to perform this operation.

In this example, the