Sql Server int vs nvarchar性能比较？

For you database design/performance gurus out there.

对于你的数据库设计/性能专家。

I'm designing a table, I have the choice of either use int or nvarchar (128) for a column, assume space is not a problem. My question is which will give performance

我正在设计一个表，我可以选择使用int或nvarchar（128）作为列，假设空间不是问题。我的问题是哪个会带来表现

when I search with int column

当我用int列搜索时

where ID = 12324

其中ID = 12324

or when I search with nvarchar column (the Key is the entire value, so I'm not using LIKE operator)

或者当我用nvarchar列搜索时（Key是整个值，所以我没有使用LIKE运算符）

where Key = 'my str'

Key ='my str'

I'm sure for smaller datasets it doesn't matter, but let's assume this data will be in the millions of rows.

我确信对于较小的数据集而言并不重要，但我们假设这些数据将在数百万行中。

4 个解决方案

#1

INT will be faster - here's why:

INT会更快 - 这就是为什么：

SQL Server organizes its data and index into pages of 8K
SQL Server将其数据和索引组织为8K的页面
if you have an index page with INT key on it, you get roughly 2'000 INT entries
如果你有一个带有INT键的索引页面，你将得到大约2'000个INT条目
if you have NVARCHAR(128) and you use on average 20 characters, that's 40 bytes per entry, or roughly 200 entries per page
如果您有NVARCHAR（128）并且平均使用20个字符，那么每个条目40个字节，或者每页大约200个条目

So for the same amount of index entries, the NVARCHAR(128) case would use ten times as many index pages.

因此，对于相同数量的索引条目，NVARCHAR（128）情况将使用十倍的索引页。

Loading and searching those index pages will incur significantly more I/O operations.

加载和搜索这些索引页面将导致更多的I / O操作。

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

所以简而言之：如果可以的话，总是使用INT。

#2

Space is always a problem in databases. Wider keys mean less entries per page, more pages scanned to aggregate and sum values, means more IO, less performance. For clustered indexes, this problem gets multiplied by each non-clustered index, as they have to reproduce the lookup key (clustered key) in their leafs. So a key of type nvarchar(128) will almost always be worse than an INT.

空间始终是数据库中的一个问题。更宽的键意味着每页的条目更少，扫描更多页面以进行聚合和求和值，意味着更多的IO，更低的性能。对于聚簇索引，此问题会被每个非聚集索引乘以，因为它们必须在其叶子中重现查找键（聚簇键）。所以nvarchar（128）类型的键几乎总是比INT差。

On the other hand, don't use an INT key if is not appropriate. Always use the appropriate key, considering your queries. If you always going to query by an nvarchar(128) column value, then is possibly a good clustered key candidate. If you're going to aggregate by the nvarchar(128) key, then is likely a good clustered key candidate.

另一方面，如果不合适，请不要使用INT密钥。考虑到您的疑问，请始终使用适当的密钥。如果您总是要通过nvarchar（128）列值进行查询，那么可能是一个很好的聚簇键候选者。如果您要通过nvarchar（128）密钥进行聚合，那么很可能是一个很好的聚类密钥候选者。

#3

The main issue with performance with this is the size of the field - an int is 4 bytes, whereas an nvarchar(128) will be 254 bytes.

性能的主要问题是字段的大小 - int是4个字节，而nvarchar（128）是254个字节。

All of this needs to be manages by SQL server, so managing an int will be much faster than an nvarchar(128).

所有这些都需要由SQL服务器管理，因此管理int将比nvarchar（128）快得多。

#4

I would use the int for performance (if this is going to have joins especially) and put a unique index on the potential natural key for data integrity.

我会使用int来提高性能（如果这将特别具有连接）并在数据完整性的潜在自然键上放置一个唯一索引。

#1