Just to clarify, Ben - it's this system you are attempting to tune:

ben.brugman (2/23/2016)

---

SQL-server 1.

Has been designed from the start with clustered indexes on the subscriberid. And this has...

Grumpy DBA (2/23/2016)

---

^ It goes back to the old proverb I threw out in another thread... Give a man a fish, he eats for a day. Teach a man...

Your two queries are logically different - sorry if I was ambiguous, what I meant was remove the NULL checks on the join columns only, like this:

SELECT COUNT(1)

FROM PaleoData.Tax.PodrobnostiTmp...

Check that "auto create statistics" is enabled for the database.

Inner joins eliminate null values of the join columns:

inner join PaleoData.Tax.PodrobnostiTmp P2

on p1.Family_13000 = p2.Family_13000

you don't need to check...

sushil_dwid (2/23/2016)

---

Hi,

Could you please suggest the index on table(dbo.person) that can fast my below sql..

select Region,type,UID,Week

(select Region,type,UID,

CASE

WHEN epu.updatetime...

spaghettidba (2/23/2016)

---

There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:

CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1, 3,2)...

The fastest index for the 2006 query was index 3, which isn't covering. Can you post the execution plan you get for this?

Have you tried a crosstab query? Immensely useful technique to learn, easier to figure out than PIVOT (and usually faster too).

This excellent article by Jeff Moden [/url]shows you how...

Preliminary results:

create index ix_helper01 ON #TestTable (StartDate,EndDate) INCLUDE (wide)

create index ix_helper02 ON #TestTable (EndDate,StartDate) INCLUDE (wide)

DECLARE @Now DATETIME = GETDATE()

select sum(datalength(wide)) Some_field_information

from #testtable WITH (INDEX (ucx_Stuff))

where StartDate < '20160301'...

Nikku (2/18/2016)

---

Hi All

I have two tables t1 & t2 which contains the same records. t1 is for bank1 & t2 is for bank2

i.e t1

accno name balance

001000 a ...

ben.brugman (2/18/2016)

---

Did read your article....

You're welcome, thanks for the feedback.

Be sure to read Dwain's excellent article linked by Luis.

SELECT COL1, COL2, COL3, x.*

FROM #TEST

CROSS APPLY (VALUES (ITEM1, DESC1),(ITEM2, DESC2),(ITEM3, DESC3)) x (COL4, COL5)

Funny - the first time I saw this technique used was in one of your posts, Mark.

Thanks for...

richardmgreen1 (2/17/2016)

---

Thanks both

I'm testing both and they are very close to what I need.

Chris - yours is closest to what I need but I have found a bit of an...