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

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

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.