Derived Tables – Sql server

Derived Tables are similar to table variables it’s exist in memory only many deferent is table variable need to declare and derived table always create within a select statements, its also called to Subsquery.

Derived tables exist only for the duration of a single T-SQL statement and it’s can’t be referenced outside the T-SQL statement in which they are created.

select a.id,a.name,v.total_Voucher,min_date,max_date
from vouchertype a
inner join (select VoucherTypeID,count(*)total_Voucher,
                           min(cast(Datestamp as date))min_date,
                           max(cast(Datestamp as date))max_date
                     from voucher group by VoucherTypeID)v on v.VoucherTypeID= a.id

Table Variables

Table variables like variable, its creating as variable like DECLARE statement.

Table variable table’s schema create in tempdb database and data store in Memory and its name start with hash (#) then add  random 8 digit like a Local temp table but it’s can’t explicitly accessible, it will automatically drop when batch completed its behavior like a In-Memory. Table variable is only accessible within the current batch or procedure.

Declare Table Variables:

declare @tbl TABLE(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));
insert into @tbl(name)
select a.name from sys.objects a,sys.objects b,sys.objects c,sys.objects d;