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.

AS cube performance question

We have a very huge cube almost 200m rows with exponential growth rate. I have another cube which is not so big.

I've noticed when I pivot the dimensions in the big cube, it takes more time than the latter.

Could you guys give me some advices to make this work much faster.

I've partitioned the warehouse but have'nt created any indexes so far, could this be a problem?

thanks,

Prok

Hello! Have you read the "Performance Guide" pointed to at the top of this newsgroup?

HTH

Thomas Ivarsson

|||

Yeah that's definitly the best advice. The problem could be any of several.

Most likely, like you said, aggregations. 200m row cube isn't all that big as far as i'm concerned. We house a cube with ~2billion rows (180GB cube), once aggregations are designed "perfectly", you should not have an issue as long as the cube is designed well.

|||

Thanks Zach84,

Yeah, I have read the pf guide before and have implemented accordingly. I must have done a bad design regarding aggregations. I'll check on that.

I appreciate your advice.

Rok.

AS 2005 Slowly Changing Dimension - adding new rows when no change

Hi , I am using the Integration Services slowly changing dimension to move data from a SQL Server 2000 database table to a SQL Server 2005 table.

The other problem is the package is not tracking changes it is spending a lot of time doing lookups (it's slow), but ends up creating new records when there has not been a change.

I'm quite sure the business key is set up correctly (I'm using the PK from the source table).

The database I am transferring from has non Unicode data types (ie varchar and char) and the destination database has Unicode data types (ie nvarchar).

Also some of the fields in the dB are NULL - does this have an effect (ie one null doesn't equal another null)? Or shouldn't that matter?sorted out his issue

http://forums.microsoft.com/MSDN/showpost.aspx?postid=1475782&siteid=1

Arranging data on multiple rows into a sigle row (converting rows into columns)

Hello,

I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:

user1 answer1
user1 answer2
user1 answer3
user2 answer1
user2 answer2
user2 answer3

For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so thateach user will be on a single row with all of that user's answers on that single row (A column for each answer):

user1 answer1 answer2 answer3
user2 answer1 answer2 answer3

How can this be done? How can all answers of a user appear on a single row

Thanx,
Danny.

sql server 2005 or 2000?

In sql server 2005, I believe the answer is with the new pivot or unpivot commands. In 2000, it gets much trickier.

Of course, I think excel can do it's own pivoting as well.

arithmetic overflow error

Hello,
I have a column of datatype int which has about 100000 rows and i am trying
to sum up the column and i am
getting the arithmetic overflow errror
This is the sql
Declare @.tot bigint
Select @.tot = sum(column1) from tab1
Select @.totHi,
TRY CAST(AS BIGINT)
> Select @.tot = sum(column1) from tab1
Select @.tot = sum(CAST(column1 AS BIGINT)) from tab1
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"SCSQL" <chalasani@.mcleodusa.net> wrote in message
news:%23TYz6iPlDHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column of datatype int which has about 100000 rows and i am
trying
> to sum up the column and i am
> getting the arithmetic overflow errror
> This is the sql
> Declare @.tot bigint
> Select @.tot = sum(column1) from tab1
> Select @.tot
>
>

Are nulls slow?

I have several tables anywhere from 50,000 to 100,000 rows with about 100 columns in each that allow nulls. How bad will this affect performance (if any)?

If it's significant, how can I replace nulls with zeros without doing it column by column? (100 columns will take too much time)

thanksI dont think null will affect the perfomance.Below code will generate update statement for ur tables,columns which allow null values.Run this code, copy and paste the result to query analyser and execute.

select 'update '+TABLE_NAME+' set '+COLUMN_NAME+'=0 where '+COLUMN_NAME+' is null'
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE='YES'