Different creates of clustered indexes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Different creates of clustered indexes

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-08-15 : 07:15:00

Is there any difference in the result of the two following table creates?

CREATE TABLE dbo.Centers2 (
	[CenterID] [int] IDENTITY (1011, 100) UNIQUE NOT NULL ,
	[ClientID] [int] NOT NULL ,
	[CenterName] [nvarchar] (200) NOT NULL ,
	[Inactive] [bit] NOT NULL  DEFAULT 0
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX IDX_CentersClustered 
  ON dbo.Centers2 (ClientID, CenterID, Inactive)
GO

- and -

CREATE TABLE dbo.Centers2 (
	[CenterID] [int] IDENTITY (1011, 100) UNIQUE NOT NULL ,
	[ClientID] [int] NOT NULL ,
	[CenterName] [nvarchar] (200) NOT NULL ,
	[Inactive] [bit] NOT NULL  DEFAULT 0
	CONSTRAINT [IDX_CentersCluster] UNIQUE CLUSTERED 
	(
		ClientID,
		CenterID,
		Inactive
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
) ON [PRIMARY]
GO

-- Corrected column names...

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-15 : 08:03:09

You mean apart from the fact that the second tries to create a constraint to non-existent columns?

I don't think there's any difference between them, but I prefer the second syntax, that's simply to have all constraints created within the table definition itself, so that I dont have to do any checking for if the table got created in the first place before it runs the create constraints. That's just me being lazy.

OS

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-15 : 08:22:41

Well the second creat table has some errors since the columns:
CourseID,
ModuleID,
ModuleFilename,
ModuleSequence
Don't exist in the table and so the constraint (and table) cannot be created.

If You alter the syntax to:


CREATE TABLE dbo.Centers2 (
        [CenterID] [int] IDENTITY (1011, 100) UNIQUE NOT NULL ,
        [ClientID] [int] NOT NULL ,
        [CenterName] [nvarchar] (200) NOT NULL ,
        [Inactive] [bit] NOT NULL DEFAULT 0
CONSTRAINT [IDX_CentersCluster] UNIQUE CLUSTERED
        (
                ClientID, CenterID, Inactive
        ) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY]
GO

<EDIT: Aaaarghh!
How do you indent ???, Looks good in edit but not in preview...
Thank You Sam/>

Then the results are the same.
Except! for the FILLFACTOR.
In the first case the default fill factor is used ( usually 0 ), in the second case the fill factor is 80.
See "fill factor option" in BOL.

/rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-15 : 08:26:16

Follow up Question:

WHY !?! does the Unique Index NOT appear in sysobjects in the first case,
But DOES so in the second case!
Confounded.

/rockmoose


CREATE TABLE dbo.Centers2 (
	[CenterID] [int] IDENTITY (1011, 100) UNIQUE NOT NULL ,
	[ClientID] [int] NOT NULL ,
	[CenterName] [nvarchar] (200) NOT NULL ,
	[Inactive] [bit] NOT NULL  DEFAULT 0
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX IDX_CentersCluster
  ON dbo.Centers2 (ClientID, CenterID, Inactive)
GO
select * from sysindexes where id = object_id('Centers2')
select * from sysobjects where name like '%IDX_CentersCluster%'

drop table dbo.Centers2
go

CREATE TABLE dbo.Centers2 (
	[CenterID] [int] IDENTITY (1011, 100) UNIQUE NOT NULL ,
	[ClientID] [int] NOT NULL ,
	[CenterName] [nvarchar] (200) NOT NULL ,
	[Inactive] [bit] NOT NULL  DEFAULT 0
	CONSTRAINT [IDX_CentersCluster] UNIQUE CLUSTERED 
	(
		ClientID, CenterID, Inactive
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
) ON [PRIMARY]
GO

select * from sysindexes where id = object_id('Centers2')
select * from sysobjects where name like '%IDX_CentersCluster%'

drop table dbo.Centers2
go

Kristen
Test

22859 Posts

Posted - 2004-08-15 : 08:39:23

Sam, I prefer the different bits and pieces separated out (style 1). Many times I have had to drop a constraint, or an Index, to get some data imported, or whatever, and then put it back. If the "build script" for the database has these things separated out it is easier to run just the bits of it that are needed.

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2004-08-15 : 08:41:33

If you want to indent, use [ code ] [ /code ] but omit the spaces.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources