Alter Table, Alter Column in MS SQL Server 2008
The Alter Column statement can modify the data type and the Nullable attribute of a column. The syntax is the same for SQL Server 2005 and SQL Server 2008 except 2008 allows the sparse attribute to be changed.
For the example below, we will begin by creating a sample table, then we will modify the columns.
CREATE TABLE dbo.Customer
(
CustomerID INT IDENTITY (1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
Address VARCHAR(150) NOT NULL
)
-- Change the datatype to support 100 characters and make NOT NULL
ALTER TABLE dbo.Customer
ALTER COLUMN FirstName VARCHAR(100) NOT NULL
-- Change datatype and allow NULLs for Address
ALTER TABLE dbo.Customer
ALTER COLUMN Address NVARCHAR NULL
-- Set SPARSE columns for Address (sql server 2008 only)
ALTER TABLE dbo.Employee
ALTER COLUMN Address VARCHAR(100) SPARSE NULL
Columns can be altered in place using alter column statement.
• Only the datatype, sparse attribute (2008) and the Nullable attribute of a column can be changed.
• You cannot add a NOT NULL specification if NULL values exist.
• In order to change or add a default value of a column, you need to use Add/Drop Constraint.
• In order to rename a column, you must use sp_rename.
More similar topics with SQL tips:
GUID formats and length.
Sparse Columns in SQL Server 2008
Oracle database - How to select several random records from a table
Database DB2 - How to select several random records from a table
MySQL database - How to select several random records from a table
PostgreSQL - How to select several random records from a table
Microsoft SQL Server - How to select several random records from a table