matt stockham (4/24/2008)

---

I agree with the above, however the non-leaf pages still need to be read - if the less selective column is first it can potentially mean more reads...

No!

It means

select @Null = count(*) from #t1 where 'Age'...

No. You can generalise my 2 column example to as many columns as you have in the index.

For example, a 3 column index (A, B, C) is most optimal for...

Scalar = single value

Hence, the where clause you had (@Nm IS NULL) is a scalar (single value) evaluation, because it's a single value (the contents of the variable) been compared...

Yes, and I did. See the last post on page 1.

If there's anything you're still not sure of, ask specifically.

I've seen a case where a person took the most-selectivel advice literally, and made the leading column of every index the primary key (an identity)

He was very curious as to...

Please don't cross post. Many of us read all the frums.

No replies to this thread please. Direct all replies to-

http://www.sqlservercentral.com/Forums/Topic489793-7-1.aspx

And some testing...

SET STATISTICS IO ON

GO

SET STATISTICS TIME ON

GO

CREATE TABLE TestingIndexOrder (

ID INT IDENTITY,

DayOfWeek smallint,

CustomerKey int

)

GO

insert INTO TestingIndexOrder (DayOfWeek, CustomerKey)

SELECT DATEPART(dw,DATEADD(dd,number, '1900/01/01')), FLOOR(RAND(number*5452)*1000)

FROM (

SELECT TOP 4000000 v1.number,...

If the values aren't in quotes, SQL interprets then as columns.

Try something like this (partial query only)

INSERT INTO ....

SELECT ''' + @EmployeeCode + ''', ....

Easiest way to find and...

In management studio, right click the database Tasks ->Generate scripts

Script everything out. Then run that script on your sQL 2000 server. Taht will create the shell of the DB. All...

If the database has never had a full backup, even in full recovery it won't keep the logs. There has to be a full backup taken before SQL starts retaining...

Oh, and with the selectivity you're talking about (1000 unique values in 10 million rows), if that index is not covering, SQL is not going to use that index. Too...

The index is implemented much like a telephone directory. If you have a composite index (colour, age as a very contrived example) then the index pages look something like this

Black...

You'll have to script the database structure, bcp the data out and then recreate the DB on SQL 2000 and import the data. There's no downgrade option for SQL.

Check the database's status in the sys.databases view. Is it restoring or recovering?