SQL Tips

Generating Comma Separated list in TSQL by using XML Path

In one to many relationships of two tables, sometimes we need to write a query which can return comma seperated list of all child values for each parent. TSQL is not designed for string parsing and it does not scale well in string processing scenarios but luckily by using XML capabilities of SQL Server [...]

Generating Calendar Table dynamically and efficiently

A while back I had posted a way to generate million rows in one second. The main purpose was to generate a number table which can be utilized in many scenarios and it is so useful that I will recommend having this table in every database where we need to perform temporal calculations. One of [...]

Understanding CTE and its impacts

Many people get confused or do not properly understand about the capabilities and impacts of Common Table Expressions CTE introduced in SQL Server 2005. Following are the major points to understand the behavior of CTE

Common Table Expressions are essentially a temporary view and do not materialize data like tables.
CTE are not temporary tables therefore, index [...]

Difference between Count(*) and Count([Column])

Many people assumes that Count(*) and Count([ColumnName]) will produce the same result if where clause is same but there is a slight difference between the working of these two. If you use Count(*) then you will get the total no. of rows but if you use Count([SomeColumn]) you may or may not get total no. [...]

Reset Identity Column without Deleting or Truncating a Table

Many of us believes that identity column value can only be reset by truncating the table which is sometimes not possible due to foreign key constraints. But this assumption is incorrect and there is a way to set the value of identity counter by using the following DBCC command
DBCC CHECKIDENT([YourTable],RESEED,0)
Go
Just replace [YourTable] with the name [...]

How SQL Server stores XML data and why it is more efficient than normal string parsing?

SQL Server 2005 supports xml as a native data type which means it has necessary tools and data types which do not treat xml as just a plain text string and try to parse it in a typical string parsing fashion. Instead SQL Server stores typed xml data in a special binary format which is [...]

Returning nth row from each group

Happy New Year!
While reading a post in SQL Server news group, I came across a question about returning 2nd row for each user. Yes, you may already started thinking that there is no concept of First, Second or Third “row” in RDBMS tables because they are SET and by definition “SET is an unordered collection [...]