Use user-defined function in CREATE TABLE statement

user_4860693 0
Viewed 224

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.

ssms tsql sql-server sql
3 Answers

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
)
user_13045730

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)
user_72977000

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.

user_6349350
Related