...and a further quick word on the evil that is string keys.

Not only are they much more work to compare on than integer keys, but the comparison depends on collation...

Hi Bruce,

Sure. I...

Bruce,

I just wanted to add that a cluster key on an identity column is not without issues:

1. While it can help keep clustered index fragmentation down, this is not...

Hi bhushanvinay,

While I have no doubt that the scheme you outline would work, I imagine it would be a nightmare on a busy system. That's true even if the...

Hi,

I can't see anything obviously problematic there. Could you possibly re-post the script for the view directly from SSMS (with the alias for count_big(*) for example) so we be...

Hi James,

Thanks! 🙂

If the SQL is difficult to change (maybe because it is generated by a third-party application or whatever), you may still be able to produce a better outcome...

Hey Grant,

You might well be right - the first query does do more logical IO, but that's not the whole story. If the whole table is scanned in allocation...

Hi again Gail,

Sure. Everything you say is true. The only thing I would say (in a small voice) is that my previous comments were 'off the top of...

One other possibility if you really need to go down this road:

CREATE TABLE dbo.IDs

(

ApplicationName VARCHAR(30) PRIMARY KEY CLUSTERED,

Counter INT NOT NULL

)

-- If more than one counter is required...

INSERT dbo.Ids...

Basically what it means is that the scan count can't be trusted as not all operators set it in the same way.[/quote]

I'm inclined to agree...

Hi!

I've just looked at the connect article and MS seem to have closed it as 'by design'.

My understanding is that a 'seek' always selects one row (either by key or...

Hi,

You might also like to consider adding two INT columns to hold CHECKSUMs of the Questions and Answers.

The CHECKSUMs can act as a hash value to allow a future query...

Hi,

In order to decide which is the 'better' query, we would need to know what defines 'best' for you:

1. Logical IO

2. CPU

3. Concurrency

4. Response time

5. ...

Hi,

When you introduce the ORDER BY, the query optimizer (QO) picks a bad plan. This often happens with TVFs, queries from remote servers, XQuery expressions and so on because...