SQL Serve 2000 introduces a new variable named table. You
can say table variable is an alternative to temporary table. Which is better in
both it depends on scenario somewhere table variable is good and somewhere
temporary table.
You can save record in table variable
Declaration:
DECLARE @SalaryDetail TABLE
(
Basic int,
PF int,
EmpId varchar
)
Fill the table
Insert into @SalaryDetail (Basic,PF,EmpId) Select Basic,PF,EmpId from
Salary.
Table
variables can be used in batches, stored procedures, and user defined
functions. It also can be update and delete as well.
Update
@SalaryDetail Set PF=PF*8/100 where EmpIt=’2’
Delete from @SalaryDetail where EmpId=’1’
Select Top 10 from @SalaryDetail OrderBy Basic Desc
Scope: Table
variable cannot use as input and output parameter in procedure or function. Just
like other variable table variable also no longer exist after the procedure
exits. No need to drop the table (table variable).
A user defined function can return a table variable.
Performance: A table variable will generally use fewer resources in
comparison of temporary variable cause is its well-defined scope. Transactions
lock the table variable at last moment so less locking time.
There is no need to recompile the stored procedure in case of table variable
unlike temporary table.
Other Features:
//////////////////////////////////////////////////////////////////////////////////////
Using a temporary table inside of a stored
procedure may result in additional re-compilations of the stored procedure.
Table variables can often avoid this recompilation hit. For more information on
why stored procedures may recompile, look at Microsoft knowledge base article
243586 (INF: Troubleshooting Stored Procedure
Recompilation).
Other Features
Constraints are an excellent way to ensure the
data in a table meets specific requirements, and you can use constraints with
table variables. The following example ensures ProductID values in the table
will be unique, and all prices are less then 10.0.
DECLARE @MyTable
TABLE
(
ProductID int UNIQUE,
Price money CHECK(Price < 10.0)
)
|
You can also declare primary keys. identity
columns, and default values.
UPDATE @ProductTotals
DECLARE @MyTable
TABLE
(
ProductID int IDENTITY(1,1)
PRIMARY KEY,
Name
varchar(10) NOT NULL DEFAULT('Unknown')
)
|
So far it seems that table variables can do
anything temporary tables can do within the scope of a stored procedure, batch,
or UDF), but there are some drawbacks.
No comments:
Post a Comment