Add a column, with a default value, to an existing table in SQL Server

Add a column, with a default value, to an existing table in SQL Server

Syntax:

ALTER TABLE TABLE_NAME

            ADD COL_NNAME COL_TYPE {NOT NULL OR NULL}

                        CONSTRAINT CONSTRAINT_NAME

                                    DEFAULT DEFAULT_VALUE

WITH VALUES

For example

ALTER TABLE CUSTOMER

            ADD NAME VARCHAR (255) NOT NULL

                        CONSTRAINT CUSTOMER_CONSTRAINT_NAME

                                    DEFAULT 'PENDING CUSTOMER'

WITH VALUES

In the above query "WITH VALUES" handles the "not null".