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

Question

Static code analysis has the following rules
SR0009 Avoid using types of variable length that are size 1 or 2
https://learn.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2010/dd193263(v=vs.100)

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

"you incur an additional storage cost to track the length of the value stored in the data type" <<< so this additional storage cost only valid for varchar(1) or varchar(2) ???

"columns of variable length are stored after all columns of fixed length, which can have performance implications" , I am also not sure why this can happen, for varchar(1), or varchar(2), the column should put inline with the data page( instead of rowoverflow allocation) , So In this case , why this will have performance implications ???

Are there any example ?

Answer 1

Hi @sakuraime ,

... so this additional storage cost only valid for varchar(1) or varchar(2) ???

No. It is valid for all the data types of variable length. Not only varchar(1) or varchar(2).

...So In this case , why this will have performance implications ???

Because fixed-width data types always require the same amount of storage, regardless of the value stored in those columns or variables,Variable-width data types always have two extra bytes of overhead.

And quote code from this doc.: sql-varchar-data-type-deep-dive

DECLARE @demochar CHAR(150) = 'This is the char value'   
DECLARE @demovarchar VARCHAR(150) = 'This is the varchar value'  
   
SELECT 'Starting ' + @demochar + ' finishing' AS 'CHAR DATA'  
SELECT 'Starting ' + @demovarchar + ' finishing' AS 'VARCHAR DATA'  

So if you are storing a value, a string value that will always be short, or a one-character value, it is better from a storage perspective to use a CHAR(1) instead of a VARCHAR(1). Even if a VARCHAR(1) field is empty, it will still require two bytes of storage.

More information: data-type-choice-affects-database-performance ,char-vs-varchar-in-sql

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

"columns of variable length are stored after all columns of fixed length, which can have performance implications" , I am also not sure why this can happen, for varchar(1), or varchar(2), the column should put inline with the data page( instead of rowoverflow allocation) , So In this case , why this will have performance implications ???

Answer 4

All variable length data types have an overhead of a actual data length added to them and are stored at the end of the row as "length+actual data". The engine does not have special consideration if your max length is 1 or 2. Hence the warning message in static code analysis.