Lynn Pettis (12/18/2013)

---

Luis Cazares (12/18/2013)

---

ScottPletcher (12/18/2013)

---

Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.

IF...

Luis Cazares (12/18/2013)

---

ScottPletcher (12/18/2013)

---

Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.

IF (@category IS...

If your lookups will most often be by column2, you need to cluster the table on column2, or an encoded version of it, rather than having a dopey, useless cluster...

Yes, definitely do a logical design first. Don't even think about artificial keys yet.

You need to identify "entity" and "attribute". Roughly:

Entity = things about which you store unique...

Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.

IF (@category IS NULL OR @category...

Rewriting the query and/or adding nonclustered indexes won't help, unless you basically rewrite the entire table by including a gazillion in the nonclus index(es) -- and constantly maintain it as...

I think you need a GROUP BY rather than a DISTINCT.

Is the code below closer/close to the final output you want?

SELECT

e.ID,e.FirstName,e.Area

,s.ID,s.Qty,s.ProductName, s.Sale

FROM...

Sean Lange (12/16/2013)

---

A slight modification to the fine code Luis posted should work for you. I changed the global temp table to instead use a temp table inside the dynamic...

kapil_kk (12/14/2013)

---

ScottPletcher (12/13/2013)

---

I suggest making index changes scripted below, following these steps:

1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.

2) Run...

I suggest making index changes scripted below, following these steps:

1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.

2) Run the code...

1) Maybe you can combine the three separate UPDATEs into one, as shown below, avoiding repeated joins of the same tables.

2) Depending on the row counts, you might consider creating...

You could also consider something like below, particularly since presumably you are on Enterprise Edition and thus can recreate all the non-clustered indexes ONLINE:

Before the shrink:

1) verify you have current...

Try changing:

!= ''

to

IS NOT NULL.

The variables should contain NULL, not be empty, if no row is found.

For performance reasons, it would almost certainly be better to have a trigger on the Calculations table that automatically identified and saved all variables used in a normalized table whenever...

Maybe something like this?:

declare @replace1 varchar(max)

declare @replace2 varchar(max)

select @replace1 = (

select 'REPLACE('

from #CalculationVariables

for xml path('')

...