Index Question
-
I have table "List"
CREATE TABLE(
[listid] [bigint] IDENTITY (1, 1) NOT NULL ,
[enterpriselistid] [bigint] NULL ,
[countrycode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[areacode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[number] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[disposition] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[callstart] [datetime] NULL ,
[recentattempts] [int] NULL ,
[totalattempts] [int] NULL ,
[timezone] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lockstatus] [tinyint] NULL ,
[callback] [datetime] NULL ,
[enterpriseagentid] [int] NULL ,
[usercode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_List] PRIMARY KEY NONCLUSTERED
(
[listid]
 
ON [PRIMARY]) ON [PRIMARY]
GO [\code]
This table has the following index: Unique clustered index IX_List_enterpriselistid on column name enterpriselistid.
When I try inserting values into the enterpriselistid field I receive the following error: "Cannot insert duplicate key row in object 'list' with unique index 'IX_list_enterpriselistid'. The statement has been terminated."
What does this mean? Does it mean that enterpriselistid cannot have the same values as listid?
Thanks,
Ninel
-
It means that enterpriselistid must unique in every row. Sounds like you are trying to insert a new row with an enterpriselistid that already exists in the database.
hth
-
Ninel
The unique clustered index on column enterpriselistid means that this field cannot have duplicate values in other records, which is what the error you are seeing.
If you have data where you can have the same id within this field in multiple records, I would drop this index from the table and create a NONCLUSTERED INDEX on enterpriseid (if you need this index for searching capabilities). I would also update your primary key constraint to be the CLUSTERED INDEX on the table (a table can only have one clustered index and it should be your primary key since the records need to be unique)
Chuck
-
>>a table can only have one clustered index and it should be your primary key since the records need to be unique
False.
The primary key is not always a good candidate for clustering. Uniqueness and clustering are unrelated. The primary key needs to be unique, clustering is not what enforces the uniqueness.
-
maybe i mistyped what i meant to say, nowhere did i say that a clustered index enforces uniqueness, the unique constraint or the primary key constraint would enforce the uniqueness.
what i was saying that since the clustered index will sort your table in the order of the index, the primary key would be a good candidate for this. As taken from BOL:
"Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order."
you are correct in stating that not all primary keys are good candidate for a clustered index but for the simplicity of this table, I would make the ListId a clustered index and then test out the performance to see if there would be good reason to change that...
-
If no searches/query's are making reference to the listid column than that being the clustered index is horrible. Plus, depending on the number of inserts into the file, an identity value is a bad canidate for a clustered index because of all of the split pages at the end of the file. If I ever have to use an identity as a clustered index I set my fill factor to 100% and rebuild the index at least once a week. The free space elsewhere in the file is just wasted if not.
Tom
-
>>an identity value is a bad canidate for a clustered index because of all of the split pages at the end of the file.
On the contrary, clustering on an identity (or any other sequentially ascending value like current date/time) actually avoids page splits. Splitting occurs when an index value needs to be inserted between existing index values, and with an identity this is never true because the row being inserted can't be between anything - it's at the end.
The issue was "hotspots", or all insert activity occurring on the same index page and potentially causing locking issues when a certain number of concurrent inserts is reached.
-
It doesn't avoid page splits they just always happen at the end of the file. Page becomes full, split happens, half of page is left empty at end of file other half fills and splits again and again and again. Leaving a bunch of half filled pages towards the end of the file. By reindexing with 100 fill, all pages will be full upto any inserts at the end of the file. Fuller pages = faster query performance. Since the clustered key is an identity any page other than the one currently in use will never get split again unless the indentity increments by more than one.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply