If I use a nvarchar(n) column as a clustered index on a SQL Server database, am I going to suffer a significant performance hit compared to a numeric (int) index? Also how does the performance of compound indexes compare?
+6
A:
Sql doesn't really care whether your index is numerical or not but there are some things you need to consider depending on what is in the column and how you are using the table.
Generally you want to keep your indexes as small as possible so nvarchar(4000) (upto 8000 bytes) would really suck but a varchar(3) (upto 3 bytes) would be smaller than int (4 bytes). Also you want to (where possible) have your index insert inserted into the end of the index this keeps the index from fragmenting and causing you performance issues.
Compound indexes can greatly improve performance if the queries you are running against the table only contain the columns in the index. This means that the actual table is never even touched as the index satisfies the query.
see Sql server index basics for an overview on indexes.
It might be more helpful if you gave more specific details about the table itself and how you want to use it?
John Hunter
2009-05-22 14:08:59
Damn, I wanted to add that reference. +1
gbn
2009-05-22 14:10:34
The maximum numeric length set for NVARCHAR is 4000. THere is however "NVARCHAR(MAX)" but a numerical length is allowed to 4000 and below not over 4000.
Andrei Rinea
2009-05-24 08:36:11
hehe yeah good point.
John Hunter
2009-05-26 07:36:41
+1
A:
Almost certainly yes.
Narrow, numeric and strictly monotonic makes a good clustered key. nvarchar is none of these.
Each non-clustered index entry refers to the clustered index so you'll also bloat NC indexes too.
This is before collation/comparison issues.
gbn
2009-05-22 14:09:56
A:
I think it also depends on the size of your table. For smaller tables I doubt you'd notice a difference but for larger ones, say 1 million rows and beyond, you may see a slight slowdown with nvarchar. I'd say it is also dependent on what the field actually contains..i.e., are they emails, etc.
ajdams
2009-05-22 14:10:51
+2
A:
Colin,
nvarchar uses double the space of a varchar column. If nvarchar column is the only index on the table then the hit may NOT be that much but if you have non-clustered Indexes also on that table then yes, you will have a performance hit. Its because the clustered index is included in all rows for the non-clustered indexes also and your non clustered indexes will be very wide. On the other hand, int column occupies only 4 bytes and has a big range to store values from -2,147,483,648 through 2,147,483,647 and tend to be narrow. For 4 bytes, a nvarchar column can only store atmost the space used of a varchar(2) as it uses double the space of a varchar column. Do you see how much space are you wasting?
Sankar Reddy
2009-05-22 14:12:32
A:
When talking about indexes, you have to split "performance" into two topics.
On insert, updates, and deletes, the index will slow your db down - more so with clustered indexes than non-clustered because it may have to move data around in underlying data store. Here I agree with John that the sequential int will perform better than the nvarchar.
But if you need to query on the nvarchar field anyway, a clustered index on this field will do more to speed up your reads.
So the answer to your question really depends on whether you are worried about performance on inserts or reads.
Bill
2009-05-22 14:33:34