How-To : Splitting delimited strings using XQuery in SQL

Well, this was just kind of an experiment of sorts of using XQuery. The idea just occured some time back when i was trying some other similar experiment.
Now as you all know there is no in-built function for Split in SQL. So lots of people have come out with different solution. This solution is [...]

Trimming extra space in SQL

Sometime back i had come across this small problem. Mind you this is not a problem you come across many times, but it is interesting little problem nonetheless. The problem was removing extra spaces in a given string.
For e.g if you have some string like so “This is a                   line which    contains multiple        spcaes.” and [...]

Dataset to XML and loss of information

When weneed to persist a dataset object, usually what we do is use the WriteXml() method for the Dataset object. For most cases the default implementation works fine.
So

DataSet dsObj = GetDatasetObject();
dsObj.WriteXml(filename);

If while saving the dataset, a particular contained NULL values, that column information will not be outputted. So when you try to [...]

Retrieving comma seperated values from SQL Server

Many a times, we are pressed with a problem of retrieving comma seperated values from SQL server. There are quite a few ways to do it, for instance using cursors, using user defined functions or using COALESCE (the most preferred). Come SQL Server 2005 and there is one more technique which can be employed (his [...]

HOW-TO: Search string value contained in comma seperated string

Quite often when we need to pass multiple values to the stored procedure, we pass the value as comma seperated string. The problem is how do we search for a column having a value contained in the comma seperated string.
For e.g. suppose you have a Books table which has "Type" property. The "Type" property can [...]

Reading SQL Trace Files using fn_trace_gettable function

While working in SQL Server, most of the time we create trace files(.trc) to identify performance bottleneck. You can create trace files using SQL Profiler or you can use SQL as well. Most of us would go SQL Profiler way, because simply putting is very easy and quite elegant too, further more it gives you [...]

… Implement pagination using CTE in SQL Server 2005

Paging is a technique where you return a selected set of rows in a ordered manner, for e.g. records from 1-10, 10-20 or 3-7 etc. You must have seen this technique while accessing your mail on say HotMail or Yahoo or GMail. They provide you with a list of mails which have from 1-10 and [...]

… Using XML data-type in SQL 2k5

As of SQL Server 2005 a new data-type has been introduced to facilitate the use of XML information. Pre SQL Server 2005 the XML support was available through OPENXML command, but from SQL Server 2005 the XML support has been inbuilt in the database engine itself through the new Data-type called ‘XML’.
I was intending on [...]

Debugging T-SQL Stored Procedures in SQL Server 2005

Debugging T-SQL Stored Procedures in SQL Server 2005
Debugging T-SQL stored procedures in SQL Server 2005 have changed from the way we used to debug stored procedures in SQL Server 2000.
In-order to debug stored procedures in SQL Server 2000, all that was needed was

Open Query Analyzer
Connect to the respective database
Find the stored procedure to debug
Right-click [...]