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