question

RiazThara avatar image
0 Votes"
RiazThara asked AmeliaGu-msft commented

SQL DB project - column size validation

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 validatio96103-nvarchar-doublelength.pngn to fire and catch this, the actual is alter table script is generated, that runs into error when executed



sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RiazThara,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RiazThara avatar image
0 Votes"
RiazThara answered TomPhillips-1744 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SSDT does not validate column sizes prior to deployment.

0 Votes 0 ·

Thanks for confirming that, it is for that reason I posted this question if this can be taken up as a feature request if that functionality is not there now.

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi @RiazThara,
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.