ALTER TABLE Statement in SQL Server(TSQL)

dbtut

6 years ago

We can perform the following operations with ALTER TABLE Statement.

Add a column to a table
Delete a column from a table
Change the data type of a column
Increase or decrease the size of a column
Switch Partition
Enable or disable Constraints or triggers

In this article I will describe the above items through an example. In order to use our examples, we create a table as below and add a few records.

USE [TestDB]
GO
CREATE TABLE [dbo].[Person](
[ID] [int] NOT NULL,
[Name] [varchar](10) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Person] VALUES (1,'Nurullah'),(2,'Faruk')

USE [TestDB]

CREATE TABLE [dbo].[Person](

[ID] [int] NOT NULL,

[Name] [varchar](10) NOT NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Person] VALUES (1,'Nurullah'),(2,'Faruk')

Add a column to the table:

We are adding a column in the varchar (10) data type with the help of the following script. After adding the column, we see that when we read the table with SELECT, the values of the column that we added are returned as NULL for existing records.

ALTER TABLE dbo.Person ADD Surname varchar(10) NULL
Select * FROM dbo.Person

1 2	ALTER TABLE dbo.Person ADD Surname varchar(10) NULL Select * FROM dbo.Person

Drop a column from the table:

We drop the column we added with the help of the following script.

ALTER TABLE dbo.Person DROP COLUMN Surname
Select * FROM dbo.Person

1 2	ALTER TABLE dbo.Person DROP COLUMN Surname Select * FROM dbo.Person

Change the column’s data type:

Changing the column’s data type is a bit more complicated. If we change the data type of the column, the values in the column must match the new data type.

With the following script, we convert the data type of the Name column from varchar (10) to nvarchar (10).

ALTER TABLE dbo.Person ALTER COLUMN [Name] nvarchar(10);

1	ALTER TABLE dbo.Person ALTER COLUMN [Name] nvarchar(10);

When there are text data in the column, if we try to change the data type to int, we will get the error as follows.

ALTER TABLE dbo.Person ALTER COLUMN [Name] int;

1	ALTER TABLE dbo.Person ALTER COLUMN [Name] int;

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the nvarchar value ‘Nurullah’ to data type int.

The statement has been terminated.

Increase or decrease the column’s size:

Set column size as a larger value:

Let’s increase the size of the column without changing the data type. We can do this quickly without any interruption.(Even if the table is too large)

ALTER TABLE dbo.Person ALTER COLUMN [Name] nvarchar(100);

1	ALTER TABLE dbo.Person ALTER COLUMN [Name] nvarchar(100);

Set column size as a smaller value:

This process is a little more complicated. Because the column’s current data may not allow it. Let’s go back to our example. There are two records in the table “dbo.Personel” (Nurullah and Faruk). Let’s try to set the data type of this column as varchar (5).

ALTER TABLE dbo.Person ALTER COLUMN [Name] varchar(5);

1	ALTER TABLE dbo.Person ALTER COLUMN [Name] varchar(5);

Since the largest data in the table is 8 characters, the smallest value we can set is varchar (8). If we try to set as a smaller value, we get the error as follows.

Msg 8152, Level 16, State 2, Line 1

String or binary data would be truncated.

The statement has been terminated.

You can use the following script to calculate the maximum data length of the column in the table.

SELECT MAX(LEN([NAME])) AS MAXIMUM_NAME_LENGHT FROM dbo.Person;

1	SELECT MAX(LEN([NAME])) AS MAXIMUM_NAME_LENGHT FROM dbo.Person;

Switch Partition:

You can find details in the article “Sliding Window-Switch Partition-Split Range-Merge Range“.

To enable or disable Constraint or Triggers:

With ALTER Table we can add and delete constraint. You can find details in the article “How To Create CHECK CONSTRAINT“. You may want to read the article “SQL Server Trigger Types“.

With the following script, we disable the trigger named myTrigger in the table.

ALTER TABLE dbo.Person DISABLE TRIGGER myTrigger;

1	ALTER TABLE dbo.Person DISABLE TRIGGER myTrigger;

With the following script, we enable the trigger named myTrigger in the table.

ALTER TABLE dbo.Person ENABLE TRIGGER myTrigger;

1	ALTER TABLE dbo.Person ENABLE TRIGGER myTrigger;

With the following script, we disable the constraint named myConstraint in the table.

ALTER TABLE dbo.Person NOCHECK CONSTRAINT myConstraint;

1	ALTER TABLE dbo.Person NOCHECK CONSTRAINT myConstraint;

With the following script, we enable the constraint named myConstraint in the table.

ALTER TABLE dbo.Person CHECK CONSTRAINT myConstraint;

1	ALTER TABLE dbo.Person CHECK CONSTRAINT myConstraint;