LOB(큰 개체) 데이터 형식은 최대 행 크기 8KB(KB)를 초과하는 데이터 형식입니다. SQL Server는 max, varchar및 nvarchar 데이터 형식에 대한 varbinary 지정자를 제공하여 2^32바이트만큼 큰 값의 스토리지를 허용합니다. 테이블 열 및 Transact-SQL 변수는 varchar(max), nvarchar(max)또는 varbinary(max) 데이터 형식을 지정할 수 있습니다. ADO.NET에서 max 데이터 형식은 DataReader에 의해 가져올 수 있으며, 특별한 처리 없이 입력 및 출력 매개변수 값으로 지정할 수도 있습니다. 대용량 varchar 데이터 형식의 경우 데이터를 증분 방식으로 검색하고 업데이트할 수 있습니다.
max 데이터 형식은 비교, Transact-SQL 변수 및 연결에 사용할 수 있습니다. 또한 SELECT 문의 DISTINCT, ORDER BY, GROUP BY 절뿐만 아니라 집계, 조인 및 하위 쿼리에서도 사용할 수 있습니다.
자세한 내용은 Large-Value 데이터 형식 사용참조하세요.
Large-Value 형식 제한
더 작은 데이터 형식에 대해 존재하지 않는 max 데이터 형식에는 다음과 같은 제한이 적용됩니다.
sql_variant큰varchar데이터 형식을 포함할 수 없습니다.큰
varchar열은 인덱스의 키 열로 지정할 수 없습니다. 비클러스터형 인덱스 내 포함된 열에서 허용됩니다.큰
varchar열은 분할 키 열로 사용할 수 없습니다.
Transact-SQL에서 Large-Value 형식 다루기
Transact-SQL OPENROWSET 함수는 원격 데이터를 연결하고 액세스하는 일회성 방법입니다. 여기에는 OLE DB 데이터 원본에서 원격 데이터에 액세스하는 데 필요한 모든 연결 정보가 포함됩니다.
OPENROWSET 테이블 이름인 것처럼 쿼리의 FROM 절에서 참조할 수 있습니다. OLE DB 공급자의 기능에 따라 INSERT, UPDATE 또는 DELETE 문의 대상 테이블로 참조할 수도 있습니다.
OPENROWSET 함수에는 대상 테이블에 데이터를 로드하지 않고 파일에서 직접 데이터를 읽을 수 있는 BULK 행 집합 공급자가 포함됩니다. 이렇게 하면 간단한 INSERT SELECT 문에서 OPENROWSET 사용할 수 있습니다.
OPENROWSET BULK 옵션 인수는 데이터 읽기를 시작하고 종료할 위치, 오류 처리 방법 및 데이터 해석 방법에 대한 중요한 제어를 제공합니다. 예를 들어 데이터 파일을 varbinary, varchar또는 nvarchar형식의 단일 행, 단일 열 행 집합으로 읽어 오라고 지정할 수 있습니다.
다음은 AdventureWorks 샘플 데이터베이스의 ProductPhoto 테이블에 사진을 삽입하는 예제입니다.
BULK OPENROWSET 공급자를 사용하는 경우 모든 열에 값을 삽입하지 않더라도 명명된 열 목록을 제공해야 합니다. 이 경우 기본 키는 ID 열로 정의되며 열 목록에서 생략할 수 있습니다. 또한 OPENROWSET 문의 끝에 상관 관계 이름을 제공해야 합니다. 이 경우 ThumbnailPhoto입니다. 이는 파일이 로드되는 ProductPhoto 테이블의 열과 상관 관계가 있습니다.
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
UPDATE .WRITE를 사용하여 데이터 업데이트하기
Transact-SQL UPDATE 문에는 varchar(max), nvarchar(max)또는 varbinary(max) 열의 내용을 수정하기 위한 새 WRITE 구문이 있습니다. 이렇게 하면 데이터의 부분 업데이트를 수행할 수 있습니다. UPDATE . 쓰기 구문은 다음과 같이 축약된 형식으로 표시됩니다.
업데이트
{ <개체> }
설정
{ column_name = { . WRITE ( 식, @Offset, @Length ) }
WRITE 메서드는 column_name 값의 섹션이 수정되도록 지정합니다. 식은 column_name로 복사될 값입니다. @Offset는 식이 작성될 시작점이고, @Length 인수는 열의 섹션 길이입니다.
| 만약 | 그때 |
|---|---|
| 표현식이 NULL로 설정됩니다. |
@Length은 무시되며, 지정된 에서 @Offset의 값이 잘립니다. |
@Offset은 NULL입니다. |
업데이트 작업은 기존 column_name 값의 끝에 식을 추가하고 @Length 무시됩니다. |
@Offset는 "column_name" 값의 길이보다 큽니다. |
SQL Server에서 오류를 반환합니다. |
@Length은 NULL입니다. |
업데이트 작업은 @Offset 모든 데이터를 column_name 값의 끝까지 제거합니다. |
비고
@Offset 또는 @Length 모두 음수가 될 수 없습니다.
예시
이 Transact-SQL 예제에서는 AdventureWorks 데이터베이스의 Document 테이블에 있는 nvarchar(max) 열인 DocumentSummary의 부분 값을 업데이트합니다. 'components'라는 단어는 대체 단어, 기존 데이터에서 바꿀 단어의 시작 위치(오프셋) 및 바꿀 문자 수(길이)를 지정하여 'features'라는 단어로 바뀝니다. 이 예제에는 결과를 비교하기 위해 UPDATE 문 앞과 뒤에 SELECT 문이 포함되어 있습니다.
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.
ADO.NET에서 Large-Value 형식과 작업하기
ADO.NET에서 큰 데이터 형식을 사용할 때, 이를 SqlParameter의 SqlDataReader 객체로 지정하여 결과 집합을 반환하거나, SqlDataAdapter를 사용하여 DataSet/DataTable를 채울 수 있습니다. 큰 값 형식으로 작업하는 방식과 관련된 더 작은 값 데이터 형식 간에는 차이가 없습니다.
GetSqlBytes를 사용하여 데이터 검색
GetSqlBytes
SqlDataReader 메서드를 사용하여 varbinary(max) 열의 내용을 검색할 수 있습니다. 다음 코드 조각은 테이블에서 SqlCommand 데이터를 선택하는 cmd 개체 varbinary(max)과 데이터를 SqlDataReader로 검색하는 reader 개체 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);
}
GetSqlChars를 사용하여 데이터 검색
GetSqlChars
SqlDataReader 메서드를 사용하여 varchar(max) 또는 nvarchar(max) 열의 내용을 검색할 수 있습니다. 다음 코드 조각에서는 테이블에서 SqlCommand 데이터를 선택하는 cmd 객체 nvarchar(max)과 데이터를 검색하는 SqlDataReader 객체 reader를 가정합니다.
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);
}
GetSqlBinary를 사용하여 데이터 검색
GetSqlBinary의 SqlDataReader 메서드를 사용하여 varbinary(max) 열의 내용을 가져올 수 있습니다. 다음 코드 조각에서는 테이블에서 SqlCommand 데이터를 선택하는 cmdvarbinary(max) 개체와 데이터를 SqlDataReader 스트림으로 검색하는 readerSqlBinary 개체를 가정합니다.
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);
}
GetBytes를 사용하여 데이터 검색
GetBytes
SqlDataReader 메서드는 지정된 열 오프셋에서 바이트 스트림을 지정된 배열 오프셋에서 시작하는 바이트 배열로 읽어들입니다. 다음 코드 조각에서는 바이트를 바이트 배열로 검색하는 SqlDataReader 명명된 reader 개체를 가정합니다.
GetSqlBytes와 달리, GetBytes은 배열 버퍼의 크기가 필요합니다.
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);
}
GetValue를 사용하여 데이터 검색
GetValue
SqlDataReader 메서드는 지정된 열 오프셋의 값을 배열에 저장합니다. 다음 코드 조각에서는 첫 번째 열 오프셋에서 이진 데이터를 검색하고, 두 번째 열 오프셋에서 문자열 데이터를 검색하는 SqlDataReader이라는 reader 개체를 가정합니다.
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);
}
큰 값 형식에서 CLR 형식으로 변환
varchar(max)같은 문자열 변환 메서드를 사용하여 nvarchar(max) 또는 ToString 열의 내용을 변환할 수 있습니다. 다음 코드 조각에서는 SqlDataReader이라는 이름의 reader 개체가 데이터를 검색한다고 가정합니다.
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);
}
예시
다음 코드는 LargePhoto 데이터베이스의 ProductPhoto 테이블에서 이름과 AdventureWorks 개체를 검색하여 파일에 저장합니다.
System.Drawing 네임스페이스에 대한 참조를 사용하여 어셈블리를 컴파일해야 합니다.
GetSqlBytes의 SqlDataReader 메서드는 SqlBytes 속성을 노출하는 Stream 객체를 반환합니다. 코드는 이를 사용하여 새 Bitmap 개체를 만든 다음 Gif ImageFormat저장합니다.
static void TestGetSqlBytes(int documentID, string filePath)
{
// Assumes GetConnectionString returns a valid connection string.
using (SqlConnection connection =
new(GetConnectionString()))
{
SqlCommand command = connection.CreateCommand();
SqlDataReader reader = default!;
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("@ProductPhotoID", SqlDbType.Int)
{
Value = documentID
};
command.Parameters.Add(paramID);
connection.Open();
string photoName = default!;
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($"{photoName} is unavailable.");
}
else
{
SqlBytes bytes = reader.GetSqlBytes(1);
using (Bitmap productImage = new(bytes.Stream))
{
var fileName = filePath + photoName;
// Save in gif format.
productImage.Save(fileName, ImageFormat.Gif);
Console.WriteLine($"Successfully created {fileName}.");
}
}
}
}
else
{
Console.WriteLine("No records returned.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
reader?.Dispose();
}
}
}
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 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
큰 값 형식 매개 변수 사용
큰 값 형식은 SqlParameter 개체에서 더 작은 값 형식을 사용하는 것과 같은 방식으로 SqlParameter 개체에서 사용할 수 있습니다. 다음 예제와 같이 큰 값 형식을 SqlParameter 값으로 검색할 수 있습니다. 이 코드는 AdventureWorks 샘플 데이터베이스에 다음 GetDocumentSummary 저장 프로시저가 있다고 가정합니다. 저장 프로시저는 @DocumentID 입력 매개 변수를 사용하고 @DocumentSummary 출력 매개 변수에서 DocumentSummary 열의 내용을 반환합니다.
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
예시
ADO.NET 코드는 SqlConnection 및 SqlCommand 개체를 만들어 GetDocumentSummary 저장 프로시저를 실행하고 큰 값 형식으로 저장된 문서 요약을 검색합니다. 이 코드는 @DocumentID 입력 매개 변수의 값을 전달하고 콘솔 창의 @DocumentSummary 출력 매개 변수에 다시 전달된 결과를 표시합니다.
static string? GetDocumentSummary(int documentID)
{
// Assumes GetConnectionString returns a valid connection string.
using (SqlConnection connection =
new(GetConnectionString()))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
try
{
// Set up 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("@DocumentID", SqlDbType.Int)
{
Value = documentID
};
command.Parameters.Add(paramID);
// Set up the output parameter to retrieve the summary.
SqlParameter paramSummary =
new("@DocumentSummary",
SqlDbType.NVarChar, -1)
{
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;
}
}
}
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
참고하십시오
- SQL Server 이진 데이터 Large-Value
- SQL Server 데이터 형식 매핑
- ADO.NET에서의 SQL Server 데이터 작업
- ADO.NET 개요