Tables in SQL Server

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I’ll discuss the differences between using temporary tables in SQL Server versus table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:

* Normal tables are exactly that, physical tables defined in your database.
* 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.
* Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
* Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It’s a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Creating indexes on SQL Server tables

Because both local and global temporary tables are physical tables within the tempdb database, indexes can be created on these tables to increase performance as needed. As with any index creation, this process can take time on larger tables. Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.

Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.

How do the internal workings of SQL Server perform differently between table variables and temporary tables?

The differences between accessing tables and variables cause the internal processes within SQL Server to treat the objects quite differently. Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tables just as they would any other database tables. Because reads to a temporary table are made (including local temporary tables), a read lock is placed on the table.

This locking process takes time and CPU resources. When reading from a table variable – because the table variable is stored partially within memory and cannot be accessed by any other user or process on the system – SQL Server knows locking is not required. In a very busy database, this lack of locking can improve system performance because locks do not have to be taken, escalated and checked for each data access operation.

Limits of temporary tables and table variables

Temporary tables and table variables both have their strengths, but they both have weaknesses too. On a heavy load system that has lots of usage of temporary tables, the disk array servicing the tempdb database will experience a higher than expected load. This happens because all reads and writes to the temporary tables are done within the tempdb database. Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables — large amounts of RAM are used because all temporary tables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).

Both temporary tables and table variables can be extremely useful tools in developers’ and administrators’ arsenals; however, care must be taken as to when to use each solution. There is no end-all solution, and you must choose the correct solution for the correct situation.

Local Temporary tables:

They are created using same syntax as CREATE TABLE except table name is preceded by ‘#’ sign. When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.

Open one session in Query Analyzer or SSMS (Management Studio) and create a temporary table as shown below.

CREATE TABLE #TEMP
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)

GO

Upon successful execution of above command, MS SQL Server creates table in tempdb database. One cannot create another temporary table with the same name in the same session. It will give an error but table with the same name can be created from another session. To do this, open another session from SSMS or query analyzer and issue same command again. It will successfully create new temporary table for that session.

In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number. This is very common scenario when temporary table is defined in the stored procedure and procedure is getting executed by different users simultaneously. Since we have created temporary table with the same name from two different sessions, we should see two entries in tempdb database. From another session or any of the current session, issue following command. Output is displayed after select statement.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘%TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb #TEMP________0000000001F7
tempdb #TEMP________0000000001F9

Now create some data from the session in which temporary table (#temp) is created.

INSERT INTO #TEMP(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO #TEMP(COL1, COL2) VALUES(2,’Information’);
INSERT INTO #TEMP(COL1, COL2) VALUES(3,’systems’);

Selecting data from temporary table will give following results.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-27 19:39:56.727
2 Information 2007-03-27 19:39:56.727
3 systems 2007-03-27 19:39:56.727

This data is not visible from another session since we are using local temporary table. We can verify it by connecting to another session and querying the #temp table. Local temporary tables are dropped when session which created the table is ended, if one has not dropped it explicitly.

Also, please do note that if you are creating temp tables in a stored procedure, the scope for the existence of those temporary tables is only the procedure execution. The temp tables automatically get dropped once the procedure execution is over (they can be explicitly dropped as well). Once the procedure execution is over, those temp tables will not be accessible from within that session. Example:

create proc test
as
begin
set nocount on
create table #temp (col1 int)
insert into #temp values (1)
end
go

exec test
select * from #temp

Msg 208, Level 16, State 0, Line 2
Invalid object name ‘#temp’.

Global Temporary tables:

Syntax difference between global and local temporary table is of an extra ‘#’ sign. Global temporary tables are preceded with two ‘#’ (##) sign. Following is the definition. In contrast of local temporary tables, global temporary tables are visible across entire instance.

CREATE TABLE ##TEMP_GLOBAL
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)
GO

Execute above statement to create global temporary table. You can verify it by checking the tempdb database. As global temporary tables are available across the instance, SQL Server doesn’t suffix it with the number. Following is the output of query ran against tempdb.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘##TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb ##TEMP_GLOBAL

There will be only single instance of global temporary table. Attempt of creating global temporary table with the same name from any other session will result into an error.

Create some data in one of the session where temporary table (##temp_global) is created.

INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(2,’Information’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(3,’systems’);

Connect to other existing session or open new session. Execute following statement and you will notice that global temporary table is available along with the data from other session as well.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-28 09:52:34.310
2 Information 2007-03-28 09:52:34.310
3 systems 2007-03-28 09:52:34.310

Global temporary tables are dropped when last session accessing the tables is closed. It is always good practice to drop the temporary tables in the same scope, once we are done with it. This will help us in avoiding creation error when same connection from the connection pool is used by different processes which access temporary tables.

Global temporary tables can be used in data warehousing application where one session performs the ETL and populate the global temporary tables and other sessions read from the table, specific data and process it.

Features of Temp Tables

We’ll list out features that differentiate a Temp Table between either a Permanent Table or a Table variable. These pointers will be helpful to keep in mind when you consider Table Variables in our next post.

Scope: Within a connection, a temporary table object is visible to the creating level and inner levels (nested). For example, if you create a stored procedure and declare a temporary table object within it, you can call another stored procedure from that stored procedure (a nested stored procedure) and perform operations like inserting, updating and deleting that temporary table object. Once the main creating level terminates, the temp table is automatically destroyed. But don’t be too complacent – you’ll have to wait for the system to perform a clean up and therefore, it is highly recommended that you manually drop your temporary table.

Locking: The prospect of table locking is reduced when it comes to local temporary tables since this table is being used by only one user. One aspect where you might want to keep this in mind is that if you cancel a transaction which contains the creation of a temp table object and then cancel that query, an exclusive and update lock can appear on the tempdb. This lock will persist till the complete transaction has closed with a COMMIT or a ROLLBACK

Logging: There is less logging involved with temporary tables compared to permanent tables.

Transaction: When using a temporary table, a temporary table is an integral part of an outer transaction and therefore, ROLLBACKs must be supported by Logging

Indexing: We can create indexes on temporary tables explicitly on them. Hence, there is scope for performance enhancement when you talk about temp tables.

Constraints: All constraints are available for exploiting on a temp table EXCEPT when it comes to referring a Foreign Key Constraints

Statistics: SQL Server can create Statistics for temp tables just like we do for permanent tables and therefore, the query optimizer has the option of choosing different plans. Hence, with this in mind, be aware of the scope of Stored Procedure Recompiles.

Recompiles: There is scope for a large number of Stored Procedure Recompiles especially when you have DDL statements mixed anywhere within your Stored Procedure.

Temp Table Size: Can hold any volume of data. This will be a strong part of the deciding factor when you want to choose between a temporary table and a table variable.

Features of Table Variables

Now that you’ve got a hang around working with a Table Variable, let me mention the main pointers that we need to keep in mind while working with. This will set the stage for differentiating between a Temp Table and Table Variable which I’ll illustrate in my next post.

Transactions: Table Variables are not bound to any transaction as they are just like any other variable

Minimum Constraints: A Table Variable permits us to use only the PRIMARY KEY, UNIQUE KEY and NULL constraint only. What this implies behind the scenes is that we can have unique indexes. The only possibility of creating a non-unique index is if we add attributes and make that blend unique and have a PRIMARY KEY or a UNIQUE KEY on the combination we just made.

No SELECT INTO: We cannot use a SELECT INTO with Table Variables in SQL Server 2000 though the feature is available with Table Variables in SQL Server 2005. Likewise, we can also have INSERT INTO working with Table Variables against a SELECT but not against an EXEC Stored Procedure.

No ALTER TABLE Variable: We cannot ALTER a Table once it has been declared. This may look a little rigid but remember that recompilations can come out like wild fire when there are DDL (Data Definition Language .i.e Schema) changes and therefore, this helps to avoid recompilations.

Scope: Just like any other variable, a Table Variable’s scope exists only within the context of the current level. Therefore, unlike Temp Tables, it is not accessible to sub levels (of Stored Procedures)

Table Variables And The TempDb: Okay, now I’ll touch upon one of the most common myths that exist among developers: that Table Variables have nothing to do with TempDb and therefore, they have no physical representation in the TempDb and therefore, they reside in ONLY memory and therefore they’re the best option for efficient processing.

Not entirely true. Table Variables do indeed have a physical representation within the TempDb and this can proved with a simple query in your database against the TempDb:

No Statistics: When it comes to Table Variables, the SQL Server optimizer does not create distribution statistics. Therefore, you run the risk of referring not-so-good query plans when the SQL Server optimizer selects after checking up with histograms. And if you consider this aspect with Tables Variables that contain huge amounts of data, we fall into serious I/O thrashing. Hence, as stated in the closing section of the last point, we have to have a thorough understanding of our scenario to choose a temporary object for the context.

A possible replacement for temp tables is a table variable.

In summary, following are the key points when temporary tables are involved.

* Temporary tables can be defined as local or global temporary tables.
* Local temporary tables are available to session in which they are created. If another session creates the table with the same name, it will be different copy of the table in tempdb database.
* Global temporary tables are available across the instance. Any user from any session can access it.
* It is best practice to drop the temporary table when related work is finished rather than relying on connection to end for the cleanup.
* Table variables can be used instead of temporary tables for performance reasons and when dealing with smaller sub-sets.
* When used in the procedure,function or trigger, its scope ends once execution is completed.

Limitations of Temporary Tables

Temporary tables are created in the tempdb database and create additional overhead for SQL Server, reducing overall performances. SQL Server has numerous problems with operations against temporary tables.

Using Temporary Tables Effectively

If you do not have any option other than to use temporary tables, use them affectively. There are few steps to be taken.

* Only include the necessary columns and rows rather than using all the columns and all the data which will not make sense of using temporary tables. Always filter your data into the temporary tables.
* When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements, create the table using DDL statement and use INSERT INTO to populate the temporary table.
* Use indexes on temporary tables. Earlier days, I always forget to use a index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables.
* After you finish the using your temporary table, delete them. This will free the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time.
* When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query.

Conclusion

Generally, temporary tables should be avoided as much as possible. If you need to use them follow the steps above so that you have the minimum impact on server performance

If you have to use a temp table, do not create it from within a transaction. If you do, then it will lock some system tables (syscolumns, sysindexes, and syscomments) and prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance.

Alternatives to @@IDENTITY

@@IDENTITY	on a connection	regardless of the table	regardless of the scope
IDENT_CURRENT('tablename')	Regardless of the connection	produced in a table	regardless of the scope
SELECT SCOPE_IDENTITY()	produced on a connection	regardless of the table	in the same scope

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Two Interceptors: HttpModule and HttpHandlers-downloading a file

Introduction

Many times we want to implement pre-processing logic before a request hits the IIS resources. For instance you would like to apply security mechanism, URL rewriting, filter something in the request, etc. ASP.NET has provided two types of interception HttpModule and HttpHandler. This article walks through it.

The Problem

Many times we need to inject some kind of logic before the page is requested. Some of the commonly used pre-processing logics are stat counters, URL rewriting, authentication / authorization and many more. We can do this in the code behind but then that can lead to lot of complication and tangled code. The code behind will not solve the purpose because in some implementations like authorization, we want the logic to execute before it reaches the resource. ASP.NET provides two ways of injecting logic in the request pipeline HttpHandlers and HttpModules.

HttpHandler - The Extension Based Preprocessor

HttpHandler help us to inject pre-processing logic based on the extension of the file name requested. So when a page is requested, HttpHandler executes on the base of extension file names and on the base of verbs. For instance, you can visualize from the figure below how we have different handlers mapped to file extension. We can also map one handler to multiple file extensions. For instance, when any client requests for file with extension ‘GIF’ and ‘JPEG’, handler3 pre-processing logic executes.

HttpModule - The Event Based Preprocessor

HttpModule is an event based methodology to inject pre-processing logic before any resource is requested. When any client sends a request for a resource, the request pipeline emits a lot of events as shown in the figure below:

Below is a detailed explanation of the events. We have just pasted this from here.

• BeginRequest: Request has been started. If you need to do something at the beginning of a request (for example, display advertisement banners at the top of each page), synchronize this event.
• AuthenticateRequest: If you want to plug in your own custom authentication scheme (for example, look up a user against a database to validate the password), build a module that synchronizes this event and authenticates the user in a way that you want to.
• AuthorizeRequest: This event is used internally to implement authorization mechanisms (for example, to store your access control lists (ACLs) in a database rather than in the file system). Although you can override this event, there are not many good reasons to do so.
• PreRequestHandlerExecute: This event occurs before the HTTP handler is executed.
• PostRequestHandlerExecute: This event occurs after the HTTP handler is executed.
• EndRequest: Request has been completed. You may want to build a debugging module that gathers information throughout the request and then writes the information to the page.

We can register these events with the HttpModules. So when the request pipe line executes depending on the event registered, the logic from the modules is processed.

The Overall Picture of Handler and Modules

Now that we have gone through the basics, let's understand what is the Microsoft definition for handler and modules to get the overall picture.

Reference: INFO: ASP.NET HTTP Modules and HTTP Handlers Overview

“Modules are called before and after the handler executes. Modules enable developers to intercept, participate in, or modify each individual request. Handlers are used to process individual endpoint requests. Handlers enable the ASP.NET Framework to process individual HTTP URLs or groups of URL extensions within an application. Unlike modules, only one handler is used to process a request”.

Steps to Implement HttpHandlers

Step 1

HttpHandlers are nothing but classes which have pre-processing logic implemented. So the first thing is to create a class project and reference System.Web namespace and implement the IHttpHandler interface as shown in the below code snippet. IHttpHandler interface has two methods which needs to be implemented; one is the ProcessRequest and the other is the IsResuable. In the ProcessRequest method, we are just entering the URL into the file and displaying the same into the browser. We have manipulated the context response object to send the display to the browser.

Collapse

using System;

using System.Web;

using System.IO;

namespace MyPipeLine

{

public class clsMyHandler : IHttpHandler

{

public void ProcessRequest(System.Web.HttpContext context)

{

context.Response.Write("The page request is " + context.Request.RawUrl.ToString());

StreamWriter sw = new StreamWriter(@"C:\requestLog.txt",true);

sw.WriteLine("Page requested at " + DateTime.Now.ToString() +

			context.Request.RawUrl); sw.Close();

}

public bool IsReusable

{

get

{

return true;

}

}

}

Step 2

In step 2, we need to make an entry of HttpHandlers tag. In the tag, we need to specify which kind of extension requested will invoke our class.

Collapse

<system.web>

<httpHandlers>

<add verb="*" path="*.Shiv,*.Koirala" type="MyPipeLine.clsMyHandler, MyPipeLine"/>

</httpHandlers>

</system.web>

Once done, request for page name with extension ‘Shiv’ and you should see a display as shown below. So what has happened is when the IIS sees that request is for a ‘.shiv’ page extension, it just calls the clsMyHandler class pre-processing logic.

Steps to Implement HttpModule

Step 1

As discussed previously, HttpModule is an event pre-processor. So the first thing is to implement the IHttpModule and register the necessary events which this module should subscribe. For instance, we have registered in this sample for BeginRequest and EndRequest events. In those events, we have just written an entry on to the log file.

Collapse

public class clsMyModule : IHttpModule

{

public clsMyModule()

{}

public void Init(HttpApplication objApplication)

{

// Register event handler of the pipe line

objApplication.BeginRequest += new EventHandler(this.context_BeginRequest);

objApplication.EndRequest += new EventHandler(this.context_EndRequest);

}

public void Dispose()

{

}

public void context_EndRequest(object sender, EventArgs e)

{

StreamWriter sw = new StreamWriter(@"C:\requestLog.txt",true);

sw.WriteLine("End Request called at " + DateTime.Now.ToString()); sw.Close();

}

public void context_BeginRequest(object sender, EventArgs e)

{

StreamWriter sw = new StreamWriter(@"C:\requestLog.txt",true);

sw.WriteLine("Begin request called at " + DateTime.Now.ToString()); sw.Close();

}

}

Step 2

We need to enter those module entries into the HttpModule tag as shown in the below code snippet:

Collapse

<httpModules>

<add name="clsMyModule" type="MyPipeLine.clsMyModule, MyPipeLine"/>

</httpModules>

The Final Output

If you run the code, you should see something like this in the RequestLog.txt. The above example is not so practical. But it will help us understand the fundamentals.

Collapse

Begin request called at 11/12/2008 6:32:00 PM

End Request called at 11/12/2008 6:32:00 PM

Begin request called at 11/12/2008 6:32:03 PM

End Request called at 11/12/2008 6:32:03 PM

Begin request called at 11/12/2008 6:32:06 PM

End Request called at 11/12/2008 6:32:06 PM

Begin request called at 11/12/2008 8:36:04 PM

End Request called at 11/12/2008 8:36:04 PM

Begin request called at 11/12/2008 8:37:06 PM

End Request called at 11/12/2008 8:37:06 PM

Begin request called at 11/12/2008 8:37:09 PM

End Request called at 11/12/2008 8:37:09 PM

Begin request called at 11/12/2008 8:37:38 PM

Page requested at 11/12/2008 8:37:38 PM/WebSiteHandlerDemo/Articles.shiv

End Request called at 11/12/2008 8:37:38 PM

Reference

• INFO: ASP.NET HTTP Modules and HTTP Handlers Overview

http://www.codeproject.com/KB/aspnet/HttpModuleandHttpHandle.aspx

A lot of questions are being asked about downloading a file from the web server to the client in ASP.NET. I have updated this blog post due to the high number of view & comments. You will realize i added a function called "ReturnExtension" which will return the proper content type and set it to the Response.ContentType property. Almost well known file types are supported.

C# Code

// Get the physical Path of the file(test.doc)
string filepath = Server.MapPath("test.doc");

// Create New instance of FileInfo class to get the properties of the file being downloaded
FileInfo file = new FileInfo(filepath);

// Checking if file exists
if (file.Exists)
{
// Clear the content of the response
Response.ClearContent();

// LINE1: Add the file name and attachment, which will force the open/cance/save dialog to show, to the header
Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);

// Add the file size into the response header
Response.AddHeader("Content-Length", file.Length.ToString());

// Set the ContentType
Response.ContentType = ReturnExtension(file.Extension.ToLower());

// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead)
Response.TransmitFile(file.FullName);

// End the response
Response.End();
}

private string ReturnExtension(string fileExtension)
{
switch (fileExtension)
{
case ".htm":
case ".html":
case ".log":
return "text/HTML";
case ".txt":
return "text/plain";
case ".doc":
return "application/ms-word";
case ".tiff":
case ".tif":
return "image/tiff";
case ".asf":
return "video/x-ms-asf";
case ".avi":
return "video/avi";
case ".zip":
return "application/zip";
case ".xls":
case ".csv":
return "application/vnd.ms-excel";
case ".gif":
return "image/gif";
case ".jpg":
case "jpeg":
return "image/jpeg";
case ".bmp":
return "image/bmp";
case ".wav":
return "audio/wav";
case ".mp3":
return "audio/mpeg3";
case ".mpg":
case "mpeg":
return "video/mpeg";
case ".rtf":
return "application/rtf";
case ".asp":
return "text/asp";
case ".pdf":
return "application/pdf";
case ".fdf":
return "application/vnd.fdf";
case ".ppt":
return "application/mspowerpoint";
case ".dwg":
return "image/vnd.dwg";
case ".msg":
return "application/msoutlook";
case ".xml":
case ".sdxl":
return "application/xml";
case ".xdp":
return "application/vnd.adobe.xdp+xml";
default:
return "application/octet-stream";
}

N.B: If you want to bypass the Open/Save/Cancel dialog you just need to replace LINE1 by the below code

Response.AddHeader("Content-Disposition", "inline; filename=" + file.Name);

Response.TransmitFile VS Response.WriteFile:
1- TransmitFile: This method sends the file to the client without loading it to the Application memory on the server. It is the ideal way to use it if the file size being download is large.
2- WriteFile: This method loads the file being download to the server's memory before sending it to the client. If the file size is large, you might the ASPNET worker process might get restarted.

Hope this helps,

50 Common Interview Questions and Answers :

Review these typical interview questions and think about how you would answer them. Read the questions listed; you will also find some strategy suggestions with it.

1. Tell me about yourself:

The most often asked question in interviews. You need to have a short statement prepared in your mind. Be careful that it does not sound rehearsed. Limit it to work-related items unless instructed otherwise. Talk about things you have done and jobs you have held that relate to the position you are interviewing for. Start with the item farthest back and work up to the present.

2. Why did you leave your last job?

Stay positive regardless of the circumstances. Never refer to a major problem with management and never speak ill of supervisors, co- workers or the organization. If you do, you will be the one looking bad. Keep smiling and talk about leaving for a positive reason such as an opportunity, a chance to do something special or other forward- looking reasons.

3. What experience do you have in this field?

Speak about specifics that relate to the position you are applying for. If you do not have specific experience, get as close as you can.

4. Do you consider yourself successful?

You should always answer yes and briefly explain why. A good explanation is that you have set goals, and you have met some and are on track to achieve the others.

5. What do co-workers say about you?

Be prepared with a quote or two from co-workers. Either a specific statement or a paraphrase will work. Jill Clark, a co-worker at Smith Company, always said I was the hardest workers she had ever known. It is as powerful as Jill having said it at the interview herself.

6. What do you know about this organization?

This question is one reason to do some research on the organization before the interview. Find out where they have been and where they are going. What are the current issues and who are the major players?

7. What have you done to improve your knowledge in the last year?

Try to include improvement activities that relate to the job. A wide variety of activities can be mentioned as positive self-improvement. Have some good ones handy to mention.

8. Are you applying for other jobs?

Be honest but do not spend a lot of time in this area. Keep the focus on this job and what you can do for this organization. Anything else is a distraction.

9. Why do you want to work for this organization?

This may take some thought and certainly, should be based on the research you have done on the organization. Sincerity is extremely important here and will easily be sensed. Relate it to your long-term career goals.

10. Do you know anyone who works for us?

Be aware of the policy on relatives working for the organization. This can affect your answer even though they asked about friends not relatives. Be careful to mention a friend only if they are well thought of.

11. What kind of salary do you need?
A loaded question. A nasty little game that you will probably lose if you answer first. So, do not answer it. Instead, say something like, That's a tough question. Can you tell me the range for this position? In most cases, the interviewer, taken off guard, will tell you. If not, say that it can depend on the details of the job. Then give a wide range.

12. Are you a team player?
You are, of course, a team player. Be sure to have examples ready. Specifics that show you often perform for the good of the team rather than for yourself are good evidence of your team attitude. Do not brag, just say it in a matter-of-fact tone. This is a key point.

13. How long would you expect to work for us if hired?

Specifics here are not good. Something like this should work: I'd like it to be a long time. Or As long as we both feel I'm doing a good job.

14. Have you ever had to fire anyone? How did you feel about that?

This is serious. Do not make light of it or in any way seem like you like to fire people. At the same time, you will do it when it is the right thing to do. When it comes to the organization versus the individual who has created a harmful situation, you will protect the organization. Remember firing is not the same as layoff or reduction in force.

15. What is your philosophy towards work?

The interviewer is not looking for a long or flowery dissertation here. Do you have strong feelings that the job gets done? Yes. That's the type of answer that works best here. Short and positive, showing a benefit to the organization.

16. If you had enough money to retire right now, would you?

Answer yes if you would. But since you need to work, this is the type of work you prefer. Do not say yes if you do not mean it.

17. Have you ever been asked to leave a position?

If you have not, say no. If you have, be honest, brief and avoid saying negative things about the people or organization involved.

18. Explain how you would be an asset to this organization.

You should be anxious for this question. It gives you a chance to highlight your best points as they relate to the position being discussed. Give a little advance thought to this relationship.

19. Why should we hire you?

Point out how your assets meet what the organization needs. Do not mention any other candidates to make a comparison.

20. Tell me about a suggestion you have made.

Have a good one ready. Be sure and use a suggestion that was accepted and was then considered successful. One related to the type of work applied for is a real plus.

21. What irritates you about co-workers?

This is a trap question. Think real hard but fail to come up with anything that irritates you. A short statement that you seem to get along with folks is great.

22. What is your greatest strength?

Numerous answers are good, just stay positive. A few good examples: Your ability to prioritize, Your problem-solving skills, Your ability to work under pressure, Your ability to focus on projects, Your professional expertise, Your leadership skills, Your positive attitude

23. Tell me about your dream job.

Stay away from a specific job. You cannot win. If you say the job you are contending for is it, you strain credibility. If you say another job is it, you plant the suspicion that you will be dissatisfied with this position if hired. The best is to stay genetic and say something like: A job where I love the work, like the people, can contribute and can't wait to get to work.

24. Why do you think you would do well at this job?

Give several reasons and include skills, experience and interest.

25. What are you looking for in a job?

See answer # 23

26. What kind of person would you refuse to work with?

Do not be trivial. It would take disloyalty to the organization, violence or lawbreaking to get you to object. Minor objections will label you as a whiner.

27. What is more important to you: the money or the work?

Money is always important, but the work is the most important. There is no better answer.

28. What would your previous supervisor say your strongest point is?

There are numerous good possibilities:
Loyalty, Energy, Positive attitude, Leadership, Team player, Expertise, Initiative, Patience, Hard work, Creativity, Problem solver

29. Tell me about a problem you had with a supervisor.

Biggest trap of all. This is a test to see if you will speak ill of your boss. If you fall for it and tell about a problem with a former boss, you may well below the interview right there. Stay positive and develop a poor memory about any trouble with a supervisor.

30. What has disappointed you about a job?

Don't get trivial or negative. Safe areas are few but can include:
Not enough of a challenge. You were laid off in a reduction Company did not win a contract, which would have given you more responsibility.

31. Tell me about your ability to work under pressure.

You may say that you thrive under certain types of pressure. Give an example that relates to the type of position applied for.

32. Do your skills match this job or another job more closely?

Probably this one. Do not give fuel to the suspicion that you may want another job more than this one.

33. What motivates you to do your best on the job?

This is a personal trait that only you can say, but good examples are: Challenge, Achievement, Recognition

34. Are you willing to work overtime? Nights? Weekends?

This is up to you. Be totally honest.

35. How would you know you were successful on this job?

Several ways are good measures:
You set high standards for yourself and meet them. Your outcomes are a success.Your boss tell you that you are successful

36. Would you be willing to relocate if required?

You should be clear on this with your family prior to the interview if you think there is a chance it may come up. Do not say yes just to get the job if the real answer is no. This can create a lot of problems later on in your career. Be honest at this point and save yourself uture grief.

37. Are you willing to put the interests of the organization ahead of your own?

This is a straight loyalty and dedication question. Do not worry about the deep ethical and philosophical implications. Just say yes.

38. Describe your management style.

Try to avoid labels. Some of the more common labels, like progressive, salesman or consensus, can have several meanings or descriptions depending on which management expert you listen to. The situational style is safe, because it says you will manage according to the situation, instead of one size fits all.

39. What have you learned from mistakes on the job?

Here you have to come up with something or you strain credibility. Make it small, well intentioned mistake with a positive lesson learned. An example would be working too far ahead of colleagues on a project and thus throwing coordination off.

40. Do you have any blind spots?

Trick question. If you know about blind spots, they are no longer blind spots. Do not reveal any personal areas of concern here. Let them do their own discovery on your bad points. Do not hand it to them.

41. If you were hiring a person for this job, what would you look for?

Be careful to mention traits that are needed and that you have.

42. Do you think you are overqualified for this position?

Regardless of your qualifications, state that you are very well qualified for the position.

43. How do you propose to compensate for your lack of experience?

First, if you have experience that the interviewer does not know about, bring that up: Then, point out (if true) that you are a hard working quick learner.

44. What qualities do you look for in a boss?

Be generic and positive. Safe qualities are knowledgeable, a sense of humor, fair, loyal to subordinates and holder of high standards. All bosses think they have these traits.

45. Tell me about a time when you helped resolve a dispute between others.

Pick a specific incident. Concentrate on your problem solving technique and not the dispute you settled.

46. What position do you prefer on a team working on a project?

Be honest. If you are comfortable in different roles, point that out.

47. Describe your work ethic.

Emphasize benefits to the organization. Things like, determination to get the job done and work hard but enjoy your work are good.

48. What has been your biggest professional disappointment?

Be sure that you refer to something that was beyond your control. Show acceptance and no negative feelings.

49. Tell me about the most fun you have had on the job.

Talk about having fun by accomplishing something for the organization.

50. Do you have any questions for me?

Always have some questions prepared. Questions prepared where you will be an asset to the organization are good. How soon will I be able to be productive? and What type of projects will I be able to assist on? are examples.

And Finally Good Luck

cursors status

1 Find out the cursors that are allocated but not opened or closed

view source

print?

01.--Method 1

02.

03.select name from sys.dm_exec_cursors(0) where is_open =0

04.

05.

06.--Method 2

07.

08.select

09. cur.cursor_name

10.from

11. sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle

12.where

13. cur.open_status =0

2 Find out the cursors that are opened and not closed

view source

print?

01.--Method 1

02.

03.select name from sys.dm_exec_cursors(0) where is_open =1

04.

05.

06.--Method 2

07.

08.select

09. cur.cursor_name

10.from

11. sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle

12.where

13. cur.open_status =1

3 Find out the cursors that are allocated but not deallocated

01.--Method 1

02.

03.select name from sys.dm_exec_cursors(0)

04.

05.

06.--Method 2

07.

08.select

09. cur.cursor_name

10.from

11. sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle