SQL Server FILESTREAM and FileTables

Monday: Using FILESTREAM and FILETABLES in SQL Server

Configuring and Using FILESTREAM and FILETABLES in SQL Server.  Developers love to use SQL Server to store files, but this causes headaches for the DBA, finally a reasonable solution for file storage in SQL Server FILETABLES and FILESTREAM. SQL Server 2008 and 2012 add the new features of FILESTREAM and FILETABLES. Learn how to configure and manipulate files in your SQL Server with FILESTREAM, then learn how to do everything that FILESTREAM sounds like it should do with FILETABLES. With FILETABLES inserting is as easy as drag and drop.

Here is the outline for the presentation:

  • FILESTREAM – SQL Server 2008 and newer
    • Introduction and Configuration
    • Creating a Table Using FILESTREAM
    • TSQL FILESTREAM Access
  • FileTables – SQL Server 2012 and newer
    • Configuring and Creating FileTables
    • Insert, Update and Delete with a FileTable
    • Drag and drop with the file system

 

Here are the slides from the presentation and the supporting sample files.

Filestream and FileTables.zip

 

SQL Sunday Fun – Word Search with New T-SQL 2012 Functionality

 

 

new_sql2012_word_jumbleClick on the grid to enlarge, then print it.

Find the following words in the grid up, down, left, right, horizontal, vertical, and diagonal.

ROWS  RANGE  PRECEDING 
FOLLOWING  UNBOUNDED  IIF 
CHOOSE  OFFSET  FETCH 
FORMAT  CONCAT  SEQUENCE 
PARSE  TRYPARSE  TRYCONVERT 
THROW  LEAD  LAG 
FIRSTVALUE  LASTVALUE  PERCENTRANK 
CUMEDIST  PERCENTILEDISC  PERCENTILECONT 

NOTE:  some of these terms normally have an underscore in them, but for the purpose of the word scramble the underscores have been omitted.

 

Related Links:

SEQUENCE object

CHOOSE function

Analytics - Analytic Functions – PERCENT_RANK

Analytics PERCENTILE_DISC and PERCENTILE_CONT

IIF function in TSQL.

TSQL Analytic Functions LEAD and LAG

OVER clause with ROWS PRECEDING and FOLLOWING in TSQL 2012

OFFSET / FETCH in a SELECT statement

Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST()

 

 

CTE Data Paging in a Procedure

Day 15 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at data paging with CTE’s in a function. Yesterday we took a look at CTEs for Data Paging, today we will build on what we used yesterday.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

The concept of data paging is taking a large set of results and breaking it up into smaller pages of result sets. For instance if you search your favorite internet search engine for a term, you are usually shown results 1 to 20 of X where X is a very large number.  For instance page 1 is results 1 to 20 of 652,443 results.

CTE Data Paging


declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

;WITH TablesAndColumns AS
(
SELECT OBJECT_NAME(sc.object_id) AS TableName,
 name AS ColumnName,
 ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
 FROM sys.columns sc
)
SELECT *
 FROM TablesAndColumns
 WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
 AND @pageNum * @pageSize ;

Now lets wrap it in a procedure to simplify things.


CREATE PROCEDURE TablesAndColumnsPager @pageNum int, @pageSize int
AS
BEGIN
SET NOCOUNT ON;

;WITH TablesAndColumns AS
  (
SELECT OBJECT_NAME(sc.object_id) AS TableName,
  name AS ColumnName,
  ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
  FROM sys.columns sc
  )
  SELECT *
  FROM TablesAndColumns
  WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
  AND @pageNum * @pageSize ;
END

Then to call the paging function it is a bit cleaner.


exec TablesAndColumnsPager 1, 10;
exec TablesAndColumnsPager 2, 10;
exec TablesAndColumnsPager 3, 10;

Which produces the following output.
DataPagingSproc1

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

T-SQL 2012 DATEFROMPARTS function

SQL Server 2012 adds a new function called DateFromParts.  This new function simplifies the creating of a DATE type in TSQL over the older ways of doing it. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL 2012.

Pre-SQL2012

First lets take a look at how you would do the equivalent to DateFromParts before SQL Server 2012:

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;
set @TheDate = cast(convert(datetime,convert(varchar(10),@year) + '-' +
 convert(varchar(10),@month) + '-' +
 convert(varchar(10),@day), 101) as date);

select @TheDate;

datefromparts1

You could certainly make it work prior to SQL Server 2012, but in SQL 2012 the new DATEFROMPARTS function is available to simplify your query.

SQL 2012 DateFromParts

Here is where it gets easier. Many of the new functions and other TSQL features in SQL Server 2012 appear to just simplify what you could do before. The way that DateFromParts works is like this:

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;
set @TheDate = DateFromParts(@year, @Month, @Day);

select @TheDate;

datefromparts2

Both the old way and the DateFromParts function produce the same results. The DateFromParts function does simplify the amount of typing, and casting and concatenation you need to do to create a date type. Keep in mind that the order of the parameters passed into DateFromParts is always Year, Month, Day independent of the specific country specific date formats you may be using.

A Function to Make DateFromParts Work Prior to SQL Server 2012

If you are not yet on SQL Server 2012 and you would like to start using the DateFromParts function, you could create your own user defined function for now, then just remove it when you get to SQL Server 2012.

If we try the function on SQL Server 2008 as shown below an error will be thrown.


DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;

set @TheDate = DateFromParts(@year, @Month, @Day);

select @TheDate;

datefromparts2005error

To fix that error, all we need to do is create a new scalar valued function called DateFromParts to do the same thing as SQL Server 2012 does:


CREATE FUNCTION DateFromParts
(
@year AS INTEGER,
@month AS INTEGER,
@day AS INTEGER
)
RETURNS [DATE]
AS
BEGIN
RETURN cast(Convert(datetime,convert(varchar(10),@year)+'-'+
            convert(varchar(10),@month)+'-'+
            convert(varchar(10),@day), 101) as date);
END

Then we can run the original query with the function prefixed with dbo to get a similar result on SQL Server 2008 as we would on SQL Server 2012.


DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;

set @TheDate = dbo.DateFromParts(@year, @Month, @Day);

select @TheDate;

datefromparts2008datefromparts

So with a simple function you can implement the same DateFromParts function on SQL Server 2008 and 2008R2 that is available on SQL Server 2012. Since the DATE type was not available in SQL 2005, you won’t be able to do this on SQL Server 2005.

Summary

Prior to SQL Server 2012 you could accomplish the same thing as DateFromParts with appropriate casting of variables. SQL Server 2012 introduces the DateFromParts scalar function to simplify things.  If you want to add your own DateFromParts function into SQL Server 2008, or 2008 R2 that can be done easily.

Enjoy!

Select Favorite SQL Server 2012 Articles

The following articles are a collection of my favorite SQL Server related posts over the last 6 months.

3 Steps to Work More Efficiently in SSMS. Posted 2/17/2013

Recently the 3 Steps to Work More Efficiently in SSMS article was one of my favorites. The three steps were:

  1. Display results in a separate tab.
  2. Hotkey for SELECT TOP 100 * FROM …
  3. sp_help by using Alt+F1

The Display results in a separate tab is one of the first things I do when working in SSMS on a new computer.

VARCHAR diff function Posted 2/13/2013

What made the VARCHAR diff function posting one of my favorite was how it built on a number of previous articles to finally get to something that was much more useful, all based on common table expressions. The VARCHAR diff function used code from a previous posting called Using a CTE to Split a String Into Rows with Line Numbers. A couple weeks after writing the VARCHAR diff function with CTE’s I ended up using this on a project to create an automated testing script to compare the output from an outdated code project to the results from the new T-SQL implementation.  The use of the VARCHAR diff function allowed for a single tester to test the work of 3 developers in a time far less than what was anticipated on this project.

The VARCHAR diff function probably isn’t something that anyone would use on a daily basis, but it is a massive time saver when needed.

TSQL 2012 – Generating letters with a SEQUENCE object Posted 11/9/2012

This article was a lot of fun. Having learned about how to use a SQL Server 2012 sequence object to generate letters at a SQL Saturday presentation, I just had to blog about this one. It was a fun post.

Adventureworks2012 data to Google GeoChart Visualization Posted 10/19/2012

On this article I pulled address data from the AdventureWorks2012 database, and covered how to extract it and reformat it correctly to display on the Google GeoChart Visualizations.

Visualization1

Again another fun posting.

I hope everyone enjoys these as much as I did.

T-SQL 2012 Procedure sp_describe_first_result_set

SQL Server 2012 adds a new stored procedure called sp_describe_first_result_set.  This new procedure returns metadata for the result set returned from a query. The metadata is information about what the results will look like.  sp_describe_first_result_set is an alternative to sp_columns, and appears to perform much better than sp_columns. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL 2012.

Setting up the database

For the purpose of this demo, we will be using the following database called tsql2012 from my SQL Saturday presentation on Whats New in TSQL 2012.  If you don’t want to use the tsql2012 database, you can use any database you like.


USE [Master];

CREATE DATABASE [tsql2012];

GO

USE [tsql2012];

Once the database is established we can run the following queries to set up a test table to work with, then we run a select statement to view the table contents:


CREATE TABLE MyTable
(
[DepartmentID] int,
[Revenue] int,
[Year] int,
[Name] varchar(1024)
);

insert into MyTable
values (1,10030,1998, 'xyz'), (2,10,1999, '10'), (3,20,2000, '20 Things');

SELECT * FROM MyTable;

sp_describe1

We can see the results are pretty simple, 3 rows with 4 columns from a very simple table.

Calling sp_describe_first_result_set

Next lets take a look at the most simple form of the sp_describe_first_result_set procedure. Here we are passing in a single NVARCHAR parameter containing a query.


exec sp_describe_first_result_set N'SELECT * FROM MyTable';

sp_describe2

In the above result set there are many columns returned, scrolling to the right will show that many of the columns are null, we will come back to that in a minute.  Here you can see several columns with various details about the results that would be returned from the query. We can see the sizes, the types, the precision, collation, and several other details.

Parameterized Queries

Next lets expand on the basic function with the parameterized query version of the stored procedure call.

[SQL]
– sp_describe_first_result_set with parameters
exec sp_describe_first_result_set
@tsql = N’SELECT * FROM MyTable WHERE Name = @name’,
@params = N’@name varchar(1024)’;

[/SQL]

sp_describe3

In this example the results returned were exactly the same as the previous example, however the parameters passed into the stored procedure allowed for parameterized queries. Specifically specifying in this case that the @name parameter is a varchar(1024) type.

Browse_Information_Mode

Next we add a third parameter called browse_information_mode which is used to specify the columns and result type to be included in the output. There are 3 options for this parameter; 0 for no additional data, 1 to analyze for browse which includes additional results, and 2 meaning to analyze as though it was preparing a cursor.


--@browse_information_mode
-- analyzed with no additional information
exec sp_describe_first_result_set N'SELECT * FROM MyTable WHERE Name = @name',
 N'@name varchar(1024)',
 0;

-- analyzed FOR BROWSE
exec sp_describe_first_result_set N'SELECT * FROM MyTable WHERE Name = @name',
 N'@name varchar(1024)',
 1;

-- analyzed as if it was preparing a cursor
exec sp_describe_first_result_set N'SELECT * FROM MyTable WHERE Name = @name',
 N'@name varchar(1024)',
 2;

When the three calls to the stored procedure are run we see three result sets, that are very similar:

sp_describe5

However the third result set analyzing it as a cursor shows an extra column called ROWSTAT that is used internally as part of a cursor.  Where we see the real differences are if we scroll the results to the right as shown below:

sp_describe6

Here you can see in the red rectangle that the 2nd and third calls to the sp_describe_first_result_set stored procedure return more details about the database, schema, table, and column names than the first call did.

Views

The sp_describe_first_result_set stored procedure can be used to examine the result from a query that is selecting from a view in a simliar fashion to what we covered here selecting from a table.

Summary

The sp_describe_first_result_set stored procedure can be called with a single parameter of just a query. In this case it defaults to no parameters, and to no additional information. The sp_describe_first_result_set stored procedure can also be called with two parameters, where the second parameter is the parameterized query information, which will return the similar results to the first call.

An optional third parameter called browse_information_mode can be used to to get additional details in the result set.

Just another new feature in SQL Server 2012.