BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
SQL 2012 has 14 new functions, a new FileTable construct and new ways to sequence and fetch records - as this blog explains.
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.
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:
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:
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
On my machine this returns:
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.
Filetables integrate SQL Server with Windows Explorer - you can think of them as like ActiveDirectory, but easier to use.