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...

I suggest adding a computed column to the table to determine the value: then the definition is only one place, and is thus very easy to change everywhere:

ALTER TABLE dbo.tablename...

The code's not really ready to be used in dev or QA either.

NO reason to search the same table multiple times, esp. not once per column.

Instead, should do a single...

Here's T-SQL code to generate the full @decode_string:

DECLARE @decode_string char(2055)

;WITH

cteDigits AS (

SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT...