ASC/DESC Clustered Index - will it make a difference in this scenario?

Imagine the following scenario-

Identity(1,1) column ID is primary key and only clustered index key.

Rows will be inserted regularly into this table, hundreds per day.

Queries will be mostly selecting on the most recent records.

In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.

Does the direction of the ID column in the clustered index make a difference?

I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.

I've read this

http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html

but it didn't address (or perhaps didn't need to) this sort of scenario.
You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)|||

Allen White wrote:

You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)

Not trying to avoid the hotspot. The primary key as IDENTITY as clustered index is very oftentimes, in my experience, the best way to go. I'm not alone, check out articles by Kim Tripp among others. I understand that in this scenario the inserts and selects are all going to be happening in the hotspot, but that's where leveraging SNAPSHOT isolation can help.

I'm pretty certain that ASC/DESC won't make a difference in this scenario but I'd welcome any more input.

ASC/DESC Clustered Index - will it make a difference in this scenario?

Imagine the following scenario-

Identity(1,1) column ID is primary key and only clustered index key.

Rows will be inserted regularly into this table, hundreds per day.

Queries will be mostly selecting on the most recent records.

In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.

Does the direction of the ID column in the clustered index make a difference?

I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.

I've read this

http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html

but it didn't address (or perhaps didn't need to) this sort of scenario.
You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)|||

Allen White wrote:

You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)

Not trying to avoid the hotspot. The primary key as IDENTITY as clustered index is very oftentimes, in my experience, the best way to go. I'm not alone, check out articles by Kim Tripp among others. I understand that in this scenario the inserts and selects are all going to be happening in the hotspot, but that's where leveraging SNAPSHOT isolation can help.

I'm pretty certain that ASC/DESC won't make a difference in this scenario but I'd welcome any more input.

ASC/DESC as SP Keywords?

Can I do something like this:
CASE WHEN @.orderBy = 'ASC' THEN ASC ELSE DESC END
So I can order by asc or desc depending on a stored procedure parameter?"Chris Ashley" <chris.ashley2@.gmail.com> wrote in message
news:1137076942.622670.97210@.g44g2000cwa.googlegroups.com...
> Can I do something like this:
> CASE WHEN @.orderBy = 'ASC' THEN ASC ELSE DESC END
> So I can order by asc or desc depending on a stored procedure parameter?
Unless someone can see something wrong with this:
declare @.orderBy char(3)
set @.orderBy = 'asc'
select .. from table order by
case @.orderBy when 'asc' then colName end asc,
case @.orderBy when 'des' then colName end desc|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:O5vTIi4FGHA.3976@.TK2MSFTNGP11.phx.gbl...
> "Chris Ashley" <chris.ashley2@.gmail.com> wrote in message
> news:1137076942.622670.97210@.g44g2000cwa.googlegroups.com...
> Unless someone can see something wrong with this:
> declare @.orderBy char(3)
> set @.orderBy = 'asc'
> select .. from table order by
> case @.orderBy when 'asc' then colName end asc,
> case @.orderBy when 'des' then colName end desc
This article explains it a lot better:
http://www.aspfaq.com/show.asp?id=2501

ASC Result Set with Nulls at the bottom

I have a results set that is sorted on LineNum. Not all items have a
LineNum (some are null). I want my result set to return all items in
ASC LineNum order and then list all items with NULL LineNum at the end
of the results. Any Suggestions?

Thanks.On 25 Jan 2006 12:22:36 -0800, ActiveX wrote:

>I have a results set that is sorted on LineNum. Not all items have a
>LineNum (some are null). I want my result set to return all items in
>ASC LineNum order and then list all items with NULL LineNum at the end
>of the results. Any Suggestions?

Hi ActiveX,

ORDER BY CASE WHEN LineNum IS NULL THEN 1 ELSE 0 END, LineNum

--
Hugo Kornelis, SQL Server MVP

ASC / DESC based on CASE

Hi is it possible to sort data ASC or DESC using a CASE Statement based on
user input parameter ?
ThanksYes, use separate CASE expressions in the ORDER BY clause.
Anith|||On Tue, 3 Jan 2006 15:06:51 -0800, Vishal wrote:

>Hi is it possible to sort data ASC or DESC using a CASE Statement based on
>user input parameter ?
>Thanks
>
Hi Vishal,
Yes - but not the exact way that you think/hope for.
Example #1:
SELECT yadda, yadda
FROM whatever
ORDER BY CASE WHEN @.Direction = 'ASC' THEN SortColumn END ASC,
CASE WHEN @.Direction = 'DESC' THEN SortColumn END DESC
Example #2 (works only for numeric columns)
SELECT yadda, yadda
FROM whatever
ORDER BY SortColumn * CASE WHEN @.Direction = 'ASC' THEN 1 ELSE -1 END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||note that this one-size-fits-all approach may perform poorly, as
described here
http://www.devx.com/dbzone/Article/30149/0/page/2