Title

XQuery Lab 65 – Restructuring an XML document using FLWOR

Fri, 09 Dec 2011 13:33:00 GMT

I found this question in this SSC forum thread requesting help to shape the XML document. Here is the input XML document.

<Root>
	<Student>Jhon </Student>
	<Student> Luka </Student>
	<Post>1</Post>
	<Post>2</Post>
</Root>

The expected output is as follows.

<Root>
  <Students>
    <Student>Jhon </Student>
    <Student> Luka </Student>
  </Students>
  <Posts>
    <Post>1</Post>
    <Post>2</Post>
  </Posts>
</Root>

This type of formatting can be achieved through a simple FLWOR operation.

DECLARE @x XML = '
<Root>
	<Student>Jhon </Student>
	<Student> Luka </Student>
	<Post>1</Post>
	<Post>2</Post>
</Root>'

SELECT @x.query ('
	for $i in (Root)
	let $s := $i/Student
	let $p := $i/Post
	return
		<Root>
			<Students> {$s} </Students>
			<Posts>{$p}</Posts>
		</Root>
')

/*
Produces: 
<Root>
  <Students>
    <Student>Jhon </Student>
    <Student> Luka </Student>
  </Students>
  <Posts>
    <Post>1</Post>
    <Post>2</Post>
  </Posts>
</Root>
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

SQL Server book from Pinal Dave

Mon, 08 Aug 2011 09:52:00 GMT

Pinal Dave is an inspiration for a large number of SQL Server professionals all over the world. He is a great teacher who knows how to explain any complex stuff in very simple words and examples that everyone understands. That is the reason why his blog is considered to be one of the best source of reference for SQL Server related stuff.

Pinal’s blog has helped thousands of people learn SQL Server. Very often I end up on his blog when searching for some SQL Server stuff online. He is a great teacher, who is always willing to travel to any extend if requested to present in a SQL Server group. He has done a large number of SQL Server training sessions and workshops all over India and US.

I was not surprised when Microsoft picked him and gave him the role of SQL Server Evangelist. They are known for acquiring the ‘best’, no matter whether it is a company, product or a person. Vinod Kumar, Microsoft evangelist and a mentor and role model for many of us, has already set up a very high standard for anyone following his foot steps. And then the experts at MS picked the right person and put him in the right place for the benefit or the entire SQL Server community in India. That gives us a lot of reasons to smile.

SQL Programming Joes 2 Pros

Pinal has been teaching the SQL Server community in a number of ways. In addition to regular blog posts, he has been contributing a lot in the form of Web casts, Presentations, workshops etc. And finally, to add sweet to the dessert, he announced a SQL Server book which has made many of us quite happy and proud. I am very excited and waiting for my copy of the book, which he promised is on the way!

Get your free copy of “SQL Programming Joes 2 Pros”

Pinal has announced a contest on his blog where he will give away 70 copies of his book for free. He is writing a 35 days series of blog posts where he shares a number of SQL Server tips and tricks that you can apply in your day-to-day SQL Server life. To get a copy of the book, all you need to do is to read his posts carefully and answer a few simple questions. Once done, wait for the postman or courier boy to knock your door and deliver you a printed copy of the book.

I would like to invite everyone to read the series. Whether you win a free copy of the book or not, it is guaranteed that you will certainly learn quite a lot from the series. Thank you Pinal for writing this book and giving us yet another reason to be proud of your achievements!

XQuery Lab 64 – Reading values from an XML column

Mon, 18 Jul 2011 02:19:00 GMT

I got a question in my personal forum this morning requesting help to read values from an XML column. My first reaction was “Well, there is an XQuery lab demonstrating this!”. However, after reviewing the existing XQuery labs, I realized there are no posts demonstrating this.

Here is a simple example that demonstrates how to read values from an XML column.

DECLARE @t TABLE (
	ID INT IDENTITY,
	Data XML
)

INSERT INTO @t (Data)
SELECT '<employee name="Jacob" />' UNION ALL
SELECT '<employee name="Michael" />'

SELECT
	x.value('@Name[1]', 'VARCHAR(20)') AS Name
FROM @t t
CROSS APPLY Data.nodes('/employee') a(x)
/*
Name
--------------------
Jacob
Michael
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

SQL Server–Partitioning FILESTREAM data with RANGE LEFT does not work

Sun, 12 Jun 2011 20:15:00 GMT

One of the problems I came across while writing the partitioning chapter for my FILESTREAM book is that the partitioning does not work correctly with RANGE LEFT. It works as expected when using RANGE RIGHT. Something is wrong with RANGE LEFT and the problem exists in SQL Server 2008, R2 and Denali CTP1.

I created a repro script and wanted to submit a connect bug. However, the connect page does not allow me to post scripts longer than 2000 characters. So I thought of posting the script here and add a link to this post in the connect item. Here is the repro script for those of you who are interested to try it, and here is the connect item I submitted.

-- -----------------------------------------------
-- Create the database
-- -----------------------------------------------
USE master
GO

IF DB_ID('NorthPole') IS NOT NULL 
	DROP DATABASE NorthPole 
GO

CREATE DATABASE NorthPole ON
PRIMARY ( 
    NAME = NorthPoleData1, 
    FILENAME = 'C:\Demos\Data\NorthPoleData1.mdf'),
FILEGROUP NorthPoleDB2(
    NAME = NorthPoleData2, 
    FILENAME = 'C:\Demos\Data\NorthPoleData2.ndf'),
FILEGROUP NorthPoleFS1 CONTAINS FILESTREAM DEFAULT( 
    NAME = NorthPoleFS1,
    FILENAME = 'C:\Demos\FS\NorthPoleFS1'),
FILEGROUP NorthPoleFS2 CONTAINS FILESTREAM( 
    NAME = NorthPoleFS2,
    FILENAME = 'C:\Demos\FS\NorthPoleFS2')
LOG ON ( 
    NAME = NorthPoleLOG,
    FILENAME = 'C:\Demos\Data\NorthPoleLOG.ldf')
GO

-- -----------------------------------------------
-- Create Partition Function and Scheme
-- -----------------------------------------------
USE NorthPole 
GO

CREATE PARTITION FUNCTION NPPartFN (INT) AS 
RANGE LEFT FOR VALUES (3)

CREATE PARTITION SCHEME NPPartDBSch AS 
PARTITION NPPartFN TO([PRIMARY],[NorthPoleDB2]) 
GO

CREATE PARTITION SCHEME NPPartFSSch AS 
PARTITION NPPartFN TO(NorthPoleFS1, NorthPoleFS2) 
GO

-- -----------------------------------------------
-- Create Table
-- -----------------------------------------------
CREATE TABLE [dbo].[Items](
	[ItemID] [int] IDENTITY(1,1) PRIMARY KEY ON NPPartDBSch(ItemID),
	[ItemGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL 
		UNIQUE ON [PRIMARY],
	[ItemNumber] [varchar](20) NULL,
	[ItemDescription] [varchar](50) NULL,
	[ItemImage] [varbinary](max) FILESTREAM  NULL
) ON NPPartDBSch(ItemID)
FILESTREAM_ON NPPartFSSch

-- -----------------------------------------------
-- Insert 6 Rows. 1-3 will go to FS1 and 4-6 will
--   go to FS2
-- -----------------------------------------------
INSERT INTO Items(itemguid, itemnumber, itemdescription, ItemImage)
SELECT NEWID(), 'ITM001', 'Item 1', CAST(1 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM002', 'Item 2', CAST(2 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM003', 'Item 3', CAST(3 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM004', 'Item 4', CAST(4 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM005', 'Item 5', CAST(5 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM006', 'Item 6', CAST(6 AS VARBINARY(MAX))


-- -----------------------------------------------
-- Add a new file group (DB and FS)
-- -----------------------------------------------
ALTER DATABASE NorthPole 
ADD FILEGROUP NorthPoleFS3 CONTAINS FILESTREAM

ALTER database NorthPole 
ADD FILE 
( 
    NAME = 'NorthPoleFS3', 
    FILENAME = 'C:\Demos\FS\NorthPoleFS3' 
) 
TO FILEGROUP NorthPoleFS3

ALTER DATABASE NorthPole 
ADD FILEGROUP NorthPoleDB3

ALTER database NorthPole 
ADD FILE 
( 
    NAME = 'NorthPoleDB3', 
    FILENAME = 'C:\Demos\Data\NorthPoleData3.ndf' 
) 
TO FILEGROUP NorthPoleDB3


-- -----------------------------------------------
-- Insert 3 more records. This will go to FS2
-- -----------------------------------------------
INSERT INTO Items(itemguid, itemnumber, itemdescription, ItemImage)
SELECT NEWID(), 'ITM007', 'Item 7', CAST(7 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM008', 'Item 8', CAST(8 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM009', 'Item 9', CAST(9 AS VARBINARY(MAX))


-- -----------------------------------------------
-- Switch partitions. Alter the partition function
--   so that rows 7 to 9 will go to the new 
--   file group
-- -----------------------------------------------
ALTER PARTITION SCHEME NPPartFSSch  
NEXT USED NorthPoleFS3

ALTER PARTITION SCHEME NPPartDBSch 
NEXT USED NorthPoleDB3

ALTER PARTITION FUNCTION NPPartFN() 
SPLIT RANGE (6);

-- -----------------------------------------------
-- After the above, a new FILESTREAM folder is
--   created in FS1. No data goes to FS3
-- -----------------------------------------------

Getting started with SQL Server Wait Types

Wed, 16 Mar 2011 13:28:00 GMT

SQL Server Wait Types are important factors to consider while analyzing performance problems of SQL Server databases. If you notice a particular wait type is occurring too often or for too long, it indicates a problem in most cases.

There are several dozens of different wait types and it is quite important that every Database Administrator understand them well. Luckily, star blogger Pinal Dave (@pinaldave | blog.sqlauthority.com) has written a long series of blog posts on various wait types. What I found very interesting about this series is the way each wait type is explained. Just like every other post on his blog, every post in this series is quite easy to understand and straight to the point.

I strongly believe that this series is a ‘must-read’ piece for every SQL Server Database Administrator and Developer. So start right now: SQL SERVER – Summary of Month – Wait Type

T-SQL Tuesday #016 - Summarizing data using GROUPING SETS()

Tue, 08 Mar 2011 05:21:00 GMT

I have been watching the TSQL Tuesday Blog Parties for quite some time and this is the first time I am participating in it. T-SQL Tuesday #16 is hosted by Jes Schultz Borland and the topic is Aggregation.

Aggregating and summarizing data is a common business requirement. Very often we come across requirements to calculate the total values presented in one or more columns on a web page. Some times the requirement may be bit more complicated such as to calculate multiple levels of sub-totals along with a grand total.

Usually this type of tasks are efficiently done by reporting tools. Most reporting tools are equipped with the functionality to partition and summarize data based on custom user requirements. Very often I see questions asking for help to write TSQL queries that returns data in specific shape and format so that the client application can directly display the information on a web page. My first answer used to be an advice to handle this type of requirements in the presentation layer of the application, until I came across a real requirement myself, a few years back.

The application had a few web pages which display various sales data in a grid control with multiple levels of sub-totals and totals. The web page had a huge chunk of code written to correctly calculate the sub totals of each levels and display them at the right position within the grid control. The page was very slow and the task was to optimize it. Moving the calculation logic into the database helped to improve performance tremendously.

There are a number of TSQL keywords available that we can use to produce sub-totals and totals along with the query results. The simplest of them is COMPUTE BY which produces a second result set with the totals of the query as given in this example. But a second result set may not be desirable on certain situations. We might need a single result set with a total row. This can be easily achieved by using using WITH ROLLUP or WITH CUBE as demonstrated here, here and here.

Enter GROUPING SETS

SQL Server 2008 introduced a new function GROUPING SETS() which gives more control over the total rows generated along with the query result. We will see a few examples to understand this. Run the following script to generate the sample table for the examples we will use in this post.

IF OBJECT_ID('Orders') IS NOT NULL DROP TABLE Orders
GO
CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATETIME,
    CustomerName VARCHAR(20),
    ItemName VARCHAR(20),
    Quantity INT,
    PricePerCase MONEY )
GO

INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2007-08-01', 'Jacob', 'Item 1', 10, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2007-08-01', 'Jacob', 'Item 2', 12, 15.0
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 2, '2008-08-02', 'Jacob', 'Item 1', 15, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 2, '2008-08-02', 'Jacob', 'Item 2', 20, 15.0
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2008-08-01', 'Mike', 'Item 1', 6, 12.5
INSERT INTO Orders( OrderID, OrderDate, CustomerName, ItemName, Quantity, PricePerCase )
    SELECT 1, '2008-08-01', 'Mike', 'Item 2', 4, 11.0

Within the GROUPING SETS() function, you can define set of columns on which you need a total row to be generated. The following example shows a query using GROUPING SETS(). It does not generate any total/subtotal rows and produces the same result as a GROUP BY clause could generate.

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName))
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Mike                 Item 1               75.00
Jacob                Item 2               480.00
Mike                 Item 2               44.00
*/

Generating only Subtotals and Grand Total Row

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((ItemName), (CustomerName),())
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                All Items            792.50
Mike                 All Items            119.00
All Customers        All Items            911.50
All Customers        Item 1               387.50
All Customers        Item 2               524.00
*/

Generating a Total Row per Customer

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (CustomerName))
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Jacob                Item 2               480.00
Jacob                All Items            792.50
Mike                 Item 1               75.00
Mike                 Item 2               44.00
Mike                 All Items            119.00
*/

Generating a Total Row per Item

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName))
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Mike                 Item 1               75.00
All Customers        Item 1               387.50
Jacob                Item 2               480.00
Mike                 Item 2               44.00
All Customers        Item 2               524.00
*/

Generating a Grand Total row

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), ())
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Jacob                Item 2               480.00
Mike                 Item 1               75.00
Mike                 Item 2               44.00
All Customers        All Items            911.50
*/

Generating Subtotal rows for Customers and a Grand Total row

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (CustomerName), ())
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Jacob                Item 2               480.00
Jacob                All Items            792.50
Mike                 Item 1               75.00
Mike                 Item 2               44.00
Mike                 All Items            119.00
All Customers        All Items            911.50
*/

Generating Subtotal rows for Items and a Grand Total Row

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), ())
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Mike                 Item 1               75.00
All Customers        Item 1               387.50
Jacob                Item 2               480.00
Mike                 Item 2               44.00
All Customers        Item 2               524.00
All Customers        All Items            911.50
*/

Generating Sub total rows for Customers and Items

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), (CustomerName))
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Mike                 Item 1               75.00
All Customers        Item 1               387.50
Jacob                Item 2               480.00
Mike                 Item 2               44.00
All Customers        Item 2               524.00
Jacob                All Items            792.50
Mike                 All Items            119.00
*/

Generating Sub total rows for Customers and Items along with a Grand Total Row

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), (CustomerName),())
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 1               312.50
Mike                 Item 1               75.00
All Customers        Item 1               387.50
Jacob                Item 2               480.00
Mike                 Item 2               44.00
All Customers        Item 2               524.00
All Customers        All Items            911.50
Jacob                All Items            792.50
Mike                 All Items            119.00
*/

Ordering Results

The above result set gives us sub totals by customer and by item and again a grand total row. However, the order of the rows is not good. If you want to retrieve the results in a specific order, you need to specify an ordering clause. The following query produces the results ordered by the grouping level.

SELECT
    CASE 
        WHEN GROUPING(CustomerName) = 1 THEN 'All Customers' 
        ELSE CustomerName 
    END AS CustomerName,
    CASE WHEN GROUPING(ItemName) = 1 THEN 'All Items'
        ELSE ItemName
    END AS ItemName,
    SUM(Quantity * PricePerCase) AS Amount
FROM Orders
GROUP BY GROUPING SETS((CustomerName, ItemName), (ItemName), (CustomerName),())
ORDER BY GROUPING(CustomerName), GROUPING(ItemName)
/*
CustomerName         ItemName             Amount
-------------------- -------------------- ---------------------
Jacob                Item 2               480.00
Mike                 Item 2               44.00
Jacob                Item 1               312.50
Mike                 Item 1               75.00
Jacob                All Items            792.50
Mike                 All Items            119.00
All Customers        Item 1               387.50
All Customers        Item 2               524.00
All Customers        All Items            911.50
*/

SQL Server – TSQL – Quiz 2011 is here, your chance to win an Apple iPad!

Sun, 06 Mar 2011 17:45:00 GMT

We are back with the first SQL Server Quiz of year 2011 – TSQL Quiz 2011. The quiz started on 1st March and will run until 31 March 2011. There are 31 questions in this series and a question is published every day.

Congratulations to the Winners of SQL Server Quiz 2010

We did a similar quiz a few months ago and it was a great experience. A large number of SQL Server enthusiasts participated actively in the discussions. We take this opportunity to congratulate the winners of SQL Server Quiz 2010.

	Mike Lewis	Apple iPad (Sponsored by Redgate)
	Docker	Amazon Kindle (Sponsored by Idera)
	IGO	iPod Nano (Sponsored by Quest)
	Sivaprasad S - SIVA	iPod Nano (Sponsored by Quest)
	Abi Chapagai	iPod Nano (Sponsored by Quest)
	Nupur Dave	iPod Nano (Sponsored by Quest)
	Ramireddy	iPod Nano (Sponsored by Quest)

In addition to the winners listed above, 44 other people won free software licenses worth over 17,000$.

Inviting you to TSQL Quiz 2011

TSQL Quiz 2011 started on 1st of March and will run until March 31, 2011. Each question will stay open for 30 days and you can post an answer within 30 days of the publication date. After 30 days the quiz master will evaluate your answers and will assign a score to each answer (between 0 and 10). The winners will be decided based on the total score you get from all the 31 questions.

I invite all of you to participate in TSQL Quiz 2011. The winner will get an Apple iPad from Redgate Software.

Happy Birthday to TSQL Challenges and its younger sibling!

Sun, 27 Feb 2011 23:01:00 GMT

TSQL Challenges – 2nd Birthday Party!

TSQL Challenges is turning two years old today. We did the first TSQL Challenge two years ago - on 27th February 2009. In two years, TSQL Challenge has grown up with the help of several volunteers and TSQL enthusiasts who stepped in and selflessly helped. I have written a detailed post about this journey in one of my previous posts. It has been a wonderful journey with 50 challenges, 700 unique participants and over 3,500 solutions.

We invite all of you to the Birthday Party on beyondrelational.com. The first course is a re-launched version of TSQL Challenge 1 which comes with a variety of birds, grains and fruits. You need to feed the birds with correct combination of food baskets before you move to your own dinner table. To move to the second course, you need to solve TSQL Challenge 1. To cater to the tastes all of you from different parts of the world, we have made it available in English, Korean, Chinese, Italian, French and Portuguese flavors.

Meet the new born baby – PLSQL Challenges

Many people from the ORACLE world suggested that it is a good idea to re-run these challenges in PLSQL as well. This will help us to learn several interesting facts about other products. For example, such a comparison will tell us how easily you can solve a problem in ORACLE which is quite hard in SQL Server. Similarly, it will be quite interesting to know that a problem that is hard to solve in PLSQL can be easily solved in TSQL.

Keeping this in mind, we have been working on bringing up the PLSQL version of TSQL Challenges. You can find the first PLSQL Challenge here. I am quite hopeful that many of you will step forward and help this infant to grow up just like many of you did for TSQL Challenges. I look forward to hear your comments, suggestion and feed back through the PLSQL Challenge Discussion Thread.

What is SQL Server Juneau?

Mon, 07 Feb 2011 10:44:00 GMT

“Juneau” is the code name given to the new SQL Server development environment which is based on Visual Studio shell. It is officially named “SQL Server Developer Tools” (SSDT) which is expected to provide a wide range of functionalities that are currently not available with SQL Server Management Studio (SSMS).

The current version of SQL Server Denali is CTP1 which does not include Juneau. It is expected to be available with the next CTP release (CTP2). See What is new in SQL Server Denali for a detailed list of features available in the next SQL Server Version (SQL11).

One of the interesting features that will be available in Juneau is more support for SQL Azure Development. A few other features that I found interesting are:

Supports connected and offline database development
Integrated application and database development
Entity Framework Integration
WPF based Text Editor

SSMS is my primary (and favorite) development environment and I am not sure whether I would ever like to switch to a different tool. I hope Juneau will not replace SSMS.

What is SQL Server Crescent?

Fri, 04 Feb 2011 15:52:00 GMT

Project Crescent is a new data visualization tool that will be part of the next version of SQL Server – code named Denali.

“Crescent” is the code name given to the new reporting/visualization tools and it looks like the name may change by the time the product is released. Crescent is expected to revolutionize ad-hoc reporting requirements by providing users new user experience and allow them to present the data in new and exciting ways.

Experts who got early access to Crescent says that Crescent expands the Self Service BI capabilities currently available with PowerPivot for Excel. Crescent provides drag-and-drop ad-hoc reporting capability. Crescent is not available with the current release of Denali (CTP1) but is expected to be part of CTP2. To see the list of new features available in Denali, visit: http://beyondrelational.com/whatisnew/sqlserver/denali/

What is more exciting is to know that Crescent is entirely built on Silverlight.

SSRS – How to find all the reports that use one or more given columns?

Fri, 21 Jan 2011 10:28:00 GMT

In the past I have published a few queries that allows you to query SQL Server Reporting Services database – to retrieve specific information about various reports deployed in the Reporting Server Instance.

Recently, I got a question in my ASK forum who wanted to identify all the reports that use a specific column. I thought of writing a blog post about it so that it can be added to the SSRS query series and help other people with similar problems.

The RDL file that gets generated when you design a report, is an XML document. When you deploy the report on Reporting Server, the RDL file (which is an XML document) gets inserted into the Report Server database (named ReportServer by default). You can query this database to obtain the XML content of all the reports and then use XQuery to read specific information about each report.

The following query demonstrates how to read all the columns used by all the reports in the given SQL Server Reporting Server instance and search for a specific column. The query given below returns the report name and data source name of all reports that references a specified column name – which is passed as a parameter.

DECLARE @FieldToSearch VARCHAR(100)
SELECT @FieldToSearch = 'PatientNumber'

;WITH XMLNAMESPACES (
	DEFAULT 
	'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
	'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
	name,
	d.value('@Name[1]', 'VARCHAR(50)') AS DataSetName,
	df.value('@Name[1]', 'VARCHAR(50)') AS ReportFieldName,
	df.value('DataField[1]', 'VARCHAR(50)') AS DataFieldName
FROM (
	select name, 
	CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
	from ReportServer.dbo.Catalog
	WHERE Type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(d)
CROSS APPLY d.nodes('Fields/Field') f(df)
WHERE df.exist('DataField[ . = sql:variable("@FieldToSearch")]') = 1
/*
Name          DataSetName   ReportFieldName  DataFieldName
------------  ------------  ---------------  ---------------
Patient List  DSPatients    PatientID        PatientNumber
AR Details    DSPatientsAR  PatientNumber    PatientNumber
*/

The code given above is tested with SSRS 2005 SP2. It is expected to work on other versions, but I have not tested. If you find a problem running this query on other SSRS versions, let me know.

I would recommend you read the following posts as well:

Inviting everyone to participate in the .NET Quiz! An Apple iPad Waiting

Sat, 01 Jan 2011 11:23:00 GMT

After the recent SQL Server Quiz we got several requests to start a .NET quiz that follows the similar model. We have been working hard for the last couple of months and finally the .NET Quiz is Ready.

The SQL Server Quiz was our first attempt in this area and we came to know about several stuff we are missing. One of the serious shortcoming was the user interface on the web pages. We have been working hard on rebuilding the quiz module and released a new version that goes live with the .NET Quiz.

I invite all of you to take part in the .NET Quiz. The winner gets an Apple iPad (sponsored by Redgate).

Storing TSQL Queries in a table without losing the formatting

Fri, 17 Dec 2010 14:49:00 GMT

One of the applications we worked on recently had to deal with storing TSQL queries in a table and executing them based on some business logic. It was not a very complicated project, but the development team had a tough time with the formatting of the queries stored in the table. This was a huge problem when the queries were to be modified.

The Problem

The developer writes a well formatted TSQL code and stores it into the table. Later on when a change request comes, he retrieves the query text from the table and finds that all the formatting is lost.

Why does it happen?

Well, the formatting is not really lost. When the developer stores a well formatted query into the VARCHAR(MAX)/NVARCHAR(MAX) column of a table, the formatting is also stored. The problem is the way he retrieves it.

The common way of retrieving the query text is to run a SELECT query which will display the result in a grid view. The grid view does not maintain the special characters (line feed, carriage return). That is the reason why you are loosing the formatting.

An immediate workaround is to change the output to text and run the SELECT query. This works if the TSQL queries stored in the table are small. If the queries are large, then it might truncate the queries.

Workaround

In this post, let us see a workaround that shows how to retrieve the query text without loosing the formatting. To see this in action, let us start by creating a table to store the queries.

USE tempdb
GO

IF OBJECT_ID('BRQueries','U') IS NOT NULL BEGIN
	DROP TABLE BRQueries
END

CREATE TABLE BRQueries(
	QueryID INT, 
	QueryText VARCHAR(MAX)
)

Next, let us write a well formed query.

SELECT 
   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
	t.object_id = c.object_id

Let us now insert this query into the table

INSERT INTO BRQueries (QueryID, QueryText)
SELECT 1, 
'
SELECT 
   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
	t.object_id = c.object_id'

Next, Let us run a SELECT query to retrieve the query text. If we use 'result to grid', we will loose the formatting. If we use 'result to text' we will get the correctly formatted text in this case. However, if the query is longer it might get truncated. If the table has several other columns and rows, the output will be completely messed up.

A workaround is to use to use the XML function processing-instruction(). Run the following query.

SELECT
	QueryID,
	(
		SELECT QueryText AS 'processing-instruction(q)' 
		FROM BRQueries b
		WHERE b.QueryID = a.QueryID
		FOR XML PATH(''), TYPE
	) AS Query
FROM BRQueries a

Take the result of the query into a grid either by pressing CTRL+D or from the toolbar and you will see the result as follows:

Click on the desired row and it will open up the query text with the original formatting.

<?q 
SELECT 
   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
	t.object_id = c.object_id
?>

You can strip of the XML tags from the top and bottom of the query text and go ahead with editing. Well, this is not a 'great' method but a quick and dirty trick that might come handy at times.

XQuery Lab 63 – Deleting empty elements from an XML document

Tue, 14 Dec 2010 17:04:00 GMT

I saw this question on the forum today and after writing an example that demonstrates this, I thought of including it as part of the XQuery Labs.

Here is the sample XML we need to process.

<userdata pageid="page9" annotationSetId="80">
    <notes />
    <notes>
      <note id="332E7A17-3E57-3540-CC28-DFA1C68A7802" 
		x1="297.25" x2="297.25" y1="447.85" y2="447.85" 
		lastModified="2010-12-13T12:08:32Z" type="mynotes">
      </note>
    </notes>
    <notes />
    <notes />
    <page_hotspots />
  </userdata>

The task is to delete all notes elements that are empty and produce the following output.

<userdata pageid="page9" annotationSetId="80">
  <notes>
    <note id="332E7A17-3E57-3540-CC28-DFA1C68A7802" 
		x1="297.25" x2="297.25" y1="447.85" y2="447.85" 
		lastModified="2010-12-13T12:08:32Z" type="mynotes" />
  </notes>
  <page_hotspots />
</userdata>

Here is the TSQL code that deletes the empty elements using an XQuery expression.

DECLARE @x XML
SELECT @x = '
<userdata pageid="page9" annotationSetId="80">
    <notes />
    <notes>
      <note id="332E7A17-3E57-3540-CC28-DFA1C68A7802" 
		x1="297.25" x2="297.25" y1="447.85" y2="447.85" 
		lastModified="2010-12-13T12:08:32Z" type="mynotes">
      </note>
    </notes>
    <notes />
    <notes />
    <page_hotspots />
  </userdata>'

SET @x.modify('
	delete /userdata/notes[empty(./*)]
')

SELECT @x

/*
<userdata pageid="page9" annotationSetId="80">
  <notes>
    <note id="332E7A17-3E57-3540-CC28-DFA1C68A7802" 
		x1="297.25" x2="297.25" y1="447.85" y2="447.85" 
		lastModified="2010-12-13T12:08:32Z" type="mynotes" />
  </notes>
  <page_hotspots />
</userdata>
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

SSMS Enhancement in SQL11 (Denali) – Support for Code Snippets

Sat, 20 Nov 2010 08:28:00 GMT

Code snippets allow you to quickly insert code templates into your query window to perform common TSQL tasks. By using a keyword short cut you can invoke the code snippet window and select the desired code snippet. This will copy the pre-defined code template to your query with placeholders which clearly indicates the locations where you need to make changes.

Inserting a code snippet

You can activate the code snippet window by using the keyboard shortcut CTRL+K followed by CTRL+X. Alternatively, you can select the Insert Snippet.. menu item from the Edit >> IntelliSense menu.

Code snippets are organized by categories. Select a category to see the available code snippets under that category. For this example, let us select “Function”.

Let us select “Create Inline Table Function” and SSMS will insert the code required to create a Table Valued Function to your query window at the current cursor position.

After inserting the function template, SSMS will also highlight the placeholders that need to be modified. This will help you to identify the places you need to make changes.

Creating your own code snippet

SSMS comes with some pre-defined code snippets. It is quite easy to add your own code snippets to SSMS and speed up developing commonly used TSQL code. To do this, you need to create a .snippet file containing your code and register it with the Code Snippet Manager. A .snippet file is an XML file that follows a pre-defined schema.

Let us create a TSQL code snippet to generate the basic code for a simple TRY-CATCH block. Here is the code that we will include in the new code snippet.

BEGIN TRY
	-- Your code here
END TRY
BEGIN CATCH
	-- Your error handling here
	;THROW
END CATCH

Here is the code snippet file created for inserting the above code fragment.

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>try-catch</Title>
      <Author>jacob</Author>
      <Description>
      </Description>
      <HelpUrl>
      </HelpUrl>
      <Shortcut>
      </Shortcut>
    </Header>
    <Snippet>
      <Code Language="sql"><![CDATA[
		BEGIN TRY
			-- Your code here
		END TRY
		BEGIN CATCH
			-- Your error handling here
			;THROW
		END CATCH
		]]>
	  </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

There are several tools available that will allow you to create code snippets easily. One such tool is Snippet Designer available at codeplex. This allows you to create code snippets right within visual studio.

Registering a code snippet

After creating a code snippet file, you need to register it with SSMS. This can be done using the “Code Snippet Manager”. Launch the “Code Snippet Manager” from the “Tools” menu.

This will open the code snippet manager window.

The Code Snippet Manager shows all the code snippets registered with SSMS. You can use the Add and Remove buttons to register new code snippets or remove the code snippets already registered.

Let us register the code snippet we created in the previous step. Click on the Add button and select the folder where you have put your code snippet files. After selecting the code snippet folder, all the code snippets available within the selected folder will show up on the Code Snippet Manager.

Testing the newly added code snippet

It is time for us to test the code snippet we just created and registered. Switch back to SSMS query window and press CTRL+K, CTRL+X and it will open the code snippet prompt as given below.

Select the code snippet “try-catch” and it will insert the TSQL code we placed within the snippet file at the current cursor position within the SSMS query window.

The code given above is very basic and used for the demonstration purpose only. You might end up creating more complex code snippets in most real world scenarios. The Snippet Designer tool mentioned earlier is quite capable of creating code snippets for Visual Studio as well as SSMS.

TSQL Error Handling Enhancements in SQL11 – Using the THROW command

Wed, 17 Nov 2010 15:47:00 GMT

One of the most interesting error handling enhancements added in SQL Server 2005 is the support for structured exception handling using TRY / CATCH blocks. This reduced the complexity of error handling in TSQL significantly.

Exception Handling using TRY/CATCH

Exception handling using TRY/CATCH is very much similar to how it is done in other programming languages. You put your code between a BEGIN TRY and END TRY block and write the error handling code within the BEGIN CATCH and END CATCH block. Here is a very basic example that shows structured exception handling in TSQL using TRY/CATCH.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	PRINT 'Error: ' + ERROR_MESSAGE()
	PRINT 'Number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
END CATCH
/*
Error: Divide by zero error encountered.
Number: 8134
*/

Throwing a Database Error to the Application

Most serious applications handle data access errors in the application as well as in the database. Database developers will put their code within TRY/CATCH blocks and log the error details into a table for offline analysis.

Some of the database errors may be relevant to the application developers as well. For example, if an error is raised due to an incorrect value being passed into the stored procedure, the application developers need to know about it. In such a case, the database developer might need to throw the error back the application after doing the database level error logging.

Here is an example that shows how to do this.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	DECLARE @errn INT = ERROR_NUMBER()
	DECLARE @errm VARCHAR(500) = ERROR_MESSAGE()
	RAISERROR('Error Number: %d, Message: %s', 16, 1, @errn, @errm)
END CATCH
/*
Msg 50000, Level 16, State 1, Line 7
Error Number: 8134, Message: Divide by zero error encountered.
*/

Problems with RAISERROR

RAISERROR, like any other function/command has got its own set of capabilities and shortcomings. One relatively small problem I found with RAISERROR is the inability to RAISE the same error after catching it in a CATCH block. In the above example, note that the error caught in the CATCH block was 8134. There is no way to RE-RAISE the same error using RAISERROR. If you attempt to raise an error with Error Number 8134 (or other system error numbers), you will get an error as shown in the following example.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	DECLARE @errn INT = ERROR_NUMBER()
	DECLARE @errm VARCHAR(500) = ERROR_MESSAGE()
	RAISERROR(8134, 16, 1)
END CATCH
/*
Msg 2732, Level 16, State 1, Line 7
Error number 8134 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.
*/

Another problem is that when re-raising an error using RAISERROR, you loose the original error line number. In the example that we saw in the previous section, the actual error occurred on line 2, but when RAISERROR is called, the error line is reset to the current line number (7).

Enter THROW

THROW is a new command introduced in SQL11 (Denali) and it helps you to overcome the limitations we saw above. After performing your error handling within the CATCH block, you can use the THROW command to re-throw the same error to the calling procedure/application.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
	PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR)
	PRINT 'Throwing error...';
	THROW
END CATCH
/*
Error Number: 8134
Error Line: 2
Throwing error...

Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
*/

Note that the same error number and line number is propagated when using the THROW command. It is important to note that the THROW command should be issued as a new batch and therefore you need to terminate the previous batch by placing a semi colon before THROW.

SQL Server 2011 (SQL11 / Denali) – TSQL Enhancements to the ORDER BY clause

Mon, 15 Nov 2010 23:44:00 GMT

In the previous post, we saw the new TSQL syntax introduced in SQL Server 2011 to support server side paging. We saw the new TSQL keywords OFFSET and FETCH NEXT in the previous post. These new keywords are introduced as an extension to the ORDER BY clause. Let us examine them in detail.

OFFSET

OFFSET specifies the number of rows to skip before start retrieving the rows. This is evaluated after sorting the result set using the columns specified in the ORDER BY clause.

What is very interesting about OFFSET is that, you can specify a variable along with it. This is helpful to write generic handlers where the page size and current page number are passed as parameters.

DECLARE @offset INT
SELECT @offset = 10
SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET @offset ROWS 
FETCH NEXT 10 ROWS ONLY;

It can also be a function, subquery or a valid TSQL expression that returns 0 or a positive integer value. If the expression returns a negative value or NULL, the query will fail with an error.

SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET (SELECT offset FROM sometable) ROWS 
FETCH NEXT 10 ROWS ONLY;

OFFSET is followed by ROWS or ROW. You can use either one of them.

FETCH NEXT

FETCH can be used with either FIRST or NEXT and they have the same meaning. NEXT or FIRST is followed by the number of rows to retrieve. The number can be a constant value, result of a query or an expression. Here is an example that uses a sub-query to specify the number of rows to retrieve.

SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET (SELECT offset FROM setuptable) ROWS 
FETCH NEXT (SELECT pagesize FROM setuptable) ROWS ONLY;

The expression (or sub-query) should return a positive integer value that is greater than 0. If 0, NULL or a negative value is specified the query will fail with an error.

TSQL Enhancements in SQL11 (SQL Server 2011 / Denali) to support Server Side Paging

Sun, 14 Nov 2010 20:52:00 GMT

Server Side Paging is a common scenario most application developers must have encountered while creating front end applications that requires paging support. Instead of returning all the rows from the database and implement paging at the client side, you might want to implement the paging on the server side so that only those rows needed for the current page are retrieved from the server. This reduces the pressure on the database server as well as help the client application to perform better.

Writing a paging Query

By writing a paging query, we are essentially telling the database engine something like the following:

/*
My application displays 10 rows on a page and I want 
data to display on page 3. 

Send me rows 21 to 30 after ordering the customer table
by Customer ID
*/

The TSQL representation of the above query is as follows

SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY;

Here is a fully functional sample code that you can run on a SQL Server Denali (2011) instance.

-- Create a table
DECLARE @t TABLE (
	CustID INT IDENTITY,
	CustName VARCHAR(100) )

-- Populate some data
INSERT INTO @t (CustName)
SELECT 'Customer ' + CAST(Number AS VARCHAR)
FROM master..spt_values WHERE type = 'p' AND number BETWEEN 1 and 100

-- Write the query
SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY;

/*
CustID      CustName
----------- -------------
21          Customer 21
22          Customer 22
23          Customer 23
24          Customer 24
25          Customer 25
26          Customer 26
27          Customer 27
28          Customer 28
29          Customer 29
30          Customer 30
*/

See this article that explains how to implement Server Side Paging in SQL Server 2005/2008/R2.

Are you ready for SQL Server Quiz 2010?

Thu, 30 Sep 2010 09:38:00 GMT

If you like to win an iPad, Amazon Kindle, iPod or software licenses worth over $20,000, it is time to get ready for the upcoming SQL Server Quiz 2010. Brush up your SQL Server skills and win your favorite gadgets and software tools.

Even if you already have those gadgets and software tools, you might still find the SQL Server Quiz interesting, because there will be considerable in-depth discussion and learning you can gain from this series.

What is SQL Server Quiz?

SQL Server Quiz is a platform to verify your understanding of SQL Server and enhance your SQL Server skills. Through SQL Server Quiz, we bring a series of questions on various SQL Server topics and will provide a platform for discussing, explaining and understanding the related SQL Server areas.

SQL Server Quiz 2010 is a series of 31 questions, managed by 31 quiz masters who are experts in SQL Server. Each quiz master will ask one question each and will moderate the discussions and answers and finally will identify the winner of each quiz. Each answer that is correct will get a certain score (between 1 and 10, where 10 is the best) the scores of all the 31 questions will be added up to identify the final winner.

I look forward to see many of you actively participating in the discussions and win some of these prizes.

SQL Server - Server-side paging with Row_number() function

Fri, 06 Aug 2010 06:38:00 GMT

If you are a programmer working with SQL Server, you must have found it little embarrassing to display information which spans across multiple pages (web pages). SQL Server 2000 did not allow you to retrieve a specific range of records, say, records 51 to 100 ordered by a certain column.

For example, assume that you are working on a web page which lists the names of all the cities in different countries. Assume that you need to display 25 records in a page. The database has 50,000 records consisting all the cities/towns across the globe. In the above scenario, it really makes sense to retrieve only the required records. for example, in the first page, retrieve 1 to 25 records. When the user clicks on "next" button, retrieve records 26 to 50 and so on. at this stage the user might click on another column to change the sort order. Earlier it was ordered by city name but now the display is based on Zip code.

With SQL Server 2000, it was not very easy to achieve this. Some times people used temp tables achieve this. Others put the paging responsibility to the application which retrieved all the records and then displayed the information needed for the current page. (this approach will not only overload server resources, but also degrades performance of the application as well as the database server.)

SQL Server 2005 introduces a helpful function ROW_NUMBER() which helps in this scenario. Using ROW_NUMBER() you can generate a sequence number based on a given sort order and then select specific records from the results. Here is an example:

ROW_NUMBER() OVER (ORDER BY City) as Seq

The syntax ideally says the following. "Order the records by City, and then assign a serial number to each record". You can use it in a query as follows.

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities

However, filtering the records is a little tricky. A TSQL statement like the following will not work.

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities 
WHERE row BETWEEN 25 AND 50

Again, using the ROW_NUMBER directly inside the WHERE clause does not work (as shown below)

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities 
WHERE ( ROW_NUMBER() OVER (ORDER BY City) AS row ) BETWEEN 50 BETWEEN 75

One option is to use an inner query:

SELECT * FROM ( 
     SELECT 
          ROW_NUMBER() OVER (ORDER BY City) AS row, * 
     FROM Cities 
) AS a WHERE row BETWEEN 101 AND 125

The inner query creates a sub-table and then the outer query filters the records from the inner result set. ROW_NUMBER() is a function that I had been waiting for so long and am glad to find with the version 2005 of SQL Server. As the SQL Server Team celebrates the 17th Anniversary this year, I would really like to congratulate them.

Edit on 16 June 2008

It is almost 2 years since I wrote the above post. I had been learning SQL Server 2005 for the last few years (Still learning uh!) and wrote a few articles and blog posts on the new features introduced by SQL Server 2005. One of the articles that is close to the subject discussed here is "Server side paging with SQL Server 2005". This article closely examines the problems related to server-side-paging and then demonstrates a few examples that implements this.

Edit on 31 July 2010

SQL Server CE 4.0 introduced a new TSQL extension that makes paging queries much easier. For example, to fetch rows 21 to 30, a query can be written like this.

SELECT 
   * 
FROM Orders 
ORDER BY OrderID 
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY;

See Support for Paging Queries in SQL Server CE 4.0 for more details of this feature. I hope this feature will be part of the next SQL Server release.

First published on May 29, 2006

Tips and Tricks for Error Handling in ASP.NET Web Applications

Thu, 05 Aug 2010 01:00:00 GMT

Error handling is very important for any serious application. It is very crucial that the application is capable of detecting the errors and take corrective measures to the maximum possible extend. If the error situation is beyond the control of the application, it should report the situation to the user/administrator so that an external action can be taken.

The following are the most common ways of handling exceptions in an ASP.NET web application.

Structured Exception Handling
Error Events
Custom Error Pages

Structured Exception Handling

The most popular error handling is Structured Exception Handling (SEH). Most of us are familiar with it in the form of try..catch blocks. All of us use SEH a lot in whatever application that we are working on. The primary focus of SEH is to make sure that a block of code is executed correctly, and if an exception takes place, we have another piece of code which can take care of the exception and take some corrective measures if possible.

SEH is used to protect the application from an exceptional situation where something unexpected happens. For example the application tries to connect to a database server and the server is not available. It is an exceptional situation or an exception :-). If such a case happens the developer can handle the situation in the catch block and take the necessary action. I have seen smart developers making use of SEH for the application logic too. for example, assume that the developer needs to open a file and display the content. Ideally he needs to check if the file exists first and then if it exists open it as follows. (This kind of programming is called DEFENSIVE Programming).

If file exists    
    work with the file 
else    
   take action 
end if

But the developer can simply use a try..catch block to open the file without even checking if the file exists as

try    
     just work with the file 
catch 
     file-does-not-exist-exception    
     take action 
end try

The above approach reduces the complexity of checking for the existing of the file etc. However it adds some overhead to the system. Generating and handling an exception takes some system resources. I have also seen people using SEH to branch the code execution to a specific upper function block, from inside a series of complex nested functions.

For example function a calls b and b calls c and c calls d and d calls e and if the developer wants to transfer control to a specific location in any of the parent methods, he or she can make use of SEH. SEH is mostly used for procedure/function level error handling. Any code which access external resources like database connections, files/folders, URLs should always be inside try..catch blocks because they are most vulnerable for exceptions.

I have experienced a lot of trouble with combo boxes too and hence would recommend that any action that you do on a combo box should be inside try/catch blocks. Another area where you might consider is working with a dataaset where a null value can cause an exception.

Error Events

Most of the times we will be able to put the code into try..catch blocks. But exceptions occurs in exceptional situations. There might be several cases when we would not be able to fore-see that a given block code is vulnerable to a specific exception.

For example,

dim CustomerName as string = new string("jacob")

The above statement is not a candidate for an error. No one would expect that the above statement can generate an error. But what if the system memory is full? The system is not able to allocate memory for that variable and it will throw an exception. Is it practical to put every line of code in try..catch blocks and catch every possible exception with every line of code? Of course not.

The best idea is to use Error Events of ASP.NET to handle such exceptions. Error events can be used to catch exceptions which are not handled with SEH. Asp.net provides three events to handle error conditions.

a. page_error
b. global_error
c. application_error

You can write excpetion handling code in page_error to handle any un-handled exceptions that occur inside your page. similarly global_error and application_error can be used for application-wide error handling. From within the error event (page_error, global_error or application_error) you need to find the exception which occured. Server.GetLastError() can be used to retrieve the exception which triggered the error. After you have processed the error, you should call Server.ClearError() so that the further exception processing/handlers are not called.

Custom Error Pages

Yet another way you can handle errors in ASP.NET Web applications is by using Custom Error pages. When an exception occurs you can redirect the user to a customized page which explains the error condition or just give a friendly note to the user. The scope of Custom error page is very much large that it can even detect errors which are not related to the web application. For example, if your web application is down, still you can instruct IIS to show the user a specific page.

Custom error pages can be configured in 3 different ways.

1. Configure the custom error page with IIS
2. Configure the custom error page with web.config
3. Configure the custom error page declaratively in each web form

Configuring a custom error page with IIS

You can configure a custom error page with IIS by opening the properties window of your application. Custom error pages in IIS work based on HTTP response codes. you can define a custom page for each HTTP error that you need to handle. The question "which error code should I handle" largely depends on your application. However, I think the following HTTP response codes may be handled in a normal web application. HTTP 401 - Unauthorized HTTP 404 - Page Not Found HTTP 408 - Request Timeout HTTP 500 - Internal Server Error HTTP 503 - Server Not Available

Configuring Custom Error Pages in Web.Config

The section if web.config can be used to configure error pages. The following example shows a typical example.

<customerrors defaultredirect="Error.aspx" mode="on">    
     <error redirect="PageNotFound.aspx" statuscode="404" />    
     <error redirect="InternalServerErr.aspx" statuscode="500" /> 
</customerrors>

Note: Custom error handling using web.config is applicable to ASP.NET resources only. For example, if a "page not found" error occurs with an aspx file, your custom error page will be displayed. However if a "page not found" error occurs for an html file, your application's error handler page will not be launched. To handle situations external to the web application, the setting needs to be configured with IIS.

Page Level Error Configuration

The error configuration on web.config has application wide scope. However, it is possible to configure the error pages for a given web form. You can specify error pages declaratively on your web form as shown in the following example.

<%@ page 
    language="vb" 
    AutoEventWireup="false" 
    codebehind="pagename.aspx.vb" 
    inherits="projectname.pagename" 
    errorPage="Error.aspx" %>

Setting on the web page supersedes the configuration setting on the web.config. In the above example, if an error occurs inside the above page (pagename.aspx) the user will be taken to "error.aspx" which might contain the details of the page. You can use Server.GetLastError() to identify the exception and can display an appropriate message to the user.

Hope this helps

First published on 27 June 2006

SQL Server FILESTREAM feature and Differential Backups

Wed, 04 Aug 2010 05:29:00 GMT

This post tries to explain the behavior of differential backups on a FILESTREAM enabled database. A number of times, I see questions looking for a confirmation whether Differential Backups will correctly backup the NTFS (FILESTREAM) files and restore them correctly when a differential backup is restored.

A differential backup of a FILESTREAM enabled database will include the FILESTREAM data files as well. When the differential backup is restored, the FILESTREAM data files will be restored on the target location. Here is a set of TSQL scripts to demonstrate this.

Creating a FILESTREAM Enabled Database

Let us start by creating a FILESTREAM enabled database. Here is the script to create the database.

-- Create a FILESTREAM enabled Database
CREATE DATABASE NorthPole ON
PRIMARY (
    NAME = NorthPoleDB,
    FILENAME = 'C:\Demos\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
    NAME = NorthPoleFS,
    FILENAME = 'C:\Demos\FS\NorthPoleFS')
LOG ON (
    NAME = NorthPoleLOG,
    FILENAME = 'C:\Demos\NorthPoleLOG.ldf')
GO

Creating a table with a FILESTREAM column

Run the following script to create a table with a FILESTREAM column.

-- Set context to the newly created DB
USE NorthPole
GO
-- Create "Items" table
CREATE TABLE [dbo].[Items](
   [ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
GO

Insert Data

Let us insert a record into the table we just created.

-- Insert 1 row into the table
INSERT INTO Items (ItemID, ItemImage)
SELECT NEWID(), CAST(1 AS VARBINARY(MAX))

Taking a FULL Backup

Let us now take a FULL BACKUP of the database we just created. Run the following script to take a FULL backup.

-- Take a Backup
BACKUP DATABASE NorthPole 
TO DISK = 'C:\Demos\NorthPoledb_Full.bak'
GO

Restoring the FULL Backup

Let us go ahead and restore the FULL backup we took in the previous step. Let us create a new database from the backup. Since we intend to restore differential backups later on, let us keep the database in recovery mode.

-- Restore
RESTORE DATABASE NorthPole2
FROM DISK = N'C:\demos\NorthPoledb_Full.bak'
WITH NORECOVERY,
MOVE 'NorthPoleDB' TO 'C:\Demos\NorthPole2DB.mdf',
MOVE 'NorthPoleLOG' TO 'C:\Demos\NorthPole2LOG.ldf',
MOVE 'NorthPoleFS' TO 'C:\Demos\FS\NorthPole2FS', FILE=1

Once the database is restored, go to the FILESTREAM data folder and you will notice that the folder contains only one file. This is exactly what we had in the primary database at the time of taking the FULL backup.

Populating more data into the PRIMARY database

let us go back to the primary database and populate the FILESTREAM table with more rows. Run the following script that will add 4 more rows to the table. This will also generate 4 more FILESTREAM data files in the FILESTREAM data container.

-- Insert 4 more rows into the table
WHILE (SELECT COUNT(*) FROM Items) < 5 BEGIN
	INSERT INTO Items (ItemID, ItemImage)
	SELECT NEWID(), CAST(1 AS VARBINARY(MAX))
END

Taking a Differential Backup

It is time to take a differential backup. Run the following script to take a differential backup.

-- Take a differential backup 
BACKUP DATABASE NorthPole 
TO DISK = 'C:\Demos\NorthPoledb_Diff.bak'
WITH DIFFERENTIAL
GO

Restoring the Differential Backup

Let us now restore the differential backup on the secondary database. After restoring the full backup, we have added 4 more rows to the Items table in the source database. The differential backup is expected to contain those 4 rows along with the associated FILESTREAM data files. After we restore the differential backup, we should have 5 rows in the Item table and 5 files in the FILESTREAM data container.

Run the following script to restore the differential backup.

RESTORE DATABASE NorthPole2 
FROM DISK = 'C:\Demos\NorthPoledb_Diff.bak' 
WITH RECOVERY
GO

After restoring the differential backup, you can verify that the FILESTREAM data files are correctly restored by doing one of the following.

Run a SELECT query on the Items table and you will see that there are 5 rows and all the 5 rows have FILESTREAM data in it.
Go to the FILESTREAM folder and you can see that there are 5 FILESTREAM data files.

Conclusions

This post tries to demonstrate that a differential backup of a FILESTREAM enabled database contains the FILESTREAM data files too.

Getting started with PIVOT Queries in SQL Server 2005/2008

Tue, 03 Aug 2010 11:12:00 GMT

This post intends to help TSQL developers get started with PIVOT/CROSS TAB queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past.

Let us say for example we need a result set as following

from a table which has data as follows.

This essentially means the task is to generate a tabular result set from the rows that we have. With SQL Server 2000, most of us might have used #TEMP tables where we update the data temporarily and SELECTED from. Another option that most of the developers might have used with SQL Server 2000 is the CASE statement. In the example above, you can use a series of 12 CASE statements which can take for each column.

SQL Server 2005 has a handy PIVOT operator which can help us with the above task. The PIVOT clause generates a resultset as shown in the example above. For the purpose of this example, let us create a table as follows.

CREATE TABLE invoice (
    InvoiceNumber VARCHAR(20),
    invoiceDate DATETIME,
    InvoiceAmount MONEY 
)

The above SQL statement creates a table for the purpose of this example. Now let us insert some data into it.

INSERT INTO invoice
SELECT 'INV001', '2005-01-01', 100 UNION ALL
SELECT 'INV002', '2005-02-01', 40 UNION ALL
SELECT 'INV003', '2005-03-01', 60 UNION ALL
SELECT 'INV004', '2005-03-10', 15 UNION ALL
SELECT 'INV005', '2005-04-01', 50 UNION ALL
SELECT 'INV006', '2005-05-01', 77 UNION ALL
SELECT 'INV007', '2005-06-01', 12 UNION ALL
SELECT 'INV008', '2005-06-05', 56 UNION ALL
SELECT 'INV009', '2005-07-01', 34 UNION ALL
SELECT 'INV010', '2005-08-01', 76 UNION ALL
SELECT 'INV011', '2005-09-01', 24 UNION ALL
SELECT 'INV012', '2005-09-20', 10 UNION ALL
SELECT 'INV013', '2005-10-01', 15 UNION ALL
SELECT 'INV014', '2005-11-01', 40 UNION ALL
SELECT 'INV015', '2005-11-15', 21 UNION ALL
SELECT 'INV016', '2005-12-01', 17 UNION ALL
SELECT 'INV017', '2006-01-01', 34 UNION ALL
SELECT 'INV018', '2006-02-01', 24 UNION ALL
SELECT 'INV019', '2006-03-01', 56 UNION ALL
SELECT 'INV020', '2006-03-10', 43 UNION ALL
SELECT 'INV021', '2006-04-01', 24 UNION ALL
SELECT 'INV022', '2006-05-01', 11 UNION ALL
SELECT 'INV023', '2006-06-01', 6 UNION ALL
SELECT 'INV024', '2006-06-05', 13

Let us create the PIVOT query now which will return a result set as per what we needed.

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year], 
        left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount) FOR [month] IN (
        jan, feb, mar, apr, 
        may, jun, jul, aug, sep, oct, nov, dec
    )
)AS p

The above query generates a result set that we needed. Let me explain the structure of the query. The first part of the query

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year], 
        left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s

The first part of the query generates a subtable (view) as per the following.

The second part of the query generates a PIVOT table from the above result set.

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year], 
        left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS p

The above query returns a result set as follows.

Hope this helps! If you found this useful or have a question, please post a comment to let me know about it.

First published on 18 June 2006

How to load a huge file into a FILESTREAM column quickly

Mon, 02 Aug 2010 21:42:00 GMT

This post shows a quick and dirty way of loading a huge file into the FILESTREAM column of a SQL Server 2008 database instance. Note that this is an unsupported method which is not recommended on a production server.

The correct way of loading BLOB data into a FILESTREAM column

The right way of loading a huge file into a FILESTREAM column is by using the OpenSqlFileStream() API or SqlFileStream() .NET class. The following steps are required to perform this.

Connect to the database
Begin a transaction
Execute a query and retrieve the PathName() and FILESTREAM Transaction Context
Open the FILESTREAM data file for WRITE operation

Open the source file

Read small chunks from the read file
Write into the FILESTREAM data file

Close the source file

Close the FILESTREAM data file
Commit Transaction
Close connection

A quick (but incorrect) way of loading BLOB data into a FILESTREAM column

As I have mentioned in a few of my previous posts, I am writing a book on FILESTREAM and am spending quite a lot of time experimenting how SQL Server behaves with files larger than 2 GB. As part of these exercises, I often have to load really huge files into FILESTREAM columns, update, delete and reload etc.

Since I am not playing with production data, I have been looking for a quick way of getting the data loaded into SQL Server, without bothering about the best practices, transactional consistency etc. So I ended up doing the following.

Insert a dummy value into the FILESTREAM column using a TSQL insert or UPDATE statement. This will create a file in the FILESTREAM folder.
Go to the FILESTREAM folder and locate the FILE SQL Server just created to store my dummy data. Replace this file with the huge data file that I wanted to load into the FILESTREAM column.

How does it work?

SQL Server does not keep a lock on the FILESTREAM data files when those files are not being accessed. SQL Server touches the file only when a connection requests to read or write the data. SQL Server identifies the file by its name and does not store any other metadata (such as the file size, creation date etc) internally. It means that if you replace the file using windows explorer (or using a software tool), SQL Server cannot detect that the file has been changed. The next request trying to access the FILESTREAM data file will get the new file.

Be careful

It is not recommended that you touch the FILESTREAM data directly. All access to the FILESTREAM data should be done through SQL Server. Direct access to FILESTREAM data might corrupt your database.

This method might help when setting up a test environment to test FILESTREAM behavior with large files. You may not want to go through all the best practices to get some really huge files loaded into FILESTREAM columns.

Backup of a FILESTREAM database may include garbage files as well

Wed, 28 Jul 2010 02:51:00 GMT

I have been playing with FILESTREAM backups for a book that I am writing and came across something that I did not really like. I observed that a FILESTREAM backup may include garbage files which gets restored along with the database.

What are garbage files?

Let us understand what are garbage files. SQL Server stores FILESTREAM data as disk files. SQL Server does not support in-place update of FILESTREAM data. It means that if you try to change the data stored in a FILESTREAM column, a NEW file will be created and the old file will be discarded. A background garbage collector thread will remove the old file(s) asynchronously. Till the garbage collector jumps into action (and deletes them) these unwanted files will stay around in the FILESTREAM folder. These unwanted files that sit in the FILESTREAM folder for a short period (till they are deleted) are called garbage files.

Your FULL Backup may include Garbage Files

The garbage collector does not run continuously. SQL Server fires the garbage collector thread when certain criteria is met (such as a CHECKPOINT). So you might see garbage files lying around quite often. When you take a backup of your database, the garbage files will also go into the backup. When the backup is restored on another location, the garbage files are also restored.

Demonstration

Here is a repro script that shows that your backup may include garbage files and they get restored along with the database. Here is what the script does.

Creates a FILESTREAM enabled database
Creates a table with a FILESTREAM column
Inserts a row into the FILESTREAM table. This will create a disk file in the FILESTREAM folder
Updates the FILESTREAM column 100 times. This will create 100 garbage files on the folder, because every time you update a FILESTREAM column value (from a NON NULL value to another NON NULL value), SQL Server will create a new file.
Take a backup of the database – The database will include the 100 garbage files.
Restore the backup – The 100 garbage files will be restored in the new location

Creating the Database

Let us start with creating the FILESTREAM enabled database. You might need to change the location of the files to run these scripts on your end.

-- Create a FILESTREAM enabled Database
CREATE DATABASE NorthPole ON
PRIMARY (
    NAME = NorthPoleDB,
    FILENAME = 'C:\Data\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
    NAME = NorthPoleFS,
    FILENAME = 'C:\FS\NorthPoleFS')
LOG ON (
    NAME = NorthPoleLOG,
    FILENAME = 'C:\Data\NorthPoleLOG.ldf')
GO

Creating a table with a FILESTREAM column

Next, let us create a table with a FILESTREAM column. Run the following script to create the table.

-- Set context to the newly created DB
USE NorthPole
GO
-- Create "Items" table
CREATE TABLE [dbo].[Items](
   [ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
GO

Inserting a new record

Let us now insert a new row into the FILESTREAM table. SQL Server will create a disk file for every NON-NULL value in the FILESTREAM columns of each row.

-- Insert 1 row into the table
INSERT INTO Items (ItemID, ItemImage)
SELECT NEWID(), CAST(1 AS VARBINARY(MAX))

Generating the garbage files

As mentioned earlier, when you update a FILESREAM column (from a NON-NULL value to another NON-NULL value), SQL Server will create a new file with the updated value. The old file will stay around till it is deleted by the garbage collector. The following script updates the FILESTREAM column of the row we just inserted 100 times. It generates 100 garbage files.

-- Update the data 100 times
DECLARE @i INT = 1
WHILE @i <= 100 BEGIN
    UPDATE Items SET ItemImage = CAST(1 AS VARBINARY(MAX))
    SET @i = @i + 1
END

Taking a Backup

The next step is to take a backup of the database. The backup file will include all the garbage files we generated in the previous step.

-- Take a Backup
BACKUP DATABASE NorthPole TO DISK = 'C:\temp\bak\NorthPoledb1.bak'
GO

Restoring the database

Let us create a new database from the backup file we just created.

-- Restore
RESTORE DATABASE NorthPole2
FROM DISK = N'C:\temp\bak\NorthPoledb1.bak'
WITH RECOVERY,
MOVE 'NorthPoleDB' TO 'C:\Data\NorthPole2DB.mdf',
MOVE 'NorthPoleLOG' TO 'C:\Data\NorthPole2LOG.ldf',
MOVE 'NorthPoleFS' TO 'C:\FS\NorthPole2FS', FILE=1

Look for the Garbage Files

After you have restored the backup, go to the FILESTREAM data container (C:\FS\NorthPole2FS - if you have not changed the path at the time of restoring your database) and you will see (in most cases) 101 files, whereas your table has only one row and only one FILESTREAM file is required. You have 100 garbage files that came in through the backup which will stay around (for ever ??).

Points to Note

After you update the FILESTREAM columns, if the garbage collector steps in and removes the unwanted files, your backup will be clean. So, some of you may not see this problem by running the above script if you wait for some time before taking the backup
If the backup is taken while the garbage collector is already running, you might see lesser number of garbage files in the new location. During my testing, at one occasion I ended up with 40 garbage files (instead of 100) when the new database is restored on the secondary location.

How do I clean the garbage?

I could not find any easy way to clean the garbage. The only workaround I could come up with (thanks Erland and Klein) is to restore additional transaction log backups from the original database. However, this may not be practical in most cases when we want to move a full backup to a secondary location, restore it and start using it right away. To be able to restore additional transaction log backups, the new database needs to be kept in recovery mode which may not be possible in many scenarios.

Conclusions

I suppose the scenario I presented in this post is not too common. Books Online says that FILESTREAM data is not ideal for storing BLOB values that gets updated often. So if you need frequent updates, you may not decide to use a FILESTREAM column at all.

But as in every environment, a small percentage of updates on the FILESTREAM columns may be expected and there may be always some garbage lying around. It would be really good if a future version of SQL Server can exclude the garbage files from the backups.