Whats wrong with these indices?
-
When looking into an existing DB, I find tables defined as follows. This looks wrong to me. Are there any reasons why the indices might be created this way? Am I missing something?
Thanks for the help...
CREATE TABLE [t1] (
[SID] [varchar] (25) NOT NULL ,
[NAME_F] [varchar] (12) NULL ,
[NAME_MI] [varchar] (1) NULL ,
... 50 to 70 more elements similar to these ...
CONSTRAINT [PK_t1] PRIMARY KEY NONCLUSTERED
(
[SID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SIDIndex]
ON [_SE_Card]([SID]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO
CREATE TABLE [t2] (
[SID] [varchar] (25) NOT NULL ,
[Info1] [char] (1) NULL ,
... 50 to 70 more elements similar to these ...
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SIDIndex]
ON [t2]([SID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [PK_Card1] ON [t2]([SID])
ON [PRIMARY]
GO
-
Sorry, but what exactly is your question?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I goofed. I copied incorrect data into my post by mistake. The point of the question is/was that the two indices on a table refer to the same field. It seemed to me that a single primary key clustered index would make more sense that two indices, but I wondered if there might be some reason to define both indices. Here is the correct example.
CREATE TABLE [t1] (
[SID] [varchar] (25) NOT NULL ,
[NAME_F] [varchar] (12) NULL ,
[NAME_MI] [varchar] (1) NULL ,
....
CONSTRAINT [PK_t1] PRIMARY KEY NONCLUSTERED
( [SID] ) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SIDIndex]
ON [t1]([SID]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO
CREATE TABLE [t2] (
[SID] [varchar] (25) NOT NULL ,
[Info1] [char] (1) NULL ,
....
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SIDIndex]
ON [t2]([SID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [PK_t2] ON [t2]([SID])
ON [PRIMARY]
GO
-
It looks like you are creating two indices on the SID column of your tables.
To compound it, one is the primary key and the other is a unique index.
CREATE TABLE [t1] (
[SID] [varchar] (25) NOT NULL ,
[NAME_F] [varchar] (12) NULL ,
[NAME_MI] [varchar] (1) NULL ,
....
CONSTRAINT [PK_t1] PRIMARY KEY NONCLUSTERED (change this to CLUSTERED)
( [SID] ) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
-- Delete this entire index.
CREATE UNIQUE CLUSTERED INDEX [SIDIndex]
ON [t1]([SID]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO
========================
He was not wholly unware of the potential lack of insignificance.
-
sorry, lost track yesterday evening....
You might also want to consider eliminating this varchar(1) thing on NAME_MI
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply