SqlTutorial-1 : Lesson-1 : Class-2: Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Altering Db-Tables and Adding Identity Column to it

You can alter the table for two purposes.

1. Columns to be added, modified(type & size), deleted.(But not renamed, use IDE or SP for it)
2. Constraints to be added, modified, deleted, disabled/enabled.(See Integrity Constraints)

Syntax:

ALTER TABLE table_name [WITH CHECK|WITH NOCHECK]
       { ADD column_name data_type [column_attributes] |
         DROP COLUMN column_name |
         ALTER COLUMN column_name new_type [NULL|NOT NULL] |
         ADD [CONSTRAINT] new_constraint_definition |
         DROP [CONSTRAINT] constraint_name}

Examplex:(Working with Columns only. For constraints see Integrity Constraints)

(1) Adding a new Column.

CREATE TABLE employee(
        id INTEGER NOT NULL PRIMARY KEY,
        name VARCHAR(10),
        salary DECIMAL(10,2),
        region VARCHAR(10),
        city VARCHAR(20),
);
ALTER TABLE employee ADD telephone_no CHAR(12) NULL

(2)Deleting Existing Column.

ALTER TABLE table-name
     DROP COLUMN column-name [, next-column-name]...

ALTER TABLE employee DROP COLUMN city, telephone_no.

(3) Emptying a column.

ALTER TABLE employee DROP COLUMN telephone_no
ALTER TABLE employee ADD telephone_no CHAR(12) NULL

(4) Changing length of a column. [ varchar(30) to varchar(50) ]

CREATE TABLE employee (
        emp_id int NOT NULL PRIMARY KEY,
        emp_name varchar(30) NOT NULL,
        mgr_id int NOT NULL REFERENCES employee(emp_id)
)
ALTER TABLE employee 
ALTER COLUMN emp_name varchar(50)

You can also decrease the size of a column. But be careful about data lost.

(5) Changing type of a column.( INTEGER to VARCHAR(200) but reverse not possible)

ALTER TABLE Billings
  ALTER COLUMN BankerID VARCHAR(200)

(6) Adding computed Column.

ALTER TABLE employee
   ADD CostPerUnit AS (col1/Col2)

(7) Designating a Column's Collation

ALTER TABLE employee
   ADD Name1 nvarchar(50) COLLATE Icelandic_CI_AI,
       Name2 nvarchar(50) COLLATE Ukrainian_CI_AS;

Adding IDENTITY Column (Auto-incremented-Valued Column)

Few aspects of IDENTITY columns

1. IDENTITY property enables you to assign an auto-number to a numeric column.
2. The values for IDENTITY propertied column are generated automatically.
3. The data type for IDENTITY column must be int, bigint, smallint, tinyint, decimal, or numeric
   with a scale of 0.
4. The default value of Seed and Increment is 1.
5. By default, once a column has been assigned the IDENTITY property, SQL server does not allow explicit values to be inserted into it.
6. IDENTITY column can be referenced by either column name (like other columns) or IDENTITYCOL property. E.g., SELECT IDENTITYCOL from abc.

Syntax:

Column_name type IDENTITY(seed,increment)] NOT NULL

Examples of creation IDENTITY column.

(1) IDENTITY column with (1,1)

CREATE TABLE Groups ( Id int IDENTITY (1, 1) NOT NULL )

(2) IDENTITY column with (+ve, -ve)

MyID Int IDENTITY(1000000, -100) NOT NULL

(3) IDENTITY column with (-ve, +ve)

MyID Int IDENTITY(-1000000,100) NOT NULL

(4) IDENNTITY column as NOT NULL/ PRIMARY KEY

cust_id int IDENTITY NOT NULL PRIMARY KEY

(5) Turning on/off the IDENTITY property. (Inserting explicit value in IDENTITY column)

CREATE TABLE MyTable(
    MyID Int IDENTITY(-1000000, 100) NOT NULL ,
    MyDescription VarChar(50) NOT NULL
  )
SET IDENTITY_INSERT MyTable ON
INSERT into MyTable VALUES (5, 'This will work')
SET IDENTITY_INSERT MyTable OFF

(6) Reseeding the IDENNTITY column to a value.(say 10)

DBCC CHECKIDENT(‘TableName’, RESEED, 10)

For RESEEDING, the IDENTITY COLUMN must not be primary key.

Examples of getting IDENTITY information.

(1) Getting current Identity value.

SELECT IDENT_CURRENT('customer')

(2) Extracting IDENTITY column values using IDENTITYCOL property.

SELECT IDENTITYCOL from Mytable where id=101

(3) Returning IDENTITY column’s Seed and Incrementing value.

SELECT IDENT_SEED('MyTable') AS seed, IDENT_INCR('MyTable') AS increment

(4) Getting maximum IDENTITY/current identity value using Global variable @@Identity.

Select @@Identity from employee. // equals to
Select Max(IDENTITYCOL) from employee //equals to
Select IDENT_CURRENT(‘employee’)

(5) Getting Last identity value generated for any table in the current session, for the current scope.

SELECT Scope_Identity() AS Expr1

1. @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
2. @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope