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),

Index Maintenance

Index Maintenance

An index is a B-tree structure that consists of all the entries from the table corresponding to the index key. Values within an index are stored on index pages according to the sort order specified for the index.When a new row is added to the table, before the operation can complete, SQL Server must add the value from this new row to the correct location within the index. Each time SQL Server writes to the table it must also perform a write operation to any affected index.

        If the leaf-level index page does not have room for the new value, SQL Server has to perform a page split and write half the rows from the full page to a newly allocated page. If this also causes an intermediate-level index page to overflow, a page split occurs at that level as well. If the new row also causes the root page to overflow, the root page is split into a new intermediate level, creating a new root page. Indexes can improve query performance, but each index created also causes performance degradation on all INSERT, UPDATE, DELETE, BULK INSERT, and BCP operations. Therefore, you need to balance the number of indexes carefully for optimal operations.

Distribution Statistics

QUERY OPTIMIZER

The component that is responsible for determining whether an index should even be used to satisfy a query is called the query optimizer. The query optimizer decides whether or not to use an index based on the distribution statistics that are stored for the index.

HISTOGRAM

When an index is created, SQL Server generates a structure called a histogram that stores information about the relative distribution of data values within a column.

Index Options

              FILLFACTOR specifies the percentage of free space that should be left on the leaf level of an index during creation or rebuild. By leaving space on the leaf level, you can write a small number of rows to a leaf-level page before a page split is required, thereby slowing the rate of fragmentation for an index.

                  FILLFACTOR applies only to the leaf level of the index. Intermediate-level pages (if applicable) and the root page are filled to near capacity. During the creation of an index, all the data values for the index key are read. After these values are read, SQL Server creates a series of internal work tables to sort the values prior to building the B-tree structure. 
By default, the work tables are created in the same database as the index. You can specify the SORT_IN_TEMPDB option, which causes the work tables for sort operations to be generated in the tempdb database,

Online Index Creation

Indexes can be created either online or off-line. 

Offline Index Creation
When an index is created using WITH ONLINE = OFF option, SQL Server locks the entire table, preventing any changes until the index is created. 
To build a clustered index off-line, the table is locked and does not allow select statements or data modifications. 

If you build a non clustered index off-line,a shared table lock is acquired, which allows select statements but not data modification.
Default option of index is ONLINE = OFF.

Online Index Creation         
When an index is created using the ONLINE = ON option, SQL Server allows changes to the table during the creation of the index by using the version store within the tempdb database.

Differece between Extent & Allocation Unit

What is the difference/relationship between extent and allocation unit?

 

The allocation unit is basically just a set of pages. It can be small (one page) or large (many many pages). It has a metadata entry in sys.allocation_units. It is tracked by a IAM chain. The most common use of allocation units is the 3 well known AUs of a rowset: IN_ROW_DATA, ROW_OVERFLOW and LOB_DATA.

An extent is any 8 consecutive pages that start from a page ID that is divisible by 8. SQL Server IO is performed in an extent aware fashion: ideally an entire extent is read in at once, an entire extent is write out at once. This is subject to current state of the buffer pool, for details see How It Works: Bob Dorr's SQL Server I/O Presentation. Extents are usually allocated together, so all pages of an extent belong to the same allocation unit. But since this would lead to overallocation for small tables a special type of extent is a so called 'mixed' extent, in which each page can belong to a separate allocation unit. For details see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps.

 

Binary Data

 Binary data is stored in a set of four data types.

                             Binary Data Types

 DATA TYPE           RANGE OF VALUES             STORAGE SPACE

 BIT                                   Null, 0, and 1                                           1 bit

 BINARY                         Fixed-length binary data                      Up to 8,000 bytes

 VARBINARY                  Variable-length binary                         Up to 8,000 bytes

XML Data Type

What is XML?

XML stands for Extensible Markup Language like HTML was designed to carry data, not to display data.

XML Data Type

 XML data type allows you to store and manipulate Extensible Markup Language (XML) documents natively. When storing XML documents, you are limited to a maximum of 2 GB, as well as a maximum of 128 levels within a document. Although you could store an XML  document in a character column, the XML data type natively understands the structure of XML data and the meaning of XML tags within the document. 

 Because the XML data type natively understands an XML structure, you can apply  additional validation to the XML column, which restricts the documents that can be stored 

based on one or more XML schemas. 

       XML schemas are stored within SQL Server in a structure called a schema collection. Schema collections can contain one or more XML schemas. When a schema collection is applied to an XML column, the only documents allowed to be stored within the XML column must fi rst validate to the associated XML schema collection. 

Spatial Data Types

 SQL Server 2008 supports two data types to store spatial data.

 Geometry
Geography

Geometric Data

 Geometric data is based on Euclidean geometry and is used to store points, lines, curves, and polygons. 

Geographic Data

Geographic data is based on an ellipsoid and is used to store data such as latitudes and longitudes.

SPECIAL COLUMNS.

You define spatial columns in a table using either the GEOMETRY or GEOGRAPHY data 

types. When values are stored in a spatial column, you have to create an instance using one 

of several spatial functions specific to the type of data being stored. A GEOMETRY column 

can contain one of seven different geometric objects with each coordinate in the definition 

separated by a space.

Geometry Data Type Definitions 

 Multi Polygon

 Contains the coordinates for multiple Polygons.

 Point

 Has x and y coordinates, with optional elevation and measure values.

 Line String

 A series of points that defines the start, end, and any bends in the line, with optional elevation and measure values.

 Polygon

 A surface defined as a sequence of points that defines an exterior boundary, along with zero or more interior rings. A polygon has at least three distinct points.

 Geometry Collection 

Contains one or more instances of other geometry shapes, such as a Point and a Line String.

MultiPoint

Contains the coordinates of multiple Points.
 Multi Line String

 Contains the coordinates of multiple Line Strings.

HIERARCHY ID Data Type

 The HIERARCHY ID data type is used to organize hierarchical data, such as organization charts, bills of materials, and flowcharts. The HIERARCHY ID stores a position within a tree hierarchy. By employing a HIERARCHYID, you can quickly locate nodes within a hierarchy as well as move data between nodes within the structure. 

Example

REFRENCES
http://msdn.microsoft.com/en-us//library/ms188362.aspx

http://technet.microsoft.com/en-us/library/ms179366(v=sql.105).aspx