Sql Server material 8

Alter Command: After creating a table if we want to make any modifications to the structure of the table we use the Alter Command. Using alter command we can perform the following:

-Increase/Decrease the width of a column.

-Change the data type of a column.

-Change Null to Not Null and Not Null to Null

-Add a new column to the table.

-Drop an existing column from the table.

-Add a constraint to a column of the table.

-Drop an existing constraint present on a column from the table.

-To perform the first 3 operations the syntax is:

ALTER TABLE <TNAME> ALTER COLUMN <COLNAME> <DTYPE> [WIDTH] [NULL | NOT NULL]

First create a table as following:

            CREATE TABLE Students (SNO int, Sname varchar(50), Class int)

Increasing the width of a column:     

            ALTER TABLE Students ALTER COLUMN Sname varchar(100)

Decreasing the width of a column:    

            ALTER TABLE Students ALTER COLUMN Sname varchar(25)

Changing the data type of the column:         

            ALTER TABLE Students ALTER COLUMN Sname nvarchar(25)

Adding a Not Null Constraint:

            ALTER TABLE Students ALTER COLUMN Sname nvarchar(25) Not Null

Removing a Not Null Constraint:

            ALTER TABLE Students ALTER COLUMN Sname nvarchar(25) Null

Syntax to add a new column:

            ALTER TABLE <TNAME> ADD <COLNAME> <DTYPE> [<WIDTH>] 

[ [CONSTRAINT <CONS NAME>] <CONS TYPE> ]

Adding a column to the table with out any constraint:         

ALTER TABLE Students ADD Fees Money

Adding a column to the table with a constraint:

            ALTER TABLE Students ADD Sid int Constraint Sid_UQ UNIQUE

Syntax to drop an existing column:

            ALTER TABLE <TNAME> DROP COLUMN <COLNAME>

Dropping the Sid Column:

            ALTER TABLE Students DROP COLUMN Sid

Syntax to Add a Constraint:

ALTER TABLE <TNAME> ADD [ CONSTRAINT <CON NAME> ] <CONS TYPE> (COLLIST)

Adding a check constraint on the Fees column:

            ALTER TABLE Students ADD Constraint Fees_CK Check (Fees>1500)

Adding a primary key constraint on the Sno column:

            ALTER TABLE Students ALTER COLUMN SNO INT NOT NULL

            ALTER TABLE Students ADD Constraint Sno_PK Primary Key(SNO)

Syntax to Drop a Constraint:

            ALTER TABLE <TNAME> DROP CONSTRAINT <CONS NAME>

Dropping the check constraint present on the Fees column:

            ALTER TABLE Students DROP Constraint Fees_CK

Drop Command: If we want to destroy the existing tables present in the database we use the Drop Command.

Syntax: DROP TABLE <TNAME>

Dropping the Students Table:

            DROP TABLE Students                 

Truncate Command: Removes all rows from a table. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause specified.

Syntax: TRUNCATE TABLE <TNAME>

Truncating the EMP Table:

            TRUNCATE TABLE Students

The difference between Truncate and Delete is:

• Truncate table is faster in execution.
• Truncate will reset the identity function if present on the table to initial value again which will not happen in delete.

<<< Previous                                                                                                                            Next >>>