I'd use a CASE expression so you can guarantee the order of execution; you don't need a CTE for that.

SELECT OBSVALUE

FROM OBS

WHERE 1 = CASE

WHEN...

Sorry:

SELECT

[values].value,

SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,

SUM(CASE WHEN Q2 = [values].value THEN 1 ELSE 0 END) AS Q2,

SUM(CASE WHEN Q3 = [values].value THEN 1 ELSE...

SQL_Enthusiast (4/25/2013)

---

Let's say, for whatever crazy reason, that temp tables are not an option. Is there a problem with using VIEWS? Especially if the views can be recycled for other...

GilaMonster (4/25/2013)

---

Table variables don't have stats, but they certainly can have...

There's no inherent reason the proc should perform so badly.

Instead of table variables, use temp tables and index the temp tables appropriately.

I suspect the time can be reduced significantly from...

You may gain some performance by avoiding counting all the rows in inserted and deleted:

ALTER TRIGGER dbo.SystemInfoTrg

ON dbo.SystemInfo

AFTER INSERT, DELETE, UPDATE

AS

SET NOCOUNT ON;

DECLARE...

I think something like this should at least be close:

SELECT

[values].value,

SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,

...

Just in case, IF you already have an index on ( YearValue, MonthValue ), then this should be extremely fast:

SELECT

MAX(YearValue) AS YearValue, MAX(MonthValue) AS MonthValue...

Sorry, forgot I ended up using a database-specific function in the code (was trying to avoid it). This approach should be safer overall anyway:

EXEC sp_MSforeachdb N'

IF ''?'' IN (''master'',...

SQL Server does not have an equivalent to that function or functionality :-(.

Yeah, that's the official story. But I don't 100% buy it.

With that many different fragments, if I were you, I'd run contig.exe on that(those) file(s) anyway.

Here's something more detailed. Btw, I avoided using I_S.SCHEMATA for the schema names because of the associated warnings in Books Online -- it's best to avoid using I_S views...

Dird (4/23/2013)

---

Is there any documentation/book which proves this? Oracle would be running implicit cursors here; I have a hard time...

I know my...

SQL Server triggers only fire once per statement, no matter how many rows are INSERTed or UPDATEd (or DELETEd).

Therefore, it's not safe to use variables to get column data (unless...