måndag 28 oktober 2013

select max from multiple columns

I have a view reading from three tables: A, B and C. Each table has a Created timestamp. I want the view to show the latest timestamp in any of these three tables, to be able to find new rows since a certain date, even if it is just a new row in one of the tables.

I was about to do a case or a function, but found this little simple method.

This snippet returns 3 with column name of "value"
SELECT Max(v)
FROM ( VALUES (1), (2), (3) ) AS value(v)

Select Col1, Col2,
(
SELECT Max(v)
FROM ( VALUES (a.Created), (b.Created), (c.Created) ) AS value(v)
) as newDataCreated
From A
inner join B
on a.id = b.a_id
inner join C
on b.id = c.b_id

Works in SQL Server 2008 and newer versions.

onsdag 28 augusti 2013

Force prints to display

I you wan't to force print statements to display continuously when executing script in management studio, insert the following statement. SQL server do not treat severity 1-10 as an error, just a message.

RAISERROR ('Push the message!', 0, 1) WITH NOWAIT

torsdag 22 augusti 2013

T-SQl Query performance

Just some short notes on what to be aware of when experiencing bad performance.

  • Table Scans for sure, but also be aware of key-lookups. Those normally can be avoided be including the lookup column in the index. This is in my experience one of the more common opportunities to improve performance and not that obvious to spot.
  •  Table compression actually increases Query performance, but slows inserts down a bit. Generally if you have big tables, use table compression
  • If using a view as a source in a merge statement, you can save a lot of time by doing it in two steps, first store the result of the view in a temporary table and then do the merge with the temporary table as source.
  • A left join can in some cases be faster than an inner join. Strange but true.