WITH PrevEnds
AS
(
    SELECT E.code_events, E.[Events], E.Events_start, E.Events_end
        ,LAG(E.Events_end) OVER (PARTITION BY E.code_events ORDER BY E.Events_start) AS Prev_Events_end
...

• This reply was modified 4 years ago by Ken McKelvey.

You have posted in a SQL2019 forum but do not say what COMPATIBILITY_LEVEL your database is using.

If COMPATIBILITY_LEVEL >= 120 you could also try OPTION (QUERYTRACEON 9481)

If COMPATIBILITY_LEVEL < 120...

If the query apart from the WHERE and ORDER BY clauses is outside your control you could try:

WHERE (EA.IsArchived = 'F' OR EA.IsArchived IS NULL) AND EN.Name...

Do not do this - it will mess up the query plan.

If the query is complicated then put it in a view and select from the view with different WHERE...

You are right. I missed it because our two SQL2019 servers were created after I had spotted the problem in SQL2017 so used the test restore routine which ignored error...

For the session's default database:

SELECT
CASE S.transaction_isolation_level
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable Read'
WHEN 4 THEN 'Serializable' 
WHEN 5...

I suspect you need to use the FIRST_VALUE() and LAST_VALUE() windowed functions but without test data it is difficult to say.

With over 800 points you should know how to post...

As mentioned in the link below,

https://www.codemag.com/article/0605061/SQL-Server-2005-Query-Notifications-Tell-.NET-2.0-Apps-When-Critical-Data-Changes

For client-side applications, query notification users should not exceed ten concurrent users , in SQL Server 2005.

Will there be any performance degradation if...

• This reply was modified 4 years, 1 month ago by Ken McKelvey.

or using Windowed functions:

WITH Gaps
AS
(
SELECT ID, eff_dt, term_dt, prod
,CASE
WHEN eff_dt = DATEADD(DAY, 1, LAG(term_dt) OVER (PARTITION BY ID, Prod ORDER BY eff_dt))
THEN 0
ELSE 1
END AS Gap
FROM #memb
)
,Grps
AS
(
SELECT...

I have no idea why you want to do this but you could try the pivoting the results of the OUTPUT clause. eg:

DECLARE @ForSecond TABLE
(  
...

I am glad the solution works.

If you find recursion to be too slow you might also want to consider the Quirky Update:

https://www.sqlservercentral.com/articles/solving-the-running-total-and-ordinal-rank-problems-rewritten

This does have the overhead of having to...

If this is really a picking algorithm it looks somewhat simplistic to me. In my limited expericence picking algorithms tend to be a lot more complex and are best done...

That data will certainly break my first effort. The safe, albeit slow, use of recursion produces slightly different results to you but maybe my understanding of the problem is wrong.