SQL create table reference

December 20, 2010

Here’s a quick reference for scripting table creation in SQL server by hand, where the text is readable and pretty rather than an autogenerated mess.

	CREATE TABLE sometable
	(
	[id] [uniqueidentifier] NOT NULL
	CONSTRAINT [DF_sometable_id] DEFAULT (newid()), /* This isn't required for DEFAULT */
	[userid] [uniqueidentifier] NOT NULL,
	[accountid] [uniqueidentifier] NOT NULL,
	[activated] [bit] NOT NULL,
	[activatedon] [datetime] NULL,
	[email] [nvarchar](128) NOT NULL,
	[key] [varchar](32) NOT NULL,
	PRIMARY KEY NONCLUSTERED (id), /* DONT USE CLUSTERED FOR uniqueidentifier */
	CONSTRAINT FK_sometable_userid FOREIGN KEY(userid) REFERENCES users (id),
	CONSTRAINT FK_sometable_accountid FOREIGN KEY(accountid) REFERENCES accounts (id),
	)
	CREATE NONCLUSTERED INDEX [idx_anothertable] ON [dbo].[anothertable]
	(
	[sometypeid] ASC,
	[fileid] ASC,
	[layoutid] ASC
	)

view raw gistfile1.sql hosted with ❤ by GitHub

It’s usually easier to bulk add the foreign keys after, that way the order of the CREATE TABLES doesn’t matter:

ALTER TABLE [dbo].[sometable] ADD CONSTRAINT [FK_sometable_users_id] 
	FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([id])

I'm Chris Small, a software engineer working in London. This is my tech blog. Find out more about me via Github, Stackoverflow, Resume