Check our online training feedbacks Here

23 December 2010

Temporary tables vs Table Variables

Temporary tables

  • They are like regular tables but stored in the tempdb database.
  • These tables get dropped after they have been used.
  • Temporary tables are of two types,
  • Local Temporary table - defined using a pound sign (#) and accessible only within the session that created it
  • Global Temporary table - defined using double pound signs (##) and visible to all users.

Table Variables

  • It is a data type that looks similar to a table but has a fixed lifetime scope (i.e within a function, stored procedure or batch that it is declared in)
Here are some differences between Temporary Table and Table Variables
Temporary Tables
Table Variables
Created and stored in tempdb database Created in Memory (although it can write to tempdb)
Transaction logs are recorded for temporary tables so they are bound to transactions. Transaction logs are not recorded for the table variables so they are not bound to any transactions. So no effect of transaction rollbacks.
Can participate in parallel operations Cannot participate in parallel operations
The log activity remains till it is manually cleared or the server restarts Table variable log activity is truncated immediately
SQL Server creates statistics for temporary tables SQL Server does not create statistics for table variables
Stored procedure containing temporary tables cannot be pre-compiled Stored procedures containing table variables can be pre-compiled
You can drop a Temporary Table You cannot manually drop a table variable
You can create indexes on them You cannot ‘explicitly’ create Indexes on them (exception is the index created while creating a Primary Key)

To be more clear about the Transaction mechanism just consider the following example
create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select 'old value #' 
insert into @T select 'old value @' 
begin transaction 
     update #T set s='new value #' 
     update @T set s='new value @' 
rollback transaction 
select * from #T 
select * from @T 

The o/p of will be
old value #
new value @

SQL Views

View is nothing but saved SQL Statements or can be defined as an object that derives its data from one or more tables or in simple words we can say it as  virtual table.
(Views don't occupy storage space,Exception is indexed view..discussed in later posts).

Why to use Views:
  • Query simplicity :-We may create a view using a complex query which can be easily queried later.
  • security:-Views ensure the security of data by restricting access only to specific rows and columns.
  • ToCreate:-->Create View ViewName as Query
  • ToDrop:-->Drop View ViewName 
  • To Modify:-->Alter View ViewName as Query
Options we can use while creating Views:
    2 important options that can be used while creating view are 
  • This will  locks the tables being referred by the view and prevents any changes that may change the table schema.
  • Requirements:-
    • Objects have to be referred to by their owner names [two part name].
    • SELECT * is not permitted.
  • Example:- 
    • Create View MyView With SchemaBinding as Select  CustomerName,CustomerId From  dbo.TblCustomer
(After creating the view, try to alter the table TblCustomer, you cant do it! This is the power of the SCHEMABINDING option.)
  • This will encrypts the definition of the view.Users will not be able to see the definition of the view after it is created.
  • Example:
    • Create View MyView With Encryption as Select CustomerName,CustomerId From dbo.TblCustomer
  • Once Encrypted,there is no way to decrypt it again.So be careful when you are using this option.
Restrictions Imposed On Views are:
  • A view can be created only in the current database.
  • A view can be created only if there is a SELECT permission on its base table.
  • Only select statement is allowed while creating view.
  • A trigger cannot be defined on a view.
  • The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.
Indexed Views:
  • When a clustered index is created on the view, SQL Server immediately allocates storage space to store the results of the view. You can then treat the view like any other table by adding additional nonclustered indexes. 
  • Requirements:- 
    • View definition must always return the same results from the same underlying data. 
    • Views cannot use non-deterministic functions. 
    • The first index on a View must be a clustered, UNIQUE index. 
    • If you use Group By, you must include the new COUNT_BIG(*) in the select list. 
    • It must be created with SCHEMABINDING option. 
    • View definition cannot contain the following 
      • TOP 
      • Text, ntext or image columns 
      • DISTINCT 
      • SUM on a nullable expression 
      • A derived table 
      • Rowset function 
      • Another view 
      • UNION 
      • Subqueries, outer joins, self joins 
      • Full-text predicates like CONTAIN or FREETEXT 
      • Cannot include order by in view definition

Updatable Views:

    Views are not only read-only but also updatable. However in order to create an updatable view, the SELECT statement which defines View has to follow several following rules:

  • SELECT statement must not reference to more than one table. It means it must not contain more than one table in FROM clause, other tables in JOIN statement, or UNION with other tables.
  • SELECT statement must not use GROUP BY or HAVING clause.
  • SELECT statement must not use DISTINCT in the selection list.
  • SELECT statement must not reference to the view that is not updatable
  • SELECT statement must not contain any expression (aggregates, functions, computed columns…)