Max Character Limit for a Table Check Constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max Character Limit for a Table Check Constraint

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-07-16 : 17:00:23

Good day Folks,

Please let me know what is the maximum character limit for table check constraint
for example

ADD CONSTRAINT [xyz]
CHECK (<how many characters are allowed??>)

Much better if you could provide me a MSDN documentation as a reference for this. Thanks and Appreciate in advance. :)

Cheers
MIK

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-16 : 19:18:00

I don't think there is one. The only thing that limits you is the data types limits with the Chars N or Var it is 4000 and 8000 respectively unless you are using (MAX). with max it is the max length.

I choose a random number for this example , but it did it with lower and higher values as well :

run the following in the 3 steps.

-- begin step 1
CREATE TABLE #T
(
COL VARCHAR(MAX)
)
ALTER TABLE #T
ADD CONSTRAINT [MaxLen]
CHECK (DATALENGTH(COL) < 20000000)

INSERT INTO #T
SELECT REPLICATE('X',8000)

-- Step 1: Run to Here

-- begin step 2
UPDATE #T
SET COL = COL + COL
GO 10

DECLARE @V1 VARCHAR(MAX) = (SELECT COL FROM #T)
DECLARE @V2 NVARCHAR(MAX) = (SELECT COL FROM #T)

SELECT DATALENGTH(@V1),LEN(@V1),DATALENGTH(@V2),LEN(@V2)
-- Step 2: Run to Here

-- begin step 3
UPDATE #T
SET COL = COL + COL
GO 2

DECLARE @V1 VARCHAR(MAX) = (SELECT COL FROM #T)
DECLARE @V2 NVARCHAR(MAX) = (SELECT COL FROM #T)

SELECT DATALENGTH(@V1),LEN(@V1),DATALENGTH(@V2),LEN(@V2)
-- step 3 run to here

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-07-16 : 20:07:09

Thanks Michael, yes that is the case but I am looking for a documentation so that based on it I can make necessary changes in our DB architecture. As of now, we have implemented a check as not to create a constraint which is greater than 255 characters.

Cheers
MIK

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-07-17 : 02:50:24

create table A(Col1 INT)

ALTER TABLE dbo.A ADD Col2 VARCHAR(20) NULL
CONSTRAINT abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0 UNIQUE ;

Msg 103, Level 15, State 4, Line 3
The identifier that starts with 'abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefghi0abcdefgh' is too long. Maximum length is 128.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-17 : 03:04:14

I believe the OP is requesting the character limit for the check constraint content, not the check constraint name.
I would hope the limit is the same as any other t-sql statement.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources