T-SQL – Creating, altering, deleting tables

A few examples how to create and alter tables.

Create
Simple table with different data types and constraints:

CREATE TABLE [dbo].[TestTable]
(
	[id] [int] IDENTITY(1,1) PRIMARY KEY,
	[name] [nvarchar](100) NOT NULL,
	[firstname] [nvarchar](30) NULL DEFAULT (N''),
	[nick] [nvarchar](20) NOT NULL UNIQUE,
	[birthyear] [numeric](4, 0) NOT NULL CHECK ([birthyear] >= 1900),
	[birthdate] [date] NULL,
	[age]  AS (datepart(year,getdate())-[birthyear]), -- computed column, data type is not explicitly given
	[birthtime] [time](0) NULL, -- time only with 0 fractional seconds
	[birthglobally] [datetimeoffset](0) NULL, -- date and time with timezone
	[inserttimestamp] [datetime2](7) NOT NULL DEFAULT (getdate()) -- timespan data type is obsolete
)

There are only two schemas which cannot be deleted:
a) dbo – database owner, default schema;
b) sys – schema for system objects.

CREATE SCHEMA arw AUTHORIZATION dbo;
ALTER SCHEMA arw TRANSFER [dbo].[TestTable];
DROP SCHEMA arw; -- drop will fail if there are any objects in it[/sourecode]

Simple table with compression:
CREATE TABLE [dbo].[TestTable2]
(
	[id] [int] IDENTITY(1,1) PRIMARY KEY,
	[name] [nvarchar](100) NOT NULL,
)
WITH (DATA_COMPRESSION = ROW) -- compress table by row, use can use also 'PAGE'

EXEC sp_estimate_data_compression_savings  'dbo', 'TestTable2', NULL, NULL, 'ROW'; -- check compression savings for table (enterprise edition only)

Constraints:

CREATE TABLE [dbo].[TT2]
(
	[id] [int] IDENTITY(1,1),
	[name] [nvarchar](100) NOT NULL,
	[firstname] [nvarchar](30) NOT NULL,
	[nick] [nvarchar](20) NOT NULL,
	[birthyear] [numeric](4, 0) NOT NULL,
	CONSTRAINT PK_TT2_id PRIMARY KEY ([id]),
	CONSTRAINT UN_TT2_nick UNIQUE([nick]),
	CONSTRAINT CK_TT2_birthyear CHECK ([birthyear] >= 1900)
);

Alternative way:

ALTER TABLE [dbo].[TT2] 
ADD CONSTRAINT PK_TT2_id PRIMARY KEY ([id]);
ALTER TABLE [dbo].[TT2] 
ADD CONSTRAINT DF_TT2_nick DEFAULT('x') FOR [nick];

Foreign key:

ALTER TABLE [Production].[Products]  
ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY([categoryid])
REFERENCES [Production].[Categories] ([categoryid])

Check constraint is faster than trigger, but trigger allows you to put your custom message when it fails.

Create table as a copy:

SELECT * INTO [dbo].[TT2_COPY]
FROM [dbo].[TT2] -- create new table as a copy of existing one (including data)

SELECT * INTO [dbo].[TT3_COPY]
FROM [dbo].[TT2] -- create new table as a copy of existing one (excluding data)
WHERE 1 = 0

Alter

ALTER TABLE [dbo].[TT2]
ADD value int NOT NULL; -- add a new column to existing table

ALTER TABLE [dbo].[TT2]
ALTER COLUMN value NUMERIC(5,1) NOT NULL; -- modify a column of existing table

ALTER TABLE [dbo].[TT2] -- delete a column of existing table
DROP COLUMN value;

ALTER TABLE [dbo].[TT2]
DROP CONSTRAINT DF_TT2_nick -- delete existing constraint

DROP TABLE [dbo].[TT2_COPY] -- delete existing table
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s