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

Configuring Fill Factor [SQL SERVER 2012]

Configuring Fill Factor

        Fill factor determines the percentage of space on each leaf-level page that is filled with data when an index is created or rebuilt. The remaining space is used for future growth.

      Fill factor is configured as a percentage value between 1 and 100. The server-wide default is 0. Fill factor can be configured at the individual index level and at the server level. 

       You can set the instance-wide fill factor value by using the sp_configure stored procedure.
You can change the fill factor only on an instance-wide basis when the advanced options
value is set to 1. For example, to set the instance-wide fill factor configuration option to 90,
use the following code:

sp_configure 'show advanced options', 1;

GO                          

RECONFIGURE;

GO

sp_configure 'fill factor', 90;

GO

RECONFIGURE;

GO

Designing and Managing Filegroups

SQL Server databases have three types of files.

PRIMARY FILEGROUP

Each database has a primary filegroup. This filegroup hosts the primary data file and any secondary files that you have not allocated to other filegroups. The system tables are hosted in the primary filegroup. You can create filegroups to host data files together for reasons including data allocation, administrative, and placement.

Primary data files use the mdf extension.

SECONDARY FILEGROUP

Secondary data files use the .ndf extension.

You can assign these secondary files to different filegroups. Secondary data files that are hosted on different volumes can be assigned to the same filegroup. When you create a table or an index, you can configure it to use a specific filegroup. When a filegroup contains more than one file, the Database Engine will write data across the files proportionally, depending on how much free space is available in each file.

Adding New FILEGROUP

To add a new filegroup to a database by using SQL Server Management Studio, perform the following steps:

1. In SQL Server Management Studio, right-click the database to which you want to add the filegroup and then choose Properties.
   
2. On the Filegroups page, click Add and then enter the name of the new filegroup,. You can add new files to a filegroup on the Files page in the Database Properties dialog box.
   
3. You can use the ALTER DATABASE statement with the ADD FILEGROUP option to add filegroups to a database.
   

To add a new filegroup named Tertiary to the AdventureWorks2012 database, use the following query:

USE [master]
GO

ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [Tertiary]
GO

Moving an Index from One Filegroup to Another

To move an index to a different filegroup or partition scheme in SQL Server Management Studio, perform the following steps:

1. In SQL Server Management Studio, right-click the index that you want to move to a new filegroup and then choose Properties.
   
2. On the Storage page, use the Filegroup drop-down list to select the filegroup to which you want to move the index.
   

You can move indexes under the following conditions:

• You cannot move indexes created using a unique or primary key constraint through SQL Server Management Studio. You can move these indexes by using the CREATE INDEX statement with the (DROP_EXISTING=ON) option.
• If the table or index is partitioned, you must select the partition scheme in which to move the index.
• You can move clustered indexes by using online processing, allowing user access to data during the move.

To move an index by using Transact-SQL, use the CREATE INDEX statement with the DROP_EXISTING = ON option and specify the target filegroup.

 Schema

A schema is also the container that owns all objects within a database.

CREATE SCHEMA < schema name> AUTHORIZATION <owner name >

Returning Name of Default Schema

SELECT SCHEMA_NAME ();

dbo

Returning Name of  Schema by using ID

SELECT SCHEMA_NAME ( 3);

INFORMATION_SCHEMA

  

How to Find All Schemas in SQL SERVER ?

SELECT name FROM sys. schemas

SELECT SCHEMA_NAME FROM IN  FORMATION_SCHEMA.SCHEMATA (Preferred)

INFORMATION_SCHEMA views is recommended as they protect you from changes to the underlying sys tables. From the SQL Server 2008 R2 Help:

Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

 Advantages Of Using Schemas

Apart from the obvious benefit that objects can now be manipulated independently of users, usage of schemas also offers the following advantages:

Managing logical entities in one physical database: Schemas provide the opportunity to simplify administration of security, backup and restore, and database management by allowing database objects, or entities, to be logically grouped together. This is especially advantageous in situations where those objects are often utilized as a unit by applications. For example, a hotel-management system may be broken down into the following logical entities or modules: Rooms, Bar/Restaurant, and Kitchen Supplies. These entities can be stored as three separate physical databases. Using schemas however, they can be combined as three logical entities in one physical database. This reduces the administrative complexity of managing three separate databases. Schemas help to manage the logical entities separately from one another, but still allow objects to work together where required

Conclusion

Schemas, introduced in SQL Server 2005, offer a convenient way to separate database users from database object owners. They give DBA’s the ability to protect sensitive objects in the database, and also to group logical entities together.

References

 https://msdn.microsoft.com/en-us/library/dd207005.aspx

www.quackit.com/sql_server/sql_server_2008/.../sql_server_database_sc...

·               

  (http://blog.sqlauthority.com),