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.