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> ...

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...

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...

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...

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....

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...

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...

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...

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...

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...

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...

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...

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...

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"...

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....