Sample Table and Index Creation Script for SQL Server 2005

This is an example table and index creation script for my current MCIS-4423 class at University College, Denver University. Since I am a lazy DBA, I typically like to create objects in the GUI of SQL Server Management Studio (SSMS) first, then script out the the final product using the "Generate Change Script" button, or by right-clicking on the object and choosing "Script Table As"… "CREATE To" …  "New Query Editor Window". Then I can go in and cleanup what SSMS generated.

At most companies, you need to make sure your database scripts are "re-runnable", which means that you can’t just blindly CREATE objects without checking to see if they already exist and/or DROP them before you try to create them. Of course, you want to be very careful doing that with tables (in fact you would want to do an ALTER TABLE on an existing table).

Finally, If you have SQL Server 2005 Enterprise Edition, you can take advantage of ONLINE index operations, which let you create or rebuild an index without having to take an outage or locking the table. In a production environment, you should also specify the MAXDOP property (which limits the number of processor cores used for index creation) to a value of roughly 25% of your available CPU cores. Otherwise, the index creation can take up too much of your available CPU capacity. This will obviously make the index creation take longer, but that is better than affecting your production system.

 

-- Sample Table Creation and Index Creation script for MCIS-4423
-- This script is designed to be "re-runnable", but you need to be careful, 
-- Since this will DROP the table, which would be bad if it was an existing table with data

-- Make sure you are in the correct database
USE [AdventureWorks]
GO

-- Drop Check Constraint
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Team_TeamID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Team]'))
ALTER TABLE [dbo].[Team] DROP CONSTRAINT [CK_Team_TeamID]
GO

-- Drop Table
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND type in (N'U'))
DROP TABLE [dbo].[Team]
GO

-- Create Table
CREATE TABLE [dbo].[Team](
    [TeamID] [char](3) NOT NULL,
    [TeamName] [varchar](20) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [StateCode] [char](2) NULL,
    [PostalCode] [char](5) NULL,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED  
(
    [TeamID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Add Check Constraint
ALTER TABLE [dbo].[Team]  WITH CHECK ADD  CONSTRAINT [CK_Team_TeamID] CHECK  (([TeamID] like '[A-Z][A-Z][A-Z]'))
GO
ALTER TABLE [dbo].[Team] CHECK CONSTRAINT [CK_Team_TeamID]
GO

-- Drop index if it exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND name = N'IX_Team_TeamName')
DROP INDEX [IX_Team_TeamName] ON [dbo].[Team] WITH ( ONLINE = OFF )
GO

-- Add non-clustered index on StateCode column
-- Use ONLINE = ON if you have Developer or Enterprise Edition
-- Use MAXDOP = 2 (set to roughly 25% of the number of CPU cores to keep index creation from affecting performance)
CREATE NONCLUSTERED INDEX [IX_Team_TeamName] ON [dbo].[Team] 
(
    [StateCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, MAXDOP = 2, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
Technorati Tags:

This entry was posted in SQL Server 2005. Bookmark the permalink.

2 Responses to Sample Table and Index Creation Script for SQL Server 2005

  1. Paul says:

    Thank you! Just what I needed.

  2. Pingback: Web-Service: How to authenticate users with the SQL Server database table using VB | PHP Developer Resource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s