locked
Reg Table Type in SQL RRS feed

  • Question

  • I am using Table type to get inputs from csv file.So That I can insert  multiple table like data set in my DB. Does that table type has any restriction in  rows?

    example:

    CREATE TYPE LocationTableType AS TABLE 
        ( LocationName VARCHAR(50)
        , CostRate INT );


    Friday, March 18, 2016 7:48 AM

Answers

  • No, not per se. Memory and storage - table variables are also stored in tempdb - are the limiting factors.
    • Proposed as answer by Naomi N Friday, March 18, 2016 12:29 PM
    • Marked as answer by Senthilmadhan-SQL Friday, March 18, 2016 1:28 PM
    Friday, March 18, 2016 8:36 AM
  • Hi Senthil,

    As Stefan Hoffman mentioned, only limited factor I see in tempdb space & memory

    Table Type is like a class, this can be used with the help of Object(Table variable)

    see the example in the below link

    https://msdn.microsoft.com/en-us/library/bb510489.aspx

    Beware table variable don't maintain stats, so please use it for small number of records if concern with performance

    When the batch or stored procedure containing the table variable is compiled, the number of rows of the table variable is unknown. Therefore, optimizer  has to make some assumptions.   It estimates very low number of rows for the table variable.   This can cause inefficient plan.  Most of the time, a nested loop join is used with the table variable as outer table.  If large number of rows exist in the table variable, this results in inner table be executed many times.

    So if you anticipate large number of rows to be populated to the table variable, you should not use it to begin with unless you don’t intend to join with other tables or views.

    If you have large number of rows to be populated into the table variable, consider this solution.    You can add option recompile to the statement that involves the table variable joining with other tables.   By doing this, SQL Server will be able to detect number of rows at recompile because the rows have already been populated.  This option is only available for SQL Server 2005 and beyond.

    Additionally, you can also use temp tables which can provide better statistics.  

    Thanks Saravana Kumar C

    Friday, March 18, 2016 11:43 AM
  • A type needs to get used.

    Either in a table, temporary table or table variable...

    The number of rows in a table is restricted by the size of the database, where the table is located. Used in temp tables and table vars means that it is limited by the size of tempdb.

    Friday, March 18, 2016 12:06 PM

All replies

  • No, not per se. Memory and storage - table variables are also stored in tempdb - are the limiting factors.
    • Proposed as answer by Naomi N Friday, March 18, 2016 12:29 PM
    • Marked as answer by Senthilmadhan-SQL Friday, March 18, 2016 1:28 PM
    Friday, March 18, 2016 8:36 AM
  • This is not a table Variable.This is table type right?
    Friday, March 18, 2016 10:55 AM
  • Hi Senthil,

    As Stefan Hoffman mentioned, only limited factor I see in tempdb space & memory

    Table Type is like a class, this can be used with the help of Object(Table variable)

    see the example in the below link

    https://msdn.microsoft.com/en-us/library/bb510489.aspx

    Beware table variable don't maintain stats, so please use it for small number of records if concern with performance

    When the batch or stored procedure containing the table variable is compiled, the number of rows of the table variable is unknown. Therefore, optimizer  has to make some assumptions.   It estimates very low number of rows for the table variable.   This can cause inefficient plan.  Most of the time, a nested loop join is used with the table variable as outer table.  If large number of rows exist in the table variable, this results in inner table be executed many times.

    So if you anticipate large number of rows to be populated to the table variable, you should not use it to begin with unless you don’t intend to join with other tables or views.

    If you have large number of rows to be populated into the table variable, consider this solution.    You can add option recompile to the statement that involves the table variable joining with other tables.   By doing this, SQL Server will be able to detect number of rows at recompile because the rows have already been populated.  This option is only available for SQL Server 2005 and beyond.

    Additionally, you can also use temp tables which can provide better statistics.  

    Thanks Saravana Kumar C

    Friday, March 18, 2016 11:43 AM
  • A type needs to get used.

    Either in a table, temporary table or table variable...

    The number of rows in a table is restricted by the size of the database, where the table is located. Used in temp tables and table vars means that it is limited by the size of tempdb.

    Friday, March 18, 2016 12:06 PM