Table Variable Vs Temp table in SQL Server

In many scenarios we need some temporary table for the processing of data. Now you have two option 1) Table Variable, 2) temp Table. Which one do you choose? Let's talk about differences between these two and make sure our decision to use one of them is best for our requirement.

Table Variable	Temp Table
Performance differences
Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features.	Temporary Tables are real tables so you can do things like CREATE Indexes, etc. If you have large amounts of data for which accessing by index will be faster than temporary tables are a good option
You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (e.g. make a function to split a string into a table of values on some arbitrary delimiter).	You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing data types over time, since you don't need to define your temp table structure upfront.
A table variable can only have a primary index,	A temp table can have indexes
If speed is an issue Table variables can be faster	But if there are a lot of records, or the need to search the temp table of a clustered index, then a Temp Table would be better.
A table variables don't have column statistics, This means that the query optimizer doesn't know how many rows are in the table variable (it guesses 1), which can lead to highly non-optimal plans been generated if the table variable actually has a large number of rows	Whereas temp tables do have column statistics so the query optimizer can choose different plans for data involving temp tables
You cannot alter a Table variable with DDL statement (so you cannot create a non clustered index on a table variable). That makes every table variable a heap, or at best a table with a single, clustered index, and every table variable access a table scan (or clustered index scan)	Temp tables can be altered with DDL statements
User table variable if there less records for processing	Use temp table if you have huge record for processing
Syntactical difference
-To create table variable declare @T table   (firstColumn varchar(100)) -insert operation insert into @T select 'some value' -select statement select * from @T	-You can create temp table create table #T   (firstColumn varchar(100)) -Insert Operation insert into #T select 'some value' -Select statement select * from #T

 
So now I think you can make wise decision which one to use when. Have fun -J


 
Happy Coding!!!

Script for Setting Database Mail and Sending Email From SQL Server 2008

Step1: First of all you need to enable and setup Data mail profile and account. Below is the script just copy and paste to your SQL Server query window and change the parameters accordingly.

/*

    Run this script after setting proper configurations

*/



-- ENABLE DATABASE MAIL FEATURE IN SYSTEM CONFIGURATION

use
master

go

sp_configure
'show advanced options',1

go

reconfigure
with
override

go

sp_configure
'Database Mail XPs',1

go

reconfigure

go



-- DECLARE DATABASE MAIL ACCOUNT PARAMETERS

Declare @qmail_account_name as
varchar(64);

Declare @qmail_account_description as
varchar(64);

Declare @qmail_account_email_address as
varchar(64);

Declare @qmail_account_display_name as
varchar(64);

Declare @qmail_account_username as
varchar(64);

Declare @qmail_account_password as
varchar(64);

Declare @qmail_account_mailserver_name as
varchar(64);



-- DECLARE PROFILE PARAMETERS

declare @qmail_profile_profile_name as
varchar(64);

declare @qmail_profile_description as
varchar(64);



-- DECLARE PRINCIPLES

declare @qprincipal_name as
varchar(16);



-- SET SMTP ACCOUNT DETAILS, USERNAME AND PASSWORD HERE

set @qmail_account_mailserver_name =
'smtp.bizmail.yahoo.com'
-- your smtp address

set @qmail_account_username='something@something.com'
-- username

set @qmail_account_email_address =
'myEmailName@something.com'
-- email address

set @qmail_account_password='password for user (something@something.com)'
-- password for username





set @qmail_account_name =
'YourAccountName (e.g MyAppName_EmailAccount)'

set @qmail_account_description =
'Mail account for administrative e-mail.'

set @qmail_account_display_name =
'MyApplicationName Automated Mailer'



-- PROFILE PARAMETERS ASSIGNMENT

set @qmail_profile_profile_name =
'MyAppNameEmailServiceProfile'

set @qmail_profile_description =
'Profile used for database mail jobs'



--PROFILE TYPE

set @qprincipal_name=
'public'

if ( @qmail_account_mailserver_name =
''
or @qmail_account_username=''
or @qmail_account_email_address =
''
or @qmail_account_password=''
)

begin

        Select
'Please enter SMTP details'
as [Comments]

end

else

Begin

            if
exists(select
*
from msdb.dbo.sysmail_account where [name]=@qmail_account_name)

            begin

            exec msdb.dbo.sysmail_delete_account_sp
@account_name = @qmail_account_name

            end



            EXECUTE msdb.dbo.sysmail_add_account_sp

             @account_name = @qmail_account_name,

                @description = @qmail_account_description,

                @email_address = @qmail_account_email_address,

                @display_name = @qmail_account_display_name,

                @username=@qmail_account_username,

                @password=@qmail_account_password,

                @mailserver_name = @qmail_account_mailserver_name



            if
exists(select
*
from msdb.dbo.sysmail_profile where [name]=@qmail_profile_profile_name)

            begin

            exec msdb.dbo.sysmail_delete_profile_sp
@profile_name = @qmail_profile_profile_name

            end



            EXECUTE msdb.dbo.sysmail_add_profile_sp

                 @profile_name = @qmail_profile_profile_name,

                 @description = @qmail_profile_description



            EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

                @profile_name = @qmail_profile_profile_name,

                @account_name = @qmail_account_name,

                @sequence_number = 1



            EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

                @profile_name = @qmail_profile_profile_name,

                @principal_name = @qprincipal_name,

                @is_default = 1 ;

end

 

 

 

Step2: Create a Sproc which will send email notification. Below is the script for that only you need to change your email To/CC, Subject, Body from your application tables or you can set anything you want. Please make sure Email profile name is proper it should be same as you have configured during Database Email Setup.

if
exists
(select 1 from
sys.objects
where name =
'p_SendEmailNotification'
and
type
=
'p')

drop
procedure p_SendEmailNotification

go



Create
Procedure [dbo].[p_SendEmailNotification]

as

Begin

Declare @ErrorCode as
int

BEGIN
TRANSACTION

declare @MailPriority varchar(8);

declare @sendAddressTo varchar (256);

declare @sendAddressCC varchar(256);

declare @MailBodyFormat varchar(8);

declare @DBMailProfile varchar(32);



set @MailBodyFormat ='HTML'

set @DBMailProfile =
'Email Profile Name which you have created during DB Mail setup (e.g MyAppNameEmailServiceProfile)'

set @MailPriority =
'Normal'



select @sendAddressTo =
'someone@someone.com,abc@abc.com'



select @sendAddressCC ='someone@someone.com,xyz@xyz.com'



EXEC msdb.dbo.sp_send_dbmail

                @profile_name = @DBMailProfile,

                @recipients=@sendAddressTo,

                @copy_recipients = @sendAddressCC,

                @importance = @MailPriority,

                @subject =
'Email Subject',

                @body =
'Email Body....hi this is test email',

                @body_format = @MailBodyFormat ;

               


set @ErrorCode =
@@ERROR


if(@ErrorCode <> 0)


begin


goto ERR_HANDLER


end




COMMIT
TRANSACTION

Set @ErrorCode =
@@Error

if @ErrorCode<>0 begin
goto ERR_HANDLER end



ERR_HANDLER:

if @ErrorCode<>0

Begin


ROLLBACK
TRANSACTION

End

End

You are done, now if you just need to call this sproc and this will send the email, if you want to Automate this email as some alert then better you create a job and within that job call this sproc as per your needed schedule.