A view on storing file data in SQL Server

Storing file data (BLOBs) (eg. pdf, word, excel ...) in SQL server always has been some kind of dark magic to a lot of people.

Before SQL Server 2008 we could store the BLOB data in VARBINARY(MAX) datatypes, in which you also had to convert the BLOB data into the binary format yourself ...


With SQL 2008, the FILESTREAM datatype was introduced. FILESTREAM enables us to integrate SQL Server with the NTFS file system ... BLOBs are stored as real files on the file system.

More on FILESTREAM: http://msdn.microsoft.com/en-us/library/bb933993.aspx

--Enable FILESTREAM

EXEC sp_filestream_configure @enable_level = 3;

--CREATE THE TABLE

CREATE TABLE TestFS

      (

            ID INT IDENTITY(1,1),

            FileName NVARCHAR(200),

            Content VARBINARY(MAX) FILESTREAM

      )

As from the new version of SQL Server, SQL 2012 (aka 'Denali'), a new feature has been introduced ... FILETABLE, based on the existing FILESTREAM feature in SQL Server. In fact, the FILETABLE feature is nothing more than storing files in special filetables in SQL server, but you will be able to access them from the file system. It is said that FILETABLE will be the next generation of FILESTREAM ...

More on FILETABLE: http://msdn.microsoft.com/en-us/library/ff929144(v=sql.110).aspx

--Create the table

CREATE TABLE DocumentStore AS FILETABLE

    WITH (

          FileTable_Directory = 'DocumentTable'

          FileTable_Collate_Filename = database_default

         );

GO
Have fun
Jurgen

SQL Server Denali - LAG and LEAD

This is a short post on 2 new T-SQL features called LAG and LEAD, they are kind like Bonny & Clyde, they are mentioned in the same phrase...

LAG and LEAD accesses the data from respectively the previous and the next row in a table without using self joins.

Syntax:

LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )


LEAD (scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] )

USE
AdventureWorks2008R2

GO

SELECT TerritoryName, BusinessEntityID, SalesYTD,

       LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales

FROM Sales.vSalesPerson

WHERE TerritoryName IN (N'Northwest', N'Canada')

ORDER BY TerritoryName;

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, MONTH(QuotaDate) AS SalesMonth, SalesQuota AS CurrentQuota, LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate), MONTH(QuotaDate)) AS NextQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

Have Fun!

New Date and Time functions in "Denali" CTP3

I've been playing around with the CTP3 of Denali and stumbled upon some new functions...

The new functions are :

•  DATEFROMPARTS(@year, @month, @day)
• returns a DATE value
• DATETIMEFROMPARTS(@year, @month, @day, @hour, @minute, @seconds, @milliseconds) 
• returns a DATETIME value
•  TIMEFROMPARTS(@hour, @minute, @seconds, @fractions, @precision) 
• returns a TIME value
• where @fractions is an integer expression and @precision is an integer literal specifying the precision of the time value to be returned.)
•   @fractions depend on @precision eg if the precision is 7 then each fraction represents 100 nanoseconds.

Remarks:

• If the arguments are invalid, then an error is raised
• If any of the parameters are null, null is returned

Some Examples:

SELECT DATEFROMPARTS(2011, 07, 20) AS 'Todays Date'

SELECT DATETIMEFROMPARTS (2011, 07, 20, 09, 51, 07, 321) AS 'Todays Date and Time'

SELECT TIMEFROMPARTS (09, 51, 07, 321 , 3) AS 'Todays Time'

have fun,
Jurgen

redgate's Sqlinthecity

On July 15th 2011 I will be attending the SqlInTheCity event of Red-Gate.