FILESTREAM in Sql Server 2008 and Example

FILESTREAM in SQL Server 2008

SQL Server 2008 introduces the new FILESTREAM attribute, which can be applied to the varbinary(max) data type. Using FILESTREAM, you can exceed the 2GB limit on stored values and take advantage of relational handling of files via SQL Server, while actually storing the files on the file system. BACKUP and RESTORE operations maintain both the database data as well as the files saved on the file system, thus handling end-to-end data recoverability for applications that store both structured and unstructured data. FILESTREAM marries the transactional consistency capabilities of SQL Server with the performance advantages of NT file system streaming.

SQL Server can manage the contents of the FILESTREAM containers on the file system for you and control access to the files, while the NT File System (NTFS) provides efficient file streaming and file system transaction support. This combination of SQL Server and NTFS functionality provides several advantages when dealing with LOB data, including increased efficiency, manageability, and concurrency.

Check whether the filestream property is enabled or not. To do this executes the following query.

SELECT SERVERPROPERTY('FilestreamShareName') ShareName,
SERVERPROPERTY('FilestreamEffectiveLevel') EffectiveLevel,
SERVERPROPERTY('FilestreamConfiguredLevel') ConfiguredLevel

If it is not enable use sp_configure procedure to enable it.

EXEC sp_ configure 'filestream access level', 2;
GO
RECONFIGURE;
GO

FILESTREAM Access Levels:


0. Disable

1. Access via T-SQL only

2. Access via T-SQL only and file system

Once you’ve enabled FILESTREAM support on your SQL Server instance, you have to create a SQL Server file group with the CONTAINS FILESTREAM option. This file group is where SQL Server will store FILESTREAM BLOB files.

Example:

CREATE DATABASE Jobin ON PRIMARY
(
NAME='Jobin_Data',
FILENAME='E:\Jobin\sql\jobin.mdf'
),
FILEGROUP DocumentFileStreamGroup CONTAINS FILESTREAM
(
NAME='FileStreamdocuments',
FILENAME='E:\Jobin\sql\jobin_documents'
)
LOG ON
(
NAME='Jobin_Log',
FILENAME='E:\Jobin\sql\jobin.ldf'
);

To enable FileStream to an existing database execute the following query

-- to add filestream to an existing database
ALTER DATABASE Jobin
ADD FILEGROUP DocumentFileStreamGroup CONTAINS FILESTREAM;
GO
ALTER DATABASE Jobin
ADD FILE
(
NAME = 'FileStreamdocuments'
FILENAME = 'E:\Jobin\sql\jobin_documents'
)
TO FILEGROUP DocumentFileStreamGroup;

FILESTREAM Enabled Tables


Once you’ve enabled FILESTREAM on the server instance and created a FILESTREAM filegroup, you’re ready to create FILESTREAM-enabled tables. FILESTREAM storage is accessed by creating a varbinary (max) column in a table with the FILESTREAM attribute. The FILESTREAM-enabled table must also have a uniqueidentifier column with a ROWGUIDCOL attribute.

CREATE TABLE Employee
(
EmpId UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY,
Name VARCHAR(50),
Document VARBINARY(MAX) FILESTREAM
)

Insert some records into the table.

INSERT INTO Employee VALUES(NEWID(),'Jobin',CAST('jobin john' AS VARBINARY(MAX)));
INSERT INTO Employee VALUES(NEWID(),'Ajith',CAST('Software Engineer' AS VARBINARY(MAX)));

Select the inserted values

SELECT * FROM Employee
SELECT EmpId,Name,CAST(Document AS VARCHAR) AS Document FROM Employee

SQL Server Views Tutorial - Create, Alter, Insert, Update, Delete etc..

SQL Server Views
Views allow you to create a virtual representation of table data defined by a SELECT statement.

Views can be used to simplify data access for query writers, obscuring the underlying complexity of the SELECT statement.

Views are also useful for managing security and protecting sensitive data. If you wish to restrict direct table access by the end user, you can grant permissions exclusively to views, rather than to the underlying tables. You can also use views to expose only those columns that you wish the end user to see, including just the necessary columns in the view definition.

Views work as an interface between database tables and user.

You cannot use certain SELECT elements in a view definition, including INTO, OPTION, COMPUTE, COMPUTE BY or ORDER BY. We cannot reference a temporary table.

Views can be used for different purposes:

• To restrict the use of particular columns and/or rows of tables.


• To hide the details of complicated queries. If database applications need queries that involve complicated join operations, the creation of corresponding views can simplify the use of such queries

CREATE VIEW Statement:
A view is created using the CREATE VIEW statement.

/*
create a view for viewing data within the table
Teachers for the user student
*/
CREATE VIEW Student.vu_SelectTeachers
AS
SELECT Name,JobTitle FROM Teacher.Teachers
GO

/*
select data by using the view
*/
SELECT * FROM Student.vu_SelectTeachers
Eg:
/*
create a view for the user teacher
*/
CREATE VIEW Teacher.vu_SelectTeachers
AS
SELECT Id, Name, JobTitle from Teacher.Teachers
GO

/*
select data
*/
SELECT * FROM Teacher.vu_SelectTeachers

Altering and Removing Views
ALTER VIEW statement is used to modify the definition of the view query. The DROP VIEW statement removes the definition of the specified view from the system tables.
Eg:

/*
Altering views
*/
ALTER VIEW Student.vu_SelectTeachers
AS
SELECT Name,JobTitle,Phone from Teacher.Teachers
GO
Eg:
/*
remove a view
*/
DROP VIEW Student.vu_SelectTeachers

INSERT, UPDATE and DELETE statement and VIEW
A view can be used with the INSERT, UPDATE and DELETE statements. When a view is used to insert, update or delete, the rows are actually insert, update or delete into the underlying base table.
The inserting, updating and deleting of rows into the underlying tables is not possible if the corresponding view contains any of the following features:

• The FROM clause in the view definition involves two or more tables and the column list includes columns from more than one table


• A column of the view is derived from an aggregate function


• The SELECT statement in the view contains the GROUP BY clause or the DISTINCT option


• A column of the view is derived from a constant or an expression (except in the case of deletion)

/*
INSERT, UPDATE and DELETE with Views
*/
INSERT INTO Teacher.vu_SelectTeachers(Id, Name,JobTitle) VALUES(4,'jobin','Faculty')

UPDATE Teacher.vu_SelectTeachers SET JobTitle='Trainer' WHERE Id=4

DELETE Teacher.vu_SelectTeachers WHERE Id=4

WITH Encryption:
The WITH ENCRYPTION option encrypts the SELECT statement, thus enhancing the security of the database system. Software vendors who use SQL Server in the back end often encrypt their views or stored procedures in order to prevent tampering or reverse-engineering from clients or competitors. If you use encryption, be sure to save the original, unencrypted definition.

Eg:
/*
creating a view with Encryption
*/
CREATE VIEW Teacher.vu_SelectTeachers
WITH ENCRYPTION
AS
SELECT Id, Name, JobTitle from Teacher.Teachers
GO

WITH SCHEMABINDING
The SCHEMABINDING clause binds the view to the schema of the underlying table. Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.

Notice two important points while creating a view with SCHEMABINDING OPTION:

• The objects should be referred to by their owner names [two part name].


• SELECT * is not permitted.

/*
create schema with SCHEMABINDING
*/
DROP VIEW Teacher.vu_SelectTeachers

CREATE VIEW Teacher.vu_SelectTeachers
WITH SCHEMABINDING
AS
SELECT Id, Name, JobTitle from Teacher.Teachers
GO

/*
try to change schema of tale Teacher
*/
alter schema student TRANSFER Teacher.Teachers
alter schema teacher TRANSFER student.Teachers

WITH CHECK OPTION
The option WITH CHECK OPTION is used to restrict the insertion of only such rows that satisfy the conditions of the query. If this option is used, Database Engine tests every inserted row to ensure that the conditions in the WHERE clause are evaluated to true.

/*
create schema with CHECK OPTION
*/
DROP VIEW Teacher.vu_SelectTeachers

CREATE VIEW Teacher.vu_SelectTeachers
AS
SELECT Id, Name, JobTitle FROM Teacher.Teachers WHERE JobTitle='faculty'
WITH CHECK OPTION
GO

-- insertion fails on the following query because not faculty
INSERT INTO Teacher.vu_SelectTeachers(Id, Name, JobTitle) VALUES (5,'fff','Manager')
--insertion succeed on the following query because his JobTitle is faculty
INSERT INTO Teacher.vu_SelectTeachers(Id, Name, JobTitle) VALUES (5,'fff','faculty')

SQL Index - Clustered Index and Non-Clustered Index

SQL INDEX


An index is a database object that, when created on a table, it can provide faster access paths to data and can facilitate faster query execution. Indexes are used to provide SQL Server with amore efficient method of accessing the data. Instead of always searching every data page in a table, an index facilitates retrieving specific rows without having to read a table’s entire content.

By default, rows in a regular un-indexed table aren’t stored in any particular order. A table in an order less state is called a heap. In order to retrieve rows from a heap based on a matching set of search conditions, SQL Server would have to read through all the rows in the table.

When an index is created, its index key data is stored in a B-tree structure. A B-tree structure starts with a root node, which is the beginning of the index. This root node has index data that contains a range of index key values that point to the next level of index nodes, called the intermediate leaf level. The bottom level of the node is called the leaf level. The leaf level differs based on whether the actual index type is clustered or non-clustered. If it is a clustered index, the leaf level is the actual data pages itself. If a non-clustered index, the leaf level contains pointers to the heap or clustered index data pages. A clustered index determines how the actual table data is physically stored. You can only designate one clustered index.

There are two types of indexes:

1. Clustered Indexes

2. Non clustered indexes

Clustered Index

A clustered index determines the physical order of the data in a table. Database Engine allows the creation of a single clustered index per table, because the rows of the table cannot be physically ordered more than one way. A clustered index is built by default for each table, for which you define the primary key using the primary key constraint.

When an index is created, its index key data is stored in a B-tree structure. The top level of the clustered index B-tree is known as the root node, the bottom level nodes are known as leaf nodes, and all nodes in between the root node and leaf nodes are collectively referred to as intermediate nodes. In a clustered index, the leaf nodes contain the actual data rows for a table, and all leaf nodes point to the next and previous leaf nodes, forming a doubly linked list. The clustered index holds a special position in SQL Server indexing: because its leaf nodes contain the actual table data, there can only be one clustered index defined per table.

                                            Figure: Clustered index B-tree structure


Eg:

Let’s take a look at a visualization of what a B-Tree looks like for a clustered index.

Non Clustered Index

A non-clustered index has the same index structure as a clustered index, with two important differences:

• A non-clustered index does not change the physical order of the rows in the table.

• Unlike clustered indexes, however, each leaf node in a non-clustered index contains the non-clustered key value and a row locator.



The physical order of rows in a table will not be changed if one or more non-clustered indices are defined for that table. For each non-clustered index, Database Engine creates an additional index structure that is stored in index pages. Non-clustered indexes are based on order of the data, but do not physically sort the data.

If a table has a clustered index, all non-clustered indexes defined on the table automatically include the clustered index columns as the row locator. If the table is a heap, SQL Server creates row locators to the rows from the combination of the file identifier, page number, and slot on the page.

                                             Figure: Non-clustered index B-tree structure

Let’s take a look at a visualization of what a B-Tree looks like for a non-clustered index

Transact SQL Constraints

1. Primary Key


Primary keys are the unique identifiers for each row. They must contain unique values. A primary key column can’t contain NULL values. A table can have a maximum of one primary key.

2. Unique Key


You can only have a single primary key defined on a table. If you wish to enforce uniqueness on other non-primary key columns, you can use a UNIQUE constraint. A unique constraint, by definition, creates an alternate key. Unlike a PRIMARY KEY constraint, you can create multiple UNIQUE constraints for a single table and are also allowed to designate a UNIQUE constraint for columns that allow NULL values.

3. Foreign Key


Foreign key constraints establish and enforce relationships between tables and help maintain referential integrity, which means that every value in the foreign key column must exist in the corresponding column for the referenced table. Each foreign key is defined using the FOREIGN KEY clause combined with the REFERENCES clause.

4. Check Constraints


The CHECK constraint is used to define what format and values are allowed for a column.

5. Default constraints


A default constraint specifies a value to be applied to a column whenever a value is not supplied in an INSERT.

Computed Columns

Computed columns in Microsoft SQL Server:


A computed column is based on an expression defined when you create or alter the table, and is not physically stored in the table unless you use the PERSISTED keyword.

A computed column is one defined to be an expression that is executed whenever the column is retrieved. If we use the PERSISTED keyword the expression is being executed while we insert or modify a row.

Syntax:

column_name AS computed_column_expression [ PERSISTED ]


Eg: Non-persisted computed column

CREATE TABLE Student
(
RollNo INT IDENTITY PRIMARY KEY,
Name VARCHAR(25),
Mark1 INT,
Mark2 INT,
Total AS Mark1+Mark2
)


Eg: Persisted computed column

CREATE TABLE Student
(
RollNo INT IDENTITY PRIMARY KEY,
Name VARCHAR(25),
Mark1 INT,
Mark2 INT,
Total AS Mark1+Mark2 PERSISTED
)

Create and alter Tables

Create and alter Tables:


A table is a repository for data, with items of data grouped in one or more columns. Tables contain zero or more rows of information.

Eg: Creating tables
CREATE TABLE Student
(
rollNo INT,
name VARCHAR(50),
mark1 INT,
mark2 INT
)
 
Altering tables:


If we need to change the definition of a table after creating it, we can use the ALTER TABLE statement.
We can use ALTER TABLE to add additional columns to a table, remove columns from the table, add and remove constraints, disable and enable constraints and disable and enable triggers.

Eg:
ALTER TABLE Student

ADD Total INT

ALTER TABLE Student
DROP COLUMN Total

Attaching detaching Databases

Attaching detaching Databases


At times, you may want to move an entire database- including all of its objects data, and log files- to another SQL server machine. To do this, SQL server enables attaching and detaching databases.

Eg: Detach

SP_DETACH_DB 'DBTEST'

Eg: Attach

SP_ATTACH_DB 'DBTEST','E:\jobin\database\DBTESTDATA.mdf','E:\jobin\database\DBTESTLOG.ldf'

Creating a Database

Database


A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.

create a database:

syntax:  create database database_name

Eg: CREATE DATABASE TEST

database_name is the name of the database. The maximum size of a database name is 128 characters.
SQL server has two operating system file types:

1. Data files (has extension .mdf)

2. Log files ( has extension .ldf)

Data files contain data and objects such as tables and indexes. Log files contain transaction log for recovering the database transactions.

Specifying the data file and log file

Eg:

CREATE DATABASE DBTEST
ON
(
NAME='DBTESTDATA',
FILENAME='E:\jobin\database\DBTESTDATA.mdf',
SIZE=10mb,
MAXSIZE=100mb,
FILEGROWTH=10mb
)


LOG ON
(
NAME='DBTESTLOG',
FILENAME='E:\jobin\database\DBTESTLOG.ldf',
SIZE=5mb,
MAXSIZE=50mb,
FILEGROWTH=5mb
)