Temporary Tables:
CREATE TABLE #T1(col1 INT NOT NULL);
INSERT INTO #T VAUES(1)
OR
SELECT ID INTO #T FROM dbo.Nums WHERE ..
- Temporary Tables are defined using #
- They are always stord in tempdb database
- Temporary tables take part in transactions
- Scope of temporary tables is current and inner queries.
- If tables has named constraints then DB never caches these. So try to use contraints like unique etc only for better performance
- Good for data set if it is big as SQL run statistics on these to optimize.
- Not good for small data set lot of recompile happens
Global Temporary variables:
- Defined using ##
- Have global scope
- Once nobody is using these SQL server cleans from tempDB.
Table Vairable:
DECLARE @t TABLE(ID INT, PERSON NVARCHAR(200) NOT null)
Select * from @t
Insert into @t Values(...)
- SQL doesn't compute statistics so generally slower if data is too big.
- Less recompiles so good if data is small.
- Stored in tempdb database
- Table definition cannot be changed once declared
- Doesn't take part in outetransactions
- Explicit indexes cannot be created for these.
- Only accessible in current contxt. Not in outer or inner queries.
- For Table Variables NSERT SELECT happens with minimal logging in SQL 2008.
No comments:
Post a Comment