SQL 2005 performance: column order when mixing varchars with int types

Question

I have a table like this:

create table SomeTable (
   tableKey    int identity(1,1) not null
,  foreignKey  int               not null
,  longDesc    nvarchar(max)     not null
,  shortName   varchar(100)      null
)

I need to use shortName in a where clause:

select 
    tableKey 
from 
    SomeTable 
where 
    foreignKey = @foreignKey 
and shortName = @shortName

and that needs to be fast - this table has millions of rows. Can I move it above the longDesc column and see a speed increase? I assume that the database can simply jump to the correct byte offset as long as the columns are constant sized. Jumping past a varchar column must incur some kind of performance penalty, yes?

Alternatively, what if I made longDesc nchar(3784)? (Which should make each row fill an 8KB page) Would that increase speed?

I see one of the answers here alludes to the fact that order matters in terms of used space, but I'm not sure how that translates to performance.

Answer 1

Can I move it above the longDesc column and see a speed increase?

No. IIRC, nvarchar(max) only keeps a placeholder/pointer with the table itself. The actual data is moved elsewhere, for precisely this reason. For the same reason, changing the size of your nvarchar(max) will also not help.

What you should do is add an index on foreignKey, shortName, and tableKey. Then your query can be entirely satisfied by the index.

Answer 2

To speed up this query, you can use a covering index ( http://www.devx.com/dbzone/Article/29530), either (foreignKey, shortName, tableKey) or (shortName, foreignKey, tableKey)

Try out both and see which gives you better performance

Answer 3

For the greatest speed, you would create a persisted computed column like shortNameCrc = checksum(shortName), then create an index on (foreignKey, checksum(shortName)) and include (shortName). Then you would query like where foreignKey = foreignKey and shortNameCrc = checksum(@shortName) and shortName = shortName

If SomeTable is not clustered on tableKey then you would want to add tableKey as an include columns. If it is clustered on tableKey then the value is already present in the covering index.

Filling a page should not cause a speed increase, this will cost you in excess memory usage and a greater likelihood in having to visit disk. Filling a page may be better for throughput in some cases for a workload that is very high in contention in the same physical area.

Making shortName char(100) may be better than varchar(100). Given unlimited ram I suspect it would be. In the real world it may depend on the average length and the likelihood of a cache miss.