Geek Answers Handbook

Sql Server int vs nvarchar performance comparison?

For you, the creators of the database / performance gurus are there.

I am developing a table, I have the choice to either use int or nvarchar (128) for the column, assuming space is not a problem. My question is what will give performance

when searching by int column

where ID = 12324

or when I look for the nvarchar column (the key is the whole value, so I do not use the LIKE operator)

where Key = 'my str'

I am sure that for smaller datasets this does not matter, but let it be assumed that this data will be in millions of rows.

+7
performance sql-server database-design
source share
4 answers

INT will be faster - this is why:

  • SQL Server Organizes Its Data and Indexes into 8K Pages
  • If you have an index page with the INT key, you get approximately 2,000 INT records.
  • if you have NVARCHAR (128) and you use an average of 20 characters, this is 40 bytes per record or approximately 200 records per page

Thus, for the same number of index entries, the NVARCHAR (128) case will use ten times as many index pages.

Downloading and searching for these index pages will result in a significant increase in I / O.

So, to make things short: if you can, always use INT.

+17
source share

Space is always a problem in databases. Wider keys mean fewer records per page, more pages scanned for aggregating and summing values, means more I / O, less performance. For clustered indexes, this problem is multiplied by each nonclustered index, since they must reproduce the search key (clustered key) in their sheets. Thus, a key of type nvarchar(128) will almost always be worse than INT.

On the other hand, do not use the INT key if this does not fit. Always use the appropriate key, taking into account your requests. If you always ask for the nvarchar (128) column value, this is probably a good cluster candidate. If you are going to aggregate the nvarchar (128) key, then this is most likely a good cluster candidate.

+8
source share

The main performance problem with this is the field size - int - 4 bytes, while nvarchar(128) will be 254 bytes.

All this must be managed by the SQL server, so managing int will be much faster than nvarchar(128) .

+5
source share

I would use int for performance (if it really has a join) and put a unique index of the potential natural key for data integrity.

0
source share

More articles:


All Articles