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, 2 months 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, 4 months 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...