Archive

Posts Tagged ‘SQL Server 2012’

SQL Server 2012 (a.k.a Denali) – New feature | FileTables

February 28, 2012 7 comments

In my [previous posts] I discussed about new features of Denali, now SQL Server 2012.

Here, in this post I’ll talk about a new feature that I explored recently, when I was working with creating tables in SQL Server 2012. I observed a new item in object explorer that took my attention and created eagerness to explore it, and that new thing is FileTables.

As per MS BOL, The new FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications. The FileTable feature builds on top of SQL Server FILESTREAM technology.
 

–> Let’s see how can we use this new feature by a simple example below:

USE [master]
GO

-- Create a new Database with Filestream enabled:
CREATE DATABASE [newFileStreamDB]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'newFileStreamDB', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\DATA\newFileStreamDB.mdf' , 
	SIZE = 5120KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024KB 
	),
 FILEGROUP newFileStreamGroup CONTAINS FILESTREAM DEFAULT (
	NAME = newFileStreamGroupFiles,
	FILENAME= 'D:\SQL_Server2012\FileTables\Files', 
	MAXSIZE = UNLIMITED 
	)
 LOG ON ( 
	NAME = N'newFileStreamDB_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\DATA\newFileStreamDB_log.ldf' , 
	SIZE = 1024KB , 
	MAXSIZE = 2048GB , 
	FILEGROWTH = 10% 
	)
 WITH FILESTREAM (
	NON_TRANSACTED_ACCESS = FULL,
	DIRECTORY_NAME = N'FileTables' 
	)
GO

-- Use the new Database:
USE [newFileStreamDB]
GO

-- Creating a new FileTable
CREATE TABLE [dbo].[firstFileTable] 
	AS FILETABLE ON [PRIMARY] 
	FILESTREAM_ON [newFileStreamGroup]
WITH (
	FILETABLE_DIRECTORY = N'myfirstFileTable', 
	FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AI
)
GO

 

So, what all objects it creates under this new FileTable:

1. The table is created with following predefined Columns:

2. with Indexes:

3. with Constraints:

4. It also creates a FK reference key:

 

–> Now, when checking under Object Explorer under Tables you won’t see any table (even after refreshing it). But there is a new folder named FileTables, expand it and you can see this table there, shown below:


 

–> Right click on it and select ‘Explore FileTable Directory’, it will open the folder as shown below:

Here, I’ve manually created 3 files, 1 notepad, 1 powerpoint and 1 word doc. You can also copy files from other locations and paste/drop them here. As you paste/drop files here, SQL Server internally updates the [firstFileTable] file table.
 

–> We can check the table by simply issuing SELECT statement on the table:

SELECT * FROM dbo.firstFileTable


-- Final Cleanup
DROP TABLE dbo.firstFileTable
GO

 

I liked this new feature very much as this will ease the work and reduce the overhead of maintaining files with databases.
 

In my [next post] you can check how you to use Full Text Search with files stored in FileTables.

Categories: SQL Server 2012 Tags: , ,

SQL Server 2012 (DENALI) TSQL – New Feature in SSMS – Surround With

August 5, 2011 Leave a comment

I just stumbled on this new exciting feature of SSMS Denali while trying my hands querying on this new environment.

Here is a scenario: You’ve  created a query or a logic with multiple lines of SQL statements. Now you want this to be validated by an IF-ELSE condition or iterate it in a WHILE loop. You will move the cursor to the top and make extra spaces for  inserting IF/WHILE {condition} BEGIN, go to the Bottom and terminate the condition/loop with END statement.

But if you are on Denali you won’t have to do this and type anything. Just a few mouse click would do for you, let’s see how:

1. Select the SQL statement or logic you want to Surround with, then right click on it, select the “Surround With…” option:

Denali SSMS - Surround With 01

2. Now double click on any 3 of your choice, here I selected the “If” condition:

Denali SSMS - Surround With 02

3. The Editor will automatically write the “IF” syntax for you:

Denali SSMS - Surround With 03

4. You just need to add the condition within brackets as per your requirement.

… nice feature introduced by Microsoft.

Categories: SQL Server 2012 Tags: , ,

SQL Server 2012 (DENALI) TSQL – New feature | OFFSET FETCH Clause (for paging/pagination)

July 28, 2011 3 comments

As per MS BOL, the new Denali’s OFFSET-FETCH Clause provides an option to fetch only a window or page of a fix set of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

This was the most awated feature for the frontend/GUI developers to display volumnous data in a small grid, page by page. Prior to this they used to devise not so complex but a bit complex SQL logic to display records page by page. Introduction of this feature has limited the complex logic to a few lines of single SQL statement which is much more optimized.

Let’s see how can we use this feature:

--// Example #1:

-- This will skip first 100 records and show next 20 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 100 ROWS
FETCH NEXT 20 ROWS ONLY;
GO

--// Example #2:

-- Start from first record and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Skip first 10 records and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 10 ROWS -- To show next page of 10 records, just change the offset by adding the page size, i.e 10.
FETCH NEXT 10 ROWS ONLY;
GO

--// Example #3:

-- To use this in front-end, the above logic can be made dynamic by using a few variables, like:
DECLARE @StartRec INT
DECLARE @PageSize INT
DECLARE @RecordEnd INT

SET @StartRec = 0
SET @PageSize = 10
SET @RecordEnd = @PageSize

WHILE @RecordEnd <> 0 -- I'm using WHILE loop to simulate it here
BEGIN
	SELECT BusinessEntityID,  LoginID, JobTitle
	FROM HumanResources.Employee
	ORDER BY BusinessEntityID
	OFFSET @StartRec ROWS
	FETCH NEXT @PageSize ROWS ONLY

	SET @RecordEnd = @@ROWCOUNT -- Exit loop at 0

	SET @StartRec = @StartRec + @PageSize
END
GO

Rules to use OFFSET FETCH (via MSDN):
1. ORDER BY is mandatory to use OFFSET and FETCH clause.
2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
3. TOP cannot be combined with OFFSET and FETCH in the same query expression.
4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

More on MSDN, link: http://msdn.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx

Categories: SQL Server 2012 Tags: , , , ,

SQL Server 2012 (DENALI) TSQL – New features | Built-in Functions

July 27, 2011 6 comments

Here is a list of some new function introduced in SQL Server Denali, some examples are taken from MSDN and here is the link for more details.
 

Logical Functions:

-- CHOOSE()
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; -- Developer
SELECT CHOOSE ( 2, 'Manoj', 'Saurabh', 'Andy', 'Dave' ) AS Result; -- Saurabh
GO

-- IIF()
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result; -- TRUE
GO

Note: The IIF() function was important for the developers that migrated from ACCESS to SQL Server. Introduction of these 2 functions will make their life easy.

Check the video Tutorial on CHOOSE() and IIF() functions.
 

Conversion Functions:

-- PARSE()
SELECT PARSE ('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;
SELECT PARSE ('€345,98' AS money USING 'de-DE') AS Result;
GO

-- TRY_CAST()
SELECT
CASE WHEN TRY_CAST ('test' as float) IS NULL
	THEN 'Cast failed'
	ELSE 'Cast succeeded'
END AS Result;
GO

-- TRY_CONVERT()
SELECT
CASE WHEN TRY_CONVERT (float,'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO

-- TRY_PARSE()
SELECT
CASE WHEN TRY_PARSE ('Aragorn' AS decimal, 'sr-Latn-CS') IS NULL
THEN 'True'
ELSE 'False'
END
AS Result;
GO

Note: The TRY_CAST(), TRY_CONVERT() & TRY_PARSE() functions added exception handling to the existing CAST(), CONVERT() and new PARSE() function respectively. Now developers can fully rely on these 2 functions while type converting values and defaulting them to something else on any execption.

 

Analytic Functions:

USE AdventureWorks2008R2;
GO

-- FIRST_VALUE() and LAST_VALUE()
SELECT 
	Name, ListPrice, ProductSubcategoryID,
	FIRST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive,
	LAST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product;

-- LAG()
SELECT 
	BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
	LAG (SalesQuota, 1, 0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

-- LEAD()
SELECT 
	TerritoryName, BusinessEntityID, SalesYTD,
	LEAD (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;

Note: The LAG() and LEAD() functions are already in Oracle and by introducing them in MS SQL Server’s Denali version is very much welcomed by TSQL developers. Plus the addition of FIRST_VALUE(), LAST_VALUE(), PERCENTILE_XXX(), etc will really add value and provide zeal to programmers in TSQL development, just like Ranking functions and Cube/Rollup did in 2005(08).

 

Some other new functions introduced are:
– CUME_DIST()
– PERCENTILE_CONT()
– PERCENTILE_DISC()
– PERCENT_RANK()
 

More details here on MS BOL: http://technet.microsoft.com/en-us/library/hh213234%28SQL.110%29.aspx

Categories: SQL Server 2012 Tags: , , , , , , , ,

SQL Server 2012 (DENALI) TSQL – New Feature | SEQUENCES

July 26, 2011 2 comments

As per MS BOL a Sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested.

--// Create a simple Sequence
CREATE SEQUENCE CountBy1
	START WITH 1
	INCREMENT BY 1 ;
GO

-- Assign new ID sorted by First Name:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy1 OVER(ORDER BY FirstName) as New_ID
FROM Person.Person;
GO
-- If you run the above query again, it won't start from 1 but starts after the max value it ended in its first run.

--// Create a Cyclic Sequence
CREATE SEQUENCE CountBy5 AS tinyint
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 5
	CYCLE ;
GO

-- Assign Persons to 5 different sections:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy5 OVER(ORDER BY FirstName, LastName) as New_Section
FROM Person.Person;
GO

--// Final cleanup
DROP SEQUENCE CountBy1
DROP SEQUENCE CountBy5
GO

More on MSDN: http://technet.microsoft.com/en-us/library/ff878058%28SQL.110%29.aspx

Categories: SQL Server 2012 Tags: , , , , ,