Eirikur Eiriksson (5/12/2014)

---

The...

Here are the execution plans and a spreadsheet with the results tab from Plan Explorer.

Here is what I have seen in a couple of separate threads already, windowing functions aren't always the best solution to solving a problem.

Here is the latest execution plans and spreadsheet with the information from SQL Sentry Plan Explorer results tab when using it to generate the actual execution plans.

Not reposting all the code again, but here is the Statistics output after adding the latest code to the group:

********** Simple Outer Apply **********

(9 row(s) affected)

Table 'Company'. Scan...

Eirikur Eiriksson (5/12/2014)

---

Here is a simpler window function (2012/2014) version. Although simpler than the earlier 12/14 one, it performs similar, mainly because of an sort operator which is around 60%...

Sean Lange (5/12/2014)

---

Ed Wagner (5/12/2014)

---

Sean Lange (5/12/2014)

---

paul.knibbs (5/12/2014)

---

You can always use good ol' Character Map that ships with Windows to grab the symbol--bit of a pain, but it works well...

Luis Cazares (5/12/2014)

---

This might not be the best option for performance (as I haven't done real performance testing) but it's quite simple.

SELECT c.IdCompany,

c.CompanyName,

...

I still haven't setup a million row test, and really should to really test the proposed solutions, but I made a few minor changes to the CompanyGroup table. I...

Eirikur Eiriksson (5/11/2014)

---

For comparison, here is a SQL 2012/2014 window function version. Without an POC index, Lynn's code is still faster.

😎

SELECT

IdCompany

,CompanyName

...

Just for comparison I ran the following two queries against the sample data:

select

c.IdCompany,

c.CompanyName,

isnull(oa.CompanyCode, c.CompanyCode) CompanyCode

from

...

And here is an alternative solution:

Create Table dbo.Company(

IdCompany int primary key,

CompanyName varchar(50),

CompanyCode varchar(50));

Insert into Company

values

...

FYI, I changed the RecordingDate data type from varchar(255) to date (since this is a SQL Server 2008 forum). Dates really should be stored using the correct data type.

A lot of extra work when it isn't really needed if you ask me.

If OBJECT_ID('tempdb..#Crusty_Table') is not null

Begin

Drop table #Crusty_Table

End

Create table #Crusty_Table (

UniqueIDvarchar(255),

RecordingDatedate

)

Insert Into #Crusty_Table(UniqueID, RecordingDate)

Select '123738','05/21/1969' UNION All

Select '123738','06/09/2010'...

David Burrows (5/11/2014)

---

Lynn Pettis (5/11/2014)

---

Eirikur Eiriksson (5/11/2014)

---

Hi David and Lynn,

thank you both for the input, I appreciate it.

Firstly, this is a valuable discussion and would merit a thread on its...