Problem

The FileTable feature of SQL Server 2012 is an enhancement to the FILESTREAM feature which was introduced in SQL Server 2008. In this tip we will take a look at how to use FileTable feature of SQL Server 2012.

Solution

A FileTable is a new user table which gets created within a FILESTREAM enabled database. Using the FileTable feature, organizations can now store files and documents within a special table in SQL Server and they will have the ability to access those files and documents from windows. When you use this feature it will appear to you as if the files and documents are residing on a file system rather than in SQL Server. However, in order to use the FileTable feature you need to enable the FILESTREAM feature on the instance of SQL Server 2012. Database administrators can define indexes, constraints and triggers; however the columns and system defined constrains cannot be altered or dropped. Also, in order to enable the FILESTREAM feature you need to be a member of the SYSADMIN or SERVERADMIN fixed server roles.

Steps to Setup

1. Execute the below mentioned TSQL code to enabling the XP_CMDSHELL feature on SQL Server 2012. Once XP_CMDSHELL feature is enabled it will create a folder on the C: drive to store the FILESTREAM data (note: you can use any drive, but I am using the C: drive for this example).

USE master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC xp_cmdshell 'IF NOT EXIST C:\DemoFileTable MKDIR C:\DemoFileTable';
GO

2. Create a database named DemoFileTable which uses the FILESTREAM feature for the purpose of the demo using the below mentioned TSQL code. In the below script you can see that we are specifying new options for the FILESTREAM clause i.e. “NON_TRANSACTED_ACCESS = FULL” and we have also provided the windows directory name “DemoFileTable” which we created in the previous step.

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DemoFileTable') BEGIN
ALTER DATABASE DemoFileTable SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE DemoFileTable;
END;
CREATE DATABASE DemoFileTable
WITH FILESTREAM
(
NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'DemoFileTable'
);
GO
/* Add a FileGroup that can be used for FILESTREAM */
ALTER DATABASE DemoFileTable
ADD FILEGROUP DemoFileTable_FG
CONTAINS FILESTREAM;
GO
/* Add the folder that needs to be used for the FILESTREAM filegroup. */
ALTER DATABASE DemoFileTable
ADD FILE
(
NAME= 'DemoFileTable_File',
FILENAME = 'C:\DemoFileTable\DemoFileTable_File'
)
TO FILEGROUP DemoFileTable_FG;
GO

3. Next will be to Create a FileTable within FILESTREAM enabled database. This can be done by executing the below mentioned TSQL script which will create a FileTable within the FILESTREAM enabled database. The name of the FileTable is DemoFileTable and you need to specify FILETABLE_DIRECTORY as DemoFileTableFiles and FILETABLE_COLLATE_FILENAME as database_default

USE DemoFileTable;
GO
/* Create a FileTable */
CREATE TABLE DemoFileTable AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'DemoFileTableFiles',
FILETABLE_COLLATE_FILENAME = database_default
);
GO

4. Once the FileTable is created successfully, in Object Explorer > Expand Databases > Expand DemoFileTable database > Expand Tables > Expand FileTables > Expand dbo.DemoFileTable > Expand Columns to view the structure of FileTable as shown below.



5. In the below snippet you can see the files which were created within the C:\DemoFileTable\DemoFileTable_File folder when the FILESTREAM enabled database is created along with the FileTable DemoFileTableFiles. The filestream.hdr is a very important system file which basically contains FILESTREAM header information. Database Administrators need to make sure that this file is not removed or modified as this will corrupt the FILESTREAM enabled database.



6. Once the FileTable is created successfully you can access the FileTable using Windows Explorer. The path to access the FileTable will be:
\\SERVERNAME\FILESTREAM_WINDOWS_SHARE_NAME\FILESTREAM_TABLE_NAME\FILETABLE_DIRECTORY\

Copying Documents and Files to the FileTable

Now that we have created a FILESTREAM enabled database and a FileTable the next step will be to copy the documents and files to the newly created FileTable in Windows Explorer. You can copy the files by dragging files or by using the Copy-and-Paste operation to the below mentioned location.

\\SERVERNAME\FILESTREAM_WINDOWS_SHARE_NAME\FILESTREAM_TABLE_NAME\FILETABLE_DIRECTORY\

In the below snippet you can see that I have copied MSSQLTIPS.gif logo to FileTable folder. To open the image file double click the MSSQLTips.gif file and it will open in Internet Explorer.



How to View Documents and Files Stored in FileTable Using SQL Server Management Studio

To view the files and documents stored in a FileTable execute the below mentioned TSQL code.

Use DemoFileTable;
GO
SELECT * FROM DemoFileTable;
GO



Finally disable the XP_CMDSHELL feature which was enabled for this demo by executing the below mentioned TSQL code.

USE master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO

Formatting numbers in an SSRS report is a common task. For example, you may want to format a number as currency or percentage.

You can select a format from the number page of the properties window.



You can let sql handle the formatting, so data in the result set is pre-formatted.

DECLARE @Sales MONEY = 32182000.85;

SELECT ‘$’
+ CONVERT(VARCHAR(32),@Sales,1);

Results:



Finally, you can use the newly introduced FORMAT() function in SQL Server 2012. Format() will, according to books online, return a value formatted with the specified format and optional culture. So, instead of converting and concatenating like we did in the previous example, FORMAT() can be used:

DECLARE @Sales MONEY = 32182000.85;

SELECT FORMAT(@Sales,‘c’,‘en-us’);

Results:



FORMAT() accepts the following parameters:

- Value. Actual value that needs to be formatted.
- Format. Value will be formatted to the specified format. Currency, percentage, and date are few examples.
- Optional Culture. Specifies the language. More about cultures on BOL.PARSE()

Consider the following query. Value is formatted to three different languages based on the culture:

Formatting Currency:

DECLARE @Sales MONEY = 32182000.85;

SELECT FORMAT(@Sales,‘c’,‘it-IT’) [Italy]
, FORMAT(@Sales,‘c’,‘fr’) [France]
, FORMAT(@Sales,‘c’,‘ru-RU’) [Russian];

Results:



Formatting percentages:

DECLARE @Per DECIMAL(2,2) = 0.72;

SELECT FORMAT(@Per,‘p0′,‘en-us’)
, FORMAT(@Per,‘p2′,‘en-us’);

Results:



Conclusion:

Similar formatting is ideally done in the presentation layer, reporting services for example. But I would want to let reporting services do minimal processing. FORMAT() simplifies string formatting. It provides functionality that most developers have always wanted.