1

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

sysadmin1138
  • 133,124
  • 18
  • 176
  • 300
user76678
  • 349
  • 3
  • 5
  • 16

2 Answers2

4

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.

Mike Insch
  • 1,254
  • 8
  • 10
  • VARCHAR(MAX) is the same as using TEXT as the data type. They are both "large value types" that have a pointer to a larger blob of data. – djangofan Aug 24 '11 at 16:24
  • 3
    @djangofan not exactly. With the MAX data types the data is stored in row until the row overflows, then the data is moved to a dedicated data page (or set of data pages) and the pointer is inserted. Also different is that when using PAGE level compression MAX data values can be compressed as long as they are stored in row. TEXT, NTEXT and IMAGE data types will never be compressed as they are never stored in row. http://www.sqlmag.com/blog/troubleshooting-sql-server-storage-problems-51/database-administration/not-all-data-compression-is-created-equal-140240 – mrdenny Aug 24 '11 at 16:33
  • I see. So I would just have to declare it as `VARCHAR(MAX)` and I will be able to store up to 300000 bytes? Also is the declation with `MAX`? I mean literal? – user76678 Aug 24 '11 at 16:34
  • @djangofan: nothing could be further from the truth. The legacy deprecated types (TEXT/NTEXT/IMAGE) have nothing to do with the new MAX types. They have a completely different API to manipulate (TEXTPTR/READTEXT/WRITETEXT), they cannot be used in functions that accept MAX types (REPLACE, SUBSTRING, STUFF etc) and they are stored in a different manner. – Remus Rusanu Aug 24 '11 at 17:59
-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
djangofan
  • 4,182
  • 10
  • 46
  • 59
  • 1
    Not true - the `VARCHAR(max)`, `CHAR(max)` and `VARBINARY(max)` datatypes in SQL Server 2005 and 2008 allow for storage of up to 2GB without using `NTEXT`, `TEXT`, `IMAGE` or other BLOB types. – Mike Insch Aug 24 '11 at 16:16
  • Your right, the length can be either 1-8000 or, by using MAX, it skips to 2,147,483,647 chars, just like "text" or "ntext". Seems kinda wierd, which is why I never thought it was possible. – djangofan Aug 24 '11 at 16:19
  • @ Mike Insch - More exact info here: http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax – djangofan Aug 24 '11 at 16:21