Home / server / Questions / 304564
Accepted
user76678
Asked: 2011-08-25 08:00:30 +0800 CST

SQL Server variable size and increase it

  • 772

It is not clear to me how a varchar(max) is declared or used in an SQL Server e.g. 2005
Is a variable declared as varchar(300000) for example considered as a varchar(max)?
E.g. I am seeing in a DB in a table a variable is declared as varchar(8000).
Can I simply increase it to varchar(300000)?
Thanks

sql-server sql database-administration

2 Answers

  1. Best Answer

    When you define a maximum size explicitly, then you cannot define higher than CHAR(8000) / VARCHAR(8000) or NCHAR(4000) / NVARCHAR(4000).

    When you define a variable as CHAR(max) or VARCHAR(max), you set the maximum size of that variable to 2GB.

    There is no way to define a variable with a maximum size between 8000 bytes and 2GB.

    • 4

  2. No, you cannot. The max size for varchar on SQL server is 8000. If you want something bigger you need to use a different type, such as ntext or text .

    Then, to find the length of data you have stored, you can do this:

    USE AdventureWorks2008R2; // this db has an example for you
GO
SELECT length = DATALENGTH(Name), Name
FROM Production.Product
    • -2