Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

Question

Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255).

The value exists in this columns are quite short strings, such as 'A', 'B' and 'ABCDEF'.

These reference tables has been referenced by a operational table that have got many records, e.g: People.

To enhance the performance issue on the operational table (e.g.: People), two indexed have been defined on these FKs which each is a nvarchar(255) column.

CREATE TABLE RefCode1(
    [Code] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_RefCode1] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)
)

CREATE TABLE RefCode2(
    [Code] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_RefCode1] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)
) 

CREATE TABLE People(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    ...
    [RefCode1] [nvarchar](255) NOT NULL,
    [RefCode2] [nvarchar](255) NOT NULL,
    ...
)

My question would be:

• Does defining an index on a nvarchar column with this length causes any issues?
• If the developer does not join the main table to the reference tables in the select queries written in the application, the performance issues will be avoided? (That's the justification he made when I raised a question about this design).

We use SQL Server 2008 r2.

Your thought will be appreciated.

Answer 1

To answer your first part, there is a performance impact because of disk space utilization and index maintenance. The wider the column the heavier the read I/O will be upon record retrieval and the slower the index maintenance tasks will be. Ideally, you want to create your field data type to be as narrow as possible to limit these two factors.

For your second part, unless you need to know what the value of the description field is, then there is no reason to join to the Ref tables when selecting out of the People table. The FKs from the People table to the RefCode tables will guarantee valid values for the RefCode1 and RefCode2 columns upon insert/update into the People table.