Table Variables:
Tables which can be declared and functions similar to a varaible are referred as Table Variables.
Table variables were created in memory inturn enable faster database access. Table variables are considered as good alternative for Temporary tables. Since, temporary tables will create tables in TempDB, there is additional resource utilization in accessing TempDB and Creating tables in TempDB.
Declaring a table variable:
The syntax for creating table varaible:
DECLARE tablevariablen-name TABLE ( COLUMN1 TYPE, COLUMN2 TYPE, COLUMN3 TYPE, .....)
Example:
declare @TableVariable table(id int,name varchar(20))
Using Table Varaibles:
After creating the table variable we can the table variables for DML Operations.
The syntax for using table varaibles in DML operation:
I have considered insert statement,
INSERT INTO tablevariablen-name values(value1,value2,value3.....)
Example:
insert into @TableVariable values(1,'Microsoft')
select * from @TableVariable
Using Join operation in Table variables:
Unlike ordinary tables, table variables can be used in join operator only by specifying alias name.
Example:
Am declaring two Table variables with DML Operations:
declare @TableVariable table(id int,[name] varchar(20))
declare @TableVariable1 table(id int,[name] varchar(20))
insert into @TableVariable values(1,'Microsoft')
insert into @TableVariable values(4,'Microsoft1')
insert into @TableVariable values(2,'Article')
update @TableVariable set id =3 where name = 'Microsoft'
select * from @TableVariable
insert into @TableVariable1 values(4,'Microsoft1')
insert into @TableVariable1 values(3,'Microsoft')
Am using inner join to retrieve datas from the tables:
select * from @TableVariable inner join @TableVariable1
on @TableVariable.id= @TableVariable1.id
In the above statement, you have got error indicating varaibles are not declared its because you have to use alias name in join operations.
Just try the below code it will work....... happy learning.
select Tab1.id,Tab2.[name] from @TableVariable as Tab1 inner join @TableVariable1
as Tab2 on Tab1.id= Tab2.id
Note: Please select Declaration,DML operations at a stretch and execute it.
Terms and Conditions in using variables:
Unlike temporary tables, table variables adhere to its scope. Temporary tables can be created first and we can execute insert statement or dml operations seperately inside the scope. For example, #a is executed first and afterwards we can execute dml operations subsequently since its stored in database we can use it. Where as, table varaibles resides in memory and doesn't have physical structure, the declaration statement and dml statements should be executed simultaneously else if we try to execute declare statement first(executed) and afterward if we try to access that table an error will be thrown indicating table is not available.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|