Sql-server – Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

database-designindexreferential-integritysql-server-2008-r2

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.

Best 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.

Related Solutions

Sql-server – Are these good indexing guidelines

Other points (as noted, "How do I index?" is a topic for a book not a single post - also, so many of the answers come down to "it depends on your database and your workload"):

Index selectivity is key. If you try and index a field that doesn't have many distinct values (i.e. a true/false bit field) then using that index is actually slower than just doing a table scan (but it still has to be maintained, thereby slowing DML calls [insert/update/delete] down to no benefit).
In general, you're right about indexing narrow fields, but I'd rephrase it to "be very careful about indexing wide fields". If the index field is too narrow, you run into the selectivity problem above, but the wider the field the bigger the index is ("how much bigger?" depends on which DBMS you use).
Indexing updated fields - If you have an index on a frequently-updated field, then yes that index will slow down updates to that field. However, if you're using that field quite often in query criteria it may still be worth it to index it. (see above: "it depends")
Multi-column indexes: This is a tricky one:
- Covering indexes (an index that contains all the fields for a query) can speed up queries (because then the query only has to look at the index - it doesn't have to refer to the base table).
- Multi-column indexes generally have higher selectivity.
- Multi-column indexes require more space.
- They're only useful if the query filters on either the full index, or a leading subset. I.e. if you have an index on (State, County, ZIP), then queries filtering on (State), (State, County) or (State, County, ZIP) can use that index. Queries that filter on (County, ZIP), (County) or (ZIP) cannot use that index.
- Corollary: The order of columns in a multi-column index is very important.
- If you have a multi-column index (State, County, ZIP), then a single-column index on (State) would be redundant (since State is the first column, the multi-column index can be used for that). Note that SQL Server and Sybase (not sure about other RDBMS systems) don't prevent you from creating completely redundant indexes.

As always, the best indexing strategy is to analyze the workload your database is under and index to suit that. If you're indexing a data warehouse your indexes are going to be radically different from the indexes on an audit history database.

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Best Answer

Related Solutions

Sql-server – Are these good indexing guidelines

Sql-server – Do I need a separate Id column for this “mapping” table

Related Question