Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kenneth Fisher » Defaults
Add to briefcase
34 posts, Page 2 of 4
««1234»»»

Defaults Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
(Bob Brown)
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: Friday, April 19, 2013 10:27 AM
Points: 690, Visits: 1,100
Great question. I guess I had to think about it more than some of the others though.
Post #1385097
ronmoses
Posted Thursday, November 15, 2012 6:19 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 651, Visits: 688
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
Roland Alexander STL
Posted Thursday, November 15, 2012 6:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:10 AM
Points: 306, Visits: 311
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
Dana Medley
Posted Thursday, November 15, 2012 6:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 1,356, Visits: 874
Thanks for the easy question this morning.


Oh no, we're toast! I've got this. *Keyboard clatter* Woah, how'd you do that? I'm a DBA...Booyah

Yeah, uh huh, you know what it is. Everything I do, I do it big
Post #1385130
Hugo Kornelis
Posted Thursday, November 15, 2012 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062
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
Thomas Abraham
Posted Thursday, November 15, 2012 7:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:58 AM
Points: 1,152, Visits: 1,457
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.
Post #1385141
Roland Alexander STL
Posted Thursday, November 15, 2012 7:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:10 AM
Points: 306, Visits: 311
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
Roland Alexander STL
Posted Thursday, November 15, 2012 7:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:10 AM
Points: 306, Visits: 311
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
Meow Now
Posted Thursday, November 15, 2012 7:53 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:15 PM
Points: 471, Visits: 485
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
Kenneth.Fisher
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: 2 days ago @ 1:16 PM
Points: 3,367, Visits: 1,567
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
34 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.