Table Variable:

Table variables are used within the scope of the routine or batch which they are defined. And its an alternative of Temporary table which can do all kinds of operations that we can perform in Temp table.

Example: Below is the syntax of table variable

USE AdventureWorks

GO

SET NOCOUNT ON

DECLARE @FirstTable TABLE (RandomInteger INT)

DECLARE @SecondTable TABLE (RandomInteger INT)

DECLARE @WhenWeStarted DATETIME

DECLARE @ii INT

BEGIN TRANSACTION

SET @ii = 0

WHILE @ii < 100

  BEGIN

    INSERT  INTO @FirstTable VALUES  (RAND() * 10000)

   

    SET @ii = @ii + 1

  END

--SELECT * FROM @FirstTable

 

SET @ii = 0

WHILE @ii < 100

  BEGIN

    INSERT  INTO @SecondTable VALUES  (RAND() * 10000)

   

    SET @ii = @ii + 1

  END

COMMIT TRANSACTION

SELECT  @WhenWeStarted = GETDATE()

SET STATISTICS PROFILE ON

SELECT  COUNT(*)

FROM    @FirstTable first

        INNER JOIN @SecondTable second

        ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)

NOTE: We can use table variable when we are dealing with small amount of data or as function parameter in SPs