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

New to SQL Server Programming

clustered index

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-28 : 05:42:25

if i have a clustered index on a date column, and the date is randomly inserted but by doing order by its sorted out
will it insert it sorted physically or only when rebuilding the index it will sort them?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-28 : 05:59:18

Depending on which way you have sorted the clustered index (ASC or DESC), it will place the rows at either the beginning or the end of the table (I am making the assumption that you are inserting data using getdate() or similar and not historic dates), so no, it is not only sorted physically when rebuilding the index.

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-28 : 06:12:03

let us say iam inserting random dates (is this column preferred to be clustered index?)
will these dates be sorted physically each time the insert happens ? if yes i guess it will not be a good choice to cluster index it since it needs to consider rearranging the whole pages in order to get them sorted out

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-28 : 06:15:58

It will be inserted in sorted order:

DECLARE @table table (
  myDate datetime not null primary key clustered
)

INSERT INTO @table 
SELECT '2008-01-01' UNION ALL
SELECT '2006-01-01' UNION ALL
SELECT '2009-01-01' UNION ALL
SELECT '2005-01-01' 

SELECT * FROM @table

EDIT: ...but adding a clustered index on a column just for the sake of ordering the data when displaying them is not a great idea. Clustered indexes should be on the column(s) that are queried the most (that a part of joins or the where statement), usually an ID of some sort.

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 06:56:49

Still you cant guarantee that select retrieves the records in sorted form unless you specify a specific order using order by.

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-28 : 07:20:15

aha so by the select without "order by" we can know how the records in the db are stored :D
another question if the data are always sorted when insert occurs why is the index fragmented if each time they are physically sorted?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:33:12

nope...not always. you cant guarantee that select will fetch data in order in which its stored in db.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 07:34:42

The data in table are stored in the order they are inserted.
The clustered index however is rebuilt/reorganized according to your requirements.

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

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-28 : 08:14:57

still this not answered. why is the index gets fragmented if each time they are physically sorted when inserted?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-28 : 08:15:21

quote:
The data in table are stored in the order they are inserted.

This is not true. From BOL: "In a clustered index, the leaf nodes contain the data pages of the underlying table" which means that the data are rearranged when you insert data that is not in the order of the clustered index. This is why you should always insert data in to a table in the order of the clustered index when doing large inserts or bulk loads.

edit: in other words, inserting data that is not in the order of the clustered index will cause fragmentation because the leaf nodes are constantly shifted to keep the data in the correct order.

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 09:09:40

You are correct. My mistake.
The clustered index IS the data pages for the tables.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 09:14:16

To ann06,
the fragmentation occurs when whole datapages are not stored in order on disk.

This happens for page splits, when a page is full and has to be split in two or more to accomodate all new data.
The new page(s) are stored last in filegroup/partition on disk, while the original remains it's physical place.

See here how to make a 10-way pagesplit!
http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/20/splitting-a-page-into-multiple-pages.aspx

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

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-28 : 09:20:17

Yeah Peter, get it right..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 09:21:28

There can also be fragmentation within a page

SET NOCOUNT ON

CREATE TABLE	#Sample
		(
			s VARCHAR(200),
			CONSTRAINT PK_s PRIMARY KEY (s)
		)
		
DBCC TRACEON(3604)

DECLARE @Now VARCHAR(30)

SET	@Now = CONVERT(VARCHAR(30), GETDATE(), 121)

SELECT	@Now

INSERT	#Sample
SELECT	'Peso' + @Now

DBCC IND('TempDB', #Sample, -1)

DBCC PAGE (TempDB, 1, 121, 2) WITH TABLERESULTS
-- Only PageType 1, data pages

INSERT	#Sample
SELECT	'Yak' + @Now

DBCC PAGE (TempDB, 1, 121, 2) WITH TABLERESULTS

INSERT	#Sample
SELECT	'SQLTeam' + @Now

DBCC PAGE (TempDB, 1, 121, 2) WITH TABLERESULTS

DBCC TRACEOFF(3604)

DROP TABLE	#Sample

You can see (offset table) that Peso is lowest offset, Yak is middle offset and Yak is last offset (insert order), but still Peso is considered Slot 0, SQLTeam Slot 1 and Yak Slot 2 (clustered index order).

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 09:46:51

This deals with internal fragmentation(page splits consuming more pages to read data)and External fragmentation(pages are not in order so SQL engine has to switch back and forth to grab data).Thats why indexes have to rebuilt(which works like drop and create strategy).This works efficiently when tables(indexes) are atleast around 100 pages or more.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 09:49:57

Hmmm. See this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118710

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

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-29 : 02:58:48

excuse me Mr. Peso i didnt get the result that showed "but still Peso is considered Slot 0, SQLTeam Slot 1 and Yak Slot 2 (clustered index order)."

what is the field name you referred to the slot #. i see m_slotCnt they are ordered seq for each: 1 for first record then 2 for 2nd then 3 for third record !!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 03:09:22

Yes they are numbered according to clustered index definition, but the offset values indicates that the actual values are physically stored in the order of insert.

You also have to pay attention foro the result in DBCC IND output and use that PageID number for the subsequent DBCC PAGE commands.

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

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-29 : 05:17:58

yes i adjusted the pageid in the dbcc page
but which field does indicate the physical sort order in dbcc page cmd?
i couldn't find what you talked about (still Peso is considered Slot 0, SQLTeam Slot 1 and Yak Slot 2 (clustered index order))
i have m_slotCnt for peso 1 , m_slotCnt for yak 2 ,m_slotCnt for sqlteam 3
thnks for your support and taking ur time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 05:26:37

Look at the DATA OFFSET records.

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

ann06
Posting Yak Master

171 Posts

Posted - 2009-02-01 : 06:43:05

thanks guys
i just wanted to put the thread on fire :p

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources