column & data type.

  • Comments posted to this topic are about the item column & data type.

  • Not always. Converting varchar to int, may fail if some value is not numeric.

  • Conversions of varchar to int was mentioned. But there are other funny combinations. Conversions of n(var)char to (var)char, very nice too.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • 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."

  • There really should have been a third choice: It Depends. It really does, what data type is being converted to what data type?

  • 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.

  • If using SSMS doesn't SQL drop and recreate the column/table behind the scenes?

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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.

  • I agree with the 'it depends' position. Always white, always black ? let me laugh !

  • 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