Start very simple things. MegaWare marketing department wanted a new site to publish the document, the development team that uses SQL Server 2000 database as a document storage warehouse will make things easier. Steve is MegaWare database administrators, this will not see any major problems; store documents in the database, rather than using the file system, means that the server more work, Danshi it will make backup and administration easier. Database becomes out of sync with the file system should also be possible.
The market sector you want to store the many documents are more than 8000 bytes, then obviously not for the VARCHAR data type of work. As an alternative, TEXT data type is used to define the storage of data fields. Because each can accommodate 2GB of content TEXT, TEXT to store marketing colleagues thrown into the largest database file is no problem.
A few months later, the market filled with plenty of boring copy the entire database. But this is not the real concern Steve. Database to function happily humming with, everyone is very satisfied with the results of the project.
Until the company's slogan changes on the important day. Marketing team that "MegaWare: It's really cool!" Than the original "It's MegaWare's Way or the Highway!" Sounds better. Because the marketing team has the slogan embedded in the original warehouse in footer of each document, and now Steve's job is to change the footer of all of these documents.
"No problem," Steve would like to open the SQL Server Query Analyzer tool, perform the following T-SQL batch:
UPDATE MarketingDocumentsSET Document = REPLACE (Document,
'It''s MegaWare''s Way or the Highway!',
'MegaWare: It''s really cool!)
When he saw the error message appears when, Steve's easy smile quickly disappeared, "replace the function of the parameter 1, text data type is invalid."
Written by replacing the time function, it does not work on TEXT data type. Likewise CHARINDEX or SUBSTRING also does not work - or at least more than 8 thousand in the case of characters does not work. Further speaking, the developer forgot to deal with IMAGE or TEXT types of local variables; in fact do not support any action. Even a simple update of a document in a sub-string needs to use something obscure and difficult to use and WRITETEXT READTEXT similar function. Rather than developers or database administrators busy because you want to find out how to properly use different types of functions used time consuming.
SQL Server developers are lucky, they will sweep aside the dark clouds and see the blue sky. SQL Server 2005 introduces a new series known as MAX data types. This is a VARCHAR, NVARCHAR, and VARBINARY types of extensions, which previously was limited to several types of the following 8000 bytes. MAX can accommodate up to 2GB of data, and as TEXT and IMAGE - and fully compatible with all of the SQL Server built-in string functions.
With MAX MAX keyword to define a certain type of variable size with replacement string (for VARCHAR / NVARCHAR time) or bytes (for VARBINARY time) as easy.
DECLARE @ BigString VARCHAR (MAX)
SET @ BigString = 'abc'
Although this variable can be freely manipulated, and can be passed to any of the built-in string functions, compatibility is still not without problems. First, the developer can not expect to specify the size of VARCHAR and VARBINARY byte variable in 8000, when the limit can be automatically "upgraded" to the MAX version. For example, the following batch:
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 specified size limit of VARCHAR is 8000. Because these two variables is not a MAX type, LEN function result is 8000, not 8002. Connecting the two variables in the time, a simple correction method is to declare one of these two variables for the VARCHAR (MAX) or to convert one variable. Provided with a connection size of the type of when the priority MAX type, the end result is the MAX type. Therefore, the following batch of result is 8002, as we expect the same:
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)
Passed to the string functions in time, developers realized that the original intent of the string by default, is to provide the size, rather than the MAX type, is also essential. For example, the following very surprising result of the query:
SELECT LEN (REPLICATE ('1 ', 8002))
Because the string '1 'is used as the size of VARCHAR treatment provided, rather than VARCHAR (MAX), the result is 8000 - but in SQL Server 2005 in, REPLICATE function to generate a string of up to 2GB. To fix this problem, you can convert a string to VARCHAR (MAX), this function will output the same type of:
SELECT LEN (REPLICATE (CONVERT (VARCHAR (MAX), '1 '), 8002))
This query will now return the expected results: 8002. Remember, always want to use the new features of the code very carefully prepared tests; hidden problems, such as the problem described above, and no doubt will be the worst time in catastrophic consequences.
In addition to variables outside, MAX type can also be used to define the table fields:
CREATE TABLE BigStrings
(
BigString VARCHAR (MAX)
)
When the time for the table, aware of the MAX types are TEXT and IMAGE types with slightly different line overflow behavior is very important. In SQL Server, the maximum line size is 8060 bytes. To exceed this limit, and still manage each have up to 2GB of storage, with the TEXT and IMAGE types of stored data is automatically off-line storage engine, leaving only the line where a 16-byte pointer. This means that the line size is decreased, which is good for performance. However, the retrieval of large data is expensive, because it is not with the same line of data stored in the same location.
MAX data types by default, use the TEXT / IMAGE overflow behavior and normal size of the VARCHAR / VARBINARY type of behavior by a hybrid method. If a field's data, plus all the other fields in the table of data, the total is less than 8060 bytes, the data stored within the line. If the data more than 8060 bytes, MAX field data will be stored outside the line. For large string tables, the following line will work with other data in the table data stored in the same pages:
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 MAX data types in each table based on the default behavior, they will behave the same and the TEXT and IMAGE types. This is the stored procedure by using the sp_tableoption "Great Value type outside the line" option implemented. In order to modify the large string table to the MAX type of approach becomes TEXT and IMAGE data types and handling the same way, you can use the following T-SQL:
EXEC sp_tableoption'BigStrings',
'Large value types out of row', '1 '
Look at how to define a MAX data type easily, with the flexibility they provide, as some of the data architect will be tempted in the following way to start the definition of the table:
CREATE TABLE Addresses
(
Name VARCHAR (MAX),
AddressLine1 VARCHAR (MAX),
AddressLine2 VARCHAR (MAX), City VARCHAR (MAX), State VARCHAR (MAX),
PostalCode VARCHAR (MAX))
Designers should pay attention to the: do not do! An enterprise data model should contain both the practical limitations of the data, but also to the user interface designer of the approximate size of the field guidance. Create a table like this what should the user interface do?
In addition to the meaning of data integration and user interface, in addition, if the designer so unnecessary use of these types of performance will bring harm. Remember, the query optimizer to use the field to judge the size of a query plan optimization is one of many criteria. For this table, the optimizer almost no choice.
So now you know the MAX data type for the SQL Server 2005 to handle large data increased very much flexibility. But MegaWare of that unfortunate database administrator, Steve What happens? Still insist on using SQL Server 2000, he began to update the resume, imagine if the updated table failed, then would lose his job. But he is lucky - and the world champion MegaWare products - with a GOOGLE search can quickly find this article "in the TEXT field, find and replace," the article told him how to correct to be updated. He spent the night in time to learn the information; a few months later, TEXT and IMAGE data type to just unpleasant memories.