将 PRIMARY KEY 从 NVARCHAR 类型切换为 INT 类型是否值得?

问题描述

在我们的 SQL SERVER 2008 R2 数据库上，我们有一个包含国家/地区的 COUNTRIES 参考表.PRIMARY KEY 是一个 nvarchar 列:

On our SQL SERVER 2008 R2 database we have an COUNTRIES referential table that contains countries. The PRIMARY KEY is a nvarchar column:

create table COUNTRIES(
   COUNTRY_ID nvarchar(50) PRIMARY KEY,
   ... other columns
)

主键包含诸如FR"、GER"、US"、UK"等值.此表包含最大值.20 行.

The primary key contains values like 'FR', 'GER', 'US', 'UK', etc. This table contains max. 20 rows.

我们还有一个包含销售数据的 SALES 表:

We also have a SALES table containing sales data:

create table SALES(
    ID int PRIMARY KEY
    COUNTRY_ID nvarchar(50),
    PRODUCT_ID int,
    DATE datetime,
    UNITS decimal(18,2)
    ... other columns
)

这个销售表包含一个名为 COUNTRY_ID 的列，也是 nvarchar 类型(不是主键).这个表要大得多，包含大约 2000 万行.

This sales table contains a column named COUNTRY_ID, also of type nvarchar (not a primary key). This table is much larger, containing around 20 million rows.

在我们的应用程序中，当查询 SALES 表时，我们几乎每次都对 COUNTRY_ID 进行过滤.即使这样，执行大多数聚合查询也需要很长时间(即使有适当的索引)

Inside our app, when querying on the SALES table, we filter almost every time on the COUNTRY_ID. Even like this it takes too long to perform most of aggregation queries (even with the proper indexes in place)

我们正处于开发阶段，以提高对 SALES 表的查询性能.我的问题是:

We're in a development phase to improve the query performance on the SALES table. My question is:

是否值得将 COUNTRY_ID 类型从 nvarchar(50) 切换到 int 类型? 如果列 COUNTRY_ID 在两个表中都被转换为 int 类型，在连接两个表时我能期待更好的性能吗?

Does it worth switching the COUNTRY_ID type from nvarchar(50) to the type int? If the column COUNTRY_ID is converted in both tables to the type int, can I expect a better performance when joining the two tables?

推荐答案

我个人建议将 COUNTRY_ID 从 nvarchar(50) 更改为 INT代码>.int 使用 4 字节的数据，通常 JOIN 比 VARCHAR 更快.​​

I would personally recommend changing COUNTRY_ID from nvarchar(50) to an INT. An int uses 4bytes of data and is usually quicker to JOIN than VARCHAR.

您还可以通过使用存储过程sp_spaceused

EXEC sp_spaceused 'TableName'

这篇关于将 PRIMARY KEY 从 NVARCHAR 类型切换为 INT 类型是否值得?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！