Clustered Index - SQL Server Forums

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

General SQL Server Forums

Database Design and Application Architecture

Clustered Index

Author

Topic

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-26 : 06:29:02

we were having an cluster index on an random generated key which is creating problem as the database is growing.as on each insertion the cluster is recreated as id is random.so what needs to be done

Either create an autogenerated key in the table and create cluster index on it and create nonclusterd index on previous randomid? and how to perform that

Is any other way is possible which require minimum changes as the database is already operating

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:34:42

See http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

E 12°55'05.63"
N 56°04'39.26"

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-26 : 06:51:00

my database already have over 75k records so i want a procedure which could help me without making much changes and what will i do with the 75k random id's (int), if needs to derive random number with the autogenerated id with some pattern

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:58:48

75,000 records only shouldn't be an issue for clustered index over an INT column.
Unless you have problems with your IO subsystem.

E 12°55'05.63"
N 56°04'39.26"

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-26 : 07:07:33

article u gives, suggests derieving random key from autogenerated id but now how will i use when i have random id but not the autogenerated id's,will it not affect.
what is your suggestion about using fill factor or any other way like that

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-06-26 : 11:17:41

Random numbers for clustered indexes can cause fragmentation. Lets take an example. Lets say the first random number that comes in is 2000. And the next number thats generated is 3123. And the third Id generated is 2340. since the column clustered index and data has to be stored in serial order, the record with 3123 is pushed down to accomodate 2340. So each time a new ID comes in, it will cause a lot of "data movement" leading to fragmentation. Setting a fill factor may or may not help much depending on how the numbers get generated.

It is best recommended that you choose a monotonically increasing value for Clustered index.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-27 : 06:22:20

ok i will add monotonically increasing value as primary key ,but my searching will be on the random generated id so apart from doing non-clustered index on random generated id ,what more should i do so it could be quickly searchable(random generated id)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-06-29 : 08:40:26

What use would the new ID be? Is seems simply so you can have a clustered index you will never use?
Just uncluster the index on the random ID.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-29 : 09:46:41

It's good to have a clustered index! It stops the table being a heap.

A monotonically increasing clustered index has very good performance for writes.

Of course add a non clustered index over the column you actually want to search!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-30 : 01:35:44

is it possible to have primary key without the cluster index on it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 02:18:26

Yes.

N 56°04'39.26"
E 12°55'05.63"

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-30 : 03:07:32

any explanation or articles

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 03:13:24

Other than Books Online?

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 04:19:29

It's probably not a good idea to have a random column as your clustered index.

It would mean that when you insert new values then your leaf nodes will all have to be shuffled around.

It would probably be better to have the increasing primary key implemented *with* the clustered index and then have a covering non clustered index on your random value column.

Really either way will not be stellar for writes (as the covering index will have to be updated) but I think having the covering index rather than the clustered on the random column will be the better scenario.

Of course you can test different combinations to find out....

Documentation here (2005):

BOL:
http://msdn.microsoft.com/en-us/library/ms130214(SQL.90).aspx

The sections you want are

CREATE TABLE
CREATE INDEX

a small example (a table with a primary key without the default clustered index


CREATE TABLE #foo (
	[Id] INT PRIMARY KEY NONCLUSTERED
	, [val] NVARCHAR(5) 
	)

CREATE CLUSTERED INDEX IX_FOO_VAL_CLUSTERED ON #foo ([val] ASC)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-30 : 07:46:36

so now i want a table(existing mean not create but alter) with the primary key but without cluster index
(so how to remove cluster index fro table without touching primary key at all)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-06-30 : 08:44:25

quote:
Originally posted by Transact Charlie

It's good to have a clustered index! It stops the table being a heap.

A monotonically increasing clustered index has very good performance for writes.

Of course add a non clustered index over the column you actually want to search!

Please explain why it would be a good thing to maintain an index you will never use and physically order a table in a way that has no benefit (due to the first point).

You would be better off having a cluster on something more useful, such as time/date created than a surrogate key you will never use.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 08:57:38

Well the point wasn't that it was good to have an index that isn't being used. The point was that it's better to have a clustered index rather than none at all.

Unfortunately because the current clustered index is on a random column then write performance will be terrible (because everything needs to be reorganised when inserting into the table).

If there is a suitable column that is strictly increasing all the time (dates on their own aren't good at that) then that is the best candidate for the clustered index. However OP didn't mention that there *was* a better candidate and suggested the new column.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 09:00:05

sql_buddy, here's an example messing around with keys and indices.

Really -- you should go READ some documentation.


IF OBJECT_ID('_foo') IS NOT NULL DROP TABLE _foo

CREATE TABLE _foo (
	[ValId] INT
	, [value] NVARCHAR(50) CONSTRAINT PK_MYKEY_VAL PRIMARY KEY 
	)

-- Put some data in
INSERT _foo ([valID], [value])
      SELECT 1, 'asdasd'
UNION SELECT 2, 'sdasd'
UNION SELECT 2, 'sdgasdgadg'

/*
DROP INDEX _foo.PK_MYKEY_VAL
*/

-- Drop the current primary key
ALTER TABLE _foo DROP CONSTRAINT PK_MYKEY_VAL

-- Add a new nonclustered primary key to value
ALTER TABLE _foo ADD CONSTRAINT PK_MYKEY_VAL PRIMARY KEY NONCLUSTERED ([value])

-- Add a IDENTITY COLUMN and a clustered index
ALTER TABLE _foo ADD [Id] INT IDENTITY(1,1)
CREATE CLUSTERED INDEX IX_FOO_Id_CLUSTERED ON _foo ([Id] ASC)

SELECT * FROM _foo

NOTE:

making clustered index(es) takes time!. Also if you have a foreign key constraint then you may be all out of luck altogether.

Take a BACKUP. And do the changes on a development box first.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-06-30 : 09:13:05

quote:
Originally posted by Transact Charlie
....
If there is a suitable column that is strictly increasing all the time (dates on their own aren't good at that) then that is the best candidate for the clustered index. However OP didn't mention that there *was* a better candidate and suggested the new column.

Yup, although date/time created is a great CI candidate assuming the rows come in date/time order.

My main point though is that the new column is not the way to go. Just uncluster the existing index. Job done and no real work on anyones part. There is no need for anything more than that.

sql_buddy
Starting Member

41 Posts

Posted - 2009-06-30 : 09:25:45

matter as i understand is that we can't remove cluster index from primary key :

so i have to first remove foreign key relation then remove primary key constraint and then remove cluster index
then apply non-cluster index
i was thinking of shortening these steps as removal foreign key relation is hectic task, and about the
cluster index columns that is not necessary as most searchable & unique cols is randomid only ,so leave that matter

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-06-30 : 09:36:39

Yes you can remove the clustered index, and you should. It is killing you while it is random.
I don't know off hand how you can do it, but it can't be that hard or slow with only 75K records.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources