How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

 

In SQL Server 2000 and SQL Server 2005, you can use the ALTER TABLE statement to add a new column with a default value to an existing table. Here's how you can do it with an example:

Assuming you have a table named Customers in your database and you want to add a new column named IsActive with a default value of 1:

sql

-- SQL Server 2000
ALTER TABLE Customers
ADD IsActive BIT DEFAULT 1

-- SQL Server 2005
ALTER TABLE Customers
ADD IsActive BIT DEFAULT 1
WITH VALUES

In the above example, we are using the ALTER TABLE statement with the ADD clause to add a new column named IsActive of data type BIT (boolean) to the Customers table. We are also using the DEFAULT constraint to specify that the default value for the IsActive column should be 1.

In SQL Server 2005, there's an additional option WITH VALUES which sets the default value for existing rows in the table. If you omit this option, the default value will be applied only to new rows that are inserted after the column is added.

After running the appropriate ALTER TABLE statement, the new column will be added to the Customers table, and existing rows (if WITH VALUES is used) will have the default value of 1 for the IsActive column.

Please note that SQL Server 2000 and SQL Server 2005 are quite old versions of SQL Server, and they have reached the end of their support lifecycle. Consider upgrading to a newer version of SQL Server for security updates and improved features.