Default Constraint Script

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    Comments posted to this topic are about the item Default Constraint Script

  • Andrew G

    SSChampion

    Points: 12687

    I dont get this issue in SQL 2014 SP1 or SQL 2014 CU6?

    It's the same statement generated with or without data.

  • TjhomasH6610

    SSCommitted

    Points: 1632

    On SQL 2012 I get script B before and after inserting the data.

    Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)

    Jul 22 2014 15:26:36

    Copyright (c) Microsoft Corporation

    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    I also prefer script A for readability.

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    I have tested it in SQL Server 2008R2 / SQL Server 2012 Express and SQL Server 2014 Express.

    You are right in SQL Server 2012 it gives 2 times the script B. (also in SQL Server 2008R2)

    That is why I was suprised that SQL Server 2014 did it on another way.

    I mentioned in the question that I used SQL Server 2014.

    So I thought it should be worth to share this issue as a QOTD.

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    And by the way I like script A the most.

    It is shorter. When you read the table script you see directly if there is a default on a column.

    It is then not necessary to look in the script afterwards the table script.

  • Mauricio_

    SSCrazy

    Points: 2813

    SQL 2012, I've got Script B and A.

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    Is that SQL Server 2012 Express or another version ?

    I have tested it in the Express Version

  • Mauricio_

    SSCrazy

    Points: 2813

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

    May 14 2014 18:34:29

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

  • Toreador

    SSChampion

    Points: 11231

    I guessed right, as I'd have expected B for both, but that would have made the question pointless so it was a question of which one gave A instead 😉

    But if this isn't documented, then presumably it could change at any point, or be different between versions/editions/releases, which could explain people's differing results.

  • Hany Helmy

    SSChampion

    Points: 13321

    TjhomasH6610 (6/3/2015)


    On SQL 2012 I get script B before and after inserting the data.

    Same here.

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    I used

    Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

    Dec 28 2012 20:23:12

    Copyright (c) Microsoft Corporation

    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    Express edition not Developper Edition for 2012 but I also used Express for 2014 so that is not the difference.

  • paul.knibbs

    SSCoach

    Points: 15270

    Just guessed, got it wrong--not too surprising since apparently the only way to answer correctly would have been to actually run the operation in SQL server (and also, judging from the replies so far, be running the right version).

  • TomThomson

    SSC Guru

    Points: 104772

    I dislike script B intensely (too verbose and splitting one operation into two without wrapping a transaction around them). Pity it's what I've become used to getting (in fact I usually edit anything in this form to be more like script A).

    The behaviour of SQL Server isn't really predictable on this stuff, it can change any time as a side effect of a hot fix or a CU or an SP or a new release, and may not be consistent across editions with the same version and update state. It may even change for some other causes too - I don't imagine anyone has done enough testing to be sure that the behaviour in the question is consistent whatever else (other constraints, indexes, CLR Types, Filestream, instore optimisation, for example) is in the table definition or between differently configured instances.

    Tom

  • Ed Wagner

    SSC Guru

    Points: 286958

    Hany Helmy (6/4/2015)


    TjhomasH6610 (6/3/2015)


    On SQL 2012 I get script B before and after inserting the data.

    Same here.

    I get the same thing.

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

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