VisualStudioFeeds

All Visual Studio blogs in one place

AddThis Social Bookmark Button
Descending indexes are really helpful if we want order by on more than one column and at least two columns are required in opposite direction in order by.  If all columns are required in same direction, index can be scanned in backward direction even if the index is created in ascending order and data required in descending order. The only cache here is backward scans can’t use parallelism. For more details on backward scan, please refer this article of Itzik Ben-Gan. But, the real problem with descending indexes is they tend to become fragmented quickly as generally the order
of inserting data in OLTP environment is in ascending order.   I have tried to reproduce the same with below example.--Script to generate test data use tempdb go if Object_id('dbo.studentAttendance') is not null drop table dbo.studentAttendance go create table dbo.studentAttendance ( attendanceID bigint identity(1, 1), studentID int, attendanceDate date, isPresent bit, constraint pk_studentattendance primary key clustered(attendanceID) ) go...(Read whole news on source site)

Search

Subscribe

Enter your email address:

Delivered by FeedBurner

Increase your website traffic with Attracta.com

Contact

Email:
Subject:
Message:
Anti-spam: How many eyes has a typical person?

Follow us on FB

Home : Blog List : Chintak's Blog : Descending Indexes and Fragmentation