Jeff Moden (12/18/2013)

---

ScottPletcher (12/18/2013)

---

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

I don't object to ISNULL() in the SELECT list, just in WHERE conditions. Since it shouldn't be used in WHERE, it's only consistent not to use it in IF...

I'm truly curious now:

Can anyone tell me:

Where "IS NULL" can't be used in place of ISNULL()?

Evil Kraig F (12/18/2013)

---

Scott,

I'm not sure how that's more or less obscure, but it's certainly easier for me to read. I work with a lot of 'iffy' data in...

Lynn Pettis (12/18/2013)

---

ScottPletcher (12/18/2013)

---

Lynn Pettis (12/18/2013)

---

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

Lynn Pettis (12/18/2013)

---

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

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