Sql 2017 upgrade from 2014 with sequential numbers

• Jeff Moden

  SSC Guru

  Points: 1004436

  More actions

  October 29, 2018 at 6:55 am

  #2011512

  ebeng - Monday, October 29, 2018 5:00 AM

  Thanks Jeff 
  Ill check out the Estimator also

  Whether it fixes it or not, the Quirky Update is written incorrectly.  Adding a unique index to the table could cause the next break.  You also have no safety checks in them.

  --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

  October 29, 2018 at 11:12 am

  #2011559

  Have scaled down the Compatibility level on his databases to 100 and this has sorted it for now until fixed 
  Have started the investigation on how to pickup all his quirky updates in code and agent jobs and replace with better structured code 
  Anyone know how I would go about doing that ?

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  October 29, 2018 at 11:17 am

  #2011561

  ebeng - Monday, October 29, 2018 11:12 AM

  Have scaled down the Compatibility level on his databases to 100 and this has sorted it for now until fixed 
  Have started the investigation on how to pickup all his quirky updates in code and agent jobs and replace with better structured code 
  Anyone know how I would go about doing that ?

  Did you check out Jason's code?

  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

• autoexcrement

  SSCertifiable

  Points: 5956

  More actions

  October 29, 2018 at 1:38 pm

  #2011587

  Jason A. Long - Friday, October 26, 2018 2:11 PM

  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_n1 a CROSS JOIN cte_n1 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.n;

  I think the above code possibly has some issues. Are cte_n2 and cte_n3 supposed to be identical? And also, i think you want "FROM cte_Tally t", not "FROM cte_Tally t.n". Maybe this?
  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), -- changed
  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; -- changed

  ---

  "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  October 29, 2018 at 2:40 pm

  #2011599

  autoexcrement - Monday, October 29, 2018 1:38 PM

  Jason A. Long - Friday, October 26, 2018 2:11 PM

  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_n1 a CROSS JOIN cte_n1 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.n;

  I think the above code possibly has some issues. Are cte_n2 and cte_n3 supposed to be identical? And also, i think you want "FROM cte_Tally t", not "FROM cte_Tally t.n". Maybe this?
  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), -- changed
  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; -- changed

  You are correct... Not sure how I let that through without noticing... Sorry about that. :crying:
  I've corrected the original post.

• ebeng

  SSC Enthusiast

  Points: 193

  More actions

  November 1, 2018 at 4:10 am

  #2012008

  Hi 

  Thanks Jason
  Yes I passed it onto the person doing the quirky updates 
  Now just to find a way to pickup all hes quirky updates 
  Thanks for all the help
  E