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

Indexing: Clustered vs. Non-clustered

Here’s what I’ve learned, but take it all with a grain of salt . . . In attending conferences & through discussions, it sounds like indexes are still a highly debated topic, but here’s what I’ve got so far:

* Every table can have only one clustered index and up to 200+ nonclustered indexes

* Every table should have a clustered index

* A clustered index is typically a fairly unique reference to a single row in the dataset, but it doesn’t have to be unique – it can be created as “Create Clustered Index Idx1 on t1(c)”

* Clustered indexes are usually surrogate keys or the columns on which tables join (e.g. PersonNum) – when doing a index seek, the server looks for the clustered index

* Clustered indexes are a sorting of the data

* Non-clustered indexes can be on anything useful :-)

* A non-clustered index works by keeping a “index” of which clustered index contains the non-clustered index information. When doing a index seek, the server looks at the non-clustered index which points it to which clustered index record(s) contain that information.


The typical analogy is that a clustered index is similar to page numbers (“page 3”) where as a nonclustered index is similar to a book index (e.g. ICD-9’s: “lumbosacral sprains is found on page 327”).

Issues currently being debated include: Should the clustered index be on the business key or the surrogate key? For incremental loading should the clustered index be on fields like loadTS? How many is too many indexes? . . . . There doesn’t seem to be any clear answers at the conferences & forums. So, we’re not alone in being unsure.

Here's a simple sample of indexing a temp table:

create table #ProductNumbers
(ProductID int,
ProductName varchar(50),
QuantityPerUnit varchar(50),
UnitPrice numeric 15,2)

create clustered index ProductIDIndex on #ProductNumbers(ProductID)
create nonclustered index ProductNameIndex on #ProductNumbers(ProductName)

insert into #ProductNumbers (ProductID, ProductName, QuantityPerUnit, UnitPrice)

Select
ProductID,
ProductName,
QuantityPerUnit,
UnitPrice
from Northwinds.dbo.Products
where Discontinued = 'True'

Simple sample of Row_Num command

Again, using the SQL Server Northwinds Database . . . Say, I want to find the most expensive product for each Category in the Product Table:

Select
CategoryID,
ProductID,
ProductName,
UnitPrice,
UnitsAvailable

from
(Select CategoryID,
ProductID,
ProductName,
UnitPrice,
UnitsAvailable = UnitsInStock+UnitsOnOrder,
[RowNumber] = row_number() over(partition by CategoryID order by UnitPrice desc)
from dbo.Products) as vt

where [RowNumber] = 1


P.S. I hate that I appear to lose formatting when the blog is posted. . . i should invest some time in learning to tweak html!