New features for SQL within SQL Server 2012
Part two of a four-part series of blogs

SQL 2012 has 14 new functions, a new FileTable construct and new ways to sequence and fetch records - as this blog explains.

  1. SQL Server 2012 - What's New when Writing SQL
  2. Storing Files in SQL Server 2012 (this blog)
  3. What's New in SQL 2012 - New Functions
  4. Sequences and Page Data using OFFSET / FETCH NEXT

Posted by Andy Brown on 08 May 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Storing Files in SQL Server 2012

This is a big subject - my intention here is just to give an overview, and explain the concept involved.

FileTables

From SQL Server 2008 you have been able to store files in SQL Server using something called FileStreams.  You can now also create FileTables, which are tables which:

  • contain (or at least give access to) files in Windows; but
  • which you can access using standard Transact-SQL

Here's the SQL to create a file table, at its most simple:

-- create a file table in current database

CREATE TABLE WolExcelFolder as FileTable

Here's what this would create:

File table design

Here I've expanded the file table created to show its columns.  Most of the column meanings are easy enough to guess.

 

How FileTables Relate to Windows Explorer

You can right-click on any file table to see its contents in Windows Explorer:

Exploring file table

Just choose this option and - providing that you have sufficient permissions - you'll see any files stored in this file table.

 

You can also run the following SQL to see where this file table's files are stored:

-- show where this file table stores its files

SELECT FileTableRootPath('WolExcelFolder')

On my machine this returns:

File table folder

The folder in which files are stored.

 

Typing the file path above into Windows Explorer and pressing Enter would therefore provide another way to get at the files stored in this file table.

Conclusion

Filetables integrate SQL Server with Windows Explorer - you can think of them as like ActiveDirectory, but easier to use.

 

This blog has 0 threads Add post