July 22, 2009 at 8:15 pm
Comments posted to this topic are about the item column & data type.
July 23, 2009 at 12:50 am
Not always. Converting varchar to int, may fail if some value is not numeric.
July 23, 2009 at 3:32 am
Conversions of varchar to int was mentioned. But there are other funny combinations. Conversions of n(var)char to (var)char, very nice too.
July 23, 2009 at 5:46 am
It would have been interesting to see the results if there was a third choice "False, the table has to be dropped and recreated with the column assigned the new data type."
July 23, 2009 at 7:03 am
There really should have been a third choice: It Depends. It really does, what data type is being converted to what data type?
July 23, 2009 at 7:20 am
Carlo Romagnano (7/23/2009)
Not always. Converting varchar to int, may fail if some value is not numeric.
I was taking the question to mean the mechanics of changing a data type. One should always think about the data types before changing and determine if there could be problems. The question was correct on the mechanics.
Now, if the question asked if one should change data type, that would be a different kettle of fish.
July 23, 2009 at 8:11 am
If using SSMS doesn't SQL drop and recreate the column/table behind the scenes?
July 23, 2009 at 8:53 am
The correct answer would have been, "It Depends".
Certain source types to certain destination types, no problem. Other combinations can not be converted.
"It depends" also on which version of SQL. I've done conversions in 2005 that would have choked if attempted in 6.5.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
July 23, 2009 at 9:59 am
Lynn Pettis (7/23/2009)
There really should have been a third choice: It Depends. It really does, what data type is being converted to what data type?
Thank you! This is what I thought.
For example, what if the original column was int and the user wanted to change it to hold the employee's home street address?
July 23, 2009 at 10:04 am
Lynn Pettis (7/23/2009)
There really should have been a third choice: It Depends. It really does, what data type is being converted to what data type?
For example you can't eliminate the IDENTITY from a column. Try this:
-- sql2000: no error generated, but the column remains IDENTITY
create table aaaa(i int identity)
alter table aaaa alter column i int
exec sp_help aaaa
July 23, 2009 at 11:00 am
Lynn Pettis (7/23/2009)
There really should have been a third choice: It Depends. It really does, what data type is being converted to what data type?
Exactly. It also depends on existing records in the said table as sometimes conversion will fail for that very reason though the types are implicitly convertable.
use AdventureWorks;
go
set nocount on;
if exists
(
select 1 from sys.objects where [object_id] = object_id(N'[dbo].[phony]') and type in (N'U')
)
drop table [dbo].[phony];
create table dbo.phony (c1 int, c2 varchar(100));
insert into dbo.phony select 1, getDate();
alter table dbo.phony alter column c2 datetime;
select 'this works' splaining;
select
cast(column_name as varchar(11)) column_name,
cast(data_type as varchar(9)) data_type
from information_schema.columns
where table_schema = 'dbo' and table_name = 'phony';
drop table dbo.phony;
create table dbo.phony (c1 int, c2 varchar(100));
insert into dbo.phony select 1, 'boloney';
alter table dbo.phony alter column c2 datetime;
go
select 'this ain''t due to existing data type conversion' splaining;
select
cast(column_name as varchar(11)) column_name,
cast(data_type as varchar(9)) data_type
from information_schema.columns
where table_schema = 'dbo' and table_name = 'phony';
drop table dbo.phony;
create table dbo.phony (c1 int, c2 varbinary(100));
alter table dbo.phony alter column c2 ntext;
go
select 'this ain''t even without data due to incompatibility' splaining;
select
cast(column_name as varchar(11)) column_name,
cast(data_type as varchar(9)) data_type
from information_schema.columns
where table_schema = 'dbo' and table_name = 'phony';
drop table dbo.phony;
set nocount off;
go
Results:
splaining
----------
this works
column_name data_type
----------- ---------
c1 int
c2 datetime
Msg 241, Level 16, State 1, Line 29
Conversion failed when converting datetime from character string.
The statement has been terminated.
splaining
-----------------------------------------------
this ain't due to existing data type conversion
column_name data_type
----------- ---------
c1 int
c2 varchar
Msg 206, Level 16, State 2, Line 14
Operand type clash: varbinary is incompatible with ntext
splaining
---------------------------------------------------
this ain't even without data due to incompatibility
column_name data_type
----------- ---------
c1 int
c2 varbinary
Oleg
July 23, 2009 at 11:04 am
The good news is that though the answer could be it depends, answering the question was not difficult given existing choices. The question asked whether it is possible or not, and yes, it is possible, just not always.
Oleg
July 23, 2009 at 11:19 am
Carlo Romagnano (7/23/2009)
Lynn Pettis (7/23/2009)
There really should have been a third choice: It Depends. It really does, what data type is being converted to what data type?For example you can't eliminate the IDENTITY from a column.
But IDENTITY is not a data type; it's an attribute of the column. 😛
The QotD is not meant to be an exhaustive study of a given aspect of SQL server; it's okay to be somewhat general. In fact, I think general questions about what you can do (like this one) are good as they generate discussions (like this one) about what you should (or shouldn't) do.
My only issue with this question is that it says "In SQL Server..." without specifying version. This implies that the functionality is available to all versions, yet the ALTER COLUMN syntax was not available in version 6.5 or earlier. I haven't used SQL Server 6.5 or earlier, so I don't know if there was an alternate syntax or if you had to drop and recreate the column, but it wouldn't have been hard to say "SQL Server 7.0 and higher" or something like that to clarify the question.
July 23, 2009 at 11:36 pm
I agree with the 'it depends' position. Always white, always black ? let me laugh !
July 24, 2009 at 4:23 am
sknox (7/23/2009)
I haven't used SQL Server 6.5 or earlier, so I don't know if there was an alternate syntax or if you had to drop and recreate the column.
From memory, you had to use sp_rename. But it was certainly possible.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply