Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Defaults Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 5:13 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Great question. I guess I had to think about it more than some of the others though.
Post #1385097
Posted Thursday, November 15, 2012 6:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 906, Visits: 892
Dag-nabbit, that'll teach me to read more carefully. I read it as "how many" and not "which"! Duh. Well at least I got it right in my head.

Ron


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #1385116
Posted Thursday, November 15, 2012 6:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:09 PM
Points: 438, Visits: 334
Good question, and one that not too many of us consider daily. We ought to make note here that DEFAULT has been deprecated in an unspecified future version of SQL Server, so don't get too attached to it

Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1385124
Posted Thursday, November 15, 2012 6:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 1,989, Visits: 1,493
Thanks for the easy question this morning.



Everything is awesome!
Post #1385130
Posted Thursday, November 15, 2012 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 6,130, Visits: 8,394
Roland Alexander STL (11/15/2012)
We ought to make note here that DEFAULT has been deprecated in an unspecified future version of SQL Server, so don't get too attached to it

I think you are confusing default constraints (which are defined in the ANSI standard, and not deprecated) with "default objects" that can be bound to columns or alias data types.

The ANSI standard DEFAULT constraint is what's used in this question - no seperate object, but a default definition used as a constraint in the table.
The deprecated feature uses CREATE DEFAULT to create a default obejct, and then calls the stored procedure sp_binddefault to bind it to either a column or an alias data type.

Reference (hyperlink!): CREATE DEFAULT



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1385136
Posted Thursday, November 15, 2012 7:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:45 AM
Points: 1,921, Visits: 2,203
Kenneth, thanks for the question. Everyone says back to basics question. Basically, it wasn't for me, as I've never even seen code (mine or anyone else's that used this.). Got it right, both here and in my head.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1385141
Posted Thursday, November 15, 2012 7:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:09 PM
Points: 438, Visits: 334
Hugo Kornelis (11/15/2012)
Roland Alexander STL (11/15/2012)
We ought to make note here that DEFAULT has been deprecated in an unspecified future version of SQL Server, so don't get too attached to it

I think you are confusing default constraints (which are defined in the ANSI standard, and not deprecated) with "default objects" that can be bound to columns or alias data types.

The ANSI standard DEFAULT constraint is what's used in this question - no seperate object, but a default definition used as a constraint in the table.
The deprecated feature uses CREATE DEFAULT to create a default obejct, and then calls the stored procedure sp_binddefault to bind it to either a column or an alias data type.

Reference (hyperlink!): CREATE DEFAULT


No, I don't believe I'm confused. Have a look at the list at http://msdn.microsoft.com/en-us/library/ms143729.aspx where you'll find this entry:

Use of DEFAULT keyword as default value.
Do not use the word DEFAULT as a default value.

under "Not Supported In A Future Version of SQL Server".

Now, it may be that I misread this, but I'm hard-pressed to see how. Nonetheless I would be quite happy to be shown wrong.


Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1385147
Posted Thursday, November 15, 2012 7:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:09 PM
Points: 438, Visits: 334
Hugo Kornelis (11/15/2012)
Roland Alexander STL (11/15/2012)
We ought to make note here that DEFAULT has been deprecated in an unspecified future version of SQL Server, so don't get too attached to it

I think you are confusing default constraints (which are defined in the ANSI standard, and not deprecated) with "default objects" that can be bound to columns or alias data types.

The ANSI standard DEFAULT constraint is what's used in this question - no seperate object, but a default definition used as a constraint in the table.
The deprecated feature uses CREATE DEFAULT to create a default obejct, and then calls the stored procedure sp_binddefault to bind it to either a column or an alias data type.

Reference (hyperlink!): CREATE DEFAULT


Re-reading the posts I realize I was not specific in what I said was deprecated. Default constraints are NOT deprecated, quite obviously, but using the DEFAULT keyword in an INSERT statement is deprecated. I apologize for the confusion and thank Hugo for pointing out the inadequacy.


Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1385148
Posted Thursday, November 15, 2012 7:53 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:50 PM
Points: 615, Visits: 693
I've never actually used the keyword DEFAULT before...seems kind of redundant. Oh well, I manage to apply logic to the other possible answers and derive that it must be a legit feature. Thanks for the 2 correct answers hint.

Aigle de Guerre!
Post #1385172
Posted Thursday, November 15, 2012 8:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 3,467, Visits: 1,839
Roland Alexander STL (11/15/2012)
Hugo Kornelis (11/15/2012)
Roland Alexander STL (11/15/2012)
We ought to make note here that DEFAULT has been deprecated in an unspecified future version of SQL Server, so don't get too attached to it

I think you are confusing default constraints (which are defined in the ANSI standard, and not deprecated) with "default objects" that can be bound to columns or alias data types.

The ANSI standard DEFAULT constraint is what's used in this question - no seperate object, but a default definition used as a constraint in the table.
The deprecated feature uses CREATE DEFAULT to create a default obejct, and then calls the stored procedure sp_binddefault to bind it to either a column or an alias data type.

Reference (hyperlink!): CREATE DEFAULT


Re-reading the posts I realize I was not specific in what I said was deprecated. Default constraints are NOT deprecated, quite obviously, but using the DEFAULT keyword in an INSERT statement is deprecated. I apologize for the confusion and thank Hugo for pointing out the inadequacy.


I wonder if that means that the DEFAULT keyword won't work in updates either. If so I hope they replace the functionality with something else. We use it in some triggers to update a "lastupdate" column. UPDATE tablename SET columname = DEFAULT. I may be missing something but I can't see a way to do this without the DEFAULT keyword.


Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #1385180
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse