Last active
November 19, 2018 01:53
-
-
Save palmerandy/7f4d472bcb239727f495d3360f95e050 to your computer and use it in GitHub Desktop.
Living example SQL script of database standards. See https://andypalmer.info/posts/a-different-take-on-database-standards-documentation/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Start with this file, rename and remove as required. | |
--Use PascalCase for table names and column names. | |
-- create new table with composite PK; this is OK if there is a clear and simple PK from one or two other columns | |
if not exists(select * from sys.tables t where t.name = 'TableName') | |
begin | |
create table TableName | |
( | |
CompositeId1 int not null, | |
CompositeId2 int not null, | |
CreatedDate datetime not null constraint DF_TableName_CreatedDate default (getutcdate()), | |
UpdatedDate datetime null, -- leave null on first creation of row | |
constraint PK_TableName primary key clustered (CompositeId1, CompositeId2) -- PK should be clustered if this is the main way you expect to select rows from the table | |
) | |
end | |
go | |
-- create new table with identity PK; this is advisable if there is not a clear simple PK from other columns, e.g. a user might have a unique row for every distinct date | |
if not exists(select * from sys.tables t where t.name = 'TableName') | |
begin | |
create table TableName | |
( | |
--Primary key named after the table | |
TableNameId int not null identity constraint PK_TableName primary key nonclustered, -- consider if your PK should be clustered or not? will most queries be by this ID or by some other index? | |
--Any table that saves user data: | |
UserId int not null, | |
AnotherId int not null, | |
--For user related dates, pass through the user's local date, and use SQL "date" type if the local time is not relevant: | |
XxxDate date not null, --the name should reflect the use of the column (e.g. "TableName.ActivityDate" or "TableName.DateAchieved") | |
--For saving system data), the user Id's or the relevant user should be saved: | |
CreatedBy int not null, | |
UpdatedBy int null, | |
--Each table should have the following columns, should always be saved as UTC: | |
CreatedDate datetime not null constraint DF_TableName_CreatedDate default (getutcdate()), --Explicitly name constraint | |
UpdatedDate datetime null, -- leave null on first creation of row | |
--Unique Constraint: | |
constraint UX_TableName_UserId_XxxDate unique (UserId, XxxDate) | |
) | |
end | |
go | |
-- add new index to existing table | |
-- if the index is for a very specfic purpose, then indicate that purpose in the name, e.g. IX_TableName_ColumnA_ColumnB_SpecificScenario | |
if not exists (select * from sys.indexes where name = 'IX_TableName_UserId_AnotherId' and object_id = object_id('TableName')) | |
begin | |
create | |
unique -- consider whether your indexes ought to be unique | |
nonclustered -- make this clustered if it will be used more often than your PK (and make the PK nonclustered) | |
index IX_TableName_UserId_AnotherId on TableName (UserId, AnotherId) -- the order of columns is important! in this example, searches by only UserId can still use the index, but searches by only AnotherId can not | |
end | |
go | |
-- add a column to an existing table | |
if not exists(select * from sys.tables t join sys.columns c on t.object_id = c.object_id where t.name = 'TableName' and c.name = 'ColumnId') | |
begin | |
alter table TableName add ColumnId int null -- new column should be nullable so it does not break old website code (use X script to enforce not null) | |
end | |
go | |
-- add a default constraint to an existing table | |
if not exists (select * from sys.tables t join sys.columns c on t.object_id = c.object_id join sys.default_constraints d on c.default_object_id = d.object_id where t.name = 'TableName' and c.name = 'ColumnId') | |
begin | |
alter table TableName add constraint DF_TableName_ColumnId default (0) for ColumnId -- change (0) to whatever default value you need e.g. getutcdate() | |
end | |
go | |
-- add a unique constraint to an existing table | |
if object_id('UX_TableName_MemberId_ActivityDate', 'UQ') is null | |
begin | |
alter table TableName add constraint UX_TableName_MemberId_ActivityDate unique (MemberId, ActivityDate) | |
end | |
go | |
-- rename an existing table; be careful not to do this if the table is actively being used by live code | |
if not exists(select * from sys.tables t join sys.columns c on t.object_id = c.object_id where t.name = 'TableName1') | |
begin | |
exec sp_rename 'TableName', 'TableName1' | |
end | |
go |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment