Checking for and dropping temp tables

IF (object_id ('tempdb.dbo.#fact_stg') is not null)

BEGIN

DROP TABLE #fact_stg

END

Again, me with the syntax ;-)

Proper case function

Many thanks to kselvia who posted this at: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37760

I'm adding it here in case the forum I found this on loses the posting:

"

CREATE FUNCTION dbo.fCapFirst(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @position INT

WHILE IsNull(@position,Len(@input)) > 1

SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))), @position = charindex(' ',@input,IsNull(@position,1)) + 1

RETURN (@input)

END

It's not as sophisticated as the others but it's about 3 times as fast:

select dbo.fCapFirst(Lower(Column)) From MyTable

"

. . . Totally awesome!

Row_number without partition by

I always forget how to do this!!

Select

RowNumber = row_number() over(order by MonthLabel),

MonthLabel

from (Select distinct MonthLabel, Year from dbo.dimTime) dt

Insert row syntax

insert into dbo.TableLoads (TableName,LoadTS) values ('dbo.Status',getdate())

Another example. . . Manipulating timestamps

declare @Today datetime
set @Today = convert(datetime,convert(char(10),getdate(),101))
declare @TodayHour datetime
set @TodayHour = dateadd(hh,(datepart(hh,getdate())),@Today)

Select @Today, @TodayHour

declare @Today15Min_pre datetime
set @Today15MIn_pre = dateadd(mi,16,@TodayHour)

declare @Today15Min datetime
set @Today15Min = dateadd(ms,-2,@Today15Min_pre)

Select @Today15min_pre, @Today15Min

Cast as varchar

I always get the parentheses in the wrong order and then have to look this up!

ProviderKey = cast(GroupID as varchar)
+'-'+cast(BillingID as varchar)
+'-'+cast(LocationID as varchar)
+'-'+cast(UserID as varchar)

Convert UTC datetime to getdate time zone

Okay, there's probably a bunch of ways to do this, but, I'm working on my Analysis Services 2005 OLAP Query Log and found that the StartTime being tracked is in UTC (universal time clock). Alas, that's not meaningful for me. . .

I've been building a SQL Server Integration Services package and needed to check to see if the OLAP Query Log has run today; if so, I'd like one of the most recent queries (alas, our time stamp doesn't capture miliseconds to pull apart queries run in close proximity). Here's what I ended up doing:


Select *
from
(SELECT RowNumber = row_number() over(partition by (1) order by StartTime desc)
,[MSOLAP_Database]
,[MSOLAP_User]
,[StartTime]
,StartTime_NonUTC = dateadd(mi,datediff(mi,getutcdate(),getdate()),StartTime)
FROM [OLAP].[dbo].[OlapQueryLog]
) as log
where RowNumber = 1 -- in case there are multiple queries for the same time (since it's not to milisecond level) . . . return only one row