Share via


SR0009: Avoid using types of variable length that are size 1 or 2

RuleId

SR0009

Category

Microsoft.Design

Breaking Change

Breaking

Cause

One or more data types of variable length have a length of 1 or 2.

Rule Description

When you use data types of variable length such as VARCHAR, NVARCHAR, and VARBINARY, you incur an additional storage cost to track the length of the value stored in the data type. In addition, columns of variable length are stored after all columns of fixed length, which can have performance implications.

Note

You will also receive a warning if you declare a type of variable length, such as VARCHAR, but you specify no length. This warning occurs because, if unspecified, the default length is 1.

How to Fix Violations

If the length of the type will be very small (size 1 or 2) and consistent, declare them as a type of fixed length, such as CHAR, NCHAR, and BINARY.

When to Suppress Warnings

You should not suppress this warning.

Example

This example shows definitions for two tables. The first table declares a string of variable length to have length 2. The second table declares a string of fixed length instead, which avoids the warning.

CREATE TABLE [dbo].[TableWithWarning]
( 
[ID] INT NOT NULL IDENTITY(0, 1), 
[c1] INT NOT NULL PRIMARY KEY, 
[c2] INT,
[c3] INT,
[SmallString] VARCHAR(2)
)
ON [PRIMARY]

CREATE TABLE [dbo].[FixedTable]
( 
[ID] INT NOT NULL IDENTITY(0, 1), 
[c1] INT NOT NULL PRIMARY KEY, 
[c2] INT,
[c3] INT,
[SmallString] CHAR(2)
)
ON [PRIMARY]

Data for types of variable length is physically stored after data for types of fixed length. Therefore, you will cause data movement if you change a column from variable to fixed length in a table that is not empty.

See Also

Concepts

Analyzing Database Code to Improve Code Quality