DDL Commands In Sql Server

Data Definition Language: This is the definition level language which includes the following statements.

1. CREATE Statement
2. ALTER Statement
3. DROP Statement
4. TRUNCATE Statement

1. CREATE Statement: This Statement is used for creating the database and its objects (Tables, Views, Indexes, Views, User Defined Stored Procedures, User Defined Functions, Triggers, Rules,  Defaults )

2. ALTER Statement: This Statement is used for modifying  the database and its objects (Tables, Views, Indexes, Views, User Defined Stored Procedures, User Defined Functions, Triggers, Rules,  Defaults )

3. DROP Statement: This Statement is used for deleting the database and its objects (Tables, Views, Indexes, Views, User Defined Stored Procedures, User Defined Functions, Triggers, Rules,  Defaults )

4. TRUNCATE Statement: This statement is used to delete the data available in a table in Row-By-Row manner but with out disturbing its structure (columns).

Syntax to create the database: 

CREATE DATABASE DATABASENAME

E.g.: CREATE DATABASE SAMPLE

After writing the above Query in SQL Server Management Studio then select it and press F5. Then server creates database with name SAMLE, that we can check it in Database list box in SSMS

Database files and File groups

A database file is nothing but an operating system file. A database spans at least two, and possible several database files. These files are specified when database is created or altered. Every database must span at least two files. One for the data And one for transaction log.

SQL Server 2005 allows three types of database files.

Primary data files: Every database has one primary data file. This file contains the startup information for the database and is used to store data. The name of primary database file has the extension MDF(master data file).

Secondary data files: These files hold all of the data that does not fit into the primary data file. A database can have zero or more Secondary data files. The name of secondary database file has the extension NDF(next data file).

Transaction Log files: These files hold the log information used to recover the database. There must be at least one log file for each database. The name of a Log file has the extension LDF(log data file).

Each database file has five properties:

            à Logical File Name.             Name

            à Physical File Name.           Filename

            à Initial Size.                         Size

            à Maximum Size.                  Maxsize

            à Growth increment.             Growth

After Creating the Database server arranges internally two files.

1.      Primary File: It was defined by the server with 1.18 mb size and with extension “. mdf” (master data file) for holding start up information of the database.

      Ex: SAMPLE. Mdf

2.      Log File: It was defined by the server with 504 kb size and with extension

 “. ldf” (log data file) for holding transaction information of the database.

Ex: SAMPLE. ldf

Syntax to create a TABLE:

CREATE TABLE Tablename(Column1 Datatype,

 Column2 Datatype,…………………)

E.g. CREATE TABLE CUSTOMER (CNO INT, CNAME VARCHAR (20),

       CITY VARCHAR (20))

E.g. CREATE TABLE EMP (EMPNO INT, ENAME VARCHAR (20),

         SAL MONEY, DEPTNO INT)

The above two queries creates two tables with names CUSTOMER, EMP

Syntax to ALTER the Database:

ALTER DATABASE DATABASENAME

      ADD FILE

(NAME          =       ’Any NAME’,

  FILENAME =    ‘PHYSICAL ADDRESS’,

  SIZE             =<n>MB,

  MAXSIZE   =<n.>MB,

  FILEGROWTH=<n>MB)

 

E.g. ALTER DATABASE SAMPLE ADD FILE

(NAME=‘RAM’,

FILENAME=’D:\ProgramFiles\MicrosoftSQL Server\MsSQL.1\MSSQL\Data\SAMPLE.NDF’

  SIZE         =5 MB,

 MAXSIZE =25 MB,

FILEGROWTH=5 MB)

The above query extends the SAMPLE database to 5mb by adding new file SAMPLE.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]

E.g.: 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

E.g.: 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]

E.g.: ALTER TABLE EMP DROP COLUMN ENAME, SAL

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

Syntax to DROP Database

DROP DATABASE DATABASENAME

E.g.   DROP DATABASE SAMPLE

T he above statement deletes SAMPLE database and its objects

Note: It is not possible to drop a particular database which is currently in use it means it is not possible to drop SAMPLE database and till we enter in to another database

Syntax to DROP the Table:

DROP TABLE TABLENAME [,…………..n]

E.g. DROP TABLE EMP

The above statement deletes EMP table including its structure (columns)

E.g. DROP TABLE CUSTOMER, STUDENT

The above statement deletes customer and student tables at a time. It means we can drop multiple tables at a time.

Note: Users can DROP multiple Databases at a time by placing comma (,) between the database names but it is not recommended approach.

Syntax for TRUNCATE Statement:

TRUNCATE TABLE TABLENAME

E.g. TRUNCATE TABLE EMP

The above statement deletes all data available in EMP table in PAGE-BY-PAGE manner (all at once) with out disturbing its structure (Columns)