Be Careful When Apply Restrictions on Outer Joins

This query doesn't work as expected.
select

rd.System_Id as BizStoryID ,0 as TechStoryID ,0 as TaskID from


#UpdatedWorkItemRawData rdleft join #ItemHierarchyPrep ihon rd.System_Id = ih.BizStoryIDand rd.System_WorkItemType = 'eScrum Product Backlog Item'and rd.TeamProjectSK = @ProductBacklogProjectNodeSKwhere ih.BizStoryID is null

To get what I need, the following query is needed.
 
select rd.System_Id as BizStoryID ,0 as TechStoryID ,0 as TaskID from

#UpdatedWorkItemRawData rdleft join #ItemHierarchyPrep ihon rd.System_Id = ih.BizStoryIDwhere rd.System_WorkItemType = 'eScrum Product Backlog Item'
and rd.TeamProjectSK = @ProductBacklogProjectNodeSKand ih.BizStoryID is null

Linked Server Name Trick

I created a report that pulls data from Oracle databases via OpenQuery() agained SQL Server linked server. I initially set the linked server name with periods (.), i.e. Environment.Schemaname.abc. Then my SQL query failed because OpenQuery() doesn't like period in the linked server name. After replacing the (.) with (_) everying worked just fine.

Strang Right() Function Behavior

I have a query that has been working for 6 months. All of a sudden it started giving me the error "Invalid length parameter passed to the RIGHT function". That query contains serveral select queries that are UNIONed together. A RIGHT() function is in one of those select queries. When I ran the select queries seperately, they all worked fine. But when I ran them together with the UNION, I got the error. This is very confusing.

The error went away after I put additional filtered in the query with Right() function. The additional filter is not necessary. But it got rid of the error.

My theory is that when SQL server parses the query, the Right() was verified against data that will be filtered out by the WHERE clause. I hope there is a way to verify that.

Performance Improvement on Recursive CTE

One big finding for today is that when using recursive CTE, the leaner the recursive member table is the better. This basically means getting only the fields that are necessary for the recursive CTE. Any other fields such as Tile, Description are to be pulled after the recursive CTE. It also means doing the recursive as early as possible in the big overall query.

This practise brought a SQL query running time from 30 seconds to 1 second. Huge, huge difference!

Convert UTC Time to Local Time

To convert a UTC time to local time, we first find out the difference between the UTC and local time by getting the current UTC time and local time.

DECLARE @UTCtoLocalTime AS INT
Set @UTCtoLocalTime = DATEDIFF(hour, GETUTCDATE(), GETDATE())

Then we just need to add the difference back to the UTC time to get the local time.
DateAdd(hour, @UTCtoLocalTime, [UTC TIME])

The benifit of this approach is that the US daylight saving is considered.

T-SQL Pivot / UnPivot Command

This is a very useful and efficient command for pivoting data.

E.g.

select * from dbo.tbl_BuildInformationField
pivot (
min(FieldValue)
for
FieldName in ([Name],[Message],StartTime,FinishTime)
) as p
where name = 'FirstTarget'

More information at:
http://www.unboxedsolutions.com/sean/archive/2004/08/30/302.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

Also UNPIVOT is equally handy in certain situations:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Here is how to unpivot multiple columns:
http://pratchev.blogspot.com/2009/02/unpivoting-multiple-columns.html

Compare Just the Date Portion of DateTime

datediff(day,@d1,@d2)=0

MDX + T-SQL: Combining relational and multi-dimensional data into one query result set

Quite interesting
http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx

Complicated WHERE Clause

Goal:
The filter is as followed.

_All Releases
Overflow
Release 1
Release 2
Release 3
Release 4
Release 5

When user selects _All Releases, the filter should only filter out Overflow. When user selects Overflow, the filter should only take Overflow. When user selects Release 1, the filter should take Iteration 1 through 9. When user selects other releases, the filter should take the selected release.

Solution:
((@Release='_All Releases' AND (i.[Iteration Path] <> '\Cobalt Product Backlog\Overflow'))OR (@Release = 'Overflow' AND i.[Iteration Path] = '\Cobalt Product Backlog\Overflow')OR (@Release <> '_All Releases' AND @Release <> 'Overflow' AND (i.[Iteration Path] LIKE '\Cobalt Product Backlog'+ CASE @Release WHEN 'Release 1' THEN '\Iteration%' ELSE '\'+@Release+'%' END)))

Thanks to the following post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54046

Use IN Statement for Multivalue Parameters

When use IN statement in the WHERE clause for multivalue parameters, the parameters needs to be in parentathes. E.g.

a.[Microsoft_VSTS_Common_Priority] IN (@Priority)

SQL: Removes the Time Portion of a DateTime Value

I encountered a situation that I need to get only the Date portion of a Datetime value. After some digging, I found a post that has exactly what I want. A SQL expression, dateadd(dd,0, datediff(dd,0,@DateTime)), was used to give just the date portion. I'm not quite sure about why it did the trick. But it worked for me.

The original post is at:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

SQL: Conditional WHERE clause

I encountered a situation that the WHERE clause in a SQL query changes based a parameter. When the parameter is '_All', there is no restriction. When the parameter is any other value, an AND section in the WHERE clause is needed.

After some digging, I found an elegant solution. Using
(@parameter = '_All' OR Field_X=@parameter). It greatly simplied the SQL query and fixed the problem of NULL value is hard to handle.

The post I saw is: http://dotnet.org.za/ilo/archive/2005/05/31/21280.aspx