Temp table VS Table variable

Most of the SQL Developers/DBA would have come across a situation where they need to store the temporary result sets. This is where Temp tables and Table variables come into effect and helps in storing the data sets in a temporary location.

Temp table:

Consider the below sample temp table which holds the information about companies.

CREATE TABLE #Tmp

(

CompanyId Int,

Name varchar (50),

Location varchar (50)

)

1. The temp table name always starts with # or ## and are created in the tempdb database. The # indicates that the temp table is a local temporary table i.e. table is accessible only by the particular connection of SQL Server which created it. The ## indicates that the temp table is a global temporary table i.e. the table is accessible from any connection. They are dropped automatically when the last session that uses them has completed.
2. Since the local temporary table is accessible only by the connection which created it, this helps in minimizing the locks.
3. We can create indexes, statistics in temp tables and hence performance can be improved.
4. We cannot have foreign key constraints on temp tables.
5. Causes recompilation within stored procedures.
6. Only undo information is logged in tempdb and not the redo information.
7. We can Rollback the transactions in temp table similar to a normal table but not in table variable.
8. Temp tables can be used in nested stored procedures.
9. The temp table names cannot exceed 116 characters whereas the permanent table can have 128 characters

The following example illustrates the transaction behavior in Temp tables:

--using temp tables where ROLLBACK happens

CREATE TABLE #Tmp

(

CompanyId Int,

Name varchar(20),

Location varchar(20)

)

GO

INSERT INTO #Tmp

VALUES (1,'Deepak','Chennai')

GO

BEGIN TRAN

UPDATE #Tmp

SET Location='CH'

WHERE CompanyId=1

ROLLBACK TRAN

SELECT * FROM #Tmp

Table variables:

The following the syntax for table variables:

DECLARE @Tmp TABLE

(

CompanyId Int,

Name varchar(20),

Location varchar(20)

)

1. Table variables are local to a stored procedure and hence cannot be used in nested stored procedures
2. We cannot create Nonclustered indexes in Table variables only Clustered index can be created by specifying them as constraints (Primary or Unique)                                                                                                                                                        DECLARE @Tmp TABLE (C1 int, C2 int, PRIMARY KEY (C1, C2))
3. Table variables store the contents in memory but not always. Under extreme memory pressure, the pages belonging to table variables will be moved to tempdb
4. We cannot Alter a table variable once its declared
5. We cannot create statistics in table variables
6. They cannot make use of multiple processors and hence Parallelism is not possible
7. Transactions cannot be rollbacked in Table variable

The following example illustrates the transaction behavior in table variables:

--using table variables where ROLLBACK NEVER happens

DECLARE @Tmp TABLE

(

CompanyId Int,

Name varchar(20),

Location varchar(20)

)

INSERT INTO @Tmp

VALUES (1,'Deepak','Chennai')

BEGIN TRAN

UPDATE @Tmp

SET Location='CH'

WHERE CompanyId=1

ROLLBACK TRAN

SELECT * FROM @Tmp

I tried the following to check the performance perspective of table variables and temp tables. I could see that Temp tables are quite faster than table variables if we load numerous records. However with <10000 records being loaded, the table variables were much faster than temp tables.

I have a table named testmember with 1.5 million records.

--took 52 seconds to complete

SET STATISTICS TIME ON

DECLARE @Tmp TABLE

(

memberid          bigint,

name    nvarchar(100),

firstname           nvarchar(100),

emailaddress     nvarchar(100)

)

INSERT INTO @Tmp

SELECT memberid, name, firstname, emailaddress FROM testmember

WHERE memberid between 1 and 1000000

SELECT T.memberid, T.name, T.firstname, T.emailaddress

FROM @Tmp T INNER JOIN testmember M

ON T.memberid=M.memberid

where M.Memberid=1000

SET STATISTICS TIME OFF

--DBCC DROPCLEANBUFFERS

--took 45 seconds to complete

SET STATISTICS TIME ON

CREATE TABLE #Tmp

(

memberid          bigint,

name    nvarchar(100),

firstname           nvarchar(100),

emailaddress     nvarchar(100)

)

INSERT INTO #Tmp

SELECT memberid, name, firstname, emailaddress FROM testmember

WHERE memberid between 1 and 1000000

SELECT T.memberid, T.name, T.firstname, T.emailaddress

FROM #Tmp T INNER JOIN testmember M

ON T.memberid=M.memberid

where M.Memberid=1000

SET STATISTICS TIME OFF

Since we can create indexes, statistics etc there is still a scope for further improvement in performance in temp tables. In general there is no hard and fast rule, if there are <10K records we can opt for table variable else use temp tables but always test the query and then take a decision.

Limitations of Temp tables and Table variables:

1. There will be high load on the disk where tempdb resides if temp tables are used frequently and to a large extent and we have to keep an eye on the tempdb growth to ensure that it doesn’t become full and consume disk space
2. Table variables will perform poorly with large record set since index cannot be created other than primary key (Clustered Index)

There is no source code available for the current location

I have a problem when i am started running windows application.  I copied that form one system to another, i got one as shown bellow. 

Debugging Error.

"There is no source code available for the current location. vs2005..."

Solution:-

Erase .pdb files that i have in my debug folder and it is working fine.

Reference Link:-

http://social.msdn.microsoft.com/Forums/en/csharpide/thread/5ee88200-cee8-44f4-a46a-774044c1ef38

Globalization using ASP.NET

Sometimes our application may need to cater content in different languages for different country users. Let us see in this article how to cater different contents based on the culture using Globalization in ASP.NET.

Globalization is the process of designing and developing a software product that function for multiple cultures. A web forms page have two culture values ,Culture and UICulture. Culture is used to determine culture dependent function such as Date, Currency. So it is used for date formatting ,number formatting. UICulture values is used to determine which language the resources should load that is which UIstring the resource should use. The two culture settings do not need to have same values. It may be different depending on the application.

Setting Culture and UICulture

 

1. Through Web.Config

<configuration>
<system.web>
<globalization fileEncoding="utf-8" requestEncoding="utf-8" responseEncoding="utf-8" culture="en-US" uiCulture="fr-FR"/>
</system.web>
</configuration>

2. In Code-inline (aspx) Page

<%@ Page UICulture="fr" Culture="en-US" ....%>

Now, suppose I want to change the current culture programmatically, I can use following code to set the new culture.

3. In Code-Behind (aspx.cs) Page

using System.Globalization;
using System.Threading;

protected void Page_Init(object sender, EventArgs e)

    {

        Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US", false);

    }

Reference Links:-

http://msdn.microsoft.com/en-us/library/bb386581.aspx

Error in File UNKNOWN.RPT: The request could not be submitted for background processing

I have a web application that uses Crystal Reports. It is working fine in one of our test server (Windows 2003). When we migrate this asp.net web apps to the production server, my Report Generate button that uses Crystal Reports did not work and got this following error.  The request could not be submitted for background processing".

Solution:-

I took the below steps to correct it:

1. Right Click on C Drive
2. Click on Security Tab
3. Add "Network Services" to the list of users. Caution: Remember to remove it later.
4. Browse to you application and crystal report should work now.
5. Go back to your server and Remove the "Network Service" user from the security list. 

Hope this fixes the issue for all.