SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length

Always specify a length when using VARCHAR or NVARCHAR types for columns, variables, or parameters to prevent inconsistency and potential data loss.

Description

In SQL Server, failing to define the length for VARCHAR or NVARCHAR can lead to unintended issues because the system automatically assigns a default length of 1 character. In certain scenarios, the length may default to 30 characters, leading to inconsistency.

For example:

SQL
Copy
1-- Example of problematic query
2DECLARE @name VARCHAR;
3SET @name = 'John Doe';
4SELECT @name;

This query assigns the value ‘John Doe’ to the variable @name. However, because no length is specified for VARCHAR, only the first character ‘J’ is stored and returned.

  • Unintentional data truncation, leading to data loss or inaccurate results.

  • Increased risk of bugs and errors, particularly in queries expecting more data.

How to fix

Specify a length for VARCHAR or NVARCHAR data types to prevent unintended data truncation and inconsistency.

Follow these steps to address the issue:

  1. Review any declared VARCHAR or NVARCHAR variables, columns, or parameters in your SQL scripts without a specified length.

  2. Determine the appropriate length for the data type based on the expected size of the data. This should align with your application’s requirements.

  3. Update the SQL declarations to include the determined length. Replace instances of VARCHAR or NVARCHAR with the correct syntax, specifying the length explicitly.

  4. Test your updates to ensure no data truncation or related errors occur and that the application behaves as expected.

For example:

SQL
Copy
1-- Corrected query with specified length
2DECLARE @name VARCHAR(50);
3SET @name = 'John Doe';
4SELECT @name;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE @var nvarchar(20)
 2DECLARE @var1 nchar(11)
 3DECLARE @var2 varchar 
 4DECLARE @var3 nvarchar
 5
 6DECLARE @var4 [VARCHAR]
 7DECLARE @var5 [NVARCHAR]
 8
 9DECLARE @var41 [VARCHAR](30)
10DECLARE @var51 [NVARCHAR](30)
11
12DECLARE @var6 [VARCHAR]
13DECLARE @var7 [NVARCHAR] -- IGNORE:SA0080
Analysis Results
  Message Line Column
1 SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length. 3 14
2 SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length. 4 14
3 SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length. 6 14
4 SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length. 7 14
5 SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length. 12 14
See Also

Other Resources

Analysis Rules