Export (0) Print
Expand All

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

RuleId

SR0009

Category

Microsoft.Design

Breaking Change

Breaking

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

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.

NoteNote

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.

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.

You should not suppress this warning.

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.

Community Additions

ADD
Show:
© 2014 Microsoft