Best way to Use #Temp table and @Table variable

Here I am going to discuss how often we can use #Temptable and @Temp variable in real T-SQL Code

Whenever we are writing T-SQL Code, we need a table in which to store temporarily data, based on this SQL offer four different table options:-

1) Normal tables
2) Local temporary tables
3) Global temporary tables
4) Table Variables

• Normal tables are that exactly physical tables defined in our database.
• Local Temporary tables are temporary table that are available only to the session that created. This tables are automatically distroyed at the termination of the procedure.
• Gloabal Temporary tables are temporary tables that are avaible to all sessions and all users. They are dropeed automatically when last session using temporary table has completed. Both local temporary tables and global temporary tables are physical are physically created in the tempdb database.
• Temp variables are stored within memory but its set as table. Table variables are partially stored on disk and partially stored in memory. The Access time for table variable can be faster than the temproray table.

The difference between accessing tables and variables cause the internal SQL server processes, Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tabels just as any other database tables. Becasue data reads on temproray table are made a READ lock is placed on the table.

If you are applying a locking process takes its time and consume CPU resources. When reading data from table variable, because table variable partially stored in memory and can not accessed by any other user. In very busy database lack of locking can improve system performance.

On Heavy load system that has good to use temporary tables, the disk array in the TEMPDB database will experience a higher than expected load, becuase all the reads and writes to the temporary tables are done withig the tempdb database. Table variable will perform poorly with large record sets. When many users start using table variable we have to concerned because large Amount of RAM are used.

Table variable should hold no more 2 megs or 3 megs of data (depends on user load system momory)

Delete Duplicate Rows using single query

Before I am going to discuss how to delete duplicate rows using single query on SQL Server 2005, I am going to explain what is CTE (Common Table Partitioning) and how to use Paritioning table.

CTE is Common Table Expressions (CTEs).

CTE is a "temporary result set" that exists only within the scope of a single SQL statement.It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

Let's take a Example of CTE

WITH CTE(Name) AS (SELECT Name = 'DIPAK') SELECT [Name] FROM CTE

when you run above query on query anlyzer you can see the output as

Name
Dipak

Another Example of CTE is

USE AdventureWorks;
WITH OrderedOrders
AS( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader )
SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;


Now Using CTE and partitioning table I am doing to delete duplicate rows on SQL Server 2005 with single query

Let's create the sample table

CREATE TABLE [dbo].temp(ID int NOT NULL,FirstName varchar(50),LastName varchar(50)) ON [PRIMARY]

Insert into temp values(1,'Dipak','Patel')
Insert into temp values(1,'Dipak','Patel')
Insert into temp values(2,'Chirag','Darji')
Insert into temp values(2,'Chirag','Darji')
Insert into temp values(3,'SK','Patel')


Now when you run select * from temp on query analyzer you could see below result with some duplicare records

ID FirstName LastName
1 Dipak Patel
1 Dipak Patel
2 Chirag Darji
2 Chirag Darji
3 SK Patel

Now when run below query

select ID,FirstName,Lastname, row_number() over (partition by ID,FirstName,LastName Order by ID) as Number from temp

you could see below result

ID FirstName LastName Number
1 Dipak Patel 1
1 Dipak Patel 2
2 Chirag Darji 1
2 Chirag Darji 2
3 SK Patel 1

Now here what happenes with partition by exactly, with the use of Partition by it Divides the result set into partitions with same records, Now we can easily remove the records using CTE,

WITH T1 AS (Select Id,FirstName,LastName, ROW_NUMBER() OVER (PARTITION BY Id,FirstName,LastName Order By Id) AS NUMBER From TEMP)Delete From T1 Where Number >1

For More Information about RowNumber and Partition By you can refer below URL

http://msdn2.microsoft.com/en-us/library/ms189461.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx


Thanks
Dipak Patel

Inline variable initialization in New Version of SQL 2008

In Katmai( A New Name of SQL 2008), A Couple of New Features Included, so here i am going to discuss How Inline Vairable initialization work in SQL 2008.

declare @i int = 1, @a = varchar(50) = 'Dipak',@b datetime = getdate();

--now to show this values

select @i,@a,@b

--to Increment the values of i

select @i+ = 1, @s += 'patel';

--to show this new value

select @i,@s;

These operation also work in DML Statements and Columns, for example Update statement in a table called 'testtable' with a column called 'testcolumn' where you want to increment the value of testcolumn by 1 could be;

update testTable set testColumn += 1;

and it is Also would work with other columns...if another column called testColumn2 existed:
update testTable set testColumn += testColumn2;

This will be New Features on SQL Server 2008.

Note: This Article is copied from Microsoft Web.

Thank you