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!

IsNumeric command

No worries, the virtual table below creates the data set and so no data tables are needed to run this query. . . .

Select
TestVariable = case when isnumeric(org.OrderAmountRangeQty)= 0 then org.OrderAmountRangeQty
else 'Its a number' end
from
(
Select OrderAmountRangeQty = 'Z'
union all
Select OrderAmountRangeQty = '1'
union all
Select OrderAmountRangeQty = '15'
union all
Select OrderAmountRangeQty = '#2'
union all
Select OrderAmountRangeQty = '3'
) as org
-- isnumeric(column) = 0 means it's not a number
-- isnumeric(column) = 1 means the column value IS a number

Similar functions: IsDate identifies if the freetext field is a date.

Simple sample of a Table variable

declare @Today datetime
set @Today = convert(datetime, convert(char(10), getdate(),101))

declare @FullDates Table (SevenDaysAgo)
insert into @FullDates (SevenDaysAgo)
Select FullDate as SevenDaysAgo
from dbo.DimDate
where FullDate >= DATEADD(dd,-7, @Today) and FullDate <= getdate() Select SevenDaysAgo
from @FullDates

Syntax to execute a Stored Procedure in query window

exec [stored procedure name] parameter1_value, parameter2_value


For example if you have a stored procedure with @Facility, @StartDate, and @EndDate parameters then:


exec [dbo].[s_PROCEDURE_Name] 'SHRB','01-01-2009','01-31-2009'

Convert getdate() to date as of midnight AM

I always have to remind myself how to do this . . . Is that sad?

Declare @today datetime

Set @Today = getdate()

Declare @Today3 smalldatetime

Set @Today3 = convert(datetime,convert(char(10),getdate(),101))

Select @Today, @Today3

Field Length

This formula may be necessary when you're dealing with a tool (e.g. Dashboard tool) that has limits to the character lengths that it can read. You may want to be proactive in identifying potential problems.

Using the SQL Server Northwind Traders Sample Database:

SELECT

ProductID,

ProductName,

FieldLength = len(ProductName)

FROM Northwind.dbo.Products

where len(ProductName) >57

Welcome to my SQL blog!

I'm just starting this out with the purpose of having a quick reference to remember SQL syntax. Enjoy!