Today, we will see a very interesting puzzle about data types and default values. Here, changing the data type ends up changing the default value.
There are three important properties for any column –
- Datatype
- Default value
- Null-ability
A column can be NULL or NOT NULL depending on the business requirements. Along with the null ability, often columns can have default values. Today we will see a puzzle where we will talk about data types and their default values.
Puzzle
Let us assume that there is a column with original datatype A and it has a default value of B. Due to some business reason, we changed the data type from original datatype A to new data type C. When the datatype got changed from A to C, even though we did not change the datatype, it was automatically changed from original default C to new default D. The value of default value D is completely different from the earlier default value of B.
You need to answer-
1 The original datatype A and default value B
2 The new datatype C and new default value D
I guess that’s it. If you think, the answer is very simple. Let us see if you can get it right or not.
Please post your answers in the comment sections. I will keep all the comments hidden till next week Thursday. This way everyone gets a chance to participate in this puzzle.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
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’