SQL SERVER Tips and Tricks

DDL: Data Definition Language

ALTER command is used to alter/change the structure of database objects.

Structure of database objects viz., database, tables,views,functions , procedures ,etc., can be altered using ALTER DDL Command.

In this article I will discuss about how database objects can be altered using ALTER Command.

• ALTER DATABASE:

Using ALTER Command on database following actions can be performed.

1. Configuration of the database can be changed
2. Database can be renamed
3. All the properties of the database can be reset to default values.

Here is the example to rename TEST database to TEST1

ALTER DATABASE TEST MODIFY NAME= TEST1

Similarly we can set the configuration parameters of database using the following syntax:

ALTER DATABASE <DataBase Name> SET <Config Parameter Name>= <Value of config Parameter>

ALTER TABLE:

Structure of the table can be altered or changed using ALTER Command.

Mostly alter command is used on tables to perform add,change and drop columns in a table.
Lets assume we have a table with the following structure:

Here is the example to perform alter actions on table.

CREATE TABLE MOBILE
(
MODEL VARCHAR(100),
PRICE INT
)

• Add New columns to table  :  Following statement adds new column BRAND with the datatype VARCHAR(10) to MOBILE table. 

  ALTER TABLE MOBILE ADD BRAND VARCHAR(10)

• Change the data type of existing coulmns: Following statement modifies the datatype of BRAND column from VARCHAR(10) to VARCHAR(20)

ALTER TABLE MOBILE ALTER COLUMN BRAND VARCHAR(20)

• Drop existing columns: Following statement Drops BRAND with BRAND column from MOBILE table.  

ALTER TABLE MOBILE DROP COLUMN BRAND

Syntax for altering procedure and function will be same as create syntax except for replacing the word CREATE with ALTER.

Synatax for ALTER PROCEDURE:
ALTER PROCEDURE <Procedure Name>
AS
BEGIN
 -- SQL Statements
END

Synatax for ALTER PROCEDURE:
ALTER FUNCTION <Function Name>(input parametres)
RETURN Output parameter
AS
BEGIN
 -- SQL Statements
END

Triggers can be altered using ALTER TRIGGER Command to chnage the properties of trigger:

That is ALTER TRIGGER can be used to

1. Enable a  TRIGGER
2. Disable a TRIGGER
3. Rename  TRIGGER
4. Compile  TRIGGER

Here is an example to disable ABC_TGR trigger

ALTER TRIGGER ABC_TGR DISABLE
Summary:

This article gives deatils explaination of how and wherer ALTER command can be used in SQL Server.

Command Used in this Article:ALTER,DATABASE,TABLE,ADD,MODIFY,DROP,COLUMN,ENABLE,DISABLE,SET,
PROCEDURE,FUNCTION,TRIGGER