FILESTREAM 데이터용 클라이언트 응용 프로그램 만들기Create Client Applications for FILESTREAM Data

이 항목 적용 대상: 예SQL Server없습니다Azure SQL 데이터베이스없습니다Azure SQL 데이터 웨어하우스 없습니다 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse Win32 API를 사용하여 FILESTREAM BLOB의 데이터를 읽고 쓸 수 있습니다. You can use Win32 APIs to read and write data to a FILESTREAM BLOB. 다음 단계가 필요합니다.The following steps are required:

  • FILESTREAM 파일 경로를 읽습니다.Read the FILESTREAM file path.

  • 현재 트랜잭션 컨텍스트를 읽습니다.Read the current transaction context.

  • Win32 핸들을 가져오고 이 핸들을 사용하여 FILESTREAM BLOB의 데이터를 읽고 씁니다.Obtain a Win32 handle and use the handle to read and write data to the FILESTREAM BLOB.

참고

이 항목의 예에서는 FILESTREAM 사용 데이터베이스 만들기FILESTREAM 데이터 저장용 테이블 만들기에서 만든 FILESTREAM 사용 데이터베이스 및 테이블이 필요합니다.The examples in this topic require the FILESTREAM-enabled database and table that are created in Create a FILESTREAM-Enabled Database and Create a Table for Storing FILESTREAM Data.

FILESTREAM 데이터 작업을 위한 함수Functions for Working with FILESTREAM Data

FILESTREAM을 사용하여 BLOB(Binary Large Object) 데이터를 저장하면 Win32 API를 사용하여 파일을 작업할 수 있습니다.When you use FILESTREAM to store binary large object (BLOB) data, you can use Win32 APIs to work with the files. Win32 응용 프로그램에서의 FILESTREAM BLOB 데이터 작업을 지원하기 위해 SQL ServerSQL Server 에서는 다음 기능과 API를 제공합니다.To support working with FILESTREAM BLOB data in Win32 applications, SQL ServerSQL Server provides the following functions and API:

  • PathName 은 BLOB에 대한 토큰으로 경로를 반환합니다.PathName returns a path as a token to a BLOB. 응용 프로그램은 이 토큰을 사용하여 Win32 핸들을 얻고 BLOB 데이터에 대한 작업을 수행합니다.An application uses this token to obtain a Win32 handle and operate on BLOB data.

    FILESTREAM 데이터가 포함된 데이터베이스가 Always On 가용성 그룹에 속하는 경우 PathName 함수가 컴퓨터 이름 대신 VNN(가상 네트워크 이름)을 반환합니다.When the database that contains FILESTREAM data belongs to an Always On availability group, then the PathName function returns a virtual network name (VNN) instead of a computer name.

  • GET_FILESTREAM_TRANSACTION_CONTEXT() 는 세션의 현재 트랜잭션을 나타내는 토큰을 반환합니다.GET_FILESTREAM_TRANSACTION_CONTEXT() returns a token that represents the current transaction of a session. 응용 프로그램은 이 토큰을 사용하여 FILESTREAM 파일 시스템 스트리밍 작업을 트랜잭션에 바인딩합니다.An application uses this token to bind FILESTREAM file system streaming operations to the transaction.

  • OpenSqlFilestream API 는 Win32 파일 핸들을 얻습니다.The OpenSqlFilestream API obtains a Win32 file handle. 응용 프로그램에서는 이 핸들을 사용하여 FILESTREAM 데이터를 스트리밍한 후 ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile또는 FlushFileBuffers같은 Win32 API 함수에 핸들을 전달합니다.The application uses the handle to stream the FILESTREAM data, and can then pass the handle to the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers. 응용 프로그램이 핸들을 사용하여 다른 API를 호출할 경우 ERROR_ACCESS_DENIED 오류가 반환됩니다.If the application calls any other API by using the handle, an ERROR_ACCESS_DENIED error is returned. 응용 프로그램에서는 CloseHandle을 사용하여 핸들을 종료해야 합니다.The application should close the handle by using CloseHandle.

    모든 FILESTREAM 데이터 컨테이너 액세스는 SQL ServerSQL Server 트랜잭션에서 수행됩니다.All FILESTREAM data container access is performed in a SQL ServerSQL Server transaction. Transact-SQLTransact-SQL 문을 실행하여 SQL 데이터 및 FILESTREAM 데이터 간에 일관성을 유지할 수 있습니다. statements can be executed in the same transaction to maintain consistency between SQL data and FILESTREAM data.

FILESTREAM 데이터에 액세스하는 단계Steps for Accessing FILESTREAM Data

FILESTREAM 파일 경로 읽기Reading the FILESTREAM File Path

FILESTREAM 테이블의 각 셀에는 해당 셀과 연결된 파일 경로가 있습니다.Each cell in a FILESTREAM table has a file path that is associated with it. 이러한 경로를 읽으려면 문에 있는 varbinary(max) 열의 PathName Transact-SQLTransact-SQL 속성을 사용합니다.To read the path, use the PathName property of a varbinary(max) column in a Transact-SQLTransact-SQL statement. 다음 예에서는 varbinary(max) 열의 파일 경로를 읽는 방법을 보여 줍니다.The following example shows how to read the file path of a varbinary(max) column.

DECLARE @filePath varchar(max)

SELECT @filePath = Chart.PathName()
FROM Archive.dbo.Records
WHERE SerialNumber = 3

PRINT @filepath

트랜잭션 컨텍스트 읽기Reading the Transaction Context

현재 트랜잭션 컨텍스트를 가져오려면 Transact-SQLTransact-SQL GET_FILESTREAM_TRANSACTION_CONTEXT() 함수를 사용합니다.To obtain the current transaction context, use the Transact-SQLTransact-SQL GET_FILESTREAM_TRANSACTION_CONTEXT() function. 다음 예에서는 트랜잭션을 시작하고 현재 트랜잭션 컨텍스트를 읽는 방법을 보여 줍니다.The following example shows how to begin a transaction and read the current transaction context.

DECLARE @txContext varbinary(max)

BEGIN TRANSACTION
SELECT @txContext = GET_FILESTREAM_TRANSACTION_CONTEXT()
PRINT @txContext
COMMIT

Win32 파일 핸들 가져오기Obtaining a Win32 File Handle

Win32 파일 핸들을 가져오려면 OpenSqlFilestream API를 호출합니다.To obtain a Win32 file handle, call the OpenSqlFilestream API. 이 API는 sqlncli.dll 파일에서 내보내집니다.This API is exported from the sqlncli.dll file. 다음 Win32 API 중 하나로 반환된 핸들이 전달될 수 있습니다. ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile또는 FlushFileBuffers.The returned handle can be passed to any of the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers. 다음 예에서는 Win32 파일 핸들을 가져오고 이를 사용하여 FILESTREAM BLOB의 데이터를 읽고 쓰는 방법을 보여 줍니다.The following examples show you how to obtain a Win32 File handle and use it to read and write data to a FILESTREAM BLOB.

using System.IO;
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace FILESTREAM
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection sqlConnection = new SqlConnection(
                "Integrated Security=true;server=(local)");

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;

            try
            {
                sqlConnection.Open();

                //The first task is to retrieve the file path
                //of the SQL FILESTREAM BLOB that we want to
                //access in the application.

                sqlCommand.CommandText =
                      "SELECT Chart.PathName()"
                    + " FROM Archive.dbo.Records"
                    + " WHERE SerialNumber = 3";

                String filePath = null;

                Object pathObj = sqlCommand.ExecuteScalar();
                if (DBNull.Value != pathObj)
                    filePath = (string)pathObj;
                else
                {
                    throw new System.Exception(
                        "Chart.PathName() failed"
                      + " to read the path name "
                      + " for the Chart column.");
                }

                //The next task is to obtain a transaction
                //context. All FILESTREAM BLOB operations
                //occur within a transaction context to
                //maintain data consistency.

                //All SQL FILESTREAM BLOB access must occur in 
                //a transaction. MARS-enabled connections
                //have specific rules for batch scoped transactions,
                //which the Transact-SQL BEGIN TRANSACTION statement
                //violates. To avoid this issue, client applications 
                //should use appropriate API facilities for transaction management, 
                //management, such as the SqlTransaction class.

                SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranaction");
                sqlCommand.Transaction = transaction;

                sqlCommand.CommandText =
                    "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

                Object obj = sqlCommand.ExecuteScalar();
                byte[] txContext = (byte[])obj;

                //The next step is to obtain a handle that
                //can be passed to the Win32 FILE APIs.

                SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite);

                byte[] buffer = new byte[512];

                int numBytes = 0;

                //Write the string, "EKG data." to the FILESTREAM BLOB.
                //In your application this string would be replaced with
                //the binary data that you want to write.

                string someData = "EKG data.";
                Encoding unicode = Encoding.GetEncoding(0);

                sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),
                    0,
                    someData.Length);

                //Read the data from the FILESTREAM
                //BLOB.

                sqlFileStream.Seek(0L, SeekOrigin.Begin);

                numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);

                string readData = unicode.GetString(buffer);

                if (numBytes != 0)
                    Console.WriteLine(readData);

                //Because reading and writing are finished, FILESTREAM 
                //must be closed. This closes the c# FileStream class, 
                //but does not necessarily close the the underlying 
                //FILESTREAM handle. 
                sqlFileStream.Close();

                //The final step is to commit or roll back the read and write
                //operations that were performed on the FILESTREAM BLOB.

                sqlCommand.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
            return;
        }
    }
}
Imports System.IO
Imports System 
Imports System.Collections.Generic 
Imports System.Text 
Imports System.Data 
Imports System.Data.SqlClient 
Imports System.Data.SqlTypes 

Module Module1
    Public Sub Main(ByVal args As String())
        '        Dim sqlConnection As New SqlConnection("Integrated Security=true;server=(local)")
        Dim sqlConnection As New SqlConnection("Integrated Security=true;server=kellyreyue\MSSQL1")

        Dim sqlCommand As New SqlCommand()
        sqlCommand.Connection = sqlConnection

        Try
            sqlConnection.Open()

            'The first task is to retrieve the file path 
            'of the SQL FILESTREAM BLOB that we want to 
            'access in the application. 

            sqlCommand.CommandText = "SELECT Chart.PathName()" + " FROM Archive.dbo.Records" + " WHERE SerialNumber = 3"

            Dim filePath As String = Nothing

            Dim pathObj As Object = sqlCommand.ExecuteScalar()
            If Not pathObj.Equals(DBNull.Value) Then
                filePath = DirectCast(pathObj, String)
            Else
                Throw New System.Exception("Chart.PathName() failed" + " to read the path name " + " for the Chart column.")
            End If

            'The next task is to obtain a transaction 
            'context. All FILESTREAM BLOB operations 
            'occur within a transaction context to 
            'maintain data consistency. 

            'All SQL FILESTREAM BLOB access must occur in 
            'a transaction. MARS-enabled connections 
            'have specific rules for batch scoped transactions, 
            'which the Transact-SQL BEGIN TRANSACTION statement 
            'violates. To avoid this issue, client applications 
            'should use appropriate API facilities for transaction management, 
            'management, such as the SqlTransaction class. 

            Dim transaction As SqlTransaction = sqlConnection.BeginTransaction("mainTranaction")
            sqlCommand.Transaction = transaction

            sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"

            Dim obj As Object = sqlCommand.ExecuteScalar()
            Dim txContext As Byte() = Nothing

            Dim contextLength As UInteger

            If Not obj.Equals(DBNull.Value) Then
                txContext = DirectCast(obj, Byte())
                contextLength = txContext.Length()
            Else
                Dim message As String = "GET_FILESTREAM_TRANSACTION_CONTEXT() failed"
                Throw New System.Exception(message)
            End If

            'The next step is to obtain a handle that 
            'can be passed to the Win32 FILE APIs. 

            Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.ReadWrite)

            Dim buffer As Byte() = New Byte(511) {}

            Dim numBytes As Integer = 0

            'Write the string, "EKG data." to the FILESTREAM BLOB. 
            'In your application this string would be replaced with 
            'the binary data that you want to write. 

            Dim someData As String = "EKG data."
            Dim unicode As Encoding = Encoding.GetEncoding(0)

            sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()), 0, someData.Length)

            'Read the data from the FILESTREAM 
            'BLOB. 

            sqlFileStream.Seek(0, SeekOrigin.Begin)

            numBytes = sqlFileStream.Read(buffer, 0, buffer.Length)

            Dim readData As String = unicode.GetString(buffer)

            If numBytes <> 0 Then
                Console.WriteLine(readData)
            End If

            'Because reading and writing are finished, FILESTREAM 
            'must be closed. This closes the c# FileStream class, 
            'but does not necessarily close the the underlying 
            'FILESTREAM handle. 
            sqlFileStream.Close()

            'The final step is to commit or roll back the read and write 
            'operations that were performed on the FILESTREAM BLOB. 

            sqlCommand.Transaction.Commit()
        Catch ex As System.Exception
            Console.WriteLine(ex.ToString())
        Finally
            sqlConnection.Close()
        End Try
        Return
    End Sub
End Module
#include <windows.h>
#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>
#include <stdio.h>
#include <sqlncli.h>

#define COPYBUFFERSIZE 4096

/// <summary>
///This class iterates though the ODBC error queue and prints all of the
///accumulated error messages to the console.
/// </summary>

class ODBCErrors
{
private:
    int         m_iLine;    //Source code line on which the error occurred
    SQLSMALLINT m_type;     //Type of handle on which the error occurred
    SQLHANDLE   m_handle;   //ODBC handle on which the error occurred

public:
    /// <summary>
    ///Default constructor for the ODBCErrors class
    ///</summary>

    ODBCErrors()
    {
        m_iLine  = -1;
        m_type   = 0;
        m_handle = SQL_NULL_HANDLE;
    }

    /// <summary>
    ///Constructor for the ODBCErrors class
    /// </summary>
    /// <param name="iLine">
    /// This parameter is the source code line
    /// at which the error occurred.
    ///</param>
    /// <param name="type">
    /// This parameter is the type of ODBC handle passed in
    /// the next parameter.
    ///</param>
    /// <param name="handle">
    /// This parameter is the handle on which the error occurred.
    ///</param>

    ODBCErrors(int iLine, SQLSMALLINT type, SQLHANDLE handle)
    {
        m_iLine  = iLine;
        m_type   = type;
        m_handle = handle;
    }

    ///<summary>
    /// This method iterates though the error stack for the handle passed
    /// into the constructor and displays those errors on the console.
    ///</summary>

    void Print()
    {
        SQLSMALLINT i = 0, len = 0;
        SQLINTEGER  native;
        SQLTCHAR    state[9], text[256];
        SQLRETURN   sqlReturn = SQL_SUCCESS;

        if ( m_handle == SQL_NULL_HANDLE )
        {
            wprintf_s(TEXT("The error handle is not a valid handle.\n"), m_iLine);
            return;
        }

        wprintf_s(TEXT("Error Line(%d)\n"), m_iLine);

        while( sqlReturn == SQL_SUCCESS )
        {
            len = 0;

            sqlReturn = SQLGetDiagRec(
                m_type,
                m_handle,
                ++i,
                state,
                &native,
                text,
                sizeof(text)/sizeof(SQLTCHAR),
                &len);

            if ( SQL_SUCCEEDED(sqlReturn) )
                wprintf_s(TEXT("Error(%d, %ld, %s) : %s\n"), i, native, state, text);
        }
    }
};


BOOL CopyFileToSQL(LPTSTR srcFilePath, LPTSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken)
{
	BOOL bRetCode = FALSE;

	HANDLE srcHandle = INVALID_HANDLE_VALUE;
	HANDLE dstHandle = INVALID_HANDLE_VALUE;
    BYTE   buffer[COPYBUFFERSIZE] = { 0 };

    TCHAR *szErrMsgSrc   = TEXT("Error opening source file.");
    TCHAR *szErrMsgDst   = TEXT("Error opening destFile file.");
    TCHAR *szErrMsgRead  = TEXT("Error reading source file.");
    TCHAR *szErrMsgWrite = TEXT("Error writing SQL file.");

    try
    {
	    if ( (srcHandle = CreateFile(
            srcFilePath,
            GENERIC_READ,
            FILE_SHARE_READ,
            NULL,
            OPEN_EXISTING,
            FILE_FLAG_SEQUENTIAL_SCAN,
            NULL)) == INVALID_HANDLE_VALUE )
            throw szErrMsgSrc;

    	if ( (dstHandle =  OpenSqlFilestream(
            dstFilePath,
            Write,
            0,
            transactionToken,
            cbTransactionToken,
            0)) == INVALID_HANDLE_VALUE)
            throw szErrMsgDst;

        DWORD bytesRead = 0;
        DWORD bytesWritten = 0;

    	do
        {
            if ( ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead, NULL) == 0 )
                throw szErrMsgRead;

    		if (bytesRead > 0)
            {
        		if ( WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL) == 0 )
                    throw szErrMsgWrite;
            }
		} while (bytesRead > 0);

        bRetCode = TRUE;
	}
    catch( TCHAR *szErrMsg )
    {
        wprintf_s(szErrMsg);
        bRetCode = FALSE;
    }

    if ( srcHandle != INVALID_HANDLE_VALUE )
        CloseHandle(srcHandle);

    if ( dstHandle != INVALID_HANDLE_VALUE )
    	CloseHandle(dstHandle);

    return bRetCode;
}

void main()
{
    TCHAR *sqlDBQuery =
       TEXT("INSERT INTO Archive.dbo.Records(Id, SerialNumber, Chart)")
       TEXT(" OUTPUT GET_FILESTREAM_TRANSACTION_CONTEXT(), inserted.Chart.PathName()")
       TEXT("VALUES (newid (), 5, CONVERT(VARBINARY, '**Temp**'))");

	SQLCHAR transactionToken[32];
    
    SQLHANDLE henv = SQL_NULL_HANDLE;
    SQLHANDLE hdbc              = SQL_NULL_HANDLE;
    SQLHANDLE hstmt             = SQL_NULL_HANDLE;

    try
    {
        //These statements Initialize ODBC for the client application and
        //connect to the database.

        if ( SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

        if ( SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3, NULL) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

        if ( SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

	    //This code assumes that the dataset name "Sql Server FILESTREAM"
	    //has been previously created on the client computer system. An
        //ODBC DSN is created with the ODBC Data Source item in
        //the Windows Control Panel.

	    if ( SQLConnect(hdbc, TEXT("Sql Server FILESTREAM"),
                SQL_NTS, NULL, 0, NULL, 0) <= 0 )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        //FILESTREAM requires that all read and write operations occur
        //within a transaction.
        if ( SQLSetConnectAttr(hdbc,
            SQL_ATTR_AUTOCOMMIT,
            (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
            SQL_IS_UINTEGER) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        if ( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        if ( SQLExecDirect(hstmt, sqlDBQuery, SQL_NTS) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        //Retrieve the transaction token.
        if ( SQLFetch(hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        SQLINTEGER cbTransactionToken = sizeof(transactionToken);

    	if ( SQLGetData(hstmt, 1,
            SQL_C_BINARY,
            transactionToken,
            sizeof(transactionToken),
            &cbTransactionToken) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        //Retrieve the file path for the inserted record.

        TCHAR dstFilePath[1024];
        SQLINTEGER cbDstFilePath;

        if ( SQLGetData(hstmt, 2, SQL_C_TCHAR, dstFilePath, sizeof(dstFilePath), &cbDstFilePath) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

    	if ( SQLCloseCursor(hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        SQLUSMALLINT mode = SQL_ROLLBACK;

        if ( CopyFileToSQL(
            TEXT("C:\\Users\\Data\\chart1.jpg"),
            dstFilePath,
            transactionToken,
            cbTransactionToken) == TRUE )
            mode = SQL_COMMIT;

        SQLTransact(henv, hdbc, mode);
    }
    catch(ODBCErrors *pErrors)
    {
        pErrors->Print();
        delete pErrors;
    }

    if ( hstmt != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    if ( hdbc != SQL_NULL_HANDLE )
        SQLDisconnect(hdbc);

    if ( hdbc != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 

    if ( henv != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

응용 프로그램 디자인 및 구현을 위한 최상의 방법Best Practices for Application Design and Implementation

  • FILESTREAM이 사용된 응용 프로그램을 디자인하고 구현할 때는 다음 지침을 고려하십시오.When you are designing and implementing applications that use FILESTREAM, consider the following guidelines:

  • 초기화되지 않은 FILESTREAM 열을 나타내는 데 0x 대신 NULL을 사용합니다.Use NULL instead of 0x to represent a non-initialized FILESTREAM column. 0x 값을 사용하면 파일이 생성되고, NULL을 사용했을 때는 그렇지 않습니다.The 0x value causes a file to be created, and NULL does not.

  • null이 아닌 FILESTREAM 열이 포함된 테이블에 삽입 및 삭제 작업을 사용하지 마십시오.Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. 삽입 및 삭제 작업은 가비지 수집에 사용되는 FILESTREAM 테이블을 수정할 수 있습니다.Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. 그러면 시간이 지남에 따라 응용 프로그램 성능이 저하됩니다.This can cause an application's performance to decrease over time.

  • 복제가 사용되는 응용 프로그램에 NEWID() 대신 NEWSEQUENTIALID()를 사용합니다.In applications that use replication, use NEWSEQUENTIALID() instead of NEWID(). NEWSEQUENTIALID()는 이런 응용 프로그램에서 GUID를 생성하는 성능이 NEWID()보다 우수합니다.NEWSEQUENTIALID() performs better than NEWID() for GUID generation in these applications.

  • FILESTREAM API는 데이터에 대한 Win32 스트리밍 액세스를 위해 디자인되었습니다.The FILESTREAM API is designed for Win32 streaming access to data. 2MB가 넘는 FILESTREAM BLOB(Binary Large Object)을 읽거나 쓰는 데 Transact-SQLTransact-SQL 을 사용하지 마세요.Avoid using Transact-SQLTransact-SQL to read or write FILESTREAM binary large objects (BLOBs) that are larger than 2 MB. Transact-SQLTransact-SQL에서 BLOB 데이터를 읽거나 써야 하는 경우에는 Win32에서 FILESTREAM BLOB을 열기 전에 먼저 모든 BLOB 데이터가 사용되었는지 확인합니다.If you must read or write BLOB data from Transact-SQLTransact-SQL, make sure that all BLOB data is consumed before you try to open the FILESTREAM BLOB from Win32. 일부 Transact-SQLTransact-SQL 데이터가 사용되지 않은 경우 후속 FILESTREAM 열기 또는 닫기 작업이 실패할 수 있습니다.Failure to consume all the Transact-SQLTransact-SQL data might cause any successive FILESTREAM open or close operations to fail.

  • FILESTREAM BLOB에 데이터를 업데이트하거나 추가하는 Transact-SQLTransact-SQL 문을 사용하지 마십시오.Avoid Transact-SQLTransact-SQL statements that update, append or prepend data to the FILESTREAM BLOB. 이러한 문을 사용하면 BLOB 데이터가 tempdb 데이터베이스에 스풀링된 후 새 물리적 파일에 스풀링됩니다.This causes the BLOB data to be spooled into the tempdb database and then back into a new physical file.

  • 소규모 BLOB 업데이트를 FILESTREAM BLOB에 추가하지 마십시오.Avoid appending small BLOB updates to a FILESTREAM BLOB. 추가할 때마다 기본 FILESTREAM 파일이 복사됩니다.Each append causes the underlying FILESTREAM files to be copied. 응용 프로그램이 소규모 BLOB을 추가해야 하는 경우에는 BLOB을 varbinary(max) 열에 쓴 후 BLOB 개수가 미리 지정된 한도에 도달하면 FILESTREAM BLOB에 대한 단일 쓰기 작업을 수행합니다.If an application has to append small BLOBs, write the BLOBs into a varbinary(max) column, and then perform a single write operation to the FILESTREAM BLOB when the number of BLOBs reaches a predetermined limit.

  • 응용 프로그램에서 다수의 BLOB 파일의 데이터 길이를 검색하지 마십시오.Avoid retrieving the data length of lots of BLOB files in an application. 데이터 크기는 SQL Server 데이터베이스 엔진SQL Server Database Engine에 저장되지 않으므로 이 작업에는 시간이 많이 소요됩니다.This is a time-consuming operation because the size is not stored in the SQL Server 데이터베이스 엔진SQL Server Database Engine. BLOB 파일의 길이를 확인해야 한다면 BLOB이 닫혀 있는 경우 Transact-SQLTransact-SQL DATALENGTH() 함수를 사용하여 그 크기를 검토하세요.If you must determine the length of a BLOB file, use the Transact-SQLTransact-SQL DATALENGTH() function to determine the size of the BLOB if it is closed. DATALENGTH()는 BLOB 파일 크기를 확인하기 위해 파일을 열지 않습니다.DATALENGTH() does not open the BLOB file to determine its size.

  • 응용 프로그램이 SMB1(메시지 블록 1) 프로토콜을 사용하는 경우 성능 최대화를 위해 FILESTREAM BLOB 데이터를 60KB의 배수로 읽어야 합니다.If an application uses Message Block1 (SMB1) protocol, FILESTREAM BLOB data should be read in 60-KB multiples to optimize performance.

참고 항목See Also

FILESTREAM 응용 프로그램에서 데이터베이스 작업과의 충돌 방지 Avoid Conflicts with Database Operations in FILESTREAM Applications
OpenSqlFilestream을 사용하여 FILESTREAM 데이터 액세스 Access FILESTREAM Data with OpenSqlFilestream
Blob(Binary Large Object) 데이터(SQL Server) Binary Large Object (Blob) Data (SQL Server)
FILESTREAM 데이터 부분 업데이트Make Partial Updates to FILESTREAM Data