SQL SERVER : SP_WHO and SP_WHO2

There is very useful system stored procedure called sp_who on Sql server that lists users and processes running on the system. It comes handy when you want to see if any process is blocked. Both sp_who and sp_who2 provides information about the current processes / sessions / users in sql server instances. sp_who2 provides some more information as compare to the sp_who.

SP_WHO : SP_WHO provides information like logged in user id, session id, host name ,session status, blocking process details , database name etc.

SP_WHO2 : SP_WHO2 provides some more information - CPU time for each session, Disk IO, Last Batch , Program name.

As such there is no difference in both SPs but the major one is that SP_WHO2 is not a documented it is an undocumented stored procedure.

Above two SPs can be used to monitor the server connections. Also these can be used to check the connections created for specific login/specific session /active session. You can filter the data returned to show only those processes that are not idle. if you specify a particular user like sp_who  [@login_name=]'Login' then you get processes for that user , otherwise all processes for all users will be listed .

SQL SERVER : Difference between SQL Server Temporary table and Table Variable

Temporary Table :

Temporary tables are created in TEMP DB. A temporary table is easy to create and back up data. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE,INSERT and DELETE statements.If temporary table created inside a store procedure they are destroyed upon completion of the stored procedure.Temporary tables are real tables so you can do things like CREATE Indexes etc. The scope of any temporary table is the session in which it is created.You can create a temporary table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temporary table structure upfront.

There are two different temporary table types : Local and Global 

Local temporary tables are prefixed with a single # sign.Local temporary table tables are available for use by the current user connection that created them.Multiple connections can create the same named temporary table for local temporary tables without encountering conflicts.The internal representation of the local table is given a unique name,so as not to conflict with other temporary tables with the same name created by the other connections in the TEMP database. Local temporary tables are dropped by using the DROP statement or are automatically removed from memory when the user connection is closed.

Global temporary tables are prefixed with a double ## sign. Global temporary table have different scope from local temporary tables.Once connection creates a global temporary table, any user with proper permission to the current database he is in, can access the table.Unlike local temporary tables, you can not create simultaneous versions of a global temporary tables, as this will produce a naming conflict.Global temporary tables are removed from sql server if explicitly dropped by DROP Table. They are also automatically removed after the connection that created it disconnects and the global temporary is no longer referenced by the other connections. 

The Syntax to creating a temporary table is similar to creating a physical table in SQL Server with the exception of the a aforementioned pound sign(#):

CREATE TABLE #TmpTable

(

ID INT NOT NULL,

Name VARCHAR(20)

)

Table Variables:

A table variable is a data type that can be used within a Transact-SQL batch, stored procedure, or function.Table variables are good replacements of temporary tables when the data set is small. Statistics are not maintained for table variables like they are for regular or temporary tables, so using too large a table variable may cause query optimization issues.Unlike regular tables or temporary tables , table variables can't have indexes or FOREIGN KEY constraints added to them. Table variables do allow some constraints to be used in the definition (PRIMARY KEY , UNIQUE,CHECK ). Table variable performance suffers when the result set becomes too large or when column data cardinality is critical to the query optimization process.

The syntax to creating a table variables is similar to creating a table, only the DECLARE keyword is used and the table name is prefixed with an @ symbol :

DECLARE @TableName TABLE

( 

ID INT NOT NULL,

Name VARCHAR(20)

)

The main difference between temporary table and table variable is that transaction logs are not recorded for the table variables. Hence they are out of scope of the transaction mechanism. Both table variables and temporary tables are stored in TEMP database. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation.Temporary tables and table variable will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database. Depending on the situation as well as the version and service pack of SQL server that is installed, the performance metrics of table variable versus temporary tables can change. Unless the performance metrics change significantly using one or the other, your best option is to pick the one that makes the most sense in the current situation and stick with it.