Varchar (max)

 

Varchar (max)

15:16:40 | category: Database | Tag: | font size, medium/small subscription

Select Len (replicate ('1', 8002 ))

 

Select Len (replicate (convert (varchar (max), '1'), 8002 ))

Note the usage of replicate and the varchar (max) Type

The following is an article about varchar (max ).

SQL Server 2005 maximum data type value 48
It's easy to start. The mongoware marketing department wants a new website to publish documents. The development team thinks that using SQL Server 2000 database as a document storage warehouse can simplify things. Steve is the database administrator of ware and does not see any major problems. storing documents in the database, rather than using a file system, means that the server needs to do more work, but it also makes backup and management much easier. It is also impossible to synchronize databases and file systems.

The market department wants to store more than 8000 bytes of many documents, so it is obvious that varchar is not the data type suitable for this job. As an alternative, the text data type is used to define fields for storing data. Because each text can contain 2 GB of content, it is okay to store the biggest file that colleagues from the marketing department threw into the database.

Several months later, the market filled up the entire database with a large number of boring copies. But this is not what Steve really cares about. The database operated happily, and everyone was satisfied with the results of the project.

Until the day when the company's slogans changed. The marketing team believes that "Ware: It's really cool !" It is better than the original "it's usually Ware's way or the highway !" It sounds better. Because the marketing team has embedded the original banners in the footer of each document in the repository, Steve's job is to change the footer of all these documents.

"No problem," Steve thought, turning on the SQL Server Query analyzer tool and executing the following T-SQL batch:

Update marketingdocuments

Set document =

Replace (document,

'Its' software's way or the highway! ',

'Ware: its' really cool !)

When he saw the error message, Steve's smile quickly disappeared. "replace function parameter 1, the text data type is invalid ."

When the replacement function is compiled, it does not work for the text data type. It also does not work for charindex or substring ?? Or at least they do not work when they exceed characters. Furthermore, developers forget to process local variables of the text or image type. In fact, they do not support any operations. Even a simple update of a sub-string in a document requires obscure things and hard-to-use functions similar to readtext and writetext. Instead of using different types of functions, developers or busy database administrators spend time figuring out how to use them correctly.

SQL Server developers are lucky to see the blue sky. SQL Server 2005 introduces a series of new data types called Max. This is an extension of the varchar, nvarchar, and varbinary types, which were previously limited to less than 8000 bytes. Max can hold up to 2 GB of data, the same as text and image ?? It is fully compatible with all SQL Server built-in string functions.

Defining a max type variable with the Max keyword is as simple as replacing the string size (when it is varchar/nvarchar) or byte (when it is varbinary.

Declare @ bigstring varchar (max)

Set @ bigstring = 'abc'

 

Although this variable can be manipulated freely and can be passed to any built-in string function, compatibility is still not a problem. First, developers cannot expect that the varchar and varbinary variables with the specified size will automatically "Upgrade" to the max version when the limit of 8000 bytes is reached. For example, the following batch processing:

Declare @ string1 varchar (4001)

Declare @ string2 varchar (4001)

Set @ string1 = replicate ('1', 4001)

Set @ string2 = replicate ('2', 4001)

Select Len (@ string1 + @ string2)

 

4001 + 4001 = 8002, but the limit of the specified varchar is 8000. Because none of the two variables is of the Max type, the result of the Len function is 8000, not 8002. When connecting two variables, a simple correction method is to declare one of the two variables as varchar (max) or convert one of them. When connecting to a specified size type, the max type is given priority, and the final result is the max type. Therefore, the following batch processing result is 8002, as we expected:

Declare @ string1 varchar (4001)

Declare @ string2 varchar (4001)

Set @ string1 = replicate ('1', 4001)

Set @ string2 = replicate ('2', 4001)

Select Len (convert (varchar (max), @ string1) + @ string2)

 

When passed to the string function, the developer realized that the size of the string is defined by default, rather than the max type, which is also crucial. For example, the following query results are amazing:

Select Len (replicate ('1', 8002 ))

Because the string '1' is treated as a varchar of the specified size, rather than a varchar (max), the result is 8000 ?? However, in SQL Server 2005, The replicate function can generate up to 2 GB strings. To solve this problem, you can convert the string to varchar (max), so that the function will output the same type:

Select Len (replicate (convert (varchar (max), '1'), 8002 ))

This query will now return the expected result: 8002. Remember, always perform very careful tests on the code written with the new features; hidden problems, such as the problems described above, it May and will undoubtedly cause disastrous consequences in the worst time.
This post from zdnetchina Chinese community http://bbs.zdnet.com.cn/, this post address: http://bbs.zdnet.com.cn/viewthread.php? Tid = 99851

 

In addition to variables, the max type can also be used to define table fields:

Create Table bigstrings

(

Bigstring varchar (max)

)

 

When used for tables, it is very important to realize that the max type has a slightly different row overflow behavior than the text and image types. In SQL Server, the row size is 8060 bytes. If this limit is exceeded, and each bucket still manages 2 GB of storage, data stored in the text and image types will be automatically disconnected by the storage engine, only a 16-byte pointer is left in the row. This means that the row size is reduced, which is good for performance. However, retrieving big data is expensive because it is not stored in the same location as the data in the same row.

By default, the max data type uses a mix of text/image overflow and normal-sized varchar/varbinary behavior. If the data of a field and the data of all other fields in the table are less than 8060 bytes, the data is stored in the row. If the data exceeds 8060 bytes, the data of the Max field is stored outside the row. For tables with large strings, the following rows are stored in the same data page as other data in the table:

Insert bigstrings (bigstring)

Values (replicate ('1', 8000 ))

But the following row will result in an overflow:

Insert bigstrings (bigstring)

Values (replicate (convert (varchar (max), '1'), 100000 ))

You can change the default behavior of the Max data type on the basis of each table, which will behave the same as the text and image types. This is achieved by using the "out-of-the-row" option in the sp_tableoption stored procedure. The following T-SQL can be used to modify a large string table to treat the max type as the text and image data types:

Exec sp_tableoption

'Bigstrings ',

'Large value types out of row ',

'1'

Let's see how easy it is to define a max data type. Just like the flexibility they provide, some data designers will be tempted to start defining the table in the following way:

Create Table addresses

(

Name varchar (max ),

Addressline1 varchar (max ),

Addressline2 varchar (max ),

City varchar (max ),

State varchar (max ),

Postalcode varchar (max)

)

The designer should pay attention: do not do this! The data model in an enterprise should contain data with actual restrictions, and provide the user interface designer with rough guidance on field sizes. What kind of user interfaces should a table like this be created?

In addition to data integration and user interface meanings, if the designer does not need to use these types, it may cause performance damage. Remember, the query optimizer uses the field size as one of the many criteria for determining the optimization query plan. The optimizer has almost no choice for this table.

So now you know that the max data type has increased a lot of flexibility for SQL Server 2005 to process big data. But what will happen to Steve, the unfortunate database administrator of ware? He still insisted on using SQL Server 2000. He started to update his resume and imagined that his job would be lost if he failed to update the table. But he is lucky, too ?? Is there any other supporter of the ware products around the world ?? Google search can quickly find this article "search and replace in text fields", which tells him how to update it correctly. He spent the whole night learning materials. After a few months, the text and image data types are just a bit unpleasant to remember.