Configuring File Tables

Configuring File Tables

     FileTables are a special type of table that enables you to store files and documents within SQLServer 2012. These files and documents can be accessed from Windows applications as though they were stored normally in the file system. For example, you can add files and folders

to the FileTable by dragging and dropping them in Windows Explorer. You can remove them from the FileTable by using the same method.

    A FileTable provides the following functionality:

■■ A FileTable provides a hierarchy of files and directories.

■■ Each row in a FileTable represents a file or directory.

■■ Each row holds the following items:

■A FILESTREAM column for stream data and a file_id (GUIDE) identifier.

■ Path_locator and parent_path_locator columns. These represent the file and directory

hierarchy.

■Ten file attributes. These include creation data and modification date.

■ Type column that supports full-text and semantic search.

■You can update FileTables by using normal Transact-SQL queries.

To enable FileTables, perform the following steps:

1. Enable FILESTREAM at the instance level. You can do this with the following query:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

2.Enable Non-Transnational Access at the database level. You can do this when creating a new database by using the CREATE DATABASE statement and the FILESTREAM

NON_TRANSACTED_ACCESS option.

 For example:

CREATE DATABASE database_name

WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'dir_name')

You can do this for an existing database by using the ALTER DATABASE statement withSET FILESTREAM option. 

For example:

ALTER DATABASE database_name

SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name')

3. Specify a Directory for FileTables at the database level if you haven’t already done so

when configuring Non-Transactional Access. You can modify the directory name by using the ALTER DATABASE statement with the SET FILESTREAM option.

  

4.To create a FileTable by using SQL Server Management Studio, right-click the Tables node and choose New FileTable to open a new script window that contains a template Transact-SQL script that you can modify. You can also create a FileTable by using the CREATE TABLE statement with the AS FileTable option. For example, to create a new FileTable named Doc Store,

use the following query:

CREATE TABLE DocStore as FileTable

Go

           File Table tables have predefined and fixed schema, so it is not possible to add or change columns. It is possible to add custom indexes, triggers, and constraints to a File Table. Dropping a File Table also drops the directory and the sub directories that it contained.

Configuring FILESTREAM

Configuring FILESTREAM

                              FILESTREAM enables SQL Server–based applications to store unstructured data, such as

images and documents, on the host computer’s file system. To use FILESTREAM, you must create or modify a database to host a special type of filegroup, after which you can create or modify tables so that they can use the varbinary(max) column with the FILESTREAM attribute.

                    You should use FILESTREAM under the following conditions:

• Objects that you want to store are greater than 1 MB. The traditional varbinary(max) limit of 2 GB does not apply to BLOBs (binary large objects) stored in the file system.

• Fast read access is importantFor objects smaller than 1 MB, use the varbinary(max) BLOB data type. You can’t enable FILESTREAM if you are running a 32-bit version of SQL Server 2012 on a 64-bit operating system.

To enable FILESTREAM, perform the following steps:

1. Open SQL Server Configuration Manager from the Configuration Tools folder.

2. Edit the properties of the instance on which you want to enable FILESTREAM.

3. On the FILESTREAM tab, select Enable FILESTREAM For Transact-SQL Access. You can

also use this dialog box to enable FILESTREAM for file I/O streaming access and to

allow rem Server Management Studio, execute the following query:

EXEC sp_configure filestream_access_level, 2    RECONFIGURE

5. Restart the SQL Server Service related to the instance on which you are enabling

FILESTREAM by using SQL Server Configuration Manager.

6. Create a FILESTREAM filegroup for the database. For example, to create a FILESTREAM

filegroup named FileStreamFileGroup for the Litware2012 database, use the following

query:

USE master

GO

ALTER DATABASE Litware2012 ADD

FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM;

GO

7. Add FILESTREAM files to the FILESTREAM filegroup by specifying a folder location that

does not currently exist. For example, to create and associate the C:\FSTRM directory

with the FILESTREAM file named FileStrmFile in the FileStreamFileGroup FILESTREAM

filegroup for the Litware2012 database, use the following query:

USE master

GO

ALTER DATABASE Litware2012 ADD FILE (NAME = FileStrmFile,

FILENAME = 'C:\FSTRM'

TO FILEGROUP FileStreamFileGroup

Managing Full-Text Indexing

Managing Full-Text Indexing

             Full-text indexes store information about significant words and their location within the columns of a database table. In SQL Server 2012, the Full-Text Engine is part of the SQL Server

process rather than a separate service. Only one full-text index can be created per table or

indexed view. A full-text index can contain up to 1,024 columns.

To create a full-text index by using SQL Server Management Studio, perform the following steps:

1. Right-click the table on which you want to create a new full-text index and choose

Design.

2. From the Table Designer menu, click Full-Text Index to open the Full-Text Index dialog

box. Click Add. Configure the properties of the index.

You can use the CREATE FULLTEXT INDEX statement to create a full-text index on a table.

For example, to create a FULLTEXT index on the Production.ProductReview table in the

AdventureWorks2012 database by using the ReviewerName, EmailAddress, and Comments

columns in the existing unique key index PK_ProductReview_ProductReviewID while also creating

a FULLTEXT catalog called production_catalog, use the following statement:

USE AdventureWorks2012;

GO

CREATE FULLTEXT CATALOG production_catalog;

GO

CREATE FULLTEXT INDEX ON Production.ProductReview(ReviewerName, EmailAddress, Comments)

KEY INDEX PK_ProductReview_ProductReviewID

ON production_catalog;

GO

To delete a full-text index, right-click the table that hosts the full-text index, choose Full-

Text Index, and then select Delete Full-Text Index. You can also delete a full-text index by

using the DROP FULLTEXT INDEX statement. For example, to drop the index created in the

previous example, use the query:

DROP FULLTEXT INDEX ON Production.ProductReview

Using Data Compression

Using Data Compression

         Data compression has the drawback of increasing CPU usage because the data must be compressed and decompressed when being accessed. You cannot use data compression with system tables, and only the Enterprise and Developer editions of SQL Server 2012 support data compression.

You can configure data compression on the following:

■■ Clustered tables

■■ Heap tables (a heap is a table without a clustered index)

■■ Non-clustered indexes

■■ Indexed views

■■ Individual partitions of a partitioned table or index

THREE FORMS OF DATA COMPRESSION

1.Row-level compression.

2.Unicode compression.

3.Page-level compression.

Row-Level CompressionRow-level compression works by using more efficient storage formats for fixed-length data.Row-level compression uses the following strategies to save space:
■■ Storing fixed-length numeric data types and CHAR data types as though they were
variable-length data types■■ Not storing NULL or 0 values
■■ Reducing metadata required to store data
 You use the following syntax to compress a table by using row-level compression:
ALTER TABLE table Name REBUILD WITH (DATA_COMPRESSION=ROW)  You use the following syntax to configure an index with row-level compression:
ALTER INDEX index Name ON table Name REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

Unicode Compression

        Unicode compression enables the database engine to compress unicode values stored in

page or row compressed objects. You can use unicode compression with the fixed-length

nchar(n) and nvarchar(n) data types. Unicode compression is automatically used where appropriate when you enable row and page compression.

 Page-Level Compression

         Page-level compression compresses data by storing repeating values and common prefixes

only once and then making references to those values from other locations within the table.

When page compression is applied to a table, row compression techniques are also applied.

Page-level compression uses the following strategies:

■■ Row-level compression is applied to maximize the number of rows stored on a page.

■■ Column prefix compression is applied by replacing repeating data patterns with references.

This data is stored in the page header.

■■ Dictionary compression scans for repeating values and then stores this information in

the page header.

  You use the following general syntax to apply page-level compression:

ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE

     You use the following syntax to configure an index with page-level compression:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)

Estimating Compression

The best way to determine the benefits of compression on an object is to use the

sp_estimate_data_compression_savings stored procedure. The benefits of compression

depend on factors such as the uniqueness of data. The sp_estimate_data_compression_savings

stored procedure is available in the Enterprise edition of SQL Server 2012 only.

The syntax of the stored procedure is as follows:

sp_estimate_data_compression_savings[ @schema_name = ] 'schema_name', [ @object_name = ]

'object_name', [@index_id = ] index_id,[

To configure an estimate of the compression benefits of using Page compression on the

same table, execute the following Transact-SQL statement:

USE AdventureWorks2012;

GO

EXEC sp_estimate_data_compression_savings 'HumanResources', 'Employee', NULL, NULL,

'PAGE';

GO