A common SQL task is to set up new tables for an application, and while you can use the GUI for this, I prefer using scripts. It’s faster, and leads to better deployment practices. (The one time I do find the designer useful is when making a column size change to a table.  In this case, I will make the change in the designer, and generate a script.  It looks ugly, but saves a lot of manual coding)

SqlTip1_TableDesignerSqlTip1_TableDesignerChangeScript

Since I don’t typically create tables every day, even as a dba, I like using a basic template. You can script out a template using an existing SQL Server table, which looks like this:

/****** Object: Table [product].[Category] Script Date: 10/6/2015 8:38:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [product].[Category](
[CategoryId] [int] IDENTITY(100,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[CustomDataFields] [nvarchar](max) NULL,
[DateModified] [datetime2](7) NULL,
[DateCreated] [datetime2](7) NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED
(
[CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Or if you are using SQL 2012 or later you can use code snippets:

CREATE TABLE dbo.Sample_Table
(
column_1 int NOT NULL,
column_2 int NULL
);

I don’t care for these much (too verbose or too simplistic), so I created my own:

CREATE TABLE tempdb.dbo.temp1
(
 Temp1Id INT IDENTITY(1,1) NOT NULL
 CONSTRAINT temp1PrimaryKey PRIMARY KEY CLUSTERED,
 VALUE VARCHAR(25),
 DateModified datetime2,
 DateCreated datetime2 default getdate()
)
GO

CREATE TABLE tempdb.dbo.temp2
(
 Temp2Id INT IDENTITY(1,1) NOT NULL
 CONSTRAINT temp2PrimaryKey PRIMARY KEY CLUSTERED,
 Temp1Id INT NOT NULL
 CONSTRAINT Temp2Temp1ForeignKey FOREIGN KEY (Temp1Id)
 REFERENCES dbo.temp1(Temp1Id),
 VALUE VARCHAR(25) NULL,
 DateModified datetime2,
 DateCreated datetime2 default getdate()
)
GO

Gist

At my current job, I allow developers to create their own tables (following standards of course!), and a very common code review item is that they forget to make the foreign key constraint! It’s simpler to add this at the time of creation. Sometimes this makes data insertion a problem when people are “testing” – but it’s far worse to have this go uncaught when deploying to production.

Additionally, unless you are using database projects, it’s a good idea to keep all your table creation logic in a single script. This will save a lot of time when deploying to staging or production.