You can indicate the MAX specifier instead of an actual size when you define a column, variable, or parameter using one of the dynamic-length datatypes: VARCHAR, NVARCHAR, or VARBINARY. By using the MAX specifier, you tell SQL Server that the stored values can potentially reach the maximum size supported by LOBs, which is currently 2 GB. SQL Server will determine how to store the values internally.

One of the more important aspects of this enhancement is that the programming model for regular types and LOBs is now unified. Namely, they can be used with column datatypes, local variables, input or output parameters, and so on. Also, unlike the older LOB datatypes, all functions that support regular datatypes now also support datatypes defined with the MAX specifier.

For example, the following code creates a table called CustomerData, with an INT custid column and txt_data, ntxt_data, and binry_data columns defined with the datatypes VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX), respectively:

USE tempdb;
GO
IF OBJECT_ID('dbo.CustomerData') IS NOT NULL
  DROP TABLE dbo.CustomerData;
GO

CREATE TABLE dbo.CustomerData
(
  custid      INT            NOT NULL PRIMARY KEY,
  txt_data    VARCHAR(MAX)   NULL,
  ntxt_data   NVARCHAR(MAX)  NULL,
  binary_data VARBINARY(MAX) NULL
);

As I mentioned earlier, you can treat these datatypes as regular ones. For example, load a new customer row with the custid 102 and the character string ′Customer 102 text data′ in the txt_data column:

INSERT INTO dbo.CustomerData(custid, txt_data)
  VALUES(102, 'Customer 102 text data'),

You can use the character string functions with which you’re already familiar to manipulate character data stored in a dynamic MAX column. However, if you want to modify a certain section within such a value, using a character string function such as STUFF would result in overriding the entire string, which is inefficient with large values. Instead, SQL Server enhances the UPDATE statement by providing you with a WRITE method for dynamic MAX columns. The WRITE method allows you to modify only a section within the string and not override the whole thing.

Logically, the WRITE method is similar to the STUFF function. It accepts three arguments: @expression, @offset, and @length. The @expression argument replaces @length units (characters/bytes) starting from @offset position in the target value.

Note

Note that @offset is zero-based.

For example, the following code operates on the txt_data column value for customer 102. It replaces the string ′102′ located at offset 9 (zero-based) with the string ′one hundred and two′, resulting in the string ′Customer one hundred and two text data′:

UPDATE dbo.CustomerData
  SET txt_data.WRITE('one hundred and two', 9, 3)
WHERE custid = 102;

Note

If the target LOB is NULL, an update that uses WRITE will fail.

If @expression is NULL, @length is ignored, and the value is truncated at the @offset position. For example, the following code truncates the string at the 28^th position, resulting in the string ′Customer one hundred and two′:

UPDATE dbo.CustomerData
  SET txt_data.WRITE(NULL, 28, 0)
WHERE custid = 102;

If @length is NULL, the string is truncated at the @offset position, and @expression is appended at the end. For example, the following code truncates the string at the ninth position, and appends ′102′ at the end, resulting in the string ′Customer 102′:

UPDATE dbo.CustomerData
  SET txt_data.WRITE('102', 9, NULL)
WHERE custid = 102;

If @offset is NULL and @length is 0, @expression is simply appended at the end. For example, the following code appends the string ′is discontinued′ at the end, resulting in the string ′Customer 102 is discontinued′:

UPDATE dbo.CustomerData
  SET txt_data.WRITE(' is discontinued', NULL, 0)
WHERE custid = 102;

If @expression is an empty string, no data is inserted; rather, you just remove a substring at the @offset position in the size of @length. For example, the following code removes 4 characters at the ninth position:

-- Removing 4 characters beginning at position 9
UPDATE dbo.CustomerData
  SET txt_data.WRITE('', 9, 4)
WHERE custid = 102;

If you query the data at this point, you will get the string ′Customer is discontinued′:

SELECT txt_data FROM dbo.CustomerData WHERE custid = 102;

SQL Server 2005 introduces the BULK rowset provider, which allows you to use the BULK engine to load file data as a rowset or as a single LOB value. You specify BULK as the provider in the OPENROWSET function, along with other options that are relevant to your request.

For example, the following code returns the data from a file called shippers.txt as a row set, based on the format file shippers.fmt:

SELECT ShipperID, CompanyName, Phone
  FROM OPENROWSET(BULK 'c:	empshippers.txt',
         FORMATFILE = 'c:	empshippers.fmt') AS S;

More Info

All files used in this section’s examples can be downloaded from http://www.insidetsql.com as part of the book’s source code download. For more information, see the Introduction.

The format file is the same format file you’re familiar with when working with bcp.exe or BULK INSERT. In fact, you can generate it either manually or by using bcp.exe as you have used it thus far. Besides FORMATFILE, you can also specify other bulk options: CODEPAGE, ERRORFILE, FIRSTROW, LASTROW, MAXERRORS and ROWS_PER_BATCH.

You can also use the BULK provider to load file data as a row set into a target table using an INSERT statement. This way, you can efficiently utilize the BULK engine. In such an INSERT statement, you can control load options using table hints, including KEEPIDENTITY, KEEPDEFAULTS, IGNORE_CONSTRAINTS, IGNORE_TRIGGERS and TABLOCK. To demonstrate loading a row set into a table using the BULK provider, first run the following code, which creates the Shippers table:

USE tempdb;
GO
IF OBJECT_ID('dbo.Shippers') IS NOT NULL
  DROP TABLE dbo.Shippers;
GO

CREATE TABLE dbo.Shippers
(
  ShipperID   INT          NOT NULL PRIMARY KEY,
  CompanyName NVARCHAR(40) NOT NULL,
  Phone       NVARCHAR(24) NOT NULL CHECK(Phone NOT LIKE '%[^0-9() ]%')
);
GO

The following code is an example for loading the content of a file called shippers.txt into the target Shippers table, using the shippers.fmt format file:

INSERT INTO dbo.Shippers WITH (IGNORE_CONSTRAINTS)
  SELECT ShipperID, CompanyName, Phone
    FROM OPENROWSET(BULK 'c:	empshippers.txt',
           FORMATFILE = 'c:	empshippers.fmt') AS S;

The table hint IGNORE_CONSTRAINTS tells SQL Server not to validate CHECK and FOREIGN KEY constraints when loading the data.

The BULK rowset provider can also be used to load the content of a file as a scalar LOB value in a SELECT, INSERT or UPDATE statement. You use the OPENROWSET function and specify the BULK option, the source file name, and one of three options for the type of data: SINGLE_CLOB for regular character data, SINGLE_NCLOB for Unicode data, and SINGLE_BLOB for binary data.

Note

When you want to load XML data from a file, you use either SINGLE_CLOB or SINGLE_NCLOB depending on whether the XML file contains regular character data or Unicode data.

To demonstrate this capability, the following INSERT statement inserts a new customer into the CustomerData table, with custid 101, and an XML value loaded from the file xmlfile101.xml into the xml_data column:

INSERT INTO dbo.CustomerData(custid, xml_data)
  SELECT 101,
  (SELECT xml_data FROM OPENROWSET(
    BULK 'c:	empxmlfile101.xml', SINGLE_NCLOB) AS F(xml_data));

Similarly, the following UPDATE statement loads the three files: textfile101.txt, unicodefile101.txt and binaryfile101.jpg into customer 101’s columns: txt_data, ntxt_data and binary_data, respectively:

UPDATE dbo.CustomerData
  SET txt_data  = (SELECT txt_data FROM OPENROWSET(
    BULK 'c:	emp	extfile101.txt', SINGLE_CLOB) AS F(txt_data)),
  ntxt_data  = (SELECT ntxt_data FROM OPENROWSET(
    BULK 'c:	empunicodefile101.txt', SINGLE_NCLOB) AS F(ntxt_data)),
  binary_data  = (SELECT binary_data FROM OPENROWSET(
    BULK 'c:	empinaryfile101.jpg', SINGLE_BLOB) AS F(binary_data))
WHERE custid = 101;