I am sorry to have to say this, but when it takes that much effort to explain what you need then it goes way beyond the amount of time virtually...

1) Your WHERE clause is VERY complex for the optimizer, leading to bad estimates and both a suboptimal plan and multiple spills to tempdb for sorting.

2) You have functions around...

1) You should always look for set-based solutions and avoid "looping" of any kind if you can avoid it.

2) Avoid scalar and multi-statement table valued functions at almost any cost....

do a web search for itzik ben-gan hierarchy

If you are ever planning on using this UDF against data in a table (as opposed to onesy stuff passed in from an app) then do NOT solve this problem...

1) There are umpteen things that could be at play here. Search the web for queries that tell you what is consuming tempdb resources.

2) Glenn Berry's SQL Server Diagnostic...

Recombinant (6/21/2016)

---

TheSQLGuru (6/21/2016)

---

It is EXCEPTIONALLY RARE that you should pick a bunch of columns as the clustered index. Those columns would get carried on every nonclustered index as the pointer...

How about a poorly configured or overwhelmed IO path between host and SAN? I've seen a silly 1Gbps iSCSI IO path at clients more times than I care to remember.

Recombinant (6/21/2016)

---

If you are reviewing the indexes then you may also want to review the decision about which index is clustered. By the time you have coalesced Index 1 and...

I would do it with an AND NOT EXISTS (SELECT ...) clause myself.

Too many "it depends" here. In general I always look to reduce indexes/indexing when they are direct or near duplicates. Just a LOT of cost in many ways for them....

1) I think to see the second from last character you would need substring(field, len(field) - 1, 1) or some such. Use ASCII to see the character code.

2) Perhaps there...

Maybe there is a terminator issue in the raw data file? there are char(10)s, char(13)s and a pair of them that can be used for line terminators and most file...

dan-572483 (6/14/2016)

---

On SQL2012 we have an Error log table with 6.7 million records going back to 2013. I am told only records from the past 30 days or so...

joe-584802 (6/14/2016)

---

Thank you so much for the replies this is what I ended up doing as a Scalar-Valued Function

CREATE FUNCTION [dbo].[GetHarvestYear] (@InputDate datetime)

RETURNS int

AS

BEGIN...