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
Add a comment |
2 Answers
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.
- 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– djangofan2011-08-24 16:24:31 +00:00Commented Aug 24, 2011 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. sqlmag.com/blog/troubleshooting-sql-server-storage-problems-51/…mrdenny– mrdenny2011-08-24 16:33:38 +00:00Commented Aug 24, 2011 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 withMAX? I mean literal?user76678– user766782011-08-24 16:34:28 +00:00Commented Aug 24, 2011 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– Remus Rusanu2011-08-24 17:59:20 +00:00Commented Aug 24, 2011 at 17:59
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 answered Aug 24, 2011 at 16:13
- 1Not true - the
VARCHAR(max),CHAR(max)andVARBINARY(max)datatypes in SQL Server 2005 and 2008 allow for storage of up to 2GB without usingNTEXT,TEXT,IMAGEor other BLOB types.Mike Insch– Mike Insch2011-08-24 16:16:02 +00:00Commented Aug 24, 2011 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– djangofan2011-08-24 16:19:09 +00:00Commented Aug 24, 2011 at 16:19
- @ Mike Insch - More exact info here: stackoverflow.com/questions/148398/…djangofan– djangofan2011-08-24 16:21:55 +00:00Commented Aug 24, 2011 at 16:21