Jacob's Blog

XQuery Lab 65 – Restructuring an XML document using FLWOR

Jacob Sebastian — Fri, 09 Dec 2011 11:33:07 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

Jacob Sebastian — Mon, 08 Aug 2011 06:51:54 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

Jacob Sebastian — Mon, 18 Jul 2011 07:19:02 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

Jacob Sebastian — Sun, 12 Jun 2011 17:15:10 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

Jacob Sebastian — Wed, 16 Mar 2011 10:27:32 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()

Jacob Sebastian — Tue, 08 Mar 2011 03:20:33 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!

Jacob Sebastian — Sun, 06 Mar 2011 15:44:48 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!

Jacob Sebastian — Sun, 27 Feb 2011 21: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?

Jacob Sebastian — Mon, 07 Feb 2011 08: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?

Jacob Sebastian — Fri, 04 Feb 2011 13:51:40 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?

Jacob Sebastian — Fri, 21 Jan 2011 08:27:49 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

Jacob Sebastian — Sat, 01 Jan 2011 09:23:19 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

Jacob Sebastian — Fri, 17 Dec 2010 12:49:23 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

Jacob Sebastian — Tue, 14 Dec 2010 15:04:02 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

Jacob Sebastian — Sat, 20 Nov 2010 06:28:29 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.