DptAk8W5

1--Start with this file, rename and remove as required.
2    --Use PascalCase for table names and column names.
3
4    -- create new table with composite PK; this is OK if there is a clear and simple PK from one or two other columns
5    if not exists(select * from sys.tables t where t.name = 'TableName')
6    begin
7        create table TableName
8        (
9            CompositeId1 int not null,
10            CompositeId2 int not null,
11
12            CreatedDate datetime not null constraint DF_TableName_CreatedDate default (getutcdate()),
13            UpdatedDate datetime null, -- leave null on first creation of row
14
15            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
16        )
17    end
18    go
19
20    -- 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
21    if not exists(select * from sys.tables t where t.name = 'TableName')
22    begin
23        create table TableName
24        (
25            --Primary key named after the table
26            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?
27
28            --Any table that saves user data:
29            UserId int not null,
30            AnotherId int not null,
31
32            --For user related dates, pass through the user's local date, and use SQL "date" type if the local time is not relevant:
33            XxxDate date not null, --the name should reflect the use of the column (e.g. "TableName.ActivityDate" or "TableName.DateAchieved")
34
35            --For saving system data), the user Id's or the relevant user should be saved:
36            CreatedBy int not null,
37            UpdatedBy int null,
38
39            --Each table should have the following columns, should always be saved as UTC:
40            CreatedDate datetime not null constraint DF_TableName_CreatedDate default (getutcdate()), --Explicitly name constraint
41            UpdatedDate datetime null, -- leave null on first creation of row
42
43            --Unique Constraint:
44            constraint UX_TableName_UserId_XxxDate unique (UserId, XxxDate)
45        )
46    end
47    go
48
49    -- add new index to existing table
50    -- if the index is for a very specfic purpose,  then indicate that purpose in the name, e.g. IX_TableName_ColumnA_ColumnB_SpecificScenario
51    if not exists (select * from sys.indexes where name = 'IX_TableName_UserId_AnotherId' and object_id = object_id('TableName'))
52    begin
53        create 
54        unique -- consider whether your indexes ought to be unique
55        nonclustered -- make this clustered if it will be used more often than your PK (and make the PK nonclustered)
56        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
57    end
58    go
59
60    -- add a column to an existing table
61    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')
62    begin
63        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)
64    end
65    go
66
67    -- add a default constraint to an existing table
68    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')
69    begin
70        alter table TableName add constraint DF_TableName_ColumnId default (0) for ColumnId -- change (0) to whatever default value you need e.g. getutcdate()
71    end
72    go
73
74    -- add a unique constraint to an existing table
75    if object_id('UX_TableName_MemberId_ActivityDate', 'UQ') is null
76    begin
77        alter table TableName add constraint UX_TableName_MemberId_ActivityDate unique (MemberId, ActivityDate)
78    end
79    go
80
81    -- rename an existing table; be careful not to do this if the table is actively being used by live code
82    if not exists(select * from sys.tables t join sys.columns c on t.object_id = c.object_id where t.name = 'TableName1')
83    begin
84        exec sp_rename 'TableName', 'TableName1'
85    end
86    go