23 December 2011

ASP.NET AJAX Calendar Extender – Tips and Tricks

The CalendarExtender is an ASP.NET AJAX control that is associated with a TextBox control. When the user clicks on the TextBox, a client-side Calendar control pops up. The user can then set a date by clicking on a day, navigate months by clicking on the left and right arrow and perform other such actions without a postback. In this article, we will see some tips and tricks that can be applied to a CalendarExtender control. 

If you are new to the CalendarExtender control, you can check out some information about it over here.


I assume you have some basic experience developing ASP.NET Ajax applications and have installed the ASP.NET Ajax Library and ASP.NET Control Toolkit. As of this writing, the toolkit version is Version 1.0.20229 (if you are targeting Framework 2.0, ASP.NET AJAX 1.0 and Visual Studio 2005) and Version 3.0.20229 (if targeting .NET Framework 3.5 and Visual Studio 2008).
All the tips shown below have been created using Version 3.0.20229 (targeting .NET Framework 3.5 and Visual Studio 2008).
Tip 1: How to display and hide a Calendar on the click of a Button
If you want to popup a Calendar on the click of a button, you can use set the PopupButtonID of the CalendarExtender to the ID of the button. In this case, we will be using an ImageButton as shown below:
       <asp:ImageButton runat="Server" ID="ImageButton1" ImageUrl="~/Images/Icon1.jpg" AlternateText="Click here to display calendar" />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
       
        <cc1:CalendarExtender ID="CalendarExtender1" runat="server"
        TargetControlID="TextBox1" PopupButtonID="ImageButton1"/>
If you are using an earlier version of the toolkit, you may observe that the ImageButton causes a postback when you click on it again, to close the Calendar. To avoid the postback, use a HTML Image Control instead of the Server side Image Control as shown below:
       <img alt="Icon" src="/Images/Icon1.jpg" id="Image1" />
       <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
       
        <cc1:CalendarExtender ID="CalendarExtender1" runat="server"
        TargetControlID="TextBox1" PopupButtonID="Image1"/>
Note: In case you are clicking on the textbox to open the calendar, then in earlier versions of the toolkit, the calendar would not hide automatically when the user clicked anywhere outside the Calendar control. However this was fixed in the later versions. In fact, in the latest version, the Calendar hides automatically when a date is selected.
If for some reason you are facing issues with the Calendar not hiding automatically, make sure that you have the latest version of the AJAX Control Toolkit.
Tip 2: How to Add a CalendarExtender to a GridView
If you want to add a CalendarExtender to a GridView, use a template field with a TextBox and CalendarExtender as shown below:
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID"
            DataSourceID="SqlDataSource1" ShowFooter="true" AllowPaging="True" AllowSorting="True">
            <Columns>           
               <asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
                 SortExpression="CategoryID" />
                  <asp:BoundField DataField="CategoryName" HeaderText="CategoryName"
                 SortExpression="CategoryName" />
              <asp:TemplateField>
                <ItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    <cc1:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="TextBox1"/>
                </ItemTemplate>
            </asp:TemplateField>               
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=SUPROTIM;Initial Catalog=Northwind;Integrated Security=True"
            SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]" >
        </asp:SqlDataSource>
        </div>
    </form>
Tip 3: Enable Year Navigation in CalendarExtender
When the calendar appears, click on the title of the calendar to change the view to Months in the current year. Clicking it again, switches the view to Years, showing 10 years at a time.
If you plan to do this programmatically, here’s some code for you. Use the OnClientShown event and switch the mode using javascript. This tip was shared by one of the Microsoft® support person at the asp.net forums.
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
 <cc1:CalendarExtender ID="CalendarExtender1"
runat="server" TargetControlID="TextBox1" OnClientShown="ChangeCalendarView" />
Then add this to the <head> section
<head runat="server">
<title>CalendarExtender</title>
<script type="text/javascript">
function ChangeCalendarView(sender,args)
{
   sender._switchMode("years", true);           
}
</script>
</head>
Tip 4: Display only the day and month in the CalendarExtender
To select only the day and month without the year, use the Format property of the CalendarExtender and set it to “dd/MM” as shown below:
<cc1:CalendarExtender ID="CalendarExtender1" runat="server" Format="dd/MM" TargetControlID="TextBox1" />
Tip 5: How to Set Culture to work with CalendarExtender
Make sure that the ScriptManager has EnableScriptGlobalization="true" and EnableScriptLocalization="true".
<asp:ScriptManager ID="ScriptManager1" runat="server"
         EnableScriptGlobalization="true" EnableScriptLocalization="true" />
Tip 6: How to make sure user does not select a date earlier than today or greater than today
There could be instances where you do not want the user to select a day earlier than the current date. For example: when you are providing the user a form to book tickets, you would not like him to choose an earlier date. To achieve this requirement, use the following javascript code.
Prevent the User from selecting a Date Earlier than today
<head runat="server">
    <title>Calendar Extender</title>
    <script type="text/javascript">
    function checkDate(sender,args)
{
 if (sender._selectedDate < new Date())
            {
                alert("You cannot select a day earlier than today!");
                sender._selectedDate = new Date(); 
                // set the date back to the current date
sender._textbox.set_Value(sender._selectedDate.format(sender._format))
            }
}
    </script>
</head>
Call the code:
   <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <div>
          
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <cc1:CalendarExtender ID="CalendarExtender1"
            runat="server" OnClientDateSelectionChanged="checkDate" TargetControlID="TextBox1" />
        </div>
    </form>
Select Date Greater than today
In the javascript, just change this line
sender._selectedDate > new Date()
Note: You may argue that the user can still change the date by typing into the textbox or entering an invalid date. Well that can be easily handled using a ValidationControl and is covered in the next tip.
Tip 7: Add validation to the CalendarExtender Control
A simple way to add validation to the Calendar is to add a ValidationControl to the textbox associated with a CalendarExtender. You have two choices:
A.  Add an ‘Extender’ to the ValidationControl. To do so, drag and drop a ValidationControl > click on the smart tag of the ValidationControl > choose ‘Add Extender’. From the Extender Wizard, choose ValidatorCalloutExtender. Using this approach makes it extremely easy to discover and attach control extenders to your controls. In VS 2005, you had to do this process manually, by wiring up control extenders.
B.  You can choose not to add the Extender.
We will go ahead with option A. We will be adding two ValidationControls to the textbox. The first, a CompareValidator to check if the user does not enter an invalid date (Eg: May 32) and second, a RangeValidator to keep the date range as desired.
Adding CompareValidator
<asp:CompareValidator ID="CompareValidator1" runat="server"
                ControlToValidate="TextBox1" Display="Dynamic" ErrorMessage="Invalid Date"
                Operator="DataTypeCheck" Type="Date">
</asp:CompareValidator>
<cc1:ValidatorCalloutExtender ID="CompareValidator1_ValidatorCalloutExtender"
                runat="server" Enabled="True" TargetControlID="CompareValidator1">
</cc1:ValidatorCalloutExtender>
Adding RangeValidator – We will restrict the user to select a date range starting from today to 15 days from now.
<asp:RangeValidator ID="RangeValidator1" runat="server"
                ControlToValidate="TextBox1" ErrorMessage="RangeValidator"
                Type="Date">
</asp:RangeValidator>
<cc1:ValidatorCalloutExtender ID="RangeValidator1_ValidatorCalloutExtender"
                runat="server" Enabled="True" TargetControlID="RangeValidator1">
</cc1:ValidatorCalloutExtender>
In the code behind of your page, add this code
C#
    protected void Page_Load(object sender, EventArgs e)
    {
        RangeValidator1.MinimumValue = System.DateTime.Now.ToShortDateString();
        RangeValidator1.MaximumValue = System.DateTime.Now.AddDays(15).ToShortDateString();
    }
Tip 8: CalendarExtender Popup Window bring to front 
When user selects a date form the Calendar Extender. In web page if there is any images bellow of the calendar extender control it will go behind of that image. To bring front we need to add the following script 
 function SetIndex(sender)
{
 sender._popupDiv.style.zIndex=501;
}
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <cc1:CalendarExtender ID="CalendarExtender1"
            runat="server" OnClientDateSelectionChanged="checkDate" OnClientShown="SetIndex" TargetControlID="TextBox1" />

Well those were some tips associated with the CalendarExtender. As future versions of the toolkit are released, we should be hopeful that there will exist easier ways, of achieving the functionality discussed in this article. I hope this article was useful and I thank you for viewing it.

20 November 2011

What is the difference between Web Farm and Web Garden?

Visual Studio has its own integrated ASP.NET engine which is used to run the ASP.NET Web application from Visual Studio. ASP.NET Development Server is responsible for executing all the requests and responses from the client. Now after the end of development, when you want to host the site on some server to allow other people to access, concept of web servers comes in between. A web server is responsible for providing the response for all the requests that are coming from clients. The below diagram shows the typical deployment structure of an ASP.NET Web application with a single IIS.



Clients request for resources and IIS process the request and send back to clients.


Web Farm


This is the case where you have only one web server and multiple clients requesting for resources from the same server. But when are is huge amount of incoming traffic for your web sites, one standalone server is not sufficient to process the request. You may need to use multiple servers to host the application and divide the traffic among them. This is called “Web Farm”. So when you are hosting your single web site on multiple web servers over load balancer is called “Web Farm”. The below diagram shows the overall representation of Web Farms.


In general web farm architecture, a single application is hosted on multiple IIS Server and those are connected with the VIP (Virtual IP) with Load Balancer. Load Balancer IPs are exposed to external world to access. So whenever some request will come to server from clients, it will first hit the Load Balancer, then based on the traffic on each server, LB distributes the request to the corresponding web server. These web servers may share the same DB server or may be they can use a replicated server in the back end.

So, in a single statement, when we host a web application over multiple web servers to distribute the load among them, it is called Web Farm.

Web Garden

Now, let’s have a look at what is Web Garden? Both the terms sound the same, but they are totally different from each other. Before starting with Web Garden, I hope you have a fundamental idea of what an Application Pool is and what a Worker Process is.

Just to recall, when we are talking about requesting processing within IIS, Worker Process (w3wp.exe) takes care of all of these. Worker Process runs the ASP.NET application in IIS. All the ASP.NET functionality inside IIS runs under the scope of worker process. Worker Process is responsible for handling all kinds of request, response, session data, cache data. Application Pool is the container of worker process. Application pool is used to separate sets of IIS worker processes and enables a better security, reliability, and availability for any web application.



Now, by default, each and every Application pool contains a single worker process. Application which contains the multiple worker process is called “Web Garden”. Below is the typical diagram for a web garden application.

In the above diagram, you can see one of the applications containing the multiple worker processes, which is now a web garden.

So, a Web application hosted on multiple servers and access based on the load on servers is called Web Farms and when a single application pool contains multiple Worker processes, it is called a web garden.

Create Web Garden in IIS 6 and IIS 7


Now, I am going to show how you can change the Number of Worker processes in both IIS 6 and IIS 7. For IIS 6, Right Click on Application Pool > Properties > Goto Performance Tab.


In the “Performance Tab” section, you would have one option called “Web Garden” where worker process sets to “1”, you can set the number of worker processes that you required.

For IIS 7, Right Click on Application Pool > Go To Advance Settings > In Process Model section, you will have “Maximum Worker Processes”. You can change it more than 1 to make it as a web garden.
In the above image, you can also check the definition of Web Garden.

Advantages of Web Farm and Web Garden


Now, let’s have a look into the advantages of both the Web Farms and Web Gardens.

Advantages of Web Farm


  • It provides high availability. If any of the servers in the farm goes down, Load balancer can redirect the requests to other servers.
  • Provides high performance response for client requests.
  • Provides better scalability of the web application and reduces the failure of the application.
  • Session and other resources can be stored in a centralized location to access by all the servers.

Advantages of Web Garden


  • Provides better application availability by sharing requests between multiple worker process.
  • Web garden uses processor affinity where application can be swapped out based on preference and tag setting.
  • Less consumption of physical space for web garden configuration.

How to Manage Session in Web Farm Mode?


While using session, requests are distributed among different servers. By default, session mode is set to In Proc where session data is stored inside worker process memory. But, in Web farm mode, we can share the session among all the servers using a single session store location by making it Out proc (State Server or SQL Server Mode). So, if some of the servers go down and request is transferred to the other server by the Load balancer, session data should be available for that request.
In the above diagram, you can see that we can both the IIS server sharing the same session data which is stored in out of worker process. You can read one of my previous articles “Exploring Session in ASP.NET” where I have explained how you can configure session mode for Out Process mode.

How to Manage Session in Web Garden Mode?


When we are using Web garden where request is being taken care of by different worker process, we have to make the session mode as out process session mode as described earlier. For Web Garden, we have to configure the out process within the same server but for different worker process. 
While using Web garden with your application, you need make a couple of configuration settings in web.config in <process Model> section where you need to set certain properties like cpuMask, RequestLimit, webGarden, ClientConnectCheck, etc.

Summary


When we host a web application over multiple web servers to distribute the load among them, it is called Web Farm and when one application has multiple worker processes, it is called a Web garden.










30 October 2011

How to not show Visual Studio line numbers for blank lines?

Is there a way to display only line number of coded lines, i.e. don’t number blank lines? Numbered blank lines makes showing code in powerpoint decks hard to follow. 
 
Here’s the screenshot 
To do this,
  1. Enable Word Wrap (Tools Options – Text Editor – All Languages – General)
  2. At the end of the first real line (like #28 above), start inserting white space until it goes off the screen. Note the Word Wrap glyph.
  3. Turn off the Word Wrap glyph (same place as in step 1) and Turn off “View White Space” (Edit – Advance – View White Space)

HTML5 Intellisense in Visual Studio 2010/2008

HTML5 provide a great intellisense for Visual Studio 2010/2008.  You can download that tool from following link.

Once you download HTML5 then install it and then you need to change your validation to HTML5 in your Visual Studio 2010/2008 configuration. For that you have to follow bellow steps.
Go to Tools -> Options ->(Screen1) and then Text Editor -> HTML -> Validation and there you need to select the HTML5 like following.  as shown below screen 2 and then click on button.

Screen1

Screen 2
Now Visual studio 2010 or 2008 will have intellisense for HTM5. 


21 October 2011

Visual Studio 2008 styles

Visual Studio allows you to completely customize the editor background and text colors to whatever you want – allowing you to tweak them to create the experience that is “just right” for your eyes and personality.  You can then optionally export/import your color scheme preferences to an XML file via the Tools->Import and Export Settings menu command.
  
A New website that makes it easy to download and share VS color schemes

Luke Sampson launched the http://studiostyles.info/ site a week ago (built using ASP.NET MVC 2, ASP.NET 4 and VS 2010). Studiostyles.info enables you to easily browse and download Visual Studio color schemes that others have already created.  The color schemes work for both VS 2008 and VS 2010 (all versions – including the free VS express editions):

You can click any of the schemes to see screen-shots of it in use for common coding scenarios.  You can then download the color settings for either VS 2010 or VS 2008: This site is cool and you can find lots of visual studio 2010 and 2008 styles of your choice. 



I have downloaded the first one(Son of Obsidian). It will be visual studio settings file. 


Once you downloaded you can import like following. GoTo Tools->Import and export settings in Visual Studio 2008. Once you click this It will import and export dialog like following.

I have selected Import selected environment setting and clicked next on next screen you will have option to save your visual studio settings like following.

Clicking on next you will have options for different visual studio 2008 Style like following.

 Click on browse and select your downloaded style like following.

Click on open and then you will represented to recent file options

Click next You will come to final settings wizard like following.


 Click on finish now our style is applied.Close the dialog and open your project and you can see new color scheme for your visual studio 2008 like following.

How to Open Visual Studio Quickly?

Do the following simple steps.
Open Your Visual Studio by typing 'Start' --> 'Run' --> 'DEVENV' then,

1. Click Tools
2. Select Options.
3. Expand Environment.
4. Click Startup.
5. Choose Show Empty Environment in the dropdown for At startup.
6. Disable the Download Content Every checkbox
7. Click OK.  as shown in the below screen shot. 


27 April 2011

What is Web.Config And Machine.Config File?

What is Web.Config File?
It is an optional XML File which stores configuration details for a specific asp.net web application. 
Note:  When you modify the settings in the Web.Config file, you do not need to restart the Web service for the modifications to take effect..  By default, the Web.Config file applies to all the pages in the current directory and its subdirectories.
Extra:  You can use the <location> tag to lock configuration settings in the Web.Config file so that they cannot be overridden by a Web.Config file located below it. You can use the allowOverride attribute to lock configuration settings. This attribute is especially valuable if you are hosting untrusted applications on your server.

What is Machine.config File?
The Machine.Config file, which specifies the settings that are global to a particular machine. This file is located at the following path:
 \WINNT\Microsoft.NET\Framework\[Framework Version]\CONFIG\machine.config
As web.config file is used to configure one asp .net web application, same way Machine.config file is used to configure the application according to a particular machine. That is, configuration done in machine.config file is affected on any application that runs on a particular machine. Usually, this file is not altered and only web.config is used which configuring applications.
You can override settings in the Machine.Config file for all the applications in a particular Web site by placing a Web.Config file in the root directory of the Web site as follows:
\InetPub\wwwroot\Web.Config
  
What can be stored in Web.config file?
There are number of important settings that can be stored in the configuration file. Here are some of the most frequently used configurations, stored conveniently inside Web.config file..
1.      Database connections.
2.      Session States
3.      Error Handling (CustomError Page Settings.)
4.      Security (Authentication modes)

What is the best place to store Database connection string?
In Web.Config, you would add a key to the AppSettings Section:

<appSettings>
 <add key="MyDBConnection" value="data source=<ServerName>;Initial catalog =<DBName>;user id=<Username>;password=<Password>;" />
 </appSettings>

Example:
<add key="ConnectionString" value= "data source=localhost;Initial catalog=northwind;user id=sa;password=mypass" />
Then, in your ASP.Net application - just refer to it like this:
using System.Configuration;
string connectionString = (string )ConfigurationSettings.AppSettings["ConnectionString"];

Difference between Web.Config and Machine.Config File

Two types of configuration files supported by ASP.Net.
Configuration files are used to control and manage the behavior of a web application.

i) Machine.config
ii)Web.config

Difference between Machine.Config and Web.Config
Machine.Config:
i)  This is automatically installed when you install Visual Studio. Net.
ii) This is also called machine level configuration file.
iii)Only one machine.config file exists on a server.
iv) This file is at the highest level in the configuration hierarchy.

Web.Config:
i)  This is automatically created when you create an ASP.Net web application project.
ii) This is also called application level configuration file.
iii)This file inherits setting from the machine.config

13 April 2011

difference between Server.MapPath("."), Server.MapPath("~"), Server.MapPath(@"\") and Server.MapPath("/")?

Server.MapPath specifies the relative or virtual path to map to a physical directory.
  • Server.MapPath(".") returns the current physical directory of the file (e.g. aspx) being executed
  • Server.MapPath("..") returns the parent directory
  • Server.MapPath("~") returns the physical path to the root of the application
  • Server.MapPath("/") returns the physical path to the root of the domain name (is not necessarily the same as the root of the application)
An example:
Let's say you pointed a web site application (http://www.example.com/) to
C:\Inetpub\wwwroot
and installed your shop application (sub web as virtual directory in IIS, marked as application) in
D:\WebApps\shop
For example, if you call Server.MapPath in following request:
http://www.example.com/shop/products/GetProduct.aspx?id=2342
then:
  • Server.MapPath(".") returns D:\WebApps\shop\products
  • Server.MapPath("..") returns D:\WebApps\shop
  • Server.MapPath("~") returns D:\WebApps\shop
  • Server.MapPath("/") returns C:\Inetpub\wwwroot
  • Server.MapPath("/shop") returns D:\WebApps\shop
If Path starts with either a forward (/) or backward slash (\), the MapPath method returns a path as if Path were a full, virtual path.
If Path doesn't start with a slash, the MapPath method returns a path relative to the directory of the request being processed.
Note: in C#, @ is the verbatim literal string operator meaning that the string should be used "as is" and not be processed for escape sequences.

18 March 2011

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)