To add an index in SQL Server use the CREATE INDEX statements. When adding indexes remember there can only be one clustered index per table. The main options when creating an index are clutered or nonclustered or unique vs non unique. Using SQL 2005+ you can also specify columns to include at the leaf level of the index. Create a single nonclustered index [cc lang=”sql”] CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name [/cc] Create a multi-column (composite) nonclustered index [cc lang=”sql”] CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber_PresidentName — specify index name ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names [/cc] Create a multi-column (composite) clustered index [cc lang=”sql”] CREATE UNIQUE CLUSTERED INDEX IX_C_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names [/cc] Create a non clustered index with included columns [cc lang=”sql”] CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name INCLUDE (President,YearsInOffice,RatingPoints) — specify included columns [/cc] Create index with fill factor [cc lang=”sql”] CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name ON dbo.Presidents (PresidentNumber) — specify table and column name WITH (FILLFACTOR = 80) — specify the fill factor [/cc] SQL Server 2008 options SQL Server 2008 provides additional index options. Most notably it provides the ability to filter an index. This can help decrease the size of the index for very large or partitioned table to only include the records that are normally […]
Continue reading ...
In order to add a primary key to an existing table we need to use the Alter Table command. Add a primary key for a new column [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD PresidentID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_PresidentID PRIMARY KEY NONCLUSTERED [/cc] Note however that the ordering of the identity field will not be predictable for the records that already exist in the table. For newly inserted records, the identity field will increment properly. Add a primary key for an existing column [cc lang=”sql”] ALTER TABLE dbo.Presidents WITH NOCHECK ADD CONSTRAINT PK_PresidentNumber PRIMARY KEY NONCLUSTERED (PresidentNumber) [/cc]
Continue reading ...
Below is the basic syntax for the create table script: [cc lang=”sql”] CREATE TABLE dbo.Employees ( EmployeeID int IDENTITY (1,1) NOT NULL CONSTRAINT PK_EmployeeID PRIMARY KEY NONCLUSTERED ,FirstName varchar(50) NULL ,LastName varchar(50) NULL ,SSN varchar(9) NULL CONSTRAINT ssn_unique CHECK (SSN like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’) UNIQUE ,IsTerminated bit NOT NULL DEFAULT 0 ,DateAdded datetime DEFAULT GETDATE() ,Comments varchar(255) SPARSE NULL — SQL Server 2008 sparse column ) [/cc]
Continue reading ...
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]
Continue reading ...
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 ...
