What is the difference between a Temporary Table and a Table variable in SQL ?

Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch.

Temporary tables and table variables, both have their own pros and cons. We need to decide which one to use and when.

Let us compile the list for differences.

⇒ Table variable (@table) is created in the memory. Whereas, a Temporary table (#temp) is created in the tempdb database. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb.

⇒ Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table.

⇒ Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.

⇒ Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables.

⇒ Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine.

⇒ Temporary table allows Schema modifications unlike Table variables.

Table Variable in SQL Server – Example

Table variable is a very useful programming construct, like that of any other variable.

1.  DECLARE @TStudent TABLE  
2.  (  
3.     RollNo INT IDENTITY(1,1),  
4.     StudentID INT,  
5.     Name INT  
6.  )   
7.  --Insert data to Table variable @TStudent   
8.  INSERT INTO @TStudent(StudentID,Name)  
9.  SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
10.    
11.  --Select data from Table variable @TStudent  
12.  SELECT * FROM @TStudent  
13.    
14.  --Next batch  
15.  GO  
16.  SELECT * FROM @TStudent --gives error  
17. DECLARE @TStudent TABLE  
18.  (  
19.     RollNo INT IDENTITY(1,1),  
20.     StudentID INT,  
21.     Name INT  
22.  )   
23.  --Insert data to Table variable @TStudent   
24.  INSERT INTO @TStudent(StudentID,Name)  
25.  SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
26.    
27.  --Select data from Table variable @TStudent  
28.  SELECT * FROM @TStudent  
29.    
30.  --Next batch  
31.  GO  
32.  SELECT * FROM @TStudent --gives error   

Temporary Tables in SQL Server – Example

In SQL Server, based on the scope and behavior, temporary tables are of two types, 

1. Local Temporary Tables (#temp)  
2. Global Temporary Tables (##temp)  
3.   
4. CREATE TABLE #StudentTemp  
5. (  
6.     StudentID int,  
7.     Name varchar(50),   
8.     Address varchar(150)  
9. )  
10. GO  
11. INSERT INTO #StudentTemp VALUES ( 1, 'Dipendra','Pune');  
12. GO  
13. SELECT * FROM #StudentTemp  
14. CREATE TABLE #StudentTemp  
15. (  
16.     StudentID int,  
17.     Name varchar(50),   
18.     Address varchar(150)  
19. )  
20. GO  
21. INSERT INTO #StudentTemp VALUES ( 1, 'Dipendra','Pune');  
22. GO  
23. SELECT * FROM #StudentTemp   

Points to Remember

• Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
  
  
• Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.
  
  
• Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables.

I hope I was able to explain the difference between Temporary Tables and Table variables in SQL Server.

Recent questions asking in interview - C# , asp.net , asp.net mvc , asp.net core , sql , entity framework

 Q. Sql Injection 
 Q. Sql cache 
 Q. Exception Filter purpose
Q. MVC Validations - Jquery ka use yaha par 
Q. Await Async

1. What is the difference between SingleOrDefault and FirstOrDefault?

LINQ provides element operators that return a single element or a specific element from a collection. The elements operators are Single, SingleOrDefault, First, FirstOrDefault, Last, LastOrDefault.

Single

It returns a single specific element from a collection of elements if element match found. An exception is thrown, if none or more than one match found for that element in the collection.

SingleOrDefault

It returns a single specific element from a collection of elements if element match found. An exception is thrown, if more than one match found for that element in the collection. A default value is returned, if no match is found for that element in the collection.

First

It returns first specific element from a collection of elements if one or more than one match found for that element. An exception is thrown, if no match is found for that element in the collection.

FirstOrDefault

It returns first specific element from a collection of elements if one or more than one match found for that element. A default value is returned, if no match is found for that element in the collection.

When to use Single, SingleOrDefault, First and FirstOrDefault

You should take care of following points while choosing Single, SingleOrDefault, First and 

FirstOrDefault:

When you want an exception to be thrown if the result set contains many records, use Single or SingleOrDefault.

When you want a default value is returned if the result set contains no record, use SingleOrDefault.
When you always want one record no matter what the result set contains, use First or FirstOrDefault.

When you want a default value if the result set contains no record, use FirstOrDefault.

Perfomance of SingleOrDefault and FirstOrDefault

FirstOrDefault usually performs faster as compared to SingleOrDefault, since these iterate the collection until they find the first match. While SingleOrDefault iterates the whole collection to find one single match.

Q.2 AllowAnonymous Attribut and Authorize Attribute.?

One of the new features in ASP.NET MVC 4 is the AllowAnonymous Attribute that helps you secure an entire ASP.NET MVC 4 Website or Controller while providing a convenient means of allowing anonymous users access to certain controller actions, like the login and register Actions.


AllowAnonymous Attribute in ASP.NET MVC 4

As mentioned, if you create a new ASP.NET MVC 4 Internet Project in Visual Studio 2010 or Visual Studio 11 and view the AccountController you will notice the generous use of the AllowAnonymous Attribute on various login and register controller actions. Here are a few of those controller actions.

[Authorize] 

public class AccountController : Controller 

{ 

 [AllowAnonymous] 

 public ActionResult Login() 

{ 

 // ... 

 } 

 [AllowAnonymous]

 [HttpPost] 

 public JsonResult JsonLogin(LoginModel model, string returnUrl) 

{ 

 // ...

 }

 [AllowAnonymous]

 [HttpPost] 

 public ActionResult Login(LoginModel model, string returnUrl) 

{ 

 // ... 

 }

 [AllowAnonymous] public ActionResult Register() 

{ 

 // ... 

 } 

 [AllowAnonymous] 

 [HttpPost] 

 public ActionResult JsonRegister(RegisterModel model)

 {

 // ... 

 }

 [AllowAnonymous]

 [HttpPost] 

 public ActionResult Register(RegisterModel model) 

{ 

 // ... 

 } 

}

The idea is pretty simple. The Authorize Attribute on the AccountController in this ASP.NET MVC 4 Application denies anonymous access to every controller action. However, we need to allow anonymous access to the login and register controller actions so we decorate them with the AllowAnonymous Attribute which negates the Authorize Attribute and allows anonymous access.

Q.3 What is mvc Filters and custom filters . ?

ASP.NET MVC provides Action Filters for executing filtering logic either before or after an action method is called. Action Filters are custom attributes that provide declarative means to add pre-action and post-action behavior to the controller's action methods.

Q.4 What is Dependency injection with examples . ?

In software engineering, dependency injection is a technique in which an object receives other objects that it depends on. These other objects are called dependencies. In the typical "using" relationship the receiving object is called a client and the passed object is called a service.