does it worth switching a PRIMARY KEY from the typ

问题:

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
)

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

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
)

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.

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)

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

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?

回答1:

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.

You can also check to see if the space used is reduced by using the stored procedure sp_spaceused

EXEC sp_spaceused 'TableName'