FileStream: T-SQL vs Win32

Question

One of our teams creates a database (and application) that will use the FileStream SQL 2008 function to store documents. Consistent with MS best practices for filtering, the Win32 APIs are the preferred way to access FileStream data as well as use T-SQL.

However, using the Win32 API requires the use of built-in authentication to connect to the database. This is a concern for the team because they do not want to provide users with direct access to the database. They want the application to connect using the SQL username and password.

What are the pros and cons of using Win32 vs T-SQL to access stream data? Are there any factors that could make using T-SQL impossible?

Answer 1

The key difference between T-SQL and Win32 Filestream access is the way the data is sent to the client — using T-SQL to retrieve Filestream data means that the storage engine must open the file in NTFS, transfer the data back through the SQL engine and through TDS (the standard way to transfer data SQL) back to the client. When using Win32, the storage mechanism is still in the way during the open file operation, but once this is completed, data can be transferred directly from the file to the client using Win32 streaming, completely bypassing the SQL mechanism.

, , , . 60 ( ), 60 -1,2 T-SQL > 1,2 Win32. , , YMMV.

, SQL , . Filestream, T-SQL.

Answer 2

: , Windows. . , , . , . . , , - , , , Google , , . "" , . , , , - , , . . , "" .

, (.. , UPDATE), ole 'a href= "http://technet.microsoft.com/en-us/library/ms188676.aspx" rel= "nofollow noreferrer" > EXECUTE . .

T-SQL Win32, FILESTREAM Best Practices :

• API FILESTREAM Win32 . Transact-SQL FILESTREAM (BLOB), 2 . BLOB Transact-SQL, , BLOB , FILESTREAM BLOB Win32. Transact-SQL FILESTREAM .
• Transact-SQL, , FILESTREAM BLOB. BLOB tempdb , .