Can I set VARCHAR size larger than 8k but less than MAX?

Question

Starting in SQL 2005, VARCHAR(MAX) is no longer limited to 8000 bytes, it instead can go up to 2GB using "overflow" pages.

But what if I want to limit this column to say, 10k bytes? It seems I get an error if I try to put anything in the size parameter above 8000. Which is odd because MAX is the same as asking for a 2GB limit. Seems like its a sort of "all or nothing" when it comes to the Max size.

Any way around this?

Answer 1

Nope, you cannot do this [directly]. It is either 0-8000 or MAX.

Attempt to using say 10000 will give:

The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

You can however use a VARCHAR(MAX) column with a CHECK contraint. Something like:

CHECK (DATALENGTH([myBigColumn]) <= 10000)

Answer 2

No you can not. either varchar(<=8000) or varchar(MAX), nothing in between.

You can do this though:

CREATE TABLE [dbo].[VarChar10000] ( [VarChar10000] VARCHAR(MAX) )
GO

ALTER TABLE [dbo].[VarChar10000] 
    ADD CONSTRAINT [MaxLength10000]
    CHECK (DATALENGTH([VarChar10000]) <= 10000)
GO

Check here.

Answer 3

You can, but it requires you to implement a CHECK constraint:

CHECK (DATALENGTH([VarChar10000]) <= 10000)

Reference:

• SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length