Temp Table Collision Between Sprocs

I recently debugged a report in which one sproc called another (let's call them OuterSproc and InnerSproc), and the resulting error was a complete mystery. When I executed InnerSproc on it's own, with the same param values provided by OuterSproc, the results were fine. After scratching my head for awhile, I decided to just keep debugging by executing lines from OuterSproc, and that's when I noticed that the two sprocs shared some temp table names. We tend to think of temp tables (as in "#temp"), as being local to the scope that created them, but that's not the whole story. Temp tables are scoped in with any code that is called from that scope also. So when OuterSproc creates data in #temp, then calls InnerSproc, InnerSproc can see and modify #temp. The problem is that InnerSproc creates its own #temp table, not thinking that it already exists. As an example of how this can be a problem, take a look at this code sample:

USE TRPTools
GO
DROP PROCEDURE InnerSproc
GO
CREATE PROCEDURE InnerSproc
AS
BEGIN
SELECT * FROM #temp
CREATE TABLE #temp (Sproc_id int, sproc_name varchar(20), when_done datetime)
INSERT #temp (Sproc_id, sproc_name) VALUES (OBJECT_ID('tempdb..#temp'), 'InnerSproc')
SELECT OBJECT_ID('tempdb..#temp')
SELECT * FROM #temp
END
GO
 
 
DROP PROCEDURE OuterSproc
GO
CREATE PROCEDURE OuterSproc
AS
BEGIN
CREATE TABLE #temp (Sproc_id int, sproc_name varchar(20), who_did varchar(20))
INSERT #temp (Sproc_id, sproc_name) VALUES (OBJECT_ID('tempdb..#temp'), 'OuterSproc')
SELECT OBJECT_ID('tempdb..#temp')
EXEC InnerSproc
SELECT * FROM #temp
END
GO
EXEC OuterSproc



Notice in sproc InnerSproc that before it creates #temp, it selects from it. This is perfectly acceptable code, because of the downstream scope of temp tables. What is weird is that the same line of code, SELECT * FROM #temp, at the beginning and end of the sproc, produces different results! The first time, it pulls from the OuterSproc version of #temp, and the second, from the InnerSproc version. It's as if the CREATE TABLE #temp statement in InnerSproc creates a version of #temp that overrides the original definition, but only within the scope of InnerSproc. Preliminary research finds no explanation of this behavior.

Takeaways:

• Use temp table names that are non-generic, that are specific to your project/code/data
• Consider using table variables instead, as these are scoped only to the code that creates them.

Case-Sensitive Comparisons

One of the first things we "learn" about SQL is that it is case-insensitive, so that "Smith", "SMITH", and "smith" are equal in comparison. In fact, it is not SQL Server that is case-insensitive, or even the database, but the collation of the database. Collation is the way the database sorts string values. An example of why this is important can be found in the ordering of alphabets of foreign languages. Another example is case-sensitivity. Most SQL Server databases are set up as case-insensitive, which allows searches for string values to ignore case.
If we right-click on a typical database in the Object Explorer of SSMS, and select the last menu option, "Properties", a window named "Database Properties" pops up. The last property listed on the General page is Collation, and we can see that it is set to SQL_Latin1_General_CP1_CI_AS. According to this SO Post, this collation has the following properties:

• Latin1 makes the server treat strings using charset Latin 1, basically ASCII
• CI case insensitive comparisons so 'ABC' would equal 'abc'
• AS accent sensitive, so 'ü' does not equal 'u'

Let's run a query to look for all of the "Smiths" in an imaginary sample table
SELECT * FROM contacts
WHERE [last_name] = 'smith'
Let's say that the query retrieves 10 rows, and that 5 of the rows have names that appear completely in upper-case, and none are completely lower-cased. If we wanted to clean up this data so that these 5 names appear in mixed-case (e.g., "Smith" rather than "SMITH"), how would we rewrite the query to isolate these rows?
If we run this
SELECT * FROM contact
WHERE [last_name] = 'smith'
AND [last_name] = UPPER([last_name])
We still get 10 rows. The additional restriction had no effect, in the same way that it makes no difference in the first query whether we search for Smith, smith, or SMITH. The way we get around this is by forcing the collation in our query
SELECT * FROM contacts
WHERE [last_name] = 'smith'
AND [last_name] = UPPER([last_name]) COLLATE SQL_Latin1_General_CP1_CS_AS
Notice the "CS" in the collation string. It specifies case-sensitive comparisons. Running this query successfully returns the 5 rows in which we are interested. This override of the database collation applies only to the comparison preceding it (otherwise no rows would be returned, because there are no rows where [last_name] = 'smith' with respect to case-sensitivity).
References
Collation - Wikipedia, the free encyclopedia
COLLATE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx
sql - what does COLLATE SQL_Latin1_General_CP1_CI_AS do - Stack Overflow

Random Numbers

While working on a project, I needed to create test data, and came across another of Pindal Dave's excellent posts: "SQL SERVER – Random Number Generator Script – SQL Query".

SQL Permissions

Good article on SQL permissions: "Understanding GRANT, DENY, and REVOKE in SQL Server".

Clever workaround for passing an array to a stored procedure or function: "Using a Variable for an IN Predicate".

Data Profiling Article

There's a good article on Data Profiling in SQL Server, complete with a script to profile tables, in SSC. It reminded me of a talk I gave at BSSUG on a data profiling script I wrote.

Interview Questions for SQL Developers

Read a good article on SSC today: "15 Quick Short Interview Questions Useful When Hiring SQL Developers". Some of the questions one might cover only in specific situations, but overall they were broad enough to cover what a Db Developer must know.

BSSUG July 2010 Presentation

On July 19th I gave a presentation to the Baltimore SQL Server Users Group titled "TSQL code to determine possible unique keys of raw data." The content can be found here. Here is the abstract:

I will be describing an algorithm I wrote that employs a hybrid recursive/iterative approach to determining the minimal unique key (if one exists), for a given table of raw data. The presentation will cover the problems involved in developing a solution and various approaches I considered, and of course a review of the solution at which I ultimately arrived.

Tip to open SSMS

If you have a bunch of related .sql script files that you want to open simultaneously (for example, a monthly process), create a MS-DOS batch file and add this command-line:

start ssms “test1.sql” “test2.sql”

That line will open SQL script files test1.sql and test2.sql in the same SSMS window.

This makes grouping scripts together easy, too (especially ones from different folders or projects).