TempData, Peek And Keep In ASP.NET MVC

TempData, Peek And Keep In ASP.NET MVC
 let us Set TempData["TempModel"] = "This is TempData Example";  in HomeController.
 
 

Set Break Point for TempData and now just run the application and check the value. 
 
 

For first call after one request we didn't fetch value in our view so TempData["TempModel"] keeps the value; see the following screen's watch window, it keeps the value which we set in Controller.
 
 
 
Now, read the TempData Value from View, and try to check the Value, 
 
 
 

It will become null because you have read the data.
 


Now, create one more TempData["TempModelKeep"], here we will first read the data, and for the next request we want to keep the data, so set the Value using TempData. Keep the method as in the following screenshot:
 
 

TempData["TempModel"] became null but TempData["TempModelKeep"] keeps the data for the next request.
 
 
Now, let's use Peek method in our View. For that declare one string variable and set the TempData Value in it by writing the following code:
  1. @{  
  2.     string msg = TempData.Peek("TempModel").ToString();  
  3. }  
  4. @msg  
Run your application and check the value for TempData["TempModel"].
 


If you read TempData value using Peek then the data will be available for the next request. Use Peek and keep smartly in your application code. 
Summary
  • If you set value for TempData and do not read the value then the data will be available for next request.
  • If you set value for TempData and read in View then the data will be deleted or will be null.
  • If you read TempData in the first request and want to keep the value for the next request then use 'Keep' Method.
  • If you read the TempData using 'Peek' then value persists for the next request also. 
In Short we can say that 

temdata uses TempDataDictionary where every data read, will be marked as available for deletion, keep() and peek() both are used to retain data, the only difference is, keep() will be used to retain data that has been marked for deletion (Read and Keep), whereas peek() will be used where you want to read data without marking it for deletion. 

Example:

@TempData["MyData"]; TempData.Keep("MyData");
here we are reading it first after that unmarking it using 

keep.stringstr = TempData.Peek("Td").ToString();
here we are reading without marking it for deletion.

Hope you like this simple article which explains Keep and Peek method.

Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable

Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable
Sometimes we're required to store the results of queries temporarily for further use in future queries. SQL Server provides CTE, Derived table, Temp table, subqueries and Temp variables for this. All of these can be used to store the data for a temporary time. We will learn the differences among all these temporary storage mechanisms and also in what type of scenario or condition which mechanism will be used.

Today I will explain all of these temporary storage mechanisms and also explain the conditions and queries where they can be used.

CTE:
The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Example:
  1. WITH CTE(Emp_ID, Emp_Name, Project_Name)  
  2. AS  
  3.     (  
  4.         SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e INNERJOIN dbo.Project p ON e.Emp_Id = p.Project_Id  
  5.     )  
  6. SELECT * FROM CTE  
Output:
output

CTE with recursion query:
  1. DECLARE @Min int;  
  2. DECLARE @Max int;  
  3. SET @Max = 10;  
  4. SET @Min = 1;  
  5.   
  6. WITH Sequence_ AS  
  7.   
  8. (  
  9.     SELECT @Min ASnum UNIONALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max  
  10. )  
  11. SELECTnumFROM Sequence_  
Output:
output

In the above example we used the recursive CTE and generated the sequence between 1 and 10.

When to use CTE
  • Use for recursive query.
  • CTE is easy to implement compared to complex queries which involves several sub-queries.
Temp Variables:
Temp Variables are also used for holding the data fora  temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.

Example:
  1. Declare Temp variable:  
  2. Declare @My_var2TABLE  
  3.     (  
  4.         IIDint,  
  5.         NameNvarchar(50),  
  6.         SalaryInt,  
  7.         City_NameNvarchar(50)  
  8.     )  
Insert result set of another query into temp variable,
  1. Insert Into @My_var2   
  2. Select * from Employee  
  3. WHERE IID<8 AND Salary>20000  
Retrieve data from temp variable: 
  1. Select*from@My_var2  
Output:
output

Limitations Of Temp Variables
  1. Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
  2. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.
  3. We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. It can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. Indexes cannot be added after the table has been created.
When to use Temp Variables:
  • When you are required to use the current result set in next queries then store result into temp variables.
  • When your data set is smaller, approximately less than 1000 records, then use temp variables , if your result set is larger then use temp table instead of temp variables.
Temp Table:
Temporary tables are tables that are available only to the session that created them. 

These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table which helps the developers in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. In SQL Server all temporary tables are present in tempdbdatabase.

Types of Temporary Tables:
SQL Server contain two types of Temporary tables:
  1. Local Temporary Tables
  2. Global Temporary Tables
Local Temporary Tables:
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables start with(“#”) hash sign.

Example:
  1. Create local temp table.  
  2. CREATE TABLE# TEMP_SAVE  
  3.     (  
  4.         Emp_IdINT,  
  5.         Emp_NameVARCHAR(30),  
  6.         Project_NameVARCHAR(30)  
  7.     )  
  8.   
  9. Insert data into temp table.  
  10.   
  11. INSERT INTO # TEMP_SAVE  
  12. SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e  
  13. INNERJOIN  
  14. dbo.Project p  
  15. ON  
  16. e.Emp_Id = p.Project_Id  
Select data from temp table.

table

Global Temporary Tables:
Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign.

Example:

Create a Global temp table 

  1. Create Table## TEmp  
  2.     (  
  3.         IIDintidentity(1, 1),  
  4.         Namenvarchar(50),  
  5.         Salaryint  
  6.     )  
Insert Data into Global Temp table
  1. Insert Into##TEmp  
  2. Select 'Pankaj', 25000 UnionAll  
  3. Select 'Rahul', 24000 UnionAll  
  4. Select 'Sanjeev', 23000 UnionAll  
  5. Select 'Sandeep', 22000 UnionAll  
  6. Select 'Naru', 27000   
Fetch Data From Global temp Table

output

When to Use Temporary Tables
  1. We required to hold data from further query.
  2. When we have a complex Join operation.
  3. In some cases it may be a replacement of cursors.
Derived Table:
Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.

Example:
  1. SELECT FROM  
  2. (SELECT e.Emp_Id,e.EmployeeName,p.Project_Name FROM dbo.Employee e  
  3. INNERJOIN  
  4. dbo.Project p  
  5. ON  
  6. e.Emp_Id=p.Project_Id)Tab  
  7. WHERE Tab.Emp_Id%2=1  
Output:
output

Example:
example

Consider the above table, now we have the sum the salary of employees for each group of age.
  1. SELECT SUM(Tab.Salary)AS Total_Salary,Tab.Age  
  2. FROM  
  3. (SELECT e.Salary,e.AgeFROMdbo.Emp e  
  4. WHERE e.Age>20)Tab  
  5. GROUPBY Tab.Age  
Output:
output

When to Use:
  • When you are required to perform some complex query and result of inner query work as table for outer query.
Subquery: 
A subquery is a SELECT statement that is nested within another statement. Subquery is also known as nested query. Result of subquery is used in the main query as a condition to further restrict the data to be retrieved. The subquery used in select, update, delete and insert command.

Example:
  1. SELECT * FROM dbo.Emp e  
  2. WHERE e.Id IN(SELECT e2.Emp_Id fromdbo.Employee e2);  
Output:
output

Example:
  1. SELECT * FROM dbo.Emp e  
  2. WHERE e.Id>(SELECT max(e2.Emp_Id) from dbo.Employee e2);  
Output:
output

When to use:
  • When you require that inner query or condition must be fulfilled before generating any result.
  • Use subquery when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.
  • If the query requires a NOT EXISTS condition, then you must use a subquery because NOT EXISTS operator only work with subquery.
After the basic introduction of temp table, temp variable, subquery, derived table and CTE now we read the difference between all these.

Difference between subquery and derived table:
SubqueryDerived
Subqueries must be enclosed within parentheses.Derived table must be enclosed within parentheses and table name must be provided.
Subquery can have only one column.Derived table can have one or more column.
Subquery mainly use in where clause.Derived table used in from clause.
Difference b/w Temp table and Temp variable:
Temp TableTempVariable
Scope of Temp Table is wider the temp variables of. Local temporary tables are temporary tables that are available only to the session that created them and Global temporary tables are temporary tables that are available to all sessions and all users.Scope of the Temp variables are limited up to current batch and current stored procedure.
Temp tables can be create using Create Table and Select Into commandsTemp variables only through Declare command can’t be created using select into command.
Temp tables can be drop through Drop Command.We can not drop a Temp variables but we can use truncate command for temp variables.
Name of Temp table can have maximum 116 characters.Name of a temp variables can have MAXIMUM 128 characters.
Temp table support foreign key concept.Temp variable doesn’t support foreign key.
Temp tables support transaction management.Temp variables doesn’t support transaction management. Rollback not work for temp variables.
Temp tables can easily handle large amount of data.Temp variables are suitable with small amount of data.
Difference b/w CTE and Derived table:
CTEDerived Table
A CTE can be referenced multiple times in the same query. So CTE can use in recursive query.Derived table can’t referenced multiple times. Derived table can’t use in recursive queries.
CTE are better structured compare to Derived table.Derived table’s structure is not good as CTE.