Support for Microsoft SQL Server 2000 ends this year and yet, one can find systems (production or otherwise) that continue to run on and support SQL Server 2000. Recently, we were tasked with porting a piece of functionality backwards, i.e. from versions that support SQL Server 2012/2008 to SQL 2000/2005. Unfortunately, this piece of functionality was designed to work with newer versions of Microsoft SQL Server and involved the use of table valued parameters which is not possible in SQL Server 2000.
After a couple of hours of brain-storming, we realized that we could share temporary tables with the underlying stored procedures as long as the connection remained the same. Because the connection that created a temporary table is still active, the temporary table will continue to be accessible if the stored procedure is executed on the same connection. Here’s a small demo to help explain the solution.
For this demonstration, we will create a simple mathematical temporary table which will be updated from within a stored procedure. Assuming that the temporary table is named #tTableValues and has columns iA, iB and iC ( = iA * iB), let us first create a stored procedure which is designed to update the value of iA:
USE tempdb ; GO IF OBJECT_ID('UpdateTableValues') IS NOT NULL DROP PROCEDURE dbo.UpdateTableValues GO CREATE PROCEDURE dbo.UpdateTableValues @replacementValue INT AS BEGIN SET NOCOUNT ON ; IF OBJECT_ID('#tTableValues') IS NOT NULL BEGIN UPDATE tv SET tv.iA = @replacementValue FROM #tTableValues AS tv END ELSE BEGIN PRINT 'Temporary table: #tTableValues does not exist or access denied.' END END GO
As you can see from the T-SQL script above, the stored procedure assumes that the temporary table is already available to it and directly makes the update. All the calling application now has to do is:
- Open a SQL Server connection, say C1
- Create the temporary table
- Insert data into the temporary table
- Call the stored procedure for the desired computation
- Fetch the value/result once the stored procedure execution is complete
- Drop the temporary table
- Close connection C1
The T-SQL script below demonstrates this workflow:
--1. Connection has been created USE tempdb ; GO --2. Check for and create the required temporary table IF OBJECT_ID('#tTableValues') IS NOT NULL DROP TABLE #tTableValues GO CREATE TABLE #tTableValues ( Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, iA INT, iB INT, iC AS iA * iB ) ; GO --3. Insert some test data INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 1 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 2 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 3 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 4 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 5 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 6 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 7 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 8 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 9 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 10 ) ; GO --Debug point 01: Select from the temporary table SELECT 'Debug Point 01' AS StepName, Id, iA, iB, iC FROM #tTableValues ; GO --4. Execute the stored procedure --NOTE that the connection has to be the same! EXEC dbo.UpdateTableValues @replacementValue = 2 ; GO --5. Fetch the result of the stored procedure execution SELECT 'Step 05 - Results' AS StepName, Id, iA, iB, iC FROM #tTableValues ; --6. Cleanup - the connection can now be closed IF OBJECT_ID('#tTableValues') IS NOT NULL DROP TABLE #tTableValues GO
The screen-shot shown below shows the output of the execution and it can be seen that the value for iA in the temporary table created by the calling application was indeed updated by the stored procedure and the results were available after the stored procedure execution completed.
As demonstrated, temporary tables may be used for data exchange between an application and stored procedures or even between any two programmability objects (procedures, functions, etc). This functionality continues to remain valid even in the days of SQL Server 2012. The question, however is whether it would be preferable to use newer features like table valued parameters or not. Do let me know your thoughts on the same.
As for those who need to continue support of Microsoft SQL Server 2000 for their products, I trust this post was helpful.
- Temporary Table Naming – Maximum Allowed Length and associated naming logic
- Microsoft SQL Server 2000 Support Life Cycle
Until we meet next time,
Nicely done, Nakul. I still use this method instead of Table Valued Parameters. It just seems easier.
@Jeff: Thank-you for your time and feedback!
Having worked on SQL Server 2000 in the initial stages of my career, it took me quite a while to get used to table valued parameters. Perhaps the only immediate benefit visible to application developers is that they don’t need to take care of explicitly dropping the temporary table. This point almost always makes the developers jump for table valued parameters. In my case, it was the application developers who made me change from temporary table to table valued parameters for generic patterns (two int columns, one int one date column and so on).
Since closing the conection in Step 7 effectively ends the session, you don’t have to drop the Temp Table. It will drop all by itself. As a matter of fact, if the script is used a whole lot, there’s a benefit to NOT explicitly dropping the Temp Table. Microsoft made it so that, like an execution plan, the system will remember the empty table structure and reuse it for a bit more performance.
The only thing else that I might add is that I’d likely use SELECT UNION ALL to drive the INSERT instead of multiple INSERT/VALUE statements. Each INSERT/VALUE statement will either create a separate execution plan or the system has to decide if there’s an execution plan that can be reused for each and every INSERT/VALUE statement. That, of course, takes relatively much more time than a single INSERT.
Since it’s also likely that the data in the Temp Table was carefully selected and that all of the data will be used, you might also consider using SELECT/INTO to create the Temp Table which will be much faster than inserting into a pre-existing Temp Table. Agreed that SELECT/INTO won’t create a PK as you’ve done in your fine demonstration but, because the data was carefully selected as previously stated, there’s a very, very high probability that the optimizer will choose to do table scans instead of using the index, anyway. In fact, in some cases (you always have to test), the unindexed Temp Table may actually outperform the indexed version in joins or whatever the stored procedure is doing because the optimizer will automatically know that it’s going to have to do a scan. I proved that in one of the million row tests I did for my articles on Hierarchy conversions.
But, again, nice blog entry. I use many of the “old ways” because they faster and easier to use than some of the “new” ways. It’s good to see someone post about the old ways because people forget about them or think they’re “not cool”. Thank YOU for taking the time to post such things.
LikeLiked by 1 person
Pingback: #0364 – SQL Server – Using Primary Key, Check and other Constraints with User Defined Table Types (UDTT) | SQLTwins by Nakul Vachhrajani