SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kenneth Fisher / Defaults / Latest Posts

RE: Defaults

asifkareem — Wed, 28 Nov 2012 10:18:40 GMT

Good and Easy One.Thansk:hehe:

RE: Defaults

Kenneth.Fisher — Mon, 26 Nov 2012 13:11:50 GMT

[quote][b]ScottC91 (11/26/2012)[/b][hr]What is the difference between option 1 and option 2? If option one would cause the default to be selected because that column will be null why doesn't manually setting it null cause the same thing?[/quote]Basically because you are inserting a value. Well the absence of a value really, but you are being specific about what you want in the field. Look at it like this. In a nullable column without a user defined default, the default is actually NULL. If you insert a value, even NULL, you are telling SQL that you want that value in the field. If you don't pass in a value (or use the keyword DEFAULT) you are asking for the default.Interestingly enough the following code will demonstrate this:[code="sql"]DECLARE @temp TABLE (temp int null)INSERT INTO @temp VALUES (DEFAULT)SELECT * FROM @temp[/code]Note that I don't have a user defined default for the column temp, but when I use the DEFAULT keyword I get a null in the field.Hope that helps.

RE: Defaults

ScottC91 — Mon, 26 Nov 2012 12:53:05 GMT

What is the difference between option 1 and option 2? If option one would cause the default to be selected because that column will be null why doesn't manually setting it null cause the same thing?

RE: Defaults

kalyani.k478 — Mon, 19 Nov 2012 03:08:44 GMT

Good, easy question.

RE: Defaults

Koen Verbeeck — Fri, 16 Nov 2012 00:04:41 GMT

[quote][b]ronmoses (11/15/2012)[/b][hr]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[/quote]Ah, I'm not the only one :-DI was thinking: "why on earth are those checkboxes instead of a radio button?" :-)

RE: Defaults

Koen Verbeeck — Fri, 16 Nov 2012 00:03:13 GMT

Great question, thanks Kenneth.

RE: Defaults

Olga B — Thu, 15 Nov 2012 17:05:29 GMT

Nice and easy afternoon question, here. :)

RE: Defaults

SQLRNNR — Thu, 15 Nov 2012 13:52:50 GMT

Thanks for the question.

RE: Defaults

Revenant — Thu, 15 Nov 2012 13:46:38 GMT

Thanks for an easy basics question, Ken!

RE: Defaults

L' Eomot Inversé — Thu, 15 Nov 2012 13:17:32 GMT

Good basics question, nice and easy.Interesting discussion on the deprecation of 'DEFAULT' used as a default value; I can't imagine a more lunatic restriction myself, and I see Hugo's amusing question about the Japanese version as being relevant to just about every other language which is supprted for SQL Server.

RE: Defaults

Roland Alexander STL — Thu, 15 Nov 2012 08:50:21 GMT

[quote][b]Hugo Kornelis (11/15/2012)[/b][hr][quote][b]Roland Alexander STL (11/15/2012)[/b][hr]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.[/quote]It toook me a lot of digging to get to the bottom of this. But finally, I stumbled upon a Connect item filed against this specific text in Books Online, back in 2007. Link: [url=http://connect.microsoft.com/SQLServer/feedback/details/293052/incorrect-depreaction-use-of-default-keyword-as-default-value]http://connect.microsoft.com/SQLServer/feedback/details/293052/incorrect-depreaction-use-of-default-keyword-as-default-value[/url].Apparently, Microsoft wants to deprecate using the literal text 'DEFAULT' as a default value. Exactly why they want to do that is not clear, nor why, five years later, the wording has still not been clarified - but at least we can all heave a sigh of relief, knowing that Microsoft is not going to deprecate the ANSI-standard usage of syntax values. They are "only" going to outlaw one specific, randomly chosen value.(By the way, when thinking about this issue I can't help wondering what will happen in localized versions of SQL Server - will the Japanese version accept the string 'DEFAULT' but produce an error on the Japanese translation of that word? This looks like a very random idea to me!)[/quote]LOL! Random is right! Thanks, Hugo, for digging into this and clearing the matter up. Much appreciated. I wonder why MS hasn't clarified the matter on the deprecation page...

RE: Defaults

KWymore — Thu, 15 Nov 2012 08:49:24 GMT

Nice question. I had never actually used the DEFAULT keyword in an insert statement before or seen it used. I have used default constraints many times but never considered doing something like; INSERT INTO #QOTD VALUES(1,DEFAULT) or UPDATE #QOTD SET WithDefault = DEFAULT.

RE: Defaults

Hugo Kornelis — Thu, 15 Nov 2012 08:24:49 GMT

[quote][b]Roland Alexander STL (11/15/2012)[/b][hr]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.[/quote]It toook me a lot of digging to get to the bottom of this. But finally, I stumbled upon a Connect item filed against this specific text in Books Online, back in 2007. Link: [url=http://connect.microsoft.com/SQLServer/feedback/details/293052/incorrect-depreaction-use-of-default-keyword-as-default-value]http://connect.microsoft.com/SQLServer/feedback/details/293052/incorrect-depreaction-use-of-default-keyword-as-default-value[/url].Apparently, Microsoft wants to deprecate using the literal text 'DEFAULT' as a default value. Exactly why they want to do that is not clear, nor why, five years later, the wording has still not been clarified - but at least we can all heave a sigh of relief, knowing that Microsoft is not going to deprecate the ANSI-standard usage of syntax values. They are "only" going to outlaw one specific, randomly chosen value.(By the way, when thinking about this issue I can't help wondering what will happen in localized versions of SQL Server - will the Japanese version accept the string 'DEFAULT' but produce an error on the Japanese translation of that word? This looks like a very random idea to me!)

RE: Defaults

Lon-860191 — Thu, 15 Nov 2012 08:13:56 GMT

Good, easy question.Thanks

RE: Defaults

Kenneth.Fisher — Thu, 15 Nov 2012 08:09:08 GMT

[quote][b]Roland Alexander STL (11/15/2012)[/b][hr][quote][b]Hugo Kornelis (11/15/2012)[/b][hr][quote][b]Roland Alexander STL (11/15/2012)[/b][hr]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 :-)[/quote]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!): [url=http://msdn.microsoft.com/en-us/library/ms173565.aspx]CREATE DEFAULT[/url][/quote]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.[/quote]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.

RE: Defaults

Meow Now — Thu, 15 Nov 2012 07:53:18 GMT

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. :cool:

RE: Defaults

Roland Alexander STL — Thu, 15 Nov 2012 07:18:19 GMT

[quote][b]Hugo Kornelis (11/15/2012)[/b][hr][quote][b]Roland Alexander STL (11/15/2012)[/b][hr]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 :-)[/quote]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!): [url=http://msdn.microsoft.com/en-us/library/ms173565.aspx]CREATE DEFAULT[/url][/quote]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.

RE: Defaults

Roland Alexander STL — Thu, 15 Nov 2012 07:16:01 GMT

[quote][b]Hugo Kornelis (11/15/2012)[/b][hr][quote][b]Roland Alexander STL (11/15/2012)[/b][hr]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 :-)[/quote]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!): [url=http://msdn.microsoft.com/en-us/library/ms173565.aspx]CREATE DEFAULT[/url][/quote]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.

RE: Defaults

Thomas Abraham — Thu, 15 Nov 2012 07:07:58 GMT

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. ;-)

RE: Defaults

Hugo Kornelis — Thu, 15 Nov 2012 07:01:13 GMT

[quote][b]Roland Alexander STL (11/15/2012)[/b][hr]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 :-)[/quote]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!): [url=http://msdn.microsoft.com/en-us/library/ms173565.aspx]CREATE DEFAULT[/url]

RE: Defaults

Dana Medley — Thu, 15 Nov 2012 06:44:35 GMT

Thanks for the easy question this morning. :-)

RE: Defaults

Roland Alexander STL — Thu, 15 Nov 2012 06:37:55 GMT

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

RE: Defaults

ronmoses — Thu, 15 Nov 2012 06:19:29 GMT

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

RE: Defaults

(Bob Brown) — Thu, 15 Nov 2012 05:13:59 GMT

Great question. I guess I had to think about it more than some of the others though.

RE: Defaults

Lokesh Vij — Thu, 15 Nov 2012 04:57:04 GMT

Thanks Kenneth. Good question to brush-up our basics :-)

RE: Defaults

call.copse — Thu, 15 Nov 2012 03:53:15 GMT

I certainly might have had to think about the NULL one if the select 2 hint had not been there - but hopefully not for too long.

RE: Defaults

Andrew Watson-478275 — Thu, 15 Nov 2012 03:45:51 GMT

[quote][b]Toreador (11/15/2012)[/b][hr]There'd have been a few more wrong answers if it weren't for the "select 2" hint...[/quote]Given that the selections only add up to 188%, I don't think everyone's taken the hint :w00t:.

RE: Defaults

Toreador — Thu, 15 Nov 2012 02:31:34 GMT

There'd have been a few more wrong answers if it weren't for the "select 2" hint...

RE: Defaults

Stuart Davies — Thu, 15 Nov 2012 01:07:13 GMT

Thanks for the question - nice and easy way to start the day

RE: Defaults

malleswarareddy_m — Thu, 15 Nov 2012 00:23:48 GMT

Easy one

RE: Defaults

Stewart "Arturius" Campbell — Wed, 14 Nov 2012 23:27:57 GMT

Good back-to-basics question, thanks Kenneth

RE: Defaults

demonfox — Wed, 14 Nov 2012 21:47:21 GMT

good and easy one ...

RE: Defaults

bitbucket-25253 — Wed, 14 Nov 2012 20:39:57 GMT

Thanks for a good back to basics question ...

Defaults

Kenneth.Fisher — Wed, 14 Nov 2012 20:38:59 GMT

Comments posted to this topic are about the item [B]Defaults[/B]