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.
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. :)CheersMIK |
|
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 #TADD CONSTRAINT [MaxLen]CHECK (DATALENGTH(COL) < 20000000)INSERT INTO #TSELECT REPLICATE('X',8000)-- Step 1: Run to Here-- begin step 2UPDATE #TSET COL = COL + COLGO 10DECLARE @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 3UPDATE #TSET COL = COL + COLGO 2DECLARE @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.CheersMIK |
|
|
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 3The 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 |
|
|
|
|
|
|
|