Results 1 to 9 of 9

Thread: Table data type

  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: Table data type

    Can anybody tell me why can't we write this...
    declare @tab1 as table
    Plz tell me if there are any use of table data type rather than UDFs..
    Last edited by rudra; 03-31-06 at 14:43.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Can anybody tell me why can't we write this...
    declare @tab1 as table
    Plz tell me if there are any use of table data type rather than UDFs..
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    set nocount on
    
    declare	@tab1 table (Column1 varchar(50))
    
    insert into @tab1 select 'Check your syntax, dude.'
    
    select * from @tab1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by blindman
    Code:
    set nocount on
    
    declare	@tab1 table (Column1 varchar(50))
    
    insert into @tab1 select 'Check your syntax, dude.'
    
    select * from @tab1
    ok so we have to mention all the columns that we are dealing with in the select statement.Thats not nice...
    So using instead of temporary table it doesn't makes any sense...Isn't it?

    Thanks a lot Batman.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It only makes sense if your a super power
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rudra
    ok so we have to mention all the columns that we are dealing with in the select statement.
    HUH?! DUDE, WHAT HAVE YOU BEEN SMOKING OVER THERE? No columns were mentioned in the SELECT statement, though it is good programming practice to do so.

    Columns were listed in the DECLARE statement, and this is required. If you are going to create a table in SQL Server, (permanent, temporary, or variable), you need to define the columns.

    Note that you CANNOT use SELECT INTO to create a table variable, as you can with temporary tables...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Rudra, stop trying to find a reason why there's anything wrong with table variables. Look them up in the BOL. It states:

    Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.

    Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:

    - A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
    Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
    INSERT INTO table_variable EXEC stored_procedure
    SELECT select_list INTO table_variable statements.

    - table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

    - table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.

    - Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.


    Lex

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Lexiflex
    Rudra, stop trying to find a reason why there's anything wrong with table variables. Look them up in the BOL. It states:

    [I]Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.

    Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:
    Thanks everybody,its crystal clear now,thanks especially to Batman and Lexiflex.Lexiflex, your info was really helpful...thanks a lot...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Brett Kaiser
    It only makes sense if your a super power
    Hey Brett,better late than never..there u are...You just missed the thread...its too old now
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •