beyondrelational.com

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. This post explains how to write this type of queries....

Posted: 08-06-2010 6:38 AM by Jacob Sebastian | with no comments

Filed under: TSQL, BRH, DBA, #TSQL, performance, #DBA

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

Posted: 08-04-2010 5:29 AM by Jacob Sebastian | with no comments

Filed under: FILESTREAM, TSQL, SQLSERVER, BRH, #FILESTREAM, DBA, #DBA, RESTORE, BACKUP

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

Posted: 08-03-2010 11:12 AM by Jacob Sebastian | with 1 comment(s)

Filed under: PIVOT, TSQL, SQLSERVER, BRH, #TSQL, #DBA, #SQLSERVER, #PIVOT

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

Posted: 07-28-2010 2:51 AM by Jacob Sebastian | with no comments

Filed under: FILESTREAM, TSQL, SQL SERVER, BRH, #FILESTREAM

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

Posted: 07-01-2010 11:53 AM by Jacob Sebastian | with no comments

Filed under: XQuery-Functions, XQuery-Labs, XML, XQuery, TSQL, SQLSERVER, BRH, XQuery Tutorials, XQuery Lab, XQuery Functions, xquery-lab, xquery labs, #XML, #XQUERY, #TSQL

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

Posted: 06-23-2010 2:23 PM by Jacob Sebastian | with no comments

Filed under: XML, XQuery, TSQL, SQLSERVER, SQLAuthority, #TSQL

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

Posted: 06-17-2010 4:25 PM by Jacob Sebastian | with no comments

Filed under: XML, XQuery, TSQL, SQLSERVER, BRH, xquery labs, sp_xml_preparedocument, OPENXML, Database, DBA, #XML, #XQUERY, sp_xml_removedocument, #SQLSERVER

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

Posted: 06-16-2010 9:07 AM by Jacob Sebastian | with no comments

Filed under: FILESTREAM, TSQL, SQLSERVER, BRH, DAC, #FILESTREAM, #TSQL

SQL Server – Issues with generating XML using string operations

The results of a SQL Server SELECT query can be transformed into an XML document by using the FOR XML command. Building XML documents using string functions – concatenating XML tags and embedding values between them using string operations – is not a...

Posted: 04-28-2010 1:09 AM by Jacob Sebastian | with no comments

Filed under: XML, FOR XML, TSQL, SQL SERVER, BRH, for xml raw, for xml auto, #XML, #TSQL

FOR XML Workshop – Understanding FOR XML AUTO

An XML document can be generated from the result of a SQL Server relational query using FOR XML. FOR XML can be used along with AUTO, RAW, PATH or EXPLICIT directives which offer different levels of control over the structure of the XML output....

Posted: 04-03-2010 11:01 PM by Jacob Sebastian | with no comments

Filed under: XML, FOR_XML, TSQL, SQLXML, SQLSERVER, FOR_XML_AUTO

XQuery Lab 53 – Reading information from SQL Server Extended Event Information XML

This installment of XQuery Labs explains how to read information from the XML document generated by SQL Server Extended Events....

Posted: 03-23-2010 6:42 PM by Jacob Sebastian | with no comments

Filed under: XQuery-Functions, XQuery-Labs, XML, XQuery, TSQL, SQL SERVER, CROSS-APPLY

Completely Removing FILESTREAM features from a SQL Server 2008 database

Configuring a software feature is usually much harder than removing it. Most of the times, removing a feature could be just a matter of a mouse click or a simple command/statement. So what do you think about this question? Enabling FILESTREAM storage...

Posted: 03-11-2010 8:05 PM by Jacob Sebastian | with no comments

Filed under: FILESTREAM, TSQL, SQL SERVER, SQL, jacob sebastian

What is the FILESTREAM access level on your server instance? Oh I have got a “3”

Excepts from a conversation between Sumit and Leena, that I overheard last week. Don’t know who they are?? Never mind, I will introduce them later! Leena : “Sumit, I am playing with the FILESTREAM access level and I am bit confused as I am getting a ...

Posted: 03-02-2010 6:26 PM by Jacob Sebastian | with no comments

Filed under: FILESTREAM, TSQL, SQL SERVER, SQL, filestream_access_level, Win32 API, IO Streaming, FilestreamEffectiveLevel, FILESTREAM Access Level, sp_configure

SQL Server – How to load data into a FILESTREAM enabled column using TSQL?

“Well, when accessing from TSQL, FILESTREAM enabled columns are not different from other regular columns and the code is not different from the regular code that you write”. This used to be my usual answer to people who ask me this question. I also had...

Posted: 08-01-2009 4:02 PM by Jacob Sebastian | with 1 comment(s)

Filed under: FILESTREAM, SQL SERVER 2008, TSQL, OPENROWSET, SQL SERVER

SQL Server - How to find out the SEED and INCREMENT values of an IDENTITY column?

Every IDENTITY column has two basic attributes: SEED and INCREMENT. The SEED attribute stores the ROOT or Starting value. INCREMENT specifies the value to be added to the current IDENTITY value to generate the next value. The default value of both these...

Posted: 02-03-2009 12:32 AM by Jacob Sebastian | with no comments

Filed under: IDENTITY, TSQL, SQL SERVER, IDENT_CURRENT, SEED, INCREMENT, IDENT_INCR, IDENT_SEED, sys.tables, sys.columns, sys.schemas, identity seed, identity increment, current-identity

Jacob's Blog

Recent Blog Posts

Most Viewed

Archive