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.

Array / Table As return Type

Hello All,

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.

I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

Any help on this would be a million worth useful.

I can excerpt the code if required.

Regards
Sandesh KadamSandesh (sandesh27uk@.gmail.com) writes:

Quote:

Originally Posted by

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.
>
I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

You cannot pass table variables as parameters.

I have an article on my web site that discusses a couple of ways to
skin the cat: http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx