ALTER the Database

Syntax to ALTER the Database:

ALTER DATABASE DATABASENAME.ADD FILE(NAME=’USER DEFINED NAME’,FILENAME=’PHYSICAL ADDRESS’,SIZE= MB,MAXSIZE= MB)

Example:

ALTER DATABASE NRSTT ADD FILE(NAME=’NRSTTNDF’,FILENAME=’C:\Program Files\Microsoft Sql Server\Mssql\Data\NRSTT.NDF’SIZE=5 MB,MAXSIZE=25 MB,FILEGROWTH=5 MB)

The above query extends the NRSTT database to 5mb by adding new file NRSTT.NDF

Syntax to ALTER the Table:

In Three ways we can modify the tables

1. By adding the new column to the Existing Table
Syntax:

ALTER TABLE TABLENAME ADD NEWCOLUMN DATATYPE [,……..N]

Example:

ALTER TABLE EMP ADD BONUS MONEY

The above statement adds Bonus column to EMP table

2. By changing the Data type of an Existing column:

Syntax:

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME NEWDATATYPE

Example: ALTER TABLE EMP ALTER COLUMN EMPNO BIGINT

The above statement changes the EMPNO data type from INT to BIGINT.

3. By Dropping the Existing column from Existing Table:

Syntax: ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME [,……..N]

Example: ALTER TABLE EMP DROP COLUMN ENAME, SAL

The above statement deletes two existing columns ENAME, SAL from EMP Table.

ALTER the Database