Sql 2017 upgrade from 2014 with sequential numbers

Question

Post reply

Sql 2017 upgrade from 2014 with sequential numbers

ebeng

SSC Enthusiast

Points: 193
More actions
October 26, 2018 at 12:01 pm

#372374

Hi
One of my users ran the following code
set nocount on ;with R0 as(select 1 as f1 union all select 1 union all select 1 union all select 1 union all select 1),
R1 as(select a.f1 as f1 from R0 a cross join R0 b),
R2 as(select a.f1 as f1 from R1 a cross join R1 b),
R3 as(select a.f1 as f1 from R2 a cross join R2 b)
select top 350000 f1
into t1
from R3
go
set nocount off
declare @v1 int = 12345 update a
set @v1 = a.f1 = @v1+1
from t1 a
GO
select * from t1 order by 1
drop table t1
In sql 2014 he got the numbers sequentially as in
12346
12347
12348
But in 2017 he gets the following
12346
12346
12346
12346
12346
12346
12346
12346
12346
12346
12347
12347
12347
etc
Thought this was because of IDENTITY_CACHE but turned that off with same results
Also tried installing CU12 on an instance but same affect
Any suggestions?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

When I run it on 2017, the numbers are sequential.

What is the point of this code?

And please do not rely on the 'quirky update' unless you are prepared to follow the rules (see here).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ebeng SSC Enthusiast Points: 193 More actions · Answer 2

Thanks for the reply

He creates an autonumber based on another table
He does this in lots of code so not as easy as changing it to an identity or SEQ number

drew.allen SSC Guru Points: 76999 More actions · Answer 3

ebeng - Friday, October 26, 2018 12:24 PM
Thanks for the reply
He creates an autonumber based on another table
He does this in lots of code so not as easy as changing it to an identity or SEQ number

There are better methods for doing this using a tally table. Itzik Ben-Gan wrote a blazing fast function that produces a tally table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ebeng SSC Enthusiast Points: 193 More actions · Answer 4

Hi

Just figured out it had nothing to do with the SQL upgrade but it does have something to do with the compatibility level
If i set it to SQL 2012 or earlier it gives it to me sequentially but anything above that it has the same wrong affect

Know how to solve it now but still dont know why
Thanks
Eben

ebeng SSC Enthusiast Points: 193 More actions · Answer 5

drew.allen - Friday, October 26, 2018 12:44 PM
ebeng - Friday, October 26, 2018 12:24 PM
Thanks for the reply
He creates an autonumber based on another table
He does this in lots of code so not as easy as changing it to an identity or SEQ number
There are better methods for doing this using a tally table. Itzik Ben-Gan wrote a blazing fast function that produces a tally table.
Drew

Hi Drew

Yip agree theres much better ways of doing it but hes implemented this code in lots of places and on lots of instances so not so easy to pickup .
See comment above on compatibility
Thanks
Eben

Phil Parkin SSC Guru Points: 246993 More actions · Answer 6

ebeng - Friday, October 26, 2018 12:47 PM
Hi
Just figured out it had nothing to do with the SQL upgrade but it does have something to do with the compatibility level
If i set it to SQL 2012 or earlier it gives it to me sequentially but anything above that it has the same wrong affect
Know how to solve it now but still dont know why
Thanks
Eben

That's not what I see. I'm running in 140 mode (2017) and see sequential numbers.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246993 More actions · Answer 7

ebeng - Friday, October 26, 2018 12:49 PM
Hi Drew
Yip agree theres much better ways of doing it but hes implemented this code in lots of places and on lots of instances so not so easy to pickup .
See comment above on compatibility
Thanks
Eben

It should be refactored ASAP. The results will be unreliable until that happens.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ebeng SSC Enthusiast Points: 193 More actions · Answer 8

Phil Parkin - Friday, October 26, 2018 12:55 PM
ebeng - Friday, October 26, 2018 12:49 PM
Hi Drew
Yip agree theres much better ways of doing it but hes implemented this code in lots of places and on lots of instances so not so easy to pickup .
See comment above on compatibility
Thanks
Eben
It should be refactored ASAP. The results will be unreliable until that happens.

Hi Phil
Thansk for the reply
Strange that it works on yours in 2014 as ive tested this now on about 4 instances across 3 servers

Thanks yip agree it needs to change asap
Just wish I knew why?

Thanks
Eben

Phil Parkin SSC Guru Points: 246993 More actions · Answer 9

ebeng - Friday, October 26, 2018 1:00 PM
Hi Phil
Thansk for the reply
Strange that it works on yours in 2014 as ive tested this now on about 4 instances across 3 servers
Thanks yip agree it needs to change asap
Just wish I knew why?
Thanks
Eben

2017, not 2014, but still strange, I agree.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 10

Phil Parkin - Friday, October 26, 2018 1:36 PM
ebeng - Friday, October 26, 2018 1:00 PM
Hi Phil
Thansk for the reply
Strange that it works on yours in 2014 as ive tested this now on about 4 instances across 3 servers
Thanks yip agree it needs to change asap
Just wish I knew why?
Thanks
Eben
2017, not 2014, but still strange, I agree.

MAXDOP / CTFP.

I bet you, Phil, have configured CTFP to be something sensible, so the query isn't going parallel.

Like you said - Quirky Update is great, but you have to follow the rules.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 11

This will product the desired results without the need for temp tables or a quirky update...

WITH cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b), cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b), cte_Tally (n) AS ( SELECT TOP (350000) ROW_NUMBER() OVER (ORDER BY (SELECT a.n)) FROM cte_n3 a CROSS JOIN cte_n3 b ) SELECT t.n FROM cte_Tally t;

ebeng SSC Enthusiast Points: 193 More actions · Answer 12

ThomasRushton - Friday, October 26, 2018 2:00 PM
Phil Parkin - Friday, October 26, 2018 1:36 PM
ebeng - Friday, October 26, 2018 1:00 PM
Hi Phil
Thansk for the reply
Strange that it works on yours in 2014 as ive tested this now on about 4 instances across 3 servers
Thanks yip agree it needs to change asap
Just wish I knew why?
Thanks
Eben
2017, not 2014, but still strange, I agree.
MAXDOP / CTFP.
I bet you, Phil, have configured CTFP to be something sensible, so the query isn't going parallel.
Like you said - Quirky Update is great, but you have to follow the rules.

Thanks for the reply
That makes sense
Ill test on an instance without MAXDOP
Although I did test on Azure instance and it came out fine and sure Maxdop is set there
Guess like Phil said I need to get cracking in changing that guys code

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 13

ebeng - Friday, October 26, 2018 12:47 PM
Hi
Just figured out it had nothing to do with the SQL upgrade but it does have something to do with the compatibility level
If i set it to SQL 2012 or earlier it gives it to me sequentially but anything above that it has the same wrong affect
Know how to solve it now but still dont know why
Thanks
Eben

It could also be from the "new" Cardinality Estimator. Since the Quirky Update was written incorrectly, it could be broken by that.

There is a Trace Flag (the number escapes me just now) to allow you to "set back" to original but I don't know how many revs into the future they'll carry that. With that in mind and the fact that the Quirky Update (as much as I loved it and used it) is no longer necessary for most things, I recommend finding and changing the code. If you don't want to do that, you'll still need to find and change the code so that the Quirky Update is written correctly.

To be honest and with the way the Quirky Update in the code you posted is written, I'm surprised that it hasn't broken yet or, worse, that you didn't notice it broke because the safety check is also missing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ebeng SSC Enthusiast Points: 193 More actions · Answer 14

ebeng

SSC Enthusiast

Points: 193

October 29, 2018 at 5:00 am

#2011496

Thanks Jeff
Ill check out the Estimator also