3

I'm trying to create the following table

CREATE TABLE Ingredient.Ingredient 
(
    GUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL,
    Name NVARCHAR(MAX) NOT NULL UNIQUE
)

but I've come to realize that the max size of a NVARCHAR UNIQUE column is 450 (at least in the current version of SQL Server). In order to not use magic literals I've created a user-defined function that returns the current max size of a NVARCHAR UNIQUE column.

CREATE FUNCTION [Max NVARCHAR Index Size]()
RETURNS INTEGER
BEGIN
    RETURN(450)
END

This function runs correctly when called as

SELECT dbo.[Max NVARCHAR Index Size]()

I was hoping to use this function in a CREATE TABLE statement, but it errors as shown below.

CREATE TABLE Ingredient.Ingredient 
(
    GUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL,
    Name NVARCHAR(dbo.[Max NVARCHAR Index Size]()) NOT NULL UNIQUE
)

Error:

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '('

To try and circumvent this I made a variable with the value of the function, and then using the variable, but that didn't work either.

DECLARE
    @NVARCHARIndexSize INTEGER = dbo.[MAX NVARCHAR Index Size]()

CREATE TABLE Ingredient.Ingredient 
(
    GUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL,
    Name NVARCHAR(@NVARCHARIndexSize) NOT NULL UNIQUE
)

Error:

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '@NVARCHARIndexSize'

where line 13 is Name NVARCHAR(@NVARCHARIndexSize) NOT NULL UNIQUE.

Is there a way to use variables/functions instead of literals in a CREATE TABLE statement?

Thanks in advance.

Improve this question
1
  • The only way you can do what you are trying to do is with dynamic SQL. You cannot define a nvarchar using a parameter as the size. Commented Jun 23, 2020 at 3:30

3 Answers 3

Reset to default
3

DDL can't be parameterized. You'd have to use dynamic SQL for that. eg

DECLARE
@NVARCHARIndexSize INTEGER = dbo.[MAX NVARCHAR Index Size]()

declare @sql nvarchar(max) = concat('
CREATE TABLE Ingredient.Ingredient 
(
    GUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL,
    Name NVARCHAR(',@NVARCHARIndexSize,') NOT NULL UNIQUE
)'
)

exec (@sql)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the response. Do you know if this was an intended design decision or a side-effect of SQL being a DDL?
3

You can create a custom type in SQL Server with following syntax

CREATE TYPE MyCustomType  
FROM NVARCHAR(420);

And later on can use the custom type while creating tables

CREATE TABLE Ingredient 
(
    GUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL,
    [Name] MyCustomType NOT NULL UNIQUE
)
Improve this answer

5 Comments

Don't you have to drop all instances of the custom type to be able to modify it in the future?
Does using a custom type require any considerations in the future? As in, is MyCustomType considered identical to NVARCHAR(420) in all situations? Just making sure I don't screw myself in the future by using a custom type.
I think you screw yourself because you can't alter it.
Using a custom type and using temp tables can lead to issues, as custom type will not be present in the temp table. Also as @DaleK suggested, you cannot alter custom type
Given the specifics of this scenario and the intent to standardize, I'd say the immutability of the custom type is appropriate and not likely to be a problem. Inaccessibility from tempdb will probably come up at some point, so that's annoying, but still the elegance of the custom type wins out for me.
1

Prior to SQL Server 2016, the maximum key length was 900 bytes. MSDN Reference

Index Key Size

The maximum size for an index key is 900 bytes for a clustered index and 1,700 bytes for a nonclustered index. (Before SQL Database and SQL Server 2016 (13.x) the limit was always 900 bytes.) Indexes on varchar columns that exceed the byte limit can be created if the existing data in the columns do not exceed the limit at the time the index is created; however, subsequent insert or update actions on the columns that cause the total size to be greater than the limit will fail. The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail.

Nonclustered indexes can include non-key columns in the leaf level of the index. These columns are not considered by the Database Engine when calculating the index key size

You can define a NVARCHAR(450) column with check constraint, to ensure that your data does not go beyond 450 characters. I would suggest you to use DATALENGTH to ensure that column length is <= 900.

CREATE TABLE #test(id int identity(1,1) not null, 
a NVARCHAR(500) CHECK (DATALENGTH(a) <= 900),
CONSTRAINT ak_a unique(a))


insert into #test
values('a') -- 1 row affected

insert into #test
values(REPLICATE('a',450)) -- 1 row affected

insert into #test
values(REPLICATE('a',451)) -- Error

Msg 547, Level 16, State 0, Line 12 The INSERT statement conflicted with the CHECK constraint "CK__#test__________a__AC6651A7". The conflict occurred in database "tempdb", table "#test", column 'a'.

In future, when you move to higher versions, you can increase length of NVARCHAR and CHECK constraint accordingly.

Improve this answer

2 Comments

Is CONSTRAINT ak_a unique(a) as performant is typing the field as unique?
both are same only. when you type UNIQUE the unique constraint name will be system generated name. when you type the name yourself, your name will be chosen

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.