Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Some questions are good for debates

  • Some questions are good for debates. There are so many myths associated with few subjects that when the discussion starts it is hard to convince stubborn friend who is not read to accept the truth. Let me see you attempt both the side of the following question: What is the difference of table variable and temp tables?

    Posted on 01-13-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

8  Answers  

Subscribe to Notifications
  • Score
    10

    Featurewise Diffrence between Table Variables and Temporary Tables

    • Feature Area :: Scope

      Table Variables - Current batch
      Temporary Tables - Current session, nested stored procedures.Global: all sessions.
      
    • Feature Area :: Usage

      Table Variables - UDFs, Stored Procedures, Triggers, Batches.
      Temporary Tables - Stored Procedures, Triggers, Batches.
      
    • Feature Area :: Creation

      Table Variables - DECLARE statement only.
      Temporary Tables - CREATE TABLE statement. SELECT INTO statement.
      
    • Feature Area :: Table name

      Table Variables - Maximum 128 characters.
      Temporary Tables - Maximum 116 characters.
      
    • Feature Area :: Column data types

      Table Variables - Can use user-defined data types.Can use XML collections.
      Temporary Tables - User-defined data types and XML collections must be in tempdb to use.
      
    • Feature Area :: Collation

      Table Variables - String columns inherit collation from current database.
      Temporary Tables - String columns inherit collation from tempdb database.
      
    • Feature Area :: Indexes

      Table Variables - Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.
      Temporary Tables - Indexes can be added after the table has been created.
      
    • Feature Area :: Constraints

      Table Variables - PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
      Temporary Tables - PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
      
    • Feature Area :: Post-creation DDL (indexes, columns)

      Table Variables - Statements are not allowed.
      Temporary Tables - Statements are allowed.
      
    • Feature Area :: Insert explicit values into identity columns (SET Id IDENTITY_INSERT).

      Table Variables - The SET IDENTITY_INSERT statement is not supported.	
      Temporary Tables - The SET IDENTITY_INSERT statement is supported.
      
    • Feature Area :: Data insertion

      Table Variables - INSERT statement (SQL 2000: cannot use INSERT/EXEC).
      Temporary Tables - INSERT statement, including INSERT/EXEC. SELECT INTO statement.
      
    • Feature Area :: Truncate table

      Table Variables - Not allowed.
      Temporary Tables - Allowed.
      
    • Feature Area :: Destruction

      Table Variables -Automatically at the end of the batch.
      Temporary Tables - Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
      
    • Feature Area :: Transactions

      Table Variables - Last only for length of update against the table variable. Uses less than temporary tables.
      Temporary Tables - Last for the length of the transaction. Uses more than table variables.
      
    • Feature Area :: Stored procedure recompilations

      Table Variables - Not applicable.
      Temporary Tables - Creating temp table and data inserts cause procedure recompilations.
      
    • Feature Area :: Rollbacks

      Table Variables - Not affected (Data not rolled back).
      Temporary Tables - Affected (Data is rolled back).
      
    • Feature Area :: Statistics

      Table Variables - Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.
      Temporary Tables - Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
      
    • Feature Area :: Pass to stored procedures

      Table Variables - SQL 2008 only, with predefined user-defined table type.
      Temporary Tables - Not allowed to pass, but they are still in scope to nested procedures.
      
    • Feature Area :: Explicitly named objects (indexes, constraints).

      Table Variables - Not allowed.
      Temporary Tables - Allowed, but be aware of multi-user issues.
      
    • Feature Area :: Dynamic SQL

      Table Variables - Must declare table variable inside the dynamic SQL.
      Temporary Tables - Can use temporary tables created prior to calling the dynamic sql.
      

    Temp Tables vs. Table Variables

    Table variables can be an excellent alternative to temporary tables. They have less overhead associated with them then temporary tables do. However, they have some major limitations

    as listed below. Consider using a table variable when it will contain a small amount of data, it will not be used in complex queries, and it does not need to be referenced in another SP call

    or a dynamic statement. Both temporary tables and derived tables are stored in tempdb.

    Table variables have the following advantages over temporary tables:

    • A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.

    • Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.

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

    Table variables have the following drawbacks as compared to temporary tables:

    • Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query

    performance when compared to a temporary table with non-clustered indexes.

    • Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE

    STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer from determining the best plan for a query, thus affecting the

    performance of that query.

    • Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex

    queries, are modified.

    • The table definition cannot be changed after the initial DECLARE statement.

    • Table variables cannot be used in an INSERT EXEC or SELECT INTO statement.

    • CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.

    • Since the scope of a table variable is local, you cannot reference a table variable defined in an SP from the following:

    o Another SP called from the SP that this variable was defined in o A dynamic query run using EXEC() or SP_EXECUTESQL

    Temporary Tables: Local vs. Global

    A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can't share a local temporary table because it is local to one user session. You also can't grant or revoke permissions on the local temporary table.

    A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. When the last user session that references the table

    disconnects, the global temporary table is lost. However, multiple users can access a global temporary table; in fact, all other database users can access it. But you can't grant or revoke

    permissions on the global temporary table because it's always available to everyone.

    Local and global temporary tables differ in a subtle way. Let's look at what SQL Server Books Online (BOL) says about temporary tables. "Temporary tables are similar to permanent

    tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use. The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes." The local table we created in our procedure by using sp_executesql won't be accessible to either the procedure or its child procedures.

    Within its execution scope, an spexecutesql system stored procedure creates and drops a local table. By definition, when the spexecutesql procedure ends, the life of the table also ends—hence the need to create a global temporary table.


    One Simple Example on follwing criteria

    Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction. Temp tables behave same as normal tables and are bound by transactions.

    A simple example shows this difference quite nicely:

    BEGIN TRAN
    declare @var table (id int, data varchar(20) )
    create table #temp (id int, data varchar(20) )
    
    insert into @var
    select 1, 'data 1' union all
    select 2, 'data 2' union all
    select 3, 'data 3'
    
    insert into #temp
    select 1, 'data 1' union all
    select 2, 'data 2' union all
    select 3, 'data 3'
    
    select * from #temp
    select * from @var
    
    ROLLBACK
    
    select * from @var
    if object_id('tempdb..#temp') is null
        select '#temp does not exist outside the transaction'
    

    We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked.


    Table variables don't participate in transactions, logging or locking. This means they're faster.

    Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option

    You can create a temp table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.

    Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.

    Thanks

    Yogesh

    Replied on Jan 13 2012 12:23AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    10

    Temporary Table:

    A temporary table is defined just like other regular tables. Temporary tables are automatically stored in the tempdb database even if we have other database context. There two different types of temporary table: Local and Global.

    Temporary tables are mainly used in the following situations:

    Replacement to cursors.
    Incremental storage of result sets.
    Temporary and low-overhead lookup table.

    Table Variable:

    Table variable is a data type that is used within T-SQL batch, stored procedure, and function. Table variable is defined and created in similar ways to a table but it with a strictly defined scope. Table variables are used in the following situations:

    Table variable well scoped.
    With short locking time period.
    With less recompilation.

    Microsoft recommends using table variables in place of temporary tables when the data set is very large

    1. Temporary tables can take advantage of parallel processing whereas table variables are single threaded.
    2. In table variables, there will be less locking but in the temporary tables, there will be high locking.
    3. With the table variable tempdb transaction log is less impacted than with temporary tables because with table variable usage, transaction log record will be cleared immediately but in temporary table usage log records will persist till the check point occurs or when the SQL Server restarts.
    4. From the execution plan perspective, both table variable and temporary table will have the same operations without adding index on the temporary table.
    5. A temp table is not allowed, e.g. inside a UDF for intermediate storage of result set needed for further processing inside that UDF or as a parameter for a stored procedure

    Scope

    Table Variables - Current batch 
    Current session, nested stored procedures. Global: all sessions.
    

    Usage

    Table Variables - UDFs, Stored Procedures, Triggers, Batches.   
    Temporary Tables - Stored Procedures, Triggers, Batches.
    

    Creation

    Table Variables - DECLARE statement only.   
    Temporary Tables -CREATE TABLE statement.
        	SELECT INTO statement.
    

    Table name

    Table Variables - Maximum 128 characters.   
    Temporary Tables - Maximum 116 characters.
    

    Column data types

    Table Variables -   Can use user-defined data types.
        	Can use XML collections.
    Temporary Tables -  User-defined data types and XML collections must be in tempdb to use.
    

    Collation

    Table Variables - String columns inherit collation from current database.   
    Temporary Tables -String columns inherit collation from tempdb database.
    

    *Indexes *

    Table Variables - Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.  
    Temporary Tables -Indexes can be added after the table has been created.
    

    Constraints

    Table Variables - 
    PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.   
    Temporary Tables -
    PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
    

    Post-creation

    Table Variables - DDL (indexes, columns)    
    Temporary Tables - Statements are not allowed.  Statements are allowed.
    

    Data insertion

    Table Variables -   INSERT statement (SQL 2000: cannot use INSERT/EXEC).	
    
    Temporary Tables - INSERT statement, including INSERT/EXEC.
        	SELECT INTO statement.
    

    Insert explicit values into identity columns (SET IDENTITY_INSERT).

    Table Variables - The SET IDENTITY_INSERT statement is not supported.   
    Temporary Tables - The SET IDENTITY_INSERT statement is supported.
    

    Truncate table

    Table Variables - Not allowed.  
    Temporary Tables - Allowed.
    

    Destruction

    Table Variables - Automatically at the end of the batch.    
    Temporary Tables - Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
    

    Transactions

    Table Variables - Last only for length of update against the table variable. Uses less than temporary tables.
    Temporary Tables -   Last for the length of the transaction. Uses more than table variables.
    

    Stored procedure recompilations

    Table Variables - Not applicable.   
    Temporary Tables - Creating temp table and data inserts cause procedure recompilations.
    

    Rollbacks

    Table Variables - Not affected (Data not rolled back).
    Temporary Tables - Affected (Data is rolled back).
    

    Statistics

    Table Variables - Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans. 
    Temporary Tables - Optimizer can create statistics on columns. 
        	Uses actual row count for generation execution plan.
    

    Pass to stored procedures

    Table Variables - SQL 2008 only, with predefined user-defined table type.   
    Temporary Tables - Not allowed to pass, but they are still in scope to nested procedures.
    

    Explicitly named objects (indexes, constraints).

    Table Variables - Not allowed.  
    Temporary Tables - Allowed, but be aware of multi-user issues.
    

    Dynamic SQL

    Table Variables - Must declare table variable inside the dynamic SQL.   
    Temporary Tables - Can use temporary tables created prior to calling the dynamic sql.
    

    http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

    Outer Transaction Participation

    Table variables don't behave like a permanent or temporary table when participating in a transaction. Consider the following:

    use tempdb
    go
    
    ------------
    -- Set up --
    ------------
    declare @t table (col1 int);
    create table #t (col1 int)
    
    ------------------------------
    -- Who has data in the end? --
    ------------------------------
    begin tran
        insert into @t values(1);
        insert into #t values(1);
    rollback
    
    select * from @t
    select * from #t
    
    -- Clean Up --
    drop table #t
    

    Scope

    Temporary tables have a scoping flexibility over table variables in that their scope goes across batches and into inner levels (e.g. stored procs, dynamic batches, triggers) as the table is scoped to the current session.

    use tempdb;
    go
    
    -----------
    -- Setup --
    -----------
    if exists(select * from sys.objects where object_id = object_id('dbo.myProc1') and type in ('P', 'PC'))
        drop procedure myProc1;
    go
    create procedure myProc1 as
        select col1 as t_batch2 from #tmp1;
    go
    
    ------------------------------
    -- Temp Table Scope Batch 1 --
    ------------------------------
    create table #tmp1 (col1 int);
    insert into #tmp1 values(1);
    
    select col1 as t_batch1 from #tmp1;
    go
    
    ------------------------------
    -- Temp Table Scope Batch 2 --
    ------------------------------
    exec myProc1;
    select col1 as [t_batch2.1] from #tmp1;
    go
    
    -------------
    -- Cleanup --
    -------------
    if exists(select * from sys.objects where object_id = object_id('dbo.myProc1') and type in ('P', 'PC'))
        drop procedure myProc1;
    go
    if (object_id('tempdb..#tmp1') is not null)
        drop table #tmp1;
    go
    
    ----------------------------
    -- Table Variable Batch 1 --
    ----------------------------
    declare @tmp1 table (col1 int)
    insert into @tmp1 values(1);
    
    select col1 as v_batch1 from @tmp1;
    go
    
    ----------------------------
    -- Table Variable Batch 2 --
    ----------------------------
    select col1 as v_batch2 from @tmp1;
    go
    

    http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/what-is-the-difference-between--table-variable-and-temp-tables.aspx?pg=1

    Replied on Jan 13 2012 6:30AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    3

    All 3 types exists in tempdb.

    A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost.

    A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. Global temporary tables are visible to all SQL Server connections. When the last user session that references the table disconnects, the global temporary table is lost.

    A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function.

    Ref - Web and BOL for exact terminology.

    Replied on Jan 13 2012 12:45PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    8

    Here are few topics that are debatable:

    "Microsoft recommends using table variables in place of temporary tables when the data set is very large" This itself debatable to me with a clause as if the temp table is not indexed. If temp table is indexed there are more advantages for very large data set than table variable. One more point as when the data is huge, table variable are also storing to tempdb, working similar as temp db with out index.

    alt text

    Replied on Jan 16 2012 2:47AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    0

    {Copy & Paste your Googled answer here.}

    Replied on Jan 16 2012 8:44AM  . 
    Cris
    194 · 1% · 242
  • Score
    4

    Table Variables

    1. The scope of the table variables is the current batch
    2. Cannot create index on a table variable
    3. Transaction log is not recorded for table variable. So, the TRANSACTION mechanism is not applied on table variables.
    4. Procedure having table variable can be pre-compiled

    Temp Tables

    1. The scope of the table variables is the current session
    2. Can create Index
    3. TRANSACTION mechanism is applied on Temp table.
    4. Procedure having table variable cannot be pre-compiled
    Replied on Jan 16 2012 10:39PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    1

    Most of the differences are listed above. One difference which I could not find is : a) Table Variable can not be joined with other tables but temp table can be joined. b) Also Temp Table can be temporary i.e. with in the scope of the current session as well as it can be declared with the scope of the server. So they are of 2 types.

    Replied on Jan 23 2012 1:14AM  . 
    manish313831
    2372 · 0% · 5
  • Score
    9

    Temporary tables are just that. They are used most often to provide workspace for the intermediate results when processing data within a batch or procedure. They are also used to pass a table from a table-valued function, to pass table-based data between stored procedures or, more recently in the form of Table-valued parameters, to send whole read-only tables from applications to SQL Server routines, or pass read-only temporary tables as parameters. Once finished with their use, they are discarded automatically.

    Temporary tables come in different flavours including, amongst others, local temporary tables (starting with #), global temporary tables (starting with ##), persistent temporary tables (prefixed by TempDB..), and table variables.(starting with (@)

    Before we get too deep into the technology, I’d advise that you should use table variables where possible. They’re easy, and SQL Server does the work for you. They also tend to cause fewer problems to a hard-working OLTP system. Just occasionally, you may need to fine-tune them to get good performance from them in joins, but I'll explain that in a moment, However, if you are doing more complex processing on temporary data or likely to use more than reasonably small amounts of data in them, then local temporary tables are likely to be a better choice.

    Table Variables

    Table variables are used within the scope of the routine or batch within which they are defined, and were originally created to make table-valued functions possible. However, they are good for many of the uses that the traditional temporary table was put to. They behave like other variables in their scoping rules. Once out of scope, they are disposed of. These are much easier to work with, and pretty secure, and they trigger fewer recompiles in the routines where they’re used than if you were to use temporary tables. Table variables require less locking resources as they are 'private' to the process that created them. Transaction rollbacks do not affect them because table variables have limited scope and are not part of the persistent database, so they are handy for creating or storing data that ought to survive roll backs such as log entries. The downside of table variables is that they are often disposed of before you can investigate their contents for debugging, or use them to try out different SQL expressions interactively.

    If your application is conservative and your data volumes light you’ll never want anything else. However, you can hit problems. One difficulty is that table variables can only be referenced in their local scope, so you cannot process them using dynamic SQL as you might with a temporary table or table-valued parameter. This is because you can’t refer an externally-defined table variable within dynamic SQL that you then execute via the EXEC statement or the sp_ExecuteSQL stored procedure because the dynamic SQL is executed outside the scope of the table variable. You can, of course, create, and then use, the table variable inside the dynamic SQL because the table variable would be in scope. However, once the dynamic SQL is run, there would be no table variable

    There are a few anomalies to be aware of too. You can’t, for example, change the table definition after the initial DECLARE statement. In SQL Server 2000, a table variable can’t be the destination of a SELECT INTO statement or a INSERT EXEC (now fixed); You can’t call user-defined functions from CHECK constraints, DEFAULT values, and computed columns in the table variable. The only constraints that you're allowed beyond CHECK constraints are PRIMARY KEY, UNIQUE KEY, and NULL / NOT NULL

    The trickiest problems, though, come with increasing size of the tables, because you can’t declare an index explicitly and distribution statistics aren’t maintained on them. The Query Optimiser assumes that there is only one row in the table. You also cannot generate parallel query plans for a SQL expression that is modifying the table's contents. To partially get around the index restriction, you can use constraints to do the same thing. Most essential is the Primary Key constraint which allows you to impose a clustered index, but unique constraints are useful for performance. The Query optimiser will happily use them if they are around. The biggest problem with table variables is that statistics aren’t maintained on the columns. This means that the query optimiser has to make a guess as to the size and distribution of the data and if it gets it wrong, then you’re going to see poor performance on joins: If this happens, there is little you can do other than to revert to using classic local temporary tables. One thing you can try is to 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 will have already been populated. This doesn't entirely solve the problem since the optimiser still has no distribution statistics and can, usually where the distribution is skewed, produce a bad plan. In this demo, the join was reduced in time by three quarters simply by adding the OPTION (RECOMPILE)

    SET nocount ON
    
    DECLARE @FirstTable TABLE (RandomInteger INT)
    DECLARE @SecondTable TABLE (RandomInteger INT)
    DECLARE @WhenWeStarted DATETIME
    DECLARE @ii INT
    
    BEGIN TRANSACTION
    SET @ii = 0
    WHILE @ii < 100000
      BEGIN
        INSERT  INTO @FirstTable
        VALUES  (RAND() * 10000)
        SET @ii = @ii + 1
      END
    SET @ii = 0
    WHILE @ii < 100000
      BEGIN
        INSERT  INTO @SecondTable
        VALUES  (RAND() * 10000)
        SET @ii = @ii + 1
      END
    COMMIT TRANSACTION
    SELECT  @WhenWeStarted = GETDATE()
    SET STATISTICS PROFILE ON
    SELECT  COUNT(*)
    FROM    @FirstTable first
            INNER JOIN @SecondTable second
            ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)
      -- 153Ms  as opposed to 653Ms without the hint
    SET STATISTICS PROFILE OFF
    SELECT  'That took '
        + CONVERT(VARCHAR(8), DATEDIFF(ms, @WhenWeStarted, GETDATE()))
        + ' ms'
    go
    

    Now if you can make what goes into the tables unique, you can then use a primary key constraint on these tables. This allowed the optimiser to use a clustered index seek instead of a table scan and the execution time was too rapid to measure

    Start with table variables, but drop back to using local temporary tables if you hit performance problems. Some people are bold enough to give advice in terms of the number of rows in a table, and I've seen 100 or 1000 offered as a maximum; but I've seen far larger table variables perform perfectly satisfactorily over time, and far smaller ones give trouble. However, in smaller tables, the trouble is less detectable!

    Table-Valued Parameters

    The Table-Valued Parameter (TVP) is a special type of table variable that extends its use. When table variables are passed as parameters, the table is materialized in the TempDB system database as a table variable and passed by reference, a pointer to the table in the TempDB.

    Table-valued parameters have been used since SQL Server 2008 to send several rows of data to a Transact-SQL routine or to a batch via sp_ExecuteSQL.. Their particular value to the programmer is that they can be used within TSQL code as well as in the client application, so they are good for sending client tables to the server. From TSQL, you can declare table-valued variables, insert data into them, and pass these variables as table-valued parameters to stored procedures and functions.Their more general usefulness is limited by the fact that they are only passed as read-only. You can't do UPDATE, DELETE, or INSERT statements on a table-valued parameter in the body of a routine.

    You need to create a User-Defined Table Type and define a table structure to use them. Here is a simple example of their use in TSQL

    /* First you need to create a table type. */
    CREATE TYPE Names AS TABLE
    (Name VARCHAR(10)) ;
    GO
    
    /* Next, Create a procedure to receive data for the table-valued parameter, the table of names and select one item from the table*/
    CREATE PROCEDURE ChooseAName
      @CandidateNames Names READONLY
    AS
    DECLARE @candidates TABLE (NAME VARCHAR(10),
                               theOrder UNIQUEIDENTIFIER)
    INSERT  INTO @candidates (name, theorder)
            SELECT  name, NEWID()
            FROM    @CandidateNames
    SELECT TOP 1
            NAME
    FROM    @Candidates
    ORDER BY theOrder
    GO
    
    /* Declare a variable that references the type for our list of cows. */
    DECLARE @MyFavouriteCowName AS Names ;
    
    /* Add data to the table variable. */
    INSERT  INTO @MyFavouriteCowName (Name)
     SELECT 'Bossy' UNION SELECT 'Bessy' UNION SELECT 'petal' UNION SELECT 'Daisy' UNION SELECT 'Lulu' UNION SELECT 'Buttercup' UNION SELECT 'Bertha' UNION SELECT 'Bubba' UNION SELECT 'Beauregard' UNION SELECT 'Brunhilde' UNION SELECT 'Lore' UNION SELECT 'Lotte' UNION SELECT 'Rosa' UNION SELECT 'Thilde' UNION SELECT 'Lisa' UNION SELECT 'Peppo' UNION SELECT 'Maxi' UNION SELECT 'Moriz' UNION SELECT 'Marla'
    
    /* Pass the table with the list of traditional nemes of cows to the stored procedure. */
    EXEC chooseAName @MyFavouriteCowName
    GO
    As with Table Variables, the table-valued parameter ceases to exist once it is out of scope but the type definition remains until it is explicitly  dropped. Like Table Variables they do not acquire locks when the data is being populated from a client, and  statistics aren't maintained  on columns of table-valued parameters. You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. As you'd expect, a table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.
    
    The TVP solves the common problem of wanting to pass a local variable to dynamic SQL that is then executed  by a sp_ExecuteSQL. It is poorly documented by Microsoft, so I'll show you a worked example to get you started
    
    
    DECLARE @SeaAreas TABLE (NAME Varchar(20))
    INSERT INTO @SeaAreas (name)
    SELECT 'Viking' UNION SELECT 'North Utsire' UNION SELECT 'South Utsire' UNION SELECT 'Forties' UNION SELECT 'Cromarty' UNION SELECT 'Forth' UNION SELECT 'Tyne' UNION SELECT 'Dogger' UNION SELECT 'Fisher' UNION SELECT 'German Bight' UNION SELECT 'Humber' UNION SELECT 'Thames' UNION SELECT 'Dover' UNION SELECT 'Wight' UNION SELECT 'Portland' UNION SELECT 'Plymouth' UNION SELECT 'Biscay' UNION SELECT 'Trafalgar' UNION SELECT 'Finisterre' UNION SELECT 'Sole' UNION SELECT 'Lundy' UNION SELECT 'Fastnet' UNION SELECT 'Irish Sea' UNION SELECT 'Shannon' UNION SELECT 'Rockall' UNION SELECT 'Malin' UNION SELECT 'Hebrides' UNION SELECT 'Bailey' UNION SELECT 'Fair Isle' UNION SELECT 'Faeroes' UNION SELECT 'Southeast Iceland'
    
    CREATE TYPE seanames AS TABLE (Name VARCHAR(20)) ;
    DECLARE @SeaAreaNames AS SeaNames ;
    INSERT  INTO @SeaAreaNames (name)
            SELECT  *
            FROM    @SeaAreas
    EXEC sp_executesql N'SELECT * FROM @MySeaAreas',
      N'@MySeaAreas [dbo].[seanames] READONLY', @MySeaAreas = @SeaAreaNames42
    

    Before we move on to describe the more traditional temporary tables and their use, we'll need to delve into the place where temporary tables are held. TempDB.

    TempDB

    Temporary tables and table variables are created in the TempDB database, which is really just another database with simple recovery: With TempDB, only sufficient 'minimal' logging is done to allow rollback, and other ACID niceties. The special difference of TempDB is that any objects such as tables are cleared out on startup. Because TempDB always uses the simple recovery model, the completed transaction are cleared from the log log on the next TempDB checkpoint, and only the live transactions are retained. This all means that temporary tables behave like any other sort of base table in that they are logged, and stored just like them. In practice, temporary tables are likely to remain cached in memory, but only if they are frequently-used: same as with a base table. TempDB operates a system called temporary object reuse, which will cache a portion of the temporary objects with the plan, if there is sufficient memory. This may account for the legend that temporary objects exist only in memory. The truth as ever is 'it depends...'.

    A lot of other things go on in TempDB: The database engine can use it for placing work tables for DBCC checks, for creating or rebuilding indexes, cursors, for example. Intermediate tables in queries described as 'hashes', 'sorts' and 'spools' are materialized in TempDB, for example, along with those required for several 'physical' operations in executing SQL Statements. It is also used as a version store for Snapshot isolation, Multiple Active Results Sets (MARS), triggers and online-index-build.

    Because temporary tables are stored just like base tables, there are one or two things you need to be wary of. You must, for example, have CREATE TABLE permission in TempDB in order to create a normal table. To save you the trouble, this is assigned by default to the DBO (db owner) role, but you may need to do it explicitly for users who aren’t assigned the DBO role. All users have permissions to create local or global temporary tables in TempDB because this is assigned to them via the GUEST user security context.

    The classic temporary table comes in two flavors, the Global, or shareable, temporary table, prefixed by ‘##’, and the local temporary table, whose name is prefixed with ‘#’.The local temporary tables are less like normal tables than the Global temporary tables: You cannot create views on them, or associate triggers with them. It is a bit tricky to work out which process, session or procedure created them. We’ll give you a bit of help with that later. Most importantly, they are more secure than a global temporary table as only the owning process can see it.

    Another oddity of the local temporary table (and the local temporary stored procedure) is that it has a different name in the metadata to the one you give it in your routine or batch. If the same routine is executed simultaneously by several processes, the Database Engine needs to be able to distinguish between the identically-named local temporary tables created by the different processes. It does this by adding a numeric string to each local temporary table name left-padded by underscore characters. Although you specify the short name such as #MyTempTable, what is actually stored in TempDB is made up of the table name specified in the CREATE TABLE statement and the suffix. Because of this suffix, local temporary table names must be 116 characters or less.

    If you’re interested in seeing what is going on, you can view the tables in TempDB just the same way you would any other table. You can even use sp_help work on temporary tables only if you invoke them from TempDB.

    **USE TempDB go execute spHelp #mytemp or you can find them in the system views of TempDB without swithching databases. SELECT name, createdate FROM TempDB.sys.tables WHERE name LIKE '#%' Or the Information Schema SELECT * FROM TempDB.informationschema.tables Even better, you can find out what process, and user, is holding on to enormous temporary tables in TempDB and refusing to give up the space -- Find out who created the temporary table,and when; the culprit and SPId. SELECT DISTINCT te.name, t.Name, t.createdate, SPID, SessionLoginName FROM ::fntracegettable(( SELECT LEFT(path, LEN(path)-CHARINDEX('\', REVERSE(path))) + '\Log.trc' FROM sys.traces -- read all five trace files WHERE isdefault = 1 ), DEFAULT) trace INNER JOIN sys.traceevents te on trace.EventClass = te.traceeventid INNER JOIN TempDB.sys.tables AS t ON trace.ObjectID = t.OBJECTID WHERE trace.DatabaseName = 'TempDB' AND t.Name LIKE '#%' AND te.name = 'Object:Created' AND DATEPART(dy,t.createdate)= DATEPART(Dy,trace.StartTime) AND ABS(DATEDIFF(Ms,t.createdate,trace.StartTime))<50 --sometimes slightly out ORDER BY t.createdate

    You cannot use user-defined datatypes in temporary tables unless the datatypes exist in TempDB; that is, unless the datatypes have been explicitly created

    User Tables in TempDB

    In normal use, you will create temporary tables, or table variables without thinking too deeply about it. However, it is interesting, though, that TempDB is there for any sort of sandbox activity. You can create ordinary base tables, views, or anything else you want. You can create schemas, stored procedures and so on. You’re unlikely to want to do this, but it is certainly possible since TempDB is just another database. I've just had to restart my development SQL Server after proving this to myself by installing AdventureWorks onto it. This means that it is possible to create a base table in TempDB, a sort of ..er... temporary permanent table. Unlike the global temporary table, you’d have to do all your own housekeeping on it: you’re on your own. The same is true of routines. The advantage of doing this is that any processing that you do uses TempDB’s simple recovery so that, if you fail to mop up, SQL Server acts as mother on the next startup: though this could be a very long time. The next stage is to have what I call a ‘persistent temporary’ table. In this table, the data itself is volatile when the server restarts, but the table itself persists. Probably the most common way to create a persistent Temporary table is to recreate on startup a global temporary table. This can be done automatically when all databases are recovered and the "Recovery is completed" message is logged. Even though this is a ‘global temporary’, it isn’t deleted when all connections using it have disappeared, because the process that runs it never disappears. Arguably, it is better to create this kind of work table in the database that uses it, though, if you are using full recovery, the temporary work will remain in the log. You can, of course, just create an ordinary table in TempDB. You can create these ‘persistent’ tables on startup by defining a stored procedure in master that creates the global temporary table

    USE master go CREATE PROCEDURE createMyGlobalTables AS CREATE TABLE ##globalTemporary1 (-- Blah blah (insert DDL here) CREATE TABLE ##globalTemporary2 (-- Blah blah (insert DDL here) --and so on…. CREATE TABLE ##globalTemporaryn (-- Blah blah (insert DDL here)

    go
    EXEC sp_procoption 'createMyGlobalTables', 'startup', 'true'
    

    A stored procedure that is set to autoexecution runs every time an instance of SQL Server is started Why use this sort of hybrid table? There are, for example, a number of techniques for passing tables between procedures via ‘persistent’ tables in a multiprocess-safe way, so as to do a series of processing to the data. These are referred to a Process-keyed tables (see ‘How to Share Data Between Stored Procedures: Process-Keyed table by Erland Sommarskog). They will initially raise the eyebrows of any seasoned DBA but they are an effective and safe solution to a perennial problem, when they are done properly.

    As well as temporary tables, there are also a number of table types that aren’t directly derived from base tables, such as ‘fake’ tables and derived tables: some of these are so fleeting that they are best thought of as ephemeral rather than temporary. The CTE uses ephemeral tables that are ‘inline’ or ‘derived’ and aren’t materialised. BOL refers to them as ‘temporary named result sets’. They exist only within the scope of the expression. In a CTE, they have the advantage over derived tables in that they can be accessed more than once.

    Local Temporary Table

    With Local temporary table (names that begin with #), what goes on under the hood is surprisingly similar to table variables. As with Table Variables, Local Temporary tables are private to the process that created it. They cannot therefore be used in views and you cannot associate triggers with them.

    They are handier than table variables if you like using SELECT INTO to create them, but I'm slightly wary about using SELECT INTO in a system that is likely to require modification, I'd much rather create my temporary tables explicitly, along with all the constraints that are needed.

    You cannot easily tell which session or procedure has created these tables. This is because, if the same stored procedure is executed simultaneously by several processes, the Database Engine needs to be able to distinguish the same tables created by the different processes. The Database Engine does this by internally appending a left-padded numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sys.objects view in TempDB is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, the table name specified for a local temporary name must be less than 116 characters.

    You get housekeeping with Local Temporary tables; they are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE. Their scope is more generous than a table Variable so you don't have problems referencing them within batches or in dynamic SQL. Local temporary tables are dropped automatically at the end of the current session or procedure. Dropping it at the end of the procedure that created it can cause head-scratching: a local temporary table that is created within a stored procedure or session is dropped when it is finished so it cannot be referenced by the process that called the stored procedure that created the table. It can, however, be referenced by any nested stored procedures executed by the stored procedure that created the table. If the nested procedure references a temporary table and two temporary tables with the same name exist at that time, which table is the query is resolved against?

    As a curiosity, you can also create Local Temporary Stored Procedures with the same scope and lifetime as a local temporary table. You can't do the same for other routines.

    Global Temporary Tables.

    Like Local temporary tables, Global temporary tables (they begin with ##) are automatically dropped when the session that created the table ends: However, because global tables aren’t private to the process that created it, they must persist thereafter until the last Transact-SQL statement that was actively referencing the table at the time when the creating session ended has finished executing and the locks are dropped. Anyone who has access to TempDB at the time these Global Temporary tables exist can directly query, modify or drop these temporary objects.

    You can associate rules, defaults, and indexes with temporary tables, but you cannot create views on temporary tables or associate triggers with them. You can use a user-defined datatype when creating a temporary table only if the datatype exists in TempDB

    Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.

    Although this works….

    CREATE table #Color(
    Color varchar(10) PRIMARY key)
    INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
     UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
    DROP TABLE #color
    go
    CREATE table #Color(
    Color varchar(10) PRIMARY key)
    INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
     UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
    DROP TABLE #color
    …this doesn’t
    
    CREATE PROCEDURE MisbehaviourWithTemporaryTables  AS
     CREATE table #Color(
    Color varchar(10) PRIMARY key)
    INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
     UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
    DROP TABLE #color
    CREATE table #Color(
    Color varchar(10) PRIMARY key)
    INSERT INTO #color SELECT 'Red' UNION SELECT 'White'
     UNION SELECT 'green'UNION SELECT'Yellow'UNION SELECT'blue'
    DROP TABLE #color
    go
    

    You can, by using local temporary tables, unintentionally force recompilation on the stored procedure every time it is used. This isn’t good because the stored procedure is unlikely to perform well. To avoid recompilation, avoid referring to a temporary table created in a calling or called stored procedure: If you can’t do so, then put the reference in a string that is then executed using the EXECUTE statement or sp_ExecuteSQL stored procedure. Also, make sure that the temporary table is created in the stored procedure or trigger before it is referenced and dropped after these references. Don’t create a temporary table within a control-of-flow statement such as IF... ELSE or WHILE.

    You are allowed to create Global Temporary Stored Procedures, but I've yet to find a use for them. Global temporary functions aren't permitted.

    Conclusions

    In any shared playground, be very careful how you swing that bat. You'll have realized, whilst reading this, that a lot of activity goes on in TempDB, and you can cause havoc to the whole SQL Server by using long-running processes that fill temporary tables, whatever type they are, with unnecessary quantities of data. In fact, I've given you clues in this article how to really, really, upset your DBA by inconsiderate use of that precious shared resource, the TempDB. (In the old days before S2005, using SELECT INTO with a huge table was the great V-weapon (Vergeltungswaffe)

    I'm always wary of providing over-generalized advice, but I always prefer my databases to use Table Variables, and TVPs wherever possible, They require less resource, and you're less likely to hold onto them when you're finished with them. I like to use them to the max, with column and table checks and constraints. You may find times when they run out of steam, especially when table sizes get larger. In cases like this, or where it isn't practical to use table variables because of their restricted scope, then I'll use local temporary tables. It takes a lot of pursed lips and shaking of heads before I'll agree to a global temporary table or persistent temporary table. They have a few valid and perfectly reasonable uses, but they place reliance on the programmer to do the necessary housekeeping

    Always bear in mind that misuse of temporary tables, such as unnecessarily large, or too long-lived, can have effects on other processes, even on other databases on the server. You are, after all, using a shared resource, and you wouldn't treat your bathroom that way would you?42939393

    Replied on Jan 25 2012 4:39AM  . 
    indika saminda kannangara
    188 · 1% · 251

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.