T-SQL

  • Comments posted to this topic are about the item T-SQL

  • Interesting question, thanks, and a good reason you should always specify the data type for hardcoded items when using a Table Value Constructor or UNION since data type precedence comes into play.

    Here is a link to the Table Value Constructor in BOL that mentions the data type precedence: http://msdn.microsoft.com/en-us/library/dd776382.aspx, and here is a link for data type precedence: http://msdn.microsoft.com/en-us/library/ms190309.aspx

    Edited to correct the information. (thanks Bradley!)

  • No. My answer is first time BEGH, second time BEGH and Third time BEGH. Bcoz these two variables are varchar only. so if u pass the interger value without single quote it will accept.

    First time :

    insert into #test values

    ('1','A'),

    ('','B')

    insert into #test values

    (2,'D'),

    ('','E')

    insert into #test values

    ('','G'),

    ('','H')

    go

    -- First select statement

    select * from #test where c1 = ' '

    delete #test

    answer :

    B

    E

    G

    H

    Second time:

    insert into #test values

    ('1','A'),

    ('','B')

    insert into #test values

    ('2','D'),

    ('','E')

    insert into #test values

    ('','G'),

    ('','H')

    go

    -- Second select statement

    select * from #test where c1 = ' '

    delete #test

    Third Time:

    insert into #test values

    (1,'A'),

    ('','B')

    insert into #test values

    (2,'D'),

    ('','E')

    insert into #test values

    ('','G'),

    ('','H')

    go

    -- Third select statement

    select * from #test where c1 = ' '

    answer :

    B

    E

    G

    H

    Note: So all three times the value comes same value only in SQLSERVER 2005

  • The question is " What is the output of the select statement from the below code

    executed in sql server 10.0.1600 and with default settings " and not in SQL Server 2005 🙂

    And are you sure this below code executed successfully in 2005 ?

    insert into #test values

    ('1','A'),

    ('','B')

    Thanks

  • Gopinath Srirangan (3/22/2011)


    The question is " What is the output of the select statement from the below code

    executed in sql server 10.0.1600 and with default settings " and not in SQL Server 2005 🙂

    And are you sure this below code executed successfully in 2005 ?

    insert into #test values

    ('1','A'),

    ('','B')

    Thanks

    The Insert statement would definitely not work in 2005. The feature of inserting multiple rows using VALUE caluse is introduced in 2008.

  • Interesting question.

    Good learning for me.

    Thanks.

  • tejaswini.patil (3/22/2011)


    Gopinath Srirangan (3/22/2011)


    The question is " What is the output of the select statement from the below code

    executed in sql server 10.0.1600 and with default settings " and not in SQL Server 2005 🙂

    And are you sure this below code executed successfully in 2005 ?

    insert into #test values

    ('1','A'),

    ('','B')

    Thanks

    The Insert statement would definitely not work in 2005. The feature of inserting multiple rows using VALUE caluse is introduced in 2008.

    If the INSERT statement syntax is changed according to SQL Server 2005 and executed the script in SQL Server 2005 then the output is B, E, G, H for all the three select statements.

  • Too much thinking for one point...

  • paul s-306273 (3/22/2011)


    Too much thinking for one point...

    "Point" does not matter however question matters.

    I have found the question interesting and am sure there will be good learning for me.

    🙂

  • Gopinath Srirangan (3/22/2011)


    And are you sure this below code executed successfully in 2005 ?

    insert into #test values

    ('1','A'),

    ('','B')

    Thanks

    It will not work in 2005. That's the server I use daily and I wish this syntax would work because it could simplify my job significantly when inserting a bunch of values manually.

  • And are you sure this below code executed successfully in 2005 ?

    This is not a correct syntax in sqlserver2005 which is below given query

    insert into #test values

    ('1','A'),

    ('','B')

    correct answer:

    insert into #test values('1','A')

    insert into #test values ('','B')

    this is the way to insert the values to the particular table in sqlserver2005

  • sorry ,i'm not getting .

    i got first answer in sql server2008

  • In SQL Server 2008 the INSERT statement has been enhanced which enables to insert multiple records using the VALUE clause in a single INSERT statement.

    insert into #test values

    (2,'D'),

    ('','E')

    Above INSERT statement will insert two records.

    In first set of values, 2 is not embedded in single quotes and that is why the data type is considered as "int". SQL Server considers that data type for the first value of all records being inserted in that INSERT statement.

    First set of values inserts a record as 2, D

    While inserting second record, the first value '' is implicitly converted as 0 so second set of values inserts a record as 0, E.

    This question gave us a learning that we have to be careful about implicit conversion that SQL Server does.

  • Hi pksutha,

    Let me explain little more..

    insert into #test values ('1','A'), ('','B')

    is not same as below.

    insert into #test values('1','A')

    insert into #test values ('','B')

    The former insert statement in introduced in 2008 and the later is the usual code that we use often.

    To be compatible in both, pls run the below code either in 2005 or 2008 and compare the results.

    select '1' as c1,'A' as c2

    union

    select '','B'

    Result1:

    c1c2

    1A

    B

    go

    select 1 as c1,'A' as c2

    union

    select '','B'

    Result2:

    c1c2

    1A

    0B

    The important thing that i want to bring out is the datatype of the final result set is based on the first statement in the insert list.

    From the above example ,

    first result set has c1 [varchar(1)] , C2 [varchar(1)]

    second result set has c1 [int, C2 [varchar(1)]

    Hope this is helpful ?

    Thanks

    Gopi

  • Hi Gopi,

    i was explaing about insertion syntax,

    not about selection process. In both 2005 and 2008 will display ans for selection command for insertion command.

    bcoz sqlseerver 2005 doesn't support this syntax like insert into #test values('1','A'),(' ','B')

    With Regards

    Sutha

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply