I have an application that I am working on that uses some small temp
tables. I am considering moving them to Table Variables - Would this
be a performance enhancement?
Some background information: The system I am working on has numerous
tables but for this exercise there are only three that really matter.
Claim, Transaction and Parties.
A Claim can have 0 or more transactions.
A Claim can have 1 or more parties.
A Transaction can have 1 or more parties.
A party can have 1 or more claim.
A party can have 1 or more transactions. Parties are really many to
many back to Claim and transaction tables.
I have three stored procs
insertClaim
insertTransaction
insertParties
From an xml point of view the data looks like this
<claim>
<parties>
<info />
insertClaim takes 3 sets of paramters - All the claim level
information (as individual parameters), All the parties on a claim (as
one xml parameter), All the transactions on a claim(As one xml
parameter with Parties as part of the xml)
insertClaim calls insertParties and passes in the parties xml -
insertParties returns a recordset of the newly inserted records.
insertClaim then uses that table to join the claim to the parties. It
then calls insertTransaction and passes the transaction xml into that
sproc.
insertTransaciton then inserts the transactions in the xml, and also
calls insertParties, passing in the XML snippet
How do I know when to use a table variable, and when to use a temp table in my stored procedures? It seems that in most cases table variables are more efficient (in terms of execution time / CPU usage) but some of my stored procedures perform an order of magnitute better with temp tables instead. Short of testing the stored proc both ways, how do I know what to do? declare @Temp table or create table #Temp
I am running SQL Server Best Practices on a SQL 2000database and it is recommending me to change the temptables inside SPs to table variables.I had read already in other places to use table variablesover temp tables. I also know I can't create indexes asI can on temp tables. Instead I'll have to create eithera primary key and/or a unique index on a table variable.One question I have is let's say I will be putting thousandsof records in a temp table, should i still choose a tablevariable over a temp table for this? Or is there arecommended limit where if I have to store certainnumber of records then it's better to store them ina temp table rather than a table variable? Or numberof records is not the factor to decide whether to usetemp tables or table variables?I would like to know when it's ideal or best to usetemp tables instead of table variables and vice versa.Thank you
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000)) INSERT ##MyTempTbl values ('Put your long message here.') INSERT ##MyTempTbl values ('Put your second long message here.') INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!') DECLARE @cmd varchar(256) DECLARE @LargestEventSize int DECLARE @Width int, @Msg varchar(128) SELECT @LargestEventSize = Max(Len(MyWords)) FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' + CONVERT(varchar(5), @LargestEventSize) + ')) FROM ##MyTempTbl order by SeqNo' SET @Width = @LargestEventSize + 1 SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------' EXECUTE Master.dbo.xp_sendmail 'YoMama@WhoKnows.com', @query = @cmd, @no_header= 'TRUE', @width = @Width, @dbuse = 'MyDB', @subject='none of your darn business', @message= @Msg DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?
[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
I am trying to add a variable to a temporary table name. Throughout a stored procedure, I do a lot with this table. I delete, insert, update, and query this table. Is there a way to do the following without having to set the entire 'select * from ...' line as a variable? Below is what I am trying to accomplish. It all works until the select * line. Is there a way to accomplish what I am trying to do below?
Declare @table varchar(255), @PassedID integer set @passedID=5
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get 'Error Source: Microsoft OLE DB Provider for SQL Server Error Description: Invalid Object name "#tempT1"'
Is there a way to make a DTS package call a stored procedure that uses temp tables?
I want to check to see if a temporary table exists before I try creating one but I can't seem to find which sys table or schema collection I check. Any ideas?
-- Add Structural data usp_AddStructural @iEstimateID, 1, 'Structural' usp_AddForming @iEstimateID, 2, 'Forming' ... ... ... GO
Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".
I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.
Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.
I am in the process of modifying some stored procedures that currently do not use temp tables. For this modification I am required to make the stored procedures use temp tables. There are several UDF's within this stored procedure that will need to use the temp tables, and this is where in lies the problem. Does anyone know of a work around that would allow UDF's to use temp tables, or does anyone know of alternate methods instead of temp tables that wouldn't involve too much change?
I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting invalid object errors on the temporary tables: Invalid object name '#temp_table1'
The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.
In these two tables im just to bring the data back where the two DesignID's dont match. Im gettin an error
Server: Msg 107, Level 16, State 3, Line 1 The column prefix '#ttTopSellers' does not match with a table name or alias name used in the query.
Declare @CustomerID as VARCHAR(25) Set @CustomerID = 'DELCOZ01-10'
/*Figure the designs that stores carry*/ Select Design.Description, Item.DesignID, CustomerClassificationID, CustomerID, Region.[ID] as RegionID, Region.Name Into #ttDesign From Mas.dbo.Item Item Inner Join MAS.dbo.Style Style on Item.StyleID = Style.[ID] Inner Join MAS.dbo.Line Line on Style.LineID = Line.[ID] Inner Join MAS.dbo.Design Design on Item.DesignID = Design.[ID] Inner Join Mas.dbo.DesignRegionIndex DRI on Design.[ID] = DRI.DesignID Inner Join MAS.dbo.Region Region on DRI.RegionID = Region.[ID] Inner Join MAS.dbo.CustomerClassificationRegionIndex CRI on Region.[ID] = CRI.RegionID Inner Join MAS.dbo.CustomerClassification CC on CRI.CustomerClassificationID = CC.[ID]
Where @CustomerID = CustomerID Group By Design.Description, Item.DesignID, CustomerClassificationID, CustomerID, Region.[ID], Region.Name
/*This finds the top retail sales globally*/ Select Top 10 Sum(Sales) as Sales, DesignID, Design.[Description] Into #ttTopSellers From Reporting.dbo.RetailSales_ByStore_ByCustomer_ByDay_ByItem DI Inner Join Mas.dbo.Item Item on DI.ItemNumber = Item.ItemNumber Inner Join MAS.dbo.Style Style on Item.StyleID = Style.[ID] Inner Join MAS.dbo.Line Line on Style.LineID = Line.[ID] Inner Join MAS.dbo.Design Design on Item.DesignID = Design.[ID] Where [Date] >= Month(getdate())-12 and DesignID <> 0 Group By DesignID, Design.[Description] Order by Sum(Sales) Desc
Select * From #ttDesign Where #ttDesign.DesignID <> #ttTopSellers.DesignID
Why cant I use the same temptable name i a stored procedure after i have droped it?I use the Pubs database for the test case.CREATE PROCEDURE spFulltUttrekk ASSELECT *INTO #tempFROM JobsSELECT *FROM #tempDROP TABLE #tempSELECT *INTO #tempFROM EmployeeSELECT *FROM #temp
I have a stored procedure which contains a temp table called #NamesThis has n rows of values which are peoples names (they are varchars)i.e#NamesRickRobFrankI have a table called tblPeople.tblPeopleid Name Telephone1 Ric 012334213452 Robert 0321120931233 Paul 123 123 123 123 I want to find all the people in tblPeople whose names are like those in the temp table #NamesHow do I do this?
Please tell me there's something I haven't set. I've done several tests now. If your final return in a stored proc is from a temp table (ala #mytable ) the system cannot read the schema - for that matter, it won't run at all, complaining that the object #mytable doesn't exists. It can't possible be that temp tables aren't allowed in procs used by SQLDatasource - please tell me what I am doing wrong. This proc, when fed to a sqldatasource, fails in the designer with #temp does not exists. CREATE PROCEDURE dbo.repTest_TempASBEGIN CREATE TABLE #Temp( [iTestID] uniqueidentifier, [bTest] [bit], [cTest] [varchar] ) INSERT INTO #TempSELECT *FROM tTest SELECT *FROM #Temp END
I have been researching some performance problems in a very large application and I have a couple of questions about temp tables. (SQL 7.0 SP2)
I have one large procedure that I have been using as a test case. Originally this procedure was a cursor with lots of processing steps involving writing to, reading from and deleting in temp tables inside the cursor. I remember reading that temp tables inside a cursor were a potential performance problem, so I rewrote the procedure, replacing the cursor with a While Loop.
Doing this showed no increase in performance. Since Profiler was showing .5 second duration times on statements in the procedure accessing the temp tables I tested some more. I moved all the create statements to the top of the procedure, as I know these statements after processing steps can cause recompiles to happen. Still no performance increase.
Finally I replaced all the temp tables with actual tables, just to see what would happen. With no other changes the performance increased by more than 500%.
Can someone give me some clues as to what is happening here, because if this is a symptom of something I don't understand, the potential performance problems from other places where temp tables are similarly used in the application are enormous.
The problem is we need local tables in Access for SubReports (can't use store procedures as record source for this), therefore trying to create temp table and link to Access for each report instance.
When we create the ## type table in tempdb this cannot be linked from Access (cannot be seen via DSN). The only other option we can find is doing CREATE TABLE tempdb.dbo.[tablename] but this requires the user to have admin permissions and therefore be 'dbo' on tempdb and not 'guest'.
Is there any way to test if a table exists in the temp dB. In a procedure I create table called ##Test but I want to test if it exists before it's created and if it does I want to delete it. (It has to be a ## table because it's in a procedure being called in another procedure depending on an IF statement). The reason I want to do this is that I have multiple users and Occasionally they get the ... There is already an object named '##Test' in the database. error
I am creating an sp, in which I check for the existence of a global temp table (using the exists) statement. If the Exists returns a false, I move on to processing without the temp table. If it returns a true, I utilize the temp table to do some inserts. I create the temp table when my application first starts up. The problem that I am facing is that the check for the temp table`s existence seems to be failing. Is there any other way to check for the existence of a global temp table??
I am creating a dts package which copies data via transform data task from a sql sever connection to an excel spreadsheet connection. The transform data task uses a query that goes like this.
create table #temp1(CName varchar(10))
Insert #temp1 select CName from tbl1
--tbl1 is in a user DB called SALES
create table #temp2(VName varchar(10))
Insert #temp2 select CName from #temp1
select * from #temp2
--I need the resultset of the last select statement into an excel spreadsheet. The problem is I can't even create the dts package..it shows up with an error saying that #temp1 is an invalid object....I am guessing that since the default database for the transform data task has been selected as SALES it is trying to locate the the #temp1 or #temp2 table in that DB instead of looking in the TEMPDB....My question is..is there any workaround for this problem.
I have 4 temporary tables that hold criteria selected through a report wizard. I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.
Would somebody be kind enough to help me out please.
To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:
(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row. (2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table. (3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.
I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.
SELECT * FROM tblActualWork [ActualWork] JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter) AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter) AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)
Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.
Please help, not having much joy with this. Many thanks.
I have 2 temporary tables from a previous operation, Tab1 and Tab2, with the same dimensions. How do I create a third table Tab3 with the same dimensions containing the the combined rows of the 2 previous tables? TIA!
how to update a temp table with another temp table for example I have #tempdashboard that has info in it.I also have #tempappscount of which I want to insert the info inside it into #tempdashboard...I believe the column in #tempdashboad is DailyAppsInINT,
I did a query that gave me the number of daily apps created. I was told to create #tempappscount and my query to dump into #tempappsaccount. Then dump the info from #tempappsaccount into #tempdashboard.
I have two temporary tables, both contain field 'BranchID'.
First table @TEMPNET returns 10 different BranchIDs, but the second table @TEMPCREDIT returns only 7 of those IDs, so when I run select from both tables 'WHERE N.BranchID = C.BranchID2' the result is blank. If I limit both tables to the IDs that are the same in both sets, it works.
How can I get ALL the results from table1 AND the results from table2 which match table1?
DECLARE @TEMPNET TABLE ( BranchID int , NetSales decimal(18,4) , Cost decimal(18,4) , SaleMonthNbr int , SaleMonth varchar(15))
Hi how can I delete a local temp table? I know a golbal is deleted by if exists(select * from tempdb..sysobjects where name='##MyTemGlobalTable' and type='U')drop table ##MyTemGlobalTable'
--************************************************************* --SET SQL STMT BASED ON SQL TYPE PASSED IN --*************************************************************/
SELECT Distinct a.station_nbr, c.station_name, a.group_code, a.beg_eff_date As beg_eff_date_DGC, a.end_eff_date As End_eff_date_DGC from TIES_Gathering.dbo.station_group a Inner Join TIES_Gathering.dbo.station c on a.station_nbr = c.station_nbr Inner Join TIES_Gathering.dbo.station_type d on a.station_nbr = d.station_nbr Inner Join TIES_Gathering.dbo.group_detail e on a.group_code = e.group_code Where ((e.group_type = 'NPN')or(e.group_type = 'WTF')or(e.group_type = 'NTF'))and ((d.type_code = 'WHM')or (d.type_code = 'CRP')) and (a.pipeline_code = '100000') Order by a.station_nbr, a.group_code
INSERT INTO #TP_DGC(station_nbr,station_name,group_code,beg_eff_date_DGC,end_eff_date_DGC) values (@stationnumber, @stationame,@groupcode,@begdateG,@endeffdateG)
Select Distinct a.station_nbr,b.station_name,a.group_code, a.beg_eff_date As beg_eff_date_SES, a.end_eff_date As End_eff_date_SES From TIES_Marketing.dbo.station_group a Inner Join TIES_Marketing.dbo.station b on a.station_nbr = b.station_nbr Inner Join TIES_Marketing.dbo.station_type c on a.station_nbr = c.station_nbr Inner Join TIES_Marketing.dbo.group_detail e on a.group_code = e.group_code Where ((c.type_code = 'WHM')or (c.type_code = 'CRP')) and (a.pipeline_code = '100001') Order by a.station_nbr, a.group_code
INSERT INTO #TP_SES(station_nbr,station_name,group_code,beg_eff_date_SES,end_eff_date_SES) values (@stationnumber, @stationame,@groupcode,@begdateM,@endeffdateM)
---------------compare both tables-------------------- Begin
Select #TP_SES.station_nbr, #TP_SES.station_name, #TP_SES.station_group, #TP_DGC.beg_eff_date As beg_eff_date_DGC, #TP_DGC.end_eff_date As End_eff_date_DGC, #TP_SES.beg_eff_date As beg_eff_date_SES,#TP_SES.end_eff_date As End_eff_date_SES from #TP_SES left outer join #TP_DGC on station_nbr=station_nb end
I need help on two questions:1. Is temp table the only way to pass recordsets from a nested storedprocedure to a calling stored procedure? Can we avoid temp tables inthis case?2. Are operations in a stored procedure are treated as a transaction?Any help will be greatly appreciated.Background: We need to use temp table to pass recordsets from a nestedstored procedure to a calling stored procedure. Our understanding isthat in this case, we have no choice but to use temp tables. So, weneed to optimize the performance as much as possible. To do this, wewanted to find out whether operations in a stored procedure are treatedas a transaction. We are using SQL 2000 SP4. I could not find anyanswers so I did the following experiment.Experiment 1:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASUpdateArticlesSETIsUpdate = 20whereArticlesId < 80000SELECT * from ArticlesGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO"SELECT * from Articles" takes a long time (about 40 seconds) tocompleteBefore executing the SP, the IsUpdate attribute for all articles is 30.Then I executed this SP. Before the SP is finished, I end the SPmanually. I checked the IsUpdate attribute again, and found that allArticles's (ArticlesId < 80000) Isupdate attribute is now 20. Theoperations did not rollback. I interpret this to mean that the whole SPis not treated as a transaction.Then, I did experiment 2 below. This time, I explicitly declared thetransaction.SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASBEGIN TRANSACTIONUpdateArticlesSETIsUpdate = 50whereArticlesId < 80000SELECT * from ArticlesIF @@ERROR <0 ROLLBACK TRANSACTIONCOMMIT TRANSACTIONGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOBefore this second SP, the IsUpdate attribute is 20 (set in the firstexperiment). I run this second SP and ended it manually before itfinished. I checked the IsUpdate attributes for all Articles's(ArticlesId < 80000), but their Isupdate attribute is 50. So theoperation did not rollback either. But we have declared the transactionexplicitly. Does this mean that the SP is still not treated as atransaction?
I'm relatively new to SQL-Server, and I have a quick question abouttemporary tables. I am using a function from Erland Sommarskog'sexcellent article about arrays in SQL server.Erland's approach uses a user-defined function, which parses a stringof delimited values (integers) into a temporary table. The temporarytable is joined to tables in a stored procedure select statement tolimit the records returned.The question is, what happens to the temporary table generated by thefunction? Is it implicitly destroyed when the stored procedurefinishes? Does it need to be dropped explicitly (something not done inhis examples)?Also, what are the implications of scope, if multiple users areaccessing the stored procedure through a web application?Thanks in advance,Tim
I want to create several global temp tables. I've created a script:
if not object_id('tempdb..##tbl_ProductTypes') is null begin drop table ##tbl_ProductTypes end select * into ##tbl_ProductTypes from dbo.tbl_ProductTypes
This script creates a global temp table. When I run it in QA the table is created. When I close QA the table is dropped. This is correct since I found the following in BoL: [Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.]
I created a job that executes the statement above. The job exists with success, however the table is not created!
