Data Definition Language Commands in SQL Server

Create:

It Creates a table. Each new table field is defined with a name, type, precision, scale, null value support, and referential integrity rules.

Syntax:

CREATE TABLE [database name].[schema name].[schemaname].tablename(col name datatype,[….n])

Example:

CREATE TABLE emp(empno int, empname varchar(20))

Alter:

Modifies an existing table. We can modify the name, type, precision, scale, null value support, and referential integrity rules for each field in the table. We can add column, drop column, add constraints etc.

Syntax:

ALTER TABLE TableName1 ALTER [COLUMN] FieldName2 [NULL | NOT NULL] [SET DEFAULT eExpression2]

Example:

Adding a new column

ALTER TABLE emp ADD COLUMN hr int, da int, ta int

Dropping a column

ALTER TABLE DROP COLUMN hr, COLUMN da

Changing the data type of a column

ALTER TABLE emp ALTER COLUMN ename varchar (25)

Adding a column with a constraint

ALTER TABLE emp ADD hr varchar (20) NULL CONSTRAINT hr_uni UNIQUE

Adding a default value

ALTER TABLE emp ADD date NULL CONSTRAINT date_def DEFAULT GETDATE() WITH VALUES

Adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

Drop:

Removes on or more table definitions and all data, indexes, triggers, constraints, permission specifications for those tables. Any view or stored procedure that references the dropped table must be explicitly dropped by using drop view or drop procedure.

Example:

DROP TABLE emp

Truncate:

Removes all rows from a table without logging the individual row deletions. Truncate table is functionally the same as the DELETE statement with no where clause. However, truncate table is faster than the delete statement.

Syntax:

TRUNCATE TABLE [database name].[schema name].[schema name].[table name]

Example:

TRUNCATE TABLE emp
Previous Post
Leave a comment

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

%d bloggers like this: