Working with Large Value Types
In previous versions of SQL Server, working with large object (LOB) data types required special handling. LOB data types are those that exceed the maximum row size of 8 kilobytes (KB). SQL Server 2005 introduces a max specifier for varchar, nvarchar, and varbinary data types to allow storage of values as large as 2^32 bytes. Table columns and Transact-SQL variables may specify varchar(max), nvarchar(max), or varbinary(max) data types. In ADO.NET, the new max data types can be fetched by a DataReader, and can also be specified as both input and output parameter values without any special handling. For large varchar data types, data can be retrieved and updated incrementally.
In SQL Server 2005, the new max data types can be used for comparisons, as Transact-SQL variables, and for concatenation. They can also be used in the DISTINCT, ORDER BY, GROUP BY clauses of a SELECT statement as well as in aggregates, joins, and subqueries. For a complete list of capabilities, see SQL Server Books Online.
Large Value Type Restrictions
The following restrictions apply to the max data types, which do not exist for smaller data types:
A sql_variant cannot contain a large varchar data type.
Large varchar columns cannot be specified as a key column in an index. They are allowed in an included column in a non-clustered index.
Large varchar columns cannot be used as partitioning key columns.
Working with Large Value Types in Transact-SQL
The Transact-SQL OPENROWSET function is a one-time method of connecting and accessing remote data. It includes all of the connection information necessary to access remote data from an OLE DB data source. OPENROWSET can be referenced in the FROM clause of a query as though it were a table name. It can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider.
The OPENROWSET function has been enhanced in SQL Server 2005 with the addition of the BULK rowset provider, which allows you to read data directly from a file without loading the data into a target table. This enables you to use OPENROWSET in a simple INSERT SELECT statement.
The OPENROWSET BULK option arguments provide significant control over where to begin and end reading data, how to deal with errors, and how data is interpreted. For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. For the complete syntax and options, see SQL Server Books Online.
The following example inserts a photo into the ProductPhoto table in the AdventureWorks sample database. When using the BULK OPENROWSET provider, you must supply the named list of columns even if you aren't inserting values into every column. The primary key in this case is defined as an identity column, and may be omitted from the column list. Note that you must also supply a correlation name at the end of the OPENROWSET statement, which in this case is ThumbnailPhoto. This correlates with the column in the ProductPhoto table into which the file is being loaded.
INSERT Production.ProductPhoto (
ThumbnailPhoto,
ThumbnailPhotoFilePath,
LargePhoto,
LargePhotoFilePath)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET
(BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto
Updating Data using UPDATE .WRITE
The Transact-SQL UPDATE statement has new WRITE syntax for modifying the contents of varchar(max), nvarchar(max), or varbinary(max) columns. This allows you to perform partial updates of the data. The UPDATE .WRITE syntax is shown here in abbreviated form:
UPDATE
{ <object> }
SET
{ column_name = { .WRITE ( expression , @Offset , @Length ) }
The WRITE method specifies that a section of the value of the column_name will be modified. The expression is the value that will be copied to the column_name, the @Offset is the beginning point at which the expression will be written, and the @Length argument is the length of the section in the column.
| If | Then |
|---|---|
The expression is set to NULL |
@Length is ignored and the value in column_name is truncated at the specified @Offset. |
@Offset is NULL |
The update operation appends the expression at the end of the existing column_name value and @Length is ignored. |
@Offset is greater than the length of the column_name value |
SQL Server returns an error. |
@Length is NULL |
The update operation removes all data from @Offset to the end of the column_name value. |
Note
Neither @Offset nor @Length can be a negative number.
Example
This Transact-SQL example updates a partial value in DocumentSummary, an nvarchar(max) column in the Document table in the AdventureWorks database. The word 'components' is replaced by the word 'features' by specifying the replacement word, the beginning location (offset) of the word to be replaced in the existing data, and the number of characters to be replaced (length). The example includes SELECT statements before and after the UPDATE statement to compare results.
USE AdventureWorks;
GO
--View the existing value.
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 3;
GO
-- The first sentence of the results will be:
-- Reflectors are vital safety components of your bicycle.
--Modify a single word in the DocumentSummary column
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
WHERE DocumentID = 3 ;
GO
--View the modified value.
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 3;
GO
-- The first sentence of the results will be:
-- Reflectors are vital safety features of your bicycle.
Working with Large Value Types in ADO.NET
You can work with large value types in ADO.NET by specifying large value types as SqlParameter objects in a SqlDataReader to return a result set, or by using a SqlDataAdapter to fill a DataSet/DataTable. There is no difference between the way you work with a large value type and its related, smaller value data type.
Using GetSqlBytes to Retrieve Data
The GetSqlBytes method of the SqlDataReader can be used to retrieve the contents of a varbinary(max) column. The following code fragment assumes a SqlCommand object named cmd that selects varbinary(max) data from a table and a SqlDataReader object named reader that retrieves the data as SqlBytes.
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
Dim bytes As SqlBytes = reader.GetSqlBytes(0)
End While
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
SqlBytes bytes = reader.GetSqlBytes(0);
}
Using GetSqlChars to Retrieve Data
The GetSqlChars method of the SqlDataReader can be used to retrieve the contents of a varchar(max) or nvarchar(max) column. The following code fragment assumes a SqlCommand object named cmd that selects nvarchar(max) data from a table and a SqlDataReader object named reader that retrieves the data.
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
Dim buffer As SqlChars = reader.GetSqlChars(0)
End While
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
SqlChars buffer = reader.GetSqlChars(0);
}
Using GetSqlBinary to Retrieve Data
The GetSqlBinary method of a SqlDataReader can be used to retrieve the contents of a varbinary(max) column. The following code fragment assumes a SqlCommand object named cmd that selects varbinary(max) data from a table and a SqlDataReader object named reader that retrieves the data as a SqlBinary stream.
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
Dim binaryStream As SqlBinary = reader.GetSqlBinary(0)
End While
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
SqlBinary binaryStream = reader.GetSqlBinary(0);
}
Using GetBytes to Retrieve Data
The GetBytes method of a SqlDataReader reads a stream of bytes from the specified column offset into a byte array starting at the specified array offset. The following code fragment assumes a SqlDataReader object named reader that retrieves bytes into a byte array. Note that, unlike GetSqlBytes, GetBytes requires a size for the array buffer.
While reader.Read()
Dim buffer(4000) As Byte
Dim byteCount As Integer = _
CInt(reader.GetBytes(1, 0, buffer, 0, 4000))
End While
while (reader.Read())
{
byte[] buffer = new byte[4000];
long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);
}
Using GetValue to Retrieve Data
The GetValue method of a SqlDataReader reads the value from the specified column offset into an array. The following code fragment assumes a SqlDataReader object named reader that retrieves binary data from the first column offset, and then string data from the second column offset.
While reader.Read()
' Read the data from varbinary(max) column
Dim binaryData() As Byte = CByte(reader.GetValue(0))
' Read the data from varchar(max) or nvarchar(max) column
Dim stringData() As String = Cstr((reader.GetValue(1))
End While
while (reader.Read())
{
// Read the data from varbinary(max) column
byte[] binaryData = (byte[])reader.GetValue(0);
// Read the data from varchar(max) or nvarchar(max) column
String stringData = (String)reader.GetValue(1);
}
Converting from Large Value Types to CLR Types
You can convert the contents of a varchar(max) or nvarchar(max) column using any of the string conversion methods, such as ToString. The following code fragment assumes a SqlDataReader object named reader that retrieves the data.
While reader.Read()
Dim str as String = reader(0).ToString()
Console.WriteLine(str)
End While
while (reader.Read())
{
string str = reader[0].ToString();
Console.WriteLine(str);
}
Example
The following code retrieves the name and the LargePhoto object from the ProductPhoto table in the AdventureWorks database and saves it to a file. The assembly needs to be compiled with a reference to the System.Drawing namespace. The GetSqlBytes method of the SqlDataReader returns a SqlBytes object that exposes a Stream property. The code uses this to create a new Bitmap object, and then saves it in the Gif ImageFormat.
Private Sub GetPhoto( _
ByVal documentID As Integer, ByVal filePath As String)
' Assumes GetConnectionString returns a valid connection string.
Using connection As New SqlConnection(GetConnectionString())
Dim command As SqlCommand = connection.CreateCommand()
Dim reader As SqlDataReader
Try
' Setup the command
command.CommandText = _
"SELECT LargePhotoFileName, LargePhoto FROM" _
& " Production.ProductPhoto" _
& " WHERE ProductPhotoID=@ProductPhotoID"
command.CommandType = CommandType.Text
' Declare the parameter
Dim paramID As SqlParameter = _
New SqlParameter("@ProductPhotoID", SqlDbType.Int)
paramID.Value = documentID
command.Parameters.Add(paramID)
connection.Open()
Dim photoName As String
reader = _
command.ExecuteReader(CommandBehavior.CloseConnection)
If reader.HasRows Then
While reader.Read()
' Get the name of the file
photoName = reader.GetString(0)
' Ensure that the column isn't null
If (reader.IsDBNull(1)) Then
Console.WriteLine("{0} is unavailable.", photoName)
Else
Dim bytes As SqlBytes = reader.GetSqlBytes(1)
Using productImage As Bitmap = _
New Bitmap(bytes.Stream)
Dim fileName As String = filePath & photoName
' Save in gif format.
productImage.Save( _
fileName, ImageFormat.Gif)
Console.WriteLine("Successfully created {0}.", fileName)
End Using
End If
End While
Else
Console.WriteLine("No records returned.")
End If
Catch ex As Exception
Console.WriteLine("Exception: {0}", ex.Message)
End Try
End Using
End Sub
static private void TestGetSqlBytes(int documentID, string filePath)
{
// Assumes GetConnectionString returns a valid connection string.
using (SqlConnection connection =
new SqlConnection(GetConnectionString()))
{
SqlCommand command = connection.CreateCommand();
SqlDataReader reader = null;
try
{
// Setup the command
command.CommandText =
"SELECT LargePhotoFileName, LargePhoto "
+ "FROM Production.ProductPhoto "
+ "WHERE ProductPhotoID=@ProductPhotoID";
command.CommandType = CommandType.Text;
// Declare the parameter
SqlParameter paramID =
new SqlParameter("@ProductPhotoID", SqlDbType.Int);
paramID.Value = documentID;
command.Parameters.Add(paramID);
connection.Open();
string photoName = null;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
while (reader.Read())
{
// Get the name of the file.
photoName = reader.GetString(0);
// Ensure that the column isn't null
if (reader.IsDBNull(1))
{
Console.WriteLine("{0} is unavailable.", photoName);
}
else
{
SqlBytes bytes = reader.GetSqlBytes(1);
using (Bitmap productImage = new Bitmap(bytes.Stream))
{
String fileName = filePath + photoName;
// Save in gif format.
productImage.Save(fileName, ImageFormat.Gif);
Console.WriteLine("Successfully created {0}.", fileName);
}
}
}
}
else
{
Console.WriteLine("No records returned.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (reader != null)
reader.Dispose();
}
}
}
Using Large Value Type Parameters
Large value types can be used in SqlParameter objects the same way you use smaller value types in SqlParameter objects. You can retrieve large value types as SqlParameter values, as shown in the following example. The code assumes that the following GetDocumentSummary stored procedure exists in the AdventureWorks sample database. The stored procedure takes an input parameter named @DocumentID and returns the contents of the DocumentSummary column in the @DocumentSummary output parameter.
CREATE PROCEDURE GetDocumentSummary
(
@DocumentID int,
@DocumentSummary nvarchar(MAX) OUTPUT
)
AS
SET NOCOUNT ON
SELECT @DocumentSummary=Convert(nvarchar(MAX), DocumentSummary)
FROM Production.Document
WHERE DocumentID=@DocumentID
Example
The ADO.NET code creates SqlConnection and SqlCommand objects to execute the GetDocumentSummary stored procedure and retrieve the document summary, which is stored as a large value type. The code passes a value for the @DocumentID input parameter, and displays the results passed back in the @DocumentSummary output parameter in the Console window.
Private Function GetDocumentSummary( _
ByVal documentID As Integer) As String
' Assumes GetConnectionString returns a valid connection string.
Using connection As New SqlConnection(GetConnectionString())
connection.Open()
Dim command As SqlCommand = connection.CreateCommand()
' Setup the command to execute the stored procedure.
command.CommandText = "GetDocumentSummary"
command.CommandType = CommandType.StoredProcedure
' Set up the input parameter for the DocumentID.
Dim paramID As SqlParameter = _
New SqlParameter("@DocumentID", SqlDbType.Int)
paramID.Value = documentID
command.Parameters.Add(paramID)
' Set up the output parameter to retrieve the summary.
Dim paramSummary As SqlParameter = _
New SqlParameter("@DocumentSummary", _
SqlDbType.NVarChar, -1)
paramSummary.Direction = ParameterDirection.Output
command.Parameters.Add(paramSummary)
' Execute the stored procedure.
command.ExecuteNonQuery()
Console.WriteLine(paramSummary.Value)
Return paramSummary.Value.ToString
End Using
End Function
static private string GetDocumentSummary(int documentID)
{
//Assumes GetConnectionString returns a valid connection string.
using (SqlConnection connection =
new SqlConnection(GetConnectionString()))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
try
{
// Setup the command to execute the stored procedure.
command.CommandText = "GetDocumentSummary";
command.CommandType = CommandType.StoredProcedure;
// Set up the input parameter for the DocumentID.
SqlParameter paramID =
new SqlParameter("@DocumentID", SqlDbType.Int);
paramID.Value = documentID;
command.Parameters.Add(paramID);
// Set up the output parameter to retrieve the summary.
SqlParameter paramSummary =
new SqlParameter("@DocumentSummary",
SqlDbType.NVarChar, -1);
paramSummary.Direction = ParameterDirection.Output;
command.Parameters.Add(paramSummary);
// Execute the stored procedure.
command.ExecuteNonQuery();
Console.WriteLine((String)(paramSummary.Value));
return (String)(paramSummary.Value);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
}
}