SQL SERVER-FILESTREAM- What is FILESTREAM in SQL Server?

Why file stream?

Generally we will not store the unstructured data (Images, docs etc) in the databases. So we will keep unstructured data outside the database and we will only store the link to the unstructured data in the database. The difficulties we are facing due to this is,

  • Database Management Complexity
  • IF unstructured data is stored in the database, it will decrease the performance.

So Microsoft comes up with a new approach File stream to store the unstructured data.

What is FILESTREAM?

File stream integrates SQL Server Database Engine with NTFS file system by storing varbinary(max) bnary large object (BLOB) data as files on the File system. Win32 File system interfaces provide streaming access to the data. FILESTREAM uses NT Cache for caching file data. This will improve DB Engine performance. FILESTREAM will not use SQL SERVER buffer pool. So SQL SERVER memory will be free, so totally it will improve the performance.

Creating a Database with File stream:

/******Creating a database with FileStream  ******/

CREATE DATABASE [Test] ON PRIMARY ( name = N'Test', filename =N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest.mdf', size = 2048kb, maxsize = unlimited, filegrowth = 1024kb ), filegroup [FSG]CONTAINS filestream DEFAULT ( name = N'FS', filename =N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATAFS' )LOG ON ( name = N'Test_log', filename =N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_log.ldf', size = 1024kb, maxsize = 2048gb, filegrowth = 10%)GO

After running the above script we can see two files created in the location C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATA

  • $FSLOG : Log of the Filestream
  • Filestream.hdr : Header of the Filestream.

Creating a table and Inserting data into it.

--Tables with Filestream Columns must have non-null unique ROWGUID Column
CREATE TABLE dbo.images
(
id INT IDENTITY(1, 1),
ui UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
image VARBINARY(MAX) FILESTREAM NULL
)

--Inserting FileStream Data into tables
-- I had Inserted 3 rows, Only 1 is displaying here.

INSERT INTO [Test].[dbo].[Images]
([UI]
,[Image])
VALUES
(NEWID()
,CAST('' as varbinary(max)))
GO


Querying File Stream Data

FILESTREAM rows in the FILESTREAM location.

This is just an introductory post on FILESTREAM; there is lot of things in FILESTREAM like File Stream Partitioning, Garbage collection etc. I will update these in upcoming posts.

SQL SERVER – TABLE VALUED CONSTRUCTOR- A QUERY FROM MAHESH THATTAPARAMBIL- A .NET EXPERT

SQL SERVER – TABLE VALUED CONSTRUCTOR- A QUERY FROM MAHESH THATTAPARAMBIL- A .NET EXPERT

Location : Orion Systems Integrators, infopark, cochin

Tea Break:  4:30, 07-07-2011

Mahesh: Hi Varun, Do you know, what is Table valued Constructor?

Varun: No, Is it TVP?

Mahesh: No it is TVC, I looked into so many blogs, I didn’t understand. Can u explain me after learning it?

Varun: Yes, Sure Mahesh.

 Mahesh is a funny guy who has 6+ years in .Net technology. He is a very hard working guy and technology lover.He is Senior Software Engineer in Orion Systems Integrators. He has quite lot of interest in SQL Server too.

 Most of the days he will ask SQL questions to me, which help me to learn more on SQL Server.

 This was one which blows a fresh air in T-SQL Programming.

What is TVC?

Table valued constructor specifies a set of row value expressions to be constructed into a table. The T-SQL TVC allows

multiple rows of data to be specified in a Single Statement. The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING <source table> clause of the MERGE statement, and in the definition of a derived table in the FROM clause.Look at the below example.

 (Ref: http://msdn.microsoft.com/en-us/library/dd776382(v=sql.100).aspx)


--One Method of Inserting data into table 

INSERT INTO [Test].[dbo].[CustomerDetails]

            ([Name],

             [Address])

VALUES      (‘Varun’,

             ‘TVM’),

            (‘FAZAL’,

             ‘Delhi’),

            (‘Mahesh’,

             ‘KLM’)



--Select  Methold of Inserting data into table

INSERT INTO [Test].[dbo].[CustomerDetails]

            ([Name],

             [Address])

SELECT‘Varun’,

      ‘TVM’

UNION ALL

SELECT ‘FAZAL’,

       ‘Delhi’

UNION ALL

SELECT ‘Mahesh’,

       ‘KLM’



--Intrducing TVC in SQL Server 2008

INSERT INTO [Test].[dbo].[CustomerDetails]

            ([Name],

             [Address])

VALUES     (‘MANU’,

            ‘TVM’),

            ((SELECT [NAME]

              FROM   dbo.customer

              WHERE  customer_id = 1),

             (SELECT [Address]

              FROM   dbo.customer

              WHERE  customer_id = 1));



GO 

Limitations of TVC:

  • The maximum number of rows that can be constructed using the table value constructor is 1000
  • Only single scalar values are allowed as a row value expression
  • A subquery that involves multiple columns is not allowed as a row value expression