SQL DB project - column size validation

Question

Is this a bug (or can there be feature to validation) to avoid generated code that would run into errors like "
The size (8000) given to the parameter 'EightK' exceeds the maximum allowed (4000)."

Steps:

1. Create SQL DB project, create a table with nvarchar(4000) field, publish to target DB
2. Modify the column definition change from nvarchar(4000) to nvarchar(8000), generate publish script
   While as a user I expect validation to fire and catch this, the actual is alter table script is generated, that runs into error when executed

Answer 1

There is nothing wrong. This is not a bug.

Your query is returning the "data byte size" not the "varchar length". nvarchar is a double byte data type. See: https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15

Answer 2

The issue posted is not about variance in the data byte size to varchar length, my question is about expectation that when I compile sql db project with that change when I try to mention a column as nvarchar(8000) I am not getting error or warning. The SSDT is able to generate an alter script and we run into error at point of execution. Is that a bug for not doing validation on column size or can I request for feature so that these issues are caught at development time rather than deployment time.

Answer 3

Hi @Riaz Thara ,
In addition, this error may occur when using nvarchar(8000).
The max length of data type nvarchar is 4000, and we cannot have nvarchar(8000).
If the data possibly exceeding 4000 character, it is recommended to use nvarchar(MAX), which can stored 2^30-1 characters (2 GB).
Please refer to this doc which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.