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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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