Changing the Default Value is the question behind this data-type puzzle. I keep the expression, data type and nullability separate.
Start with data type A and default expression B. Change the column to type C without intentionally changing the default expression. The resulting value in this scenario is D. D differs from B.
- Identify the original type A and default B.
- Identify the new type C and resulting default value D.
Include a small reproducible script with the actual constraint definition. Show the stored result as well. That helps distinguish a changed definition from conversion or display behavior.
The challenge doesn’t assert that ALTER COLUMN silently rewrites every default constraint. Explain the specific scenario and its assumptions. The comment-holding deadline belonged to the original 2017 event.
A different displayed value is not proof of a rewritten default, it is a result that needs explanation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
18 Comments. Leave new
1. data type a default s or else
2. data type e default g
1 The original datatype A and default value A
2 The new datatype Y and new default value G
data type N default Y
Hi Pinal,
It won’t allow to modify the datatype, because already the datatype was inherit to the default value.
You need to drop the default value and modify the column datatype.
Thanks
In case interested here are the open bug cases I have with MS , I havn’t heard back from them since summer but often there are data transformation issues with float and varchar or character based string functions.
https://docs.microsoft.com/en-us/collaborate/connect-redirect
https://docs.microsoft.com/en-us/collaborate/connect-redirect
https://docs.microsoft.com/en-us/collaborate/connect-redirect
https://docs.microsoft.com/en-us/collaborate/connect-redirect
1 The original datatype T and default value B
2 The new datatype P and new default value D
1 The original datatype o and default value B
2 The new datatype P and new default value D
Hi All,
Though the puzzle was simple, I think not many understood it.
Here is the answer –
1 The original datatype was character datatype (char, varchar,etc) with default value empty string
2 the new datatype is INT datatype with default value zero
New datatype can be bit and default value false ?
Then
1 The original datatype was character datatype (char, varchar,etc) with default value P
2 the new datatype is INT datatype with default value zero
@Pinal: this answer is good.
While trying to modify the datatype, it will give an error and suggest to drop the constraint. Once the constraint is dropped, with the new default we have two options with check and with no check.
1. If we use with check, it would check for existing values and needs to be updated to latest default as it gives an error.
2. with no check, the existing values are ignored and the constraint applies to new insertions.
Forgot my least favorite SQL transition:
select cast(” as date) as test
results in 1900-01-01. If you use sas I believe its 1960-01-01.
The original datatype bit and default value 1
The new datatype int and new default value 6
Hi Pinal,
1 The original datatype was nvarchar with default N’E’
2 the new datatype is varchar datatype with default value ‘A’
Hi Pinal,
1 The original datatype was varchar with default –
2 the new datatype is varchar datatype with default value +
Hi Pinal,
1 The original datatype was bit with default 1
2 the new datatype is varchar datatype with default value ‘!’
or
1 The original datatype was varchar with default ”
2 the new datatype is int datatype with default value 0
or
1 The original datatype was nvarchar with default N’e’
2 the new datatype is varchar datatype with default value ‘Q’