Archive
SQL Tips – Check count of records in Temporary Tables from other sessions
Recently one Stored Procedure was giving issues on our Production system. The SP had some temporary (#) tables that were getting populated in sequence, but the final result was not as expected. As Temp-Tables cannot be accessed across other sessions you cannot see what’s going on. Also as its a PROD system I could not dissect or change the SP and see what’s happening inside.
Debugging a SP on a DEV machine is simple. I can add some extra checks after temp-table population code to get the record count by using “SELECT @@rowcount”, and was able to see the issues.
But this cannot be done on PROD, as you cannot alter the SPs there. So, to see which temp table is being populated you can use below query. This will also show the records count if any temp table is in mid of population.
SELECT T.NAME AS TABLE_NAME, S.ROW_COUNT FROM TEMPDB.sys.dm_db_partition_stats AS S INNER JOIN TEMPDB.sys.tables AS T ON S.OBJECT_ID = T.OBJECT_ID WHERE S.INDEX_ID < 2 and T.NAME like '%#TempTable%'; -- Give #Table name here
Pass an array of multiple values with a single Parameter in a Stored Procedure – SQL Server
This post comes from an old discussion on MSDN T-SQL Forum[link], where someone asked about the same topic.
Check my reply there and here with this post I’m going to collate all the different methods I’ve blog previously that can be used for the same purpose.
Stored Procedures accept only a fixed and pre-defined number of parameters thus there is a limitation you cannot provide dynamic number of params, like you can do with other languages, like C, C#, Java, etc. In these languages there is concept of Arrays which you can pass in a method/function, but in SQL there are no array variables and it does not have any datatype that support arrays. Thus if you have to provide multiple values to any parameter you cannot do it directly, though there are some workarounds.
–> Here are some of the methods or workarounds by which we can pass multiple values as a single Parameter in a Stored Procedure or a Function:
Method #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF, check here.
Method #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.
Method #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP, check here.
Method #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters, check here.
Method #5 – Passing a JSON string: as a NVARCHAR datatype parameter. We will need to parse the JSON inside the SP, check here.
Thanks a lot, please provide your valuable comments and suggestions on this topic.
>> Check & Subscribe my [YouTube videos] on SQL Server.
Difference between Temporary Table and Table Variable, which one is better in performance? – MSDN TSQL forum
–> Question:
Anyone could you explain What is difference between Temp Table (#, ##) and Table Variable (DECLARE @V TABLE (EMP_ID INT)) ?
Which one is recommended to use for better performance?
Also is it possible to create CLUSTER and NONCLUSTER Index on Table Variables?
In my case: 1-2 days transnational data are more than 3-4 Millions. I tried using both # and table variable and found table variable is faster.
Is that Table variable using Memory or Disk space?
–> My Answer:
Check this link to see differences b/w Temp Table & Table Variable.
TempTables & TableVariables both use memory & tempDB in similar manner, check this blog post.
Performance wise if you are dealing with millions of records then Temp Table is ideal, as you can create explicit indexes on top of them. But if there are less records then Table Variables are good suited.
On Tables Variable explicit index are not allowed, if you define a PK column, then a Clustered Index will be created automatically.
Ref Link.
SQL Server 2012 | Temp Tables are created with negative Object IDs
These days I’m working on SQL Server upgrade from 2008 R2 to 2012 for one of our project module.
Today while working on it I got blocked while installing a Build. The build was failing with following error:
Error SQL72014: .Net SqlClient Data Provider: Msg 2714, Level 16, State 6, Line 115 There is already an object named ‘#temp’ in the database.
I checked the code and found the line where it was failing:
IF object_id('tempdb.dbo.#temp') > 0
DROP TABLE #temp
I checked this code with SQL Server 2008 R2 and it was working perfectly.
So to check and validate this I created a temp-table on SQL Server 2012 and found that it is created by negative Object ID, check this:
This is a new change done with SQL 2012 version, but this is not mentioned anywhere in MSDN BOL.
So, to make this legacy code work we have to re-factor all such cases, by:
IF object_id('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
Confirmation form Microsoft SQL team blog [CSS SQL Server Engineers]:
“in SQL Server 2012, we made a conscious change to the algorithm so that objectids for user-defined temporary tables would be a particular range of values. Most of the time we use hex arithmetic to define these ranges and for this new algorithm these hex values spill into a specific set of negative numbers for object_id, which is a signed integer or LONG type. So in SQL Server 2012, you will now always see object_id values < 0 for user-defined temp tables when looking at a catalog view like sys.objects.”
More Info on: http://blogs.msdn.com/b/psssql/archive/2012/09/09/revisiting-inside-tempdb.aspx
Table variables are not stored in memory, but in tempdb
Table-Variables just like Temp-Tables also gets stored in tempdb.
This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory.
–> To prove this I executed following code:
-- Batch #1 SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #2 CREATE TABLE #tempTab (j INT) INSERT INTO #tempTab VALUES (1) SELECT * FROM #tempTab SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #3 DECLARE @tabVar TABLE (i INT) INSERT INTO @tabVar VALUES (1) SELECT * FROM @tabVar SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO DROP TABLE #tempTab GO
Batch #1: Excuting first batch gives me 0 rows, means no tables exists in tempdb system database.
Batch #2: The second batch of stmt gives me 1 row with following table name:
#tempTab_____________________________________________________________000000019D39
Batch #3: and the third batch of stmt gives me 2 rows with following table name:
#tempTab_____________________________________________________________000000019D39 #0C4F413A
This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.
–> Check the full demo here:
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 