Geek Answers Handbook

Can I set the VARCHAR size to more than 8k but less than MAX?

Starting with SQL 2005, VARCHAR (MAX) is no longer limited to 8000 bytes; instead, it can switch to 2 GB using overflow pages.

But what if I want to limit this column to, say, 10k bytes? It seems that I am getting an error if I try to put something in a parameter of size above 8000. Which is strange, because MAX is the same as a request for a 2 GB limit. It seems to be a kind of “all or nothing” when it comes to maximum size.

How to get around this?

+5
source share
3 answers

, CHECK:

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

:

+12

, . varchar (< = 8000), varchar (MAX), .

, :

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

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

.

+8

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

Attempting to use say 10000 will give:

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

However, you can use the VARCHAR (MAX) column with a CHECK metric. Sort of:

CHECK (DATALENGTH([myBigColumn]) <= 10000)
+3
source

More articles:


All Articles