Indexing Noob: Sort Order - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Indexing Noob: Sort Order

Author

Topic

VentureFree
Starting Member

19 Posts

Posted - 2009-11-26 : 08:28:21

I'm new to indexing, and I've got a question about the sort order (I'm learning to do this from online tutorials, which aren't always so clear). Essentially the data in my database is "active" for about a month and a half, meaning that it's likely to be accessed and modified a lot within that time. After that it's mostly left alone except for data-mining. I've got about 3 years worth of data in there. That being the case, when I index the tables, should I set the sort order to DESC so that it accesses the newest ones first? Sorry if this is a stupid question.

EDIT: For clarity, I'm accessing it through the "Table Designer->Indexes/Keys" menu, and I'm looking at the "Columns" property.

Things should be made as simple as possible, but not any simpler - Albert Einstein

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-11-26 : 09:37:07

That's a good question, I've never paid attention to that.

In terms of retrieving unsorted records/single records, there's no difference.

But according to these, there can be a performance improvement when you return large volumes that need to be sorted.

http://stackoverflow.com/questions/743858/sql-server-indexes-ascending-or-descending-what-difference-does-it-make

http://msdn.microsoft.com/en-us/library/ms181154.aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-26 : 11:54:21

before you make any choices you might want to post the table structure and details of any keys or indices that may exist. You can do that by right clicking on the table and goint to SCRIPT TABLE AS ->

If you also post some sample queries that run against the table then folks here will be kind enough to suggest some good indices.

Given any thought to the clustered index on each table?

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-27 : 06:44:20

you could also look at "partitioning" so that older data is stored in a table which is likely not to be accessed.

in effect you get
select * from myconsolidateddataview
where myconsolidateddataview =
select * from myrecentdatta
union all
select * from myolddata

then when you go
select * from myconsolidateddataview where datadate >= myinputdate
if "myinputdate" is only in the "recentdata" table only that table will be queried.

search here for proper examples of how to set this up.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-28 : 04:14:19

this does look like a candidate for partitioning.

anyway, there are definitely cases where an index should be sorted descending...depends on how the data is used. test it out in your dev environment

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources