Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.