Jacob's Blog

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

Most application does some sort of paging on the client side to display only a certain number of rows to the user. For the best performance, this type of applications want to retrieve only the information needed for the current page from the database...

by Jacob Sebastian on 08-06-2010 | 1,828 Views

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

This post highlights some of the most common Error Handling techniques in ASP.NET Web Applications...

by Jacob Sebastian on 08-05-2010 | 1,388 Views

SQL Server FILESTREAM feature and Differential Backups

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

by Jacob Sebastian on 08-04-2010 | 635 Views

Getting started with PIVOT Queries in SQL Server 2005/2008

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

by Jacob Sebastian on 08-03-2010 | 2,067 Views

How to load a huge file into a FILESTREAM column quickly

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

by Jacob Sebastian on 08-02-2010 | 1,090 Views

Backup of a FILESTREAM database may include garbage files as well

This post is based on the observation that the backup of a FILESTREAM database includes garbage files as well. The repro example given in this post demonstrates that....

by Jacob Sebastian on 07-28-2010 | 679 Views

Support for Paging Queries in SQL Server CE 4.0

Almost every business application I have worked with, needed some sort of paging requirement to display information in the presentation layer. At any point of time, users will be looking at a small number of records (say 25 or 50) in most cases. Some...

by Jacob Sebastian on 07-13-2010 | 976 Views

XQuery Lab 62 – Revisiting simple TSQL SELECT operations using XQuery

This post aims to be a quick reference source for the beginners and shows a few simple SELECT examples....

by Jacob Sebastian on 07-01-2010 | 1,415 Views

XQuery Lab 61 – Writing a Recursive CTE to process an XML document

We have seen several examples of writing recursive queries in the earlier blog posts. For a quick recap, you can find some of those posts in the list given below. TSQL Lab 10 - Performing recursive updates in SQL Server TSQL Lab 11 - Writing a recursive...

by Jacob Sebastian on 06-29-2010 | 1,098 Views

XQuery Lab 60 – Executing dynamic query strings with OPENXML()

This installment of XQuery Lab shows how to execute dynamic queries that contain OPENXML() calls. We will see examples that use EXEC() and sp_executesql...

by Jacob Sebastian on 06-24-2010 | 836 Views

Creating reports that the end user can customize

This is the 15th year in my career building serious business applications using various technologies (starting from DBase, going through Clipper, Foxpro, Visual Basic, Visual C++, and finally sticking to SQL Server) and one of the points I observed is...

by Jacob Sebastian on 06-23-2010 | 557 Views

Using the XMLTable() Function

A few weeks back, I published a function named XMLTable() that helps to query XML documents easily. The idea behind the function is to be able to blindly query XML documents just like you do a ‘SELECT *’ on a relational table. Not only that this function...

by Jacob Sebastian on 06-23-2010 | 774 Views

XQuery Lab 59 – OPENXML() and XML Namespace Declarations

In the previous post , we saw a basic example that reads information from an XML document using OPENXML(). In this post, we will focus on reading information from XML documents having namespace declarations. Here is a simple example: DECLARE @x VARCHAR...

by Jacob Sebastian on 06-22-2010 | 836 Views

XQuery Lab 57 – Getting Started with OPENXML

This post intends to help you get started with OPENXML() function. OPENXML() lets you shred an XML document or fragment into a result set. Though OPENXML() has got no direct relationship with XQuery, I thought of including it in the XQuery Labs series...

by Jacob Sebastian on 06-17-2010 | 842 Views

FILESTREAM directory structure - where do the GUIDs come from?

One of the questions that might come to your mind while getting started with FILESTREAM is the way FILESTREAM directories and files are named. Within the FILESTREAM Data Container, SQL Server creates a root folder per FILESTREAM enabled table. If the...

by Jacob Sebastian on 06-16-2010 | 1,083 Views