DataBase Concepts!

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Sql Server Important Concepts
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
ALter Table Command in Sql Server
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Alter table table alter column columnname(new datatype)






mmmmmmmmmmmmmmmmmmmmmmmmmmm
Use of Isnull in Sql server
mmmmmmmmmmmmmmmmmmmmmmmmmmm

SELECT
ISnull(RemedyPlan,'') AS RemedyPlan ,
ISnull(Convert(VARCHAR(12),RemedyImplementationDate,107),Getdate()) AS RemedyDate ,
ISnull(Justification,'') AS Justification ,
ISnull(ReasonForNonCompliance,'') AS ReasonForNonCompliance
FROM
tblRemedy



mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Use of @@RowCount in Sql server
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm


Select * From tblSurveyApprovalLevel
declare @count int
set @count=@@RowCount
select @count


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
How to Give Comment in Sql server
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

/*
Author : MANPREET sINGH
Date : 24 Nov 2004
PROCEDURE dbo. : PROCEDURE dbo. to check n send mails to survey or survey approval defaulters

*/

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
How to Declare variable in Sql server
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

CREATE Procedure dbo.USPSentSurveyMails

AS

Declare @MailDate DateTime
Declare @StartDate DateTime
Declare @DaysBefore INT
Declare @QuarterID INT

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
How to set value to a variable in Sql server
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
SET @QuarterID=0

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
How to do mathematical operation
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Select
@MailDate = @StartDate + DaysAfter + DaysBefore +1

from
tblSurveyRollout
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Table type variable and its use
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

Declare @Mails Table //// @Mails ia table type variable
(
ID INT Identity,
Days INT,
Priority INT,
Subject Varchar(100),
Matter Varchar(500),
ApproverLevelId INT,
Recipients Varchar(7000)
)

INSERT INTO @Mails(Days,Priority,Subject,Matter,ApproverLevelId,Recipients)
Select NumberOfDays,Priority,Subject,Matter,ApproverLevelId,Recipients from tblSurveyMailer


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
While loop in Sql server
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
-----------1
WHILE (@LoopCount <= @Counter)
BEGIN
SELECT @SurveyResultId = SurveyResultId FROM #Temp WHERE TempId = @LoopCount
UPDATE #Temp SET UserId =(SELECT UserId FROM tblSurveyResult WHERE SurveyResultId = @SurveyResultId)
WHERE TempId = @LoopCount
SET @LoopCount = @LoopCount + 1
END


-------------2

Declare @Loop INT
Declare @Count INT
Set @Loop=0
INSERT INTO @Mails(Days,Priority,Subject,Matter,ApproverLevelId,Recipients)
Select NumberOfDays,Priority,Subject,Matter,ApproverLevelId,Recipients from tblSurveyMailer
SET @Count = @@Rowcount

WHILE @Loop<>@Count
Begin
SET @Loop =@Loop +1
Declare @Days INT
Declare @ApproverLevelId INT

Select @Days = Days,@ApproverLevelId=ApproverLevelId,@Subject=Subject,@Matter=Matter,@Recipients=Recipients From @Mails Where Id = @Loop

Select 'Final Mail Date' , Convert(Varchar,@MailDate - @Days,107)
IF Convert(Varchar,@MailDate - @Days,107)=Convert(Varchar,Getdate(),107)
BEGIN

IF @ApproverLevelId=1
BEGIN
Insert Into @Email(Email)
Select Email From tblUser
Where UserId In (Select UserId FRom UDFGetSurveyDefaulters())
SET @MCount = @@Rowcount
Set @MLoop=0
While @MLoop<>@MCount
BEGIN
SET @MLoop = @MLoop + 1
SELECT @TO = EMail From @Email Where ID=@MLoop
EXEC sp_SMTPemail 'rupa.s.kolnurkar@gsk.com',@To,@Subject, @Matter

END
END
ELSE
BEGIN
Insert Into @Email(Email)
Select Email From tblUser
Where UserId In (
select UserId FRom UDFGetSurveyApprovalDefaulters())
SET @MCount = @@Rowcount
Set @MLoop=0
While @MLoop<>@MCount
BEGIN
SET @MLoop = @MLoop + 1
SELECT @TO = EMail From @Email Where ID=@MLoop
EXEC sp_SMTPemail 'rupa.s.kolnurkar@gsk.com',@To,@Subject, @Matter

END
END
END


End

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Pick value from one table and insert into other table
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Insert Into @Email(Email)
Select Email From tblUser Where UserId In (Select UserId FRom UDFGetSurveyDefaulters())

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
send mail through Sqlserver
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Syntax--EXEC sp_SMTPemail 'from','To','Subject', 'Matter'

EXEC sp_SMTPemail 'rupa.s.kolnurkar@gsk.com',@To,@Subject, @Matter


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
TEMPORARY TABLE IN sQL SERVER
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
EVERY TEMPORARY TABLE IF NOT DELETE AFTER USE STORE IN THE TEMPDB NAMED DATABASE
IF WE WANT TO DELETE ALL THE TEMPORAY TABLE FROM THESE Database Following statements write


IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE type = 'U' and NAME = @TempTableName)
BEGIN
EXEC('DROP TABLE tempdb..' + @TempTableName)
END





CREATE TABLE ##TempTable
(
LocProcId INT ,
LocId INT ,
ProcId INT ,
ProcessDesc VARCHAR(500),
Title VARCHAR(500),
ParentId INT ,
IsDelete INT
)

INSERT ##TempTable EXEC USPProcessHierarchy @LocProcId

-------After using temporay table it should be dropped by following statement
DROP table ##TempTable

aaaaaaaaaaaaaaaaaaaaaaaaaa
USPProcessHierarchy
aaaaaaaaaaaaaaaaaaaaaaaaaaa



CREATE PROCEDURE dbo. USPProcessHierarchy
(
@LOCPROCID INT

)
AS

SET NOCOUNT ON
SET CURSOR_CLOSE_ON_COMMIT ON

BEGIN TRAN



DECLARE @ProcID INT
DECLARE @LocID INT
SET @PROCID=0
SET @LOCID=0
--Fetching Process Id and Location Id for Filtering
SELECT @ProcID=ProcID,@LocID=LocID From tblMapLocationProcess
Where LocProcId = @LocProcId



DECLARE @CUR_Name VARCHAR(40)
DECLARE @TableName VARCHAR(40)

--Getting Random Cursor & Table Name String
EXEC USPGenerateRandomString @CUR_Name OutPut
EXEC USPGenerateRandomString @TableName OutPut



--Calling Main PROCEDURE dbo.
EXEC uspProcessTree @ProcID,@CUR_Name,@TableName

DECLARE @Query NVARCHAR(1500)

SET @Query = N'Select MLP.LocProcId, MLP.LocId, MLP.ProcId, P.ProcessDesc, P.Title, P.ParentId, MLP.IsDelete From ##' + @TableName +
N' P,tblMapLocationProcess MLP Where P.ProcID = MLP.ProcID AND IsDelete=0
And MLP.LocID = ' + CONVERT(VARCHAR, @LocID)

EXECUTE sp_executesql @Query


--Droping Temp Table After Select
DECLARE @Query2 NVARCHAR(200)
SET @Query2 = N'Drop Table ##' + @TableName
EXECUTE sp_executesql @Query2


COMMIT TRAN
GO





mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
CASE AND CAST STATEMENT
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
CAST( CASE
When MLU.UserID<> 0 Then
1
Else
0
End
AS BIT) AS [User],


CAST(COLUMN NAME AS DATATYPE)

-------2
CASE SR.Result
WHEN 1 THEN ISNULL(R.ReasonForNonCompliance, '')
WHEN 2 THEN ISNULL(SR.Justification, '')
WHEN 3 THEN IsNull(SR.NoProofJustification,'')
ELSE ''
END AS ReasonForNonCompliance,

---------3

CASE
WHEN(SR.Result = 3 AND IsNull(SR.NoProofJustification,'') = '') THEN 'Available'
WHEN(SR.Result = 3 AND IsNull(SR.NoProofJustification,'') <> '') THEN 'Not Available'
ELSE ''
END AS ProofOfCompliance


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
INNER JOIN AND OUTER JOIN IN sQL SERVER
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm


FROM
TABLE1 T1
INNER JOIN TABLE2 T2
ON T1.COLNAME = T2.COLNAME
INNER JOIN TABLE T3
ON T1.COLNAME = T3.COLNAME,
tblRemedy R
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
PICK THE CURRENT DATE
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
getdate()


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
How to ACCESS A TABLE INFORMATION RESTORED IN OTHER DATABASE
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

SELECT * FROM DATABASENAME..OBJECTNAME

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
use cancatination operation in sqlserver
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
select

U.Fname + ' ' + U.LName+', '+U.Designation as PrimResp

from tablename

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
IF STATEMENT
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

IF ((SELECT IsSubmit FROM tblSurveyResult WHERE SurveyResultId=@SurveyResultId)=0)

BEGIN
END
ELSE
BEGIN
END


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
EXECUTE QUERY IN STORED PROCEDURE!OR EXEC() FUNCTION
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

SET @QueryApproved = 'UPDATE tblCentralPlans
SET IsDelete = 0
WHERE CentralPlanId IN ('+@CentralPlansApproved+')'

EXEC (@QueryApproved)

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
USING CURSOUR IN sQL SERVER
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

UDFAdmin_New_LocDrillDown in Gsk_latest(Database)


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Column with user Defined datatype
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm


CREATE TABLE [dbo].[Profileuser](

[age] AS (datepart(year,getdate())-datepart(year,[birthday])),

use cancatination operation in sqlserver
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
pick the time only from column of datatype datetime
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm


select convert(varchar,Replydate,108) as replydate from ForumReply

mmmmmmmmmmmmmmmmmmmmmmm
Best Example of Cursor
mmmmmmmmmmmmmmmmmmmmmmm

/*set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[ForumPost_Extract]

as*/
--declare @CountReply int
--set @CountReply=select count(*) from ForumReply where ForumPostId in

--Insert into ForumPost values(@ForumTypeId,@ForumTopic,@ForumBody,@PosterId,@PostDate)

--select count(*) from ForumReply where ForumPostid in(Select Distinct ForumPostid from forumreply)

Declare @replies int
Declare @forumpostid int
Declare @ForumTopic varchar(50)
Declare @postdate varchar(100)
Declare @createdby varchar(100)
Declare @Image varchar(100)

Declare @repcount int

declare @Table table
(
forumpostid int,
ForumTopic varchar(50),
postdate varchar(100),
createdby varchar(100),
[Image] varchar(100),
replies int
)




Declare cur_survey cursor Fast_Forward for
select forumpostid, ForumTopic,username as createdby,convert(varchar,Postdate,108)+' | '+ convert(varchar,Postdate,107)
as postdate,[Image],0 from Forumpost
inner join users on ForumPost.PosterId=users.userid
order by postdate desc

OPEN cur_survey
FETCH NEXT FROM cur_survey INTO @forumpostid,@ForumTopic,@createdby,@postdate,@Image,@replies
WHILE @@FETCH_STATUS = 0
BEGIN
set @repcount=0
select @repcount = count(*) from forumreply where forumpostid=@forumpostid
insert into @Table values(@forumpostid,@ForumTopic,@postdate,@createdby,@Image,@repcount)
FETCH NEXT FROM cur_survey INTO @forumpostid,@ForumTopic,@postdate,@createdby,@Image,@replies
end
CLOSE cur_survey
DEALLOCATE cur_survey

select * from @Table




mmmmmmmmmmmmmmmmmmmmmmm
substring in Sqlserver
mmmmmmmmmmmmmmmmmmmmmmm
Select substring('Manpreet Singh Bhatia',0,10)

Ans-Manpreet

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Division operation in Sqlserver
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

Floor(DATEDIFF(day, '03/12/2007',GetDate())/365.25)


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Unique Identifier Datatype in SqlServer2005
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm


First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:

Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)

That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time. I don't know the full mechanics of creating a GUID, but I seem to remember that it has something to do with the MAC address on your network card and the system time.

To get a GUID in SQL Server (7.0+), you call the NEWID() function.

The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.

This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.


mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Identity Column in SqlServer
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm



Creating an Identity Column
In it's simplest form an identity column creates a numeric sequence for you. You can specify a column as an identity in the CREATE TABLE statement:

CREATE TABLE dbo.Yaks ( YakID smallint identity(7,2), YakName char(20) )The identity clause specifies that the column YakID
is going to be an identity column. The first record added will automatically be assigned a value of 7 (the seed) and each
subsequent record will be assigned a value 2 higher (the increment) than the previous inserted row. Most identity columns
I see are specified as IDENTITY(1,1) but I used IDENTITY(7,2) so the difference would be clear. If you don't specify the
identity and seed they both default to 1. Identity columns can be int, bigint, smallint, tinyint, or decimal or numeric
with a scale of 0 (i.e. no places to the right of the decimal).



mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Finding the Identity Value that was Inserted

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
SELECT SCOPE_IDENTITY() as NewRec

or
SELECT @@identity

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
How to Insert Values into an Identity Column in SQL Server

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Identity columns are bad because...
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm




They're not standard SQL. Most products have it but there's no consistent implementation.
They can't be updated. This violates the relational data model (not fatal, but not good either). Duplicates can be accidentally inserted (fatal).
They only create numeric values. GUID/NewID() are also numeric only, and are hard to read.
Numeric values are not meaningful in many tables, and adding them complicates relationships between other tables.


Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.


You cannot use a table variable in either of the following situations:

INSERT @table EXEC sp_someProcedure

SELECT * INTO @table FROM someTable


You cannot truncate a table variable.


Table variables cannot be altered after they have been declared.


You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does with them is another story. One thing to note is that you cannot explicitly name your constraints, e.g.:

DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
CONSTRAINT myPK PRIMARY KEY (CPK1, CPK2)
)

-- yields:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'CONSTRAINT'.

-- yet the following works:
DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
PRIMARY KEY (CPK1, CPK2)
)


You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column, or DEFAULT CONSTRAINT.


You cannot use a user-defined type (UDT) in a column definition.



mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
Limitation of Temprory variable as compared to temporary table...
mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm


mmmmmmmmmmTemporary table mmmmmmmmmmmmm

Create table #table
(
ID int identity(2,2),
ename varchar(50)
)
insert into #table values('manpreet')
insert into #table values('Amandeep')
insert into #table values('Gagan')

mmmmmmmmmmTemporary variable mmmmmmmmmmmmm

Create @table table
(
ID int identity(2,2),
ename varchar(50)
)
insert into @table values('manpreet')
insert into @table values('Amandeep')
insert into @table values('Gagan')




Unlike a #temp table, you cannot drop a table variable when it is no longer necessary—you just need to let it go out of scope.


You cannot generate a table variable's column list dynamically, e.g. you can't do this:

SELECT * INTO @tableVariable

-- yields:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@tableVariable'.

You also can't build the table variable inside dynamic SQL, and expect to use it outside that scope, e.g.:

DECLARE @colList VARCHAR(8000), @sql VARCHAR(8000)
SET @colList = 'a INT,b INT,c INT'
SET @sql = 'DECLARE @foo TABLE('+@colList+')'
EXEC(@sql)
INSERT @foo SELECT 1,2,3

-- this last line fails:

Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@foo'.

This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.


The system will not generate automatic statistics on table variables. Likewise, you cannot manually create statistics (statistics are used to help the optimizer pick the best possible query plan).


An INSERT into a table variable will not take advantage of parallelism.


A table variable will always have a cardinality of 1, because the table doesn't exist at compile time.


Table variables must be referenced by an alias, except in the FROM clause. Consider the following two scripts:

CREATE TABLE #foo(id INT)
DECLARE @foo TABLE(id INT)
INSERT #foo VALUES(1)
INSERT #foo VALUES(2)
INSERT #foo VALUES(3)
INSERT @foo SELECT * FROM #foo

SELECT id
FROM @foo
INNER JOIN #foo
ON @foo.id = #foo.id

DROP TABLE #foo

The above fails with the following error:

Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@foo'.

This query, on the other hand, works fine:

SELECT id
FROM @foo f
INNER JOIN #foo
ON f.id = #foo.id



Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables:

CREATE PROCEDURE faq_outer
AS
BEGIN
CREATE TABLE #outer
(
letter CHAR(1)
)

EXEC faq_inner

SELECT letter FROM #outer

DROP TABLE #outer
END
GO

CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT #outer VALUES('a')
END
GO


EXEC faq_outer

Results:

letter
------
a

(1 row(s) affected)

However, you cannot do this with table variables. The parser will find the error before you can even create it:

CREATE PROCEDURE faq_outer
AS
BEGIN
DECLARE @outer TABLE
(
letter CHAR(1)
)

EXEC faq_inner

SELECT letter FROM @outer
END
GO

CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT @outer VALUES('a')
END
GO

Results:

Server: Msg 137, Level 15, State 2, Procedure faq_inner, Line 4
Must declare the variable '@outer'.

For more information about sharing data between stored procedures, please see this article by Erland Sommarskog.

--------------------------------------------------------------------------------
Conclusion

Like many other areas of technology, there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are typically choosing between #temp tables and table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense. Depending on your environment, that threshold could be anywhere — however you will obviously need to use #temp tables if any of the above limitations represents a significant roadblock.