Improving Availability and enhancing Programmability - Microsoft SQL Server 2008 R2 Administration - page 244

5. The successful execution of the INSERT process will create a new subdirectory and
file, the sample filename will be 00000023-00000082-0007 under the folder D:\
ENTZ\FS\DBIASSQA\091665b3-a692-4e74-8ae0-42d7f7cb2737\d9da9467-
541b-4a4c-94e2-f7876f1647de .
6. As we can see, files created using FILESTREAM are accessed within the context
of TSQL and associated with Win32 APIs. Using the TSQL context, let us view the
contents of the dbo.MuzikStore table:
SELECT * from dbo.MuzikStore

7. Let us assume that our application uses OLEDB to query the SQL Server data and

collect the appropriate information about the file system file in order to stream using
the application. To obtain the default collation of the server instance, execute the
following TSQL statement:
SELECT SERVERPROPERTY('Collation')

8. In addition to the instance level collation, we also need to obtain information on the

database collation using the following TSQL statement:
SELECT DATABASEPROPERTYEX ( 'AdventureWorks 2008R2' , 'Collation'
)

9. Unless otherwise selected, the SQL Server setup offers the SQL_Latin1_General_

CP1_CI_AS collation as the default collation for server installations. To obtain the
actual settings that a collation is applied to, run the following TSQL statement:
SELECT description FROM sys.fn_helpcollations() WHERE name = 'SQL_
Latin1_General_CP1_CI_AS'

10. Now, there is a requirement to add extra information on the dbo.MuzikStore table
to store the filename in internationalization text. Use the following TSQL statement to
add a new column with matching collation:
ALTER TABLE dbo.MuzikStore
ADD ItalianFileName nvarchar(50) COLLATE Latin1_General_100_,
IndianFileName nvarchar(50) COLLATE Indic_General_100_

This completes the required steps to design a storage solution for unstructured data and new
collations recipe.

How it works...

The FILESTREAM feature and new collations setup can be managed during the SQL Server
setup or using SQL Server configuration manager or TSQL statements.

After we enable FILESTREAM during the setup or using SQL Server Configuration Manager, a
new share is created on the host server with the specified name. This share name is intended
only to allow or use a low-level streaming interaction between the SQL Server and authorized
application clients.