SSMS Freeze Workaround

I'm not sure if you've ever had this problem, but while quickly cutting/pasting in a SQL script using the shortcut keys (ctrl+c, ctrl+v), in rare occasions SSMS will freeze. It doesn't crash, there's no error, it doesn't explode, it just freezes - which in and of itself is no big deal, but losing the code you've written since the last save is. What makes this worse is that there's nothing autosaved (that I've ever seen), in the SQL temp folders. The only thing I could do is shut SSMS down via Task Manager (or unplug my PC out of spite). But after a little research on the interwebs (yes, this problem just happened to me again!), I discovered a way to break the freeze. That link suggests directly right-clicking the SSMS window in the Task Bar, but I found that I had to hover above it, then right-click the instance that pops up, like this:



One time this didn't work. Playing around with it, I found that when the single instance of SSMS running popped up, I right-clicked it, then selected "Restore" from the popup menu (which only appears if there is no modal popup already in SSMS), and then re-maximized it. Then the blinking cursor reappeared, and all was well.

 

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).

Temp Table Structure

Related to the post on getting the name of a temp table, here's the code to get the structure:

SELECT * FROM TempDb.INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#x'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))

I'm using this to create a stored proc to examine the contents of a temp table column-by-column, and record values that violate assumptions (e.g., is null), in an audit table. I'm adding this audit to several reporting procs, so creating this audit proc will save me a lot of work.

To get the same results, but in a format you can use to build a SELECT:

DECLARE @sql varchar(max) = 'SELECT' + CHAR(13) + CHAR(10)

SELECT @sql += ',' + QUOTENAME([COLUMN_NAME]) + CHAR(13) + CHAR(10)

FROM TempDb.INFORMATION_SCHEMA.COLUMNS

WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#x'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))

PRINT @sql

Create SQL Column with Variable Default

I wanted to create an integer column in a metadata table that would prioritize the rows of the table (this is for column [execution_order]), and for ease-of-use I wanted inserts into the table to default to a unique value. Although this sounds a lot like an identity column, I wanted it to be nullable, and not necessarily unique.

This is what I came up with:

CREATE TABLE [dbo].[metadata] (
[metadata_id] int NOT NULL identity(1,1)
,[project_id] int NOT NULL
,[descr] varchar(100) NOT NULL
,[delete_stmt] varchar(max) NOT NULL
,[execution_order] int NULL
,[insert_date] datetime NOT NULL
,[insert_userid] varchar(50) NOT NULL
,[is_active] bit NOT NULL
,CONSTRAINT pk_dbo_metadata PRIMARY KEY ([metadata_id])
)
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__insert_date
DEFAULT(GETDATE()) FOR [insert_date]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__insert_userid
DEFAULT(SUSER_NAME()) FOR [insert_userid]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__is_active
DEFAULT(1) FOR [is_active]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__execution_order
DEFAULT((IDENT_CURRENT('[db].[dbo].[metadata]')) * 100) FOR [execution_order]
GO

Parsing Stored Procedure Result Set

One thing I've always wanted to be able to do is to parse the result set(s) of a stored procedure, so that I can easily create a structure to contain it's output. This thread on sqlteam.com details a way to capture the output without knowing the structure ahead of time. This is done by using a linked server that loops back to itself, and then runs this statement:

select * into #t from openquery(loopback, 'exec yourSproc')

to select the results into a temp table, the metadata of which can then be parsed as needed. This leads to the 'pie in the sky' idea of a stored proc that, given the name of another stored proc, will output a CREATE TABLE script that mirrors the result set of the second stored proc.

Removing Trailing Zeroes

I recently encountered the classic problem of removing trailing zeroes from formatted numeric output, and after mocking up some complicated code and doing some research, I came across a thread that says to convert it to float before varchar. It's a perfect solution that is simple and works correctly.

View EPs Neatly

Here's some code to view Extended Properties of a table in a neat denormalized report:

-- replace 'TABLENAME' with the name of your table:
DECLARE @ObjId int; SET @ObjId = OBJECT_ID('TABLENAME')

DECLARE @sql varchar(max)
SET @sql =
'SELECT
TableName = T.Name
,ColumnName = C.Name
'

; WITH TableNameBase AS (
SELECT *
FROM SYS.TABLES
WHERE OBJECT_ID = @ObjId
), PropNames0 AS (
SELECT DISTINCT Name
FROM SYS.EXTENDED_PROPERTIES
WHERE Major_Id = (SELECT [OBJECT_ID] FROM TableNameBase)
AND Minor_Id > 0
), PropNames AS (
SELECT Name, NameOrder = ROW_NUMBER() OVER (ORDER BY Name)
FROM PropNames0
)
SELECT * INTO #PropNames FROM PropNames

SELECT @sql = @sql +
' ,[' + P.Name + '] = ISNULL(P' + LTRIM(STR(P.NameOrder)) + '.Value, '''')
'
FROM #PropNames P

SET @sql = @sql +
'FROM SYS.TABLES T
JOIN SYS.COLUMNS C
ON C.OBJECT_ID = T.OBJECT_ID
'

SELECT @sql = @sql +
'LEFT JOIN SYS.EXTENDED_PROPERTIES P' + LTRIM(STR(P.NameOrder)) + '
ON P' + LTRIM(STR(P.NameOrder)) + '.Major_Id = T.OBJECT_ID
AND P' + LTRIM(STR(P.NameOrder)) + '.Minor_Id = C.Column_Id
AND P' + LTRIM(STR(P.NameOrder)) + '.Name = ''' + P.Name + '''
'
FROM #PropNames P

SET @sql = @sql +
'WHERE T.OBJECT_ID = ' + LTRIM(STR(@ObjId))

PRINT @sql
EXEC(@sql)

DROP TABLE #PropNames

Query to List Tables and their Primary Keys

This query produces a resultset of two columns: every table in the current database, and the corresponding primary key expression if one exists:

; WITH Base AS (
SELECT
TABLE_NAME = QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME)
,C.Column_Name
,C.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON PK.TABLE_NAME = T.TABLE_NAME
AND PK.TABLE_SCHEMA = T.TABLE_SCHEMA
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
ON C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND C.TABLE_NAME = PK.TABLE_NAME
AND C.TABLE_SCHEMA = C.TABLE_SCHEMA
), OrderIt AS (
SELECT
TABLE_NAME
,Column_Name = CONVERT(varchar(max), Column_Name)
,ORDINAL_POSITION
,RecurseVar = ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY ORDINAL_POSITION)
FROM Base
), Recursed AS (
SELECT
Table_Name
,Column_Name
,RecurseVar
FROM OrderIt
WHERE RecurseVar = 1
UNION ALL
SELECT
B.Table_Name
,Column_Name = RTRIM(R.Column_Name) + ', ' + B.Column_Name
,B.RecurseVar
FROM Recursed R
JOIN OrderIt B
ON B.Table_Name = R.Table_Name
AND B.RecurseVar = R.RecurseVar + 1
), GetMax AS (
SELECT
Table_Name
,MAX_RecurseVar = MAX(RecurseVar)
FROM Recursed
GROUP BY Table_Name
), Results AS (
SELECT
R.Table_Name
,Primary_Key = R.Column_Name
FROM Recursed R
JOIN GetMax G
ON G.Table_Name = R.Table_Name
AND G.MAX_RecurseVar = R.RecurseVar
)
SELECT * FROM Results
ORDER BY Table_Name

XML SQL - Probing Depth of Document

Now let's say that you want to know how many levels deep this relationship goes. Without knowing the answer ahead of time, we have to use a recursive query to determine this:

; WITH XML_Doc AS (
SELECT
id
,parentid
,[level] = 1
,nodetype
,localname
,prev
,text
FROM #tmp2
WHERE LocalName = 'RootNode'
UNION ALL
SELECT
T.id
,T.parentid
,[level] = R.[level] + 1
,T.nodetype
,T.localname
,T.prev
,T.text
FROM XML_Doc R
JOIN #tmp2 T
ON R.Id = T.ParentId
)
SELECT *
INTO #XML_Doc
FROM XML_Doc

XML Parsing with Recursive Table Structure

After you load the XML data into the temp table using the code from yesterday's post, you end up with a hierarchical structure representing the XML data with simple id/parentid columns. So a node at the top level with an id=1 would have nodes at the next level with parentid=1, where id is unique and parentid always references id. There is also a column called 'nodetype' that seems to determine what this node represents. Now just on inspection, I deduced that nodetype=1 are root nodes, 3's are leaf nodes, and 2's are a combination (they have attribute info and children). This code will condense type 3's into their parent:

SELECT
X1.Id
,X1.ParentId
,X1.NodeType
,X1.LocalName
,X1.prev
,text = COALESCE(X1.text, X3.text)
INTO #tmp2
FROM #tmp X1
LEFT JOIN #tmp X3
ON X3.ParentId = X1.Id
AND X3.NodeType = 3
WHERE X1.NodeType <> 3
ORDER BY Id

XML into SQL

I'm working on loading an XML file into SQL right now, and struggling a bit with it. Not so much the coding itself, but figuring out how to represent the hierarchical XML data in a relational SQL table.

I did discover this tidbit for taking advantage of SQL's built-in tools for handling XML:

DECLARE @hdoc int DECLARE @doc varchar(max) SELECT @doc = CONVERT(varchar(max), XML_Column) FROM dbo.XML_Table EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc PRINT @hdoc SELECT * INTO #tmp FROM OPENXML (@hdoc, '/RootNodeName',2) EXEC sp_xml_removedocument @hdoc

Works well.

Getting the Real Name of a Temp Table

If you've ever wanted to retrieve the schema for a temp table, you probably noticed that in the TempDb database, the name of your temp table is not quite the same (unless you're using a global temp table - this entry does not apply to those). Say that you create a temp table named "#tmp" in your development database. If you go into TempDb, for example in the Sys.Tables view, you will find your table name, but padded on the right with underscores and a string of digits, to a length of 128 characters. So if you try to look up the schema in TempDb.Information_Schema.Columns using "#tmp", it will fail. To alleviate this problem, I wrote a simple SELECT that returns the true name of that temp table as it is stored in TempDb:

SELECT TempTableName = OBJECT_NAME(OBJECT_ID('TempDb..#tmp'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))