SQL Server Merge Statement

Examples, best practices, uses, and benefits of the SQL Server 2008 MERGE statement. Continue reading ...

Rename Column

In order to rename a column name, you must use sp_rename. The syntax of sp_rename is the following: exec sp_rename ‘tablename.ColumnName’, ‘NewColumnName’, ‘column’ –objecttype Here is an example: [cc lang=”sql”] CREATE TABLE Employee ( ID int, FName varchar(50) ) GO EXEC sp_rename ‘Employees.FName’, ‘FirstName’, ‘column’ [/cc] After executing the script above, you will receive the following informational message: This comes up as a reminder that the rename you just performed may have a negative effect on any stored procedures referencing the column. Make sure to check to see if any stored procedures are referencing the column prior to renaming it. If the column has a constraint, you must drop the constraint prior to renaming the column. Otherwise you will get the following error message: [code] Msg 15336, Level 16, State 1, Procedure sp_rename, Line 444 Object ‘Orders.OrderAmount’ cannot be renamed because the object participates in enforced dependencies. [/code] To drop the constraint, use the following example: [cc lang=”sql”] ALTER TABLE Employee DROP CONSTRAINT pk_employee [/cc]

Alter Table Alter Column

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. [cc lang=”sql”] CREATE TABLE dbo.Employee ( EmployeeID INT IDENTITY (1,1) NOT NULL ,FirstName VARCHAR(50) NULL ,MiddleName VARCHAR(50) NULL ,LastName VARCHAR(50) NULL ,DateHired datetime NOT NULL ) — Change the datatype to support 100 characters and make NOT NULL ALTER TABLE dbo.Employee ALTER COLUMN FirstName VARCHAR(100) NOT NULL — Change datatype and allow NULLs for DateHired ALTER TABLE dbo.Employee ALTER COLUMN DateHired SMALLDATETIME NULL — Set SPARSE columns for Middle Name (sql server 2008 only) ALTER TABLE dbo.Employee ALTER COLUMN MiddleName VARCHAR(100) SPARSE NULL [/cc] 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. Continue reading ...

Alter Table Add Column

Adding a column to a table in SQL Server is done using the ALTER TABLE tablename ADD command. When adding columns you can specify all the same settings available when creating a table. In the example below, we will create a small sample table, then add columns using the ALTER TABLE command. Multiple columns can be specificied by using the syntax below. First issue an alter table command then add the column name, data type, nullable, and default value. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..#employees’) IS NOT NULL BEGIN DROP TABLE #employees END CREATE TABLE #employees ( EmployeeID int IDENTITY (1,1) CONSTRAINT PK_employee NOT NULL PRIMARY KEY CLUSTERED ,FirstName VARCHAR(50) ) GO INSERT INTO #employees (FirstName) VALUES (‘John’); ALTER TABLE #employees ADD LastName varchar(50) NULL ,SSN varchar(9) NULL CONSTRAINT ssn_unique UNIQUE ,IsTerminated bit NOT NULL DEFAULT 0 ,DateAdded datetime NOT NULL DEFAULT GETDATE() ,DateModified DATETIME NULL DEFAULT GETDATE() ,Comments varchar(255) SPARSE NULL — SQL Server 2008 sparse column SELECT * FROM #employees [/cc] Also note, when you add columns with a default value, the value will only get populated when you specify the column as NOT NULL. Otherwise, NULL values will populate the column initially. However once the column is created, subsequent insert statements will populate the default value. In the above example, DateAdded is initially populated whereas DateModified is only populated after a row is inserted.