How to: exec sp_help on temp tables?

Say you have a temp table and you want to see the columns names.

For example, I was trying to convert a query from using a #temp table to a CTE instead, and wanted to see the column list and resulting data types of the #temp table. 

Sp_help is a helpful SQL Server system sproc to return schema of objects. It's that magic that happens when you press Alt+F1 in SSMS. (Side note: showing someone the Alt+F1 shortcut in SSMS for the first time and seeing their life change for the better is really rewarding.) 

But Alt+F1 doesn't work on #temp tables because it tries to execute this:

exec sp_help #temp;
  

Which fails with the error:

Msg 15009, Level 16, State 1, Procedure sp_help, Line 79 [Batch Start Line 21]
The object '#temp' does not exist in database 'myuserdb' or is invalid for this operation.

That's because as far as sys.objects is concerned, temp tables don't exist in the user database you're working in, but always in the TempDB.

So you change your database context to TempDB:

use tempdb;
exec sp_help #temp;

 
And it works! You get back the expected sp_help output. 

But what if you don't want to change your database context, or you are working in Azure SQL Database, where you youths can't use USE? Use this, instead:

exec tempdb.sys.sp_help #temp;

You can also shorten this to:

    exec tempdb..sp_help #temp;

Why? The ".." syntax or "dot dot" syntax shortens the schema by assuming the default schema. The default schema is users is dbo. This still works for sp_help and other system objects are in the sys schema but are addressable through the dbo schema. 

Helpfully, both of these work:

    exec tempdb.sys.sp_help #temp;
    exec tempdb.dbo.sp_help #temp;

As for Alt+F1, resign yourself to not using it for temp tables. You could map another shortcut key in SSMS or Azure Data Studio to exec tempdb..sp_help instead of exec sp_help if you wanted to.

Untrusted FK's demo + ADS notebooks

Back in 2012 at SQLSaturday Pensacola, I did a lunch-time lightning talk on indexing, which was supposed to have a big finale on how FK's could silently help with performance, and it didn't work. Didn't show the performance boost. It was because, thanks to poor scripting by SSMS, FK's are enabled but not trusted. This blog post serves as a long-belated knowledge share of a problem I didn't figure out live, as well as a successful recreation of that big finale.

Fast forward to a couple weeks ago at the May 2019 Baton Rouge SQL Server User Group, and local fellow SQL pro John Wells gave a presentation to a mixed audience of dev's and DBA's on performance tips, and one of them was to check up on untrusted FK's. Thought occurred to me to create some lab's to demonstrate not only how to untrusted FK's get accidentally created by well-intentioned dev's and DBA's, but how to fix, and also a demonstration on how they can be silent performance drags. So with all respect to John, Chris Bell who John mentioned in his presentation, and many others who have spoken and written well on the topic already, below is a lab demonstrating the issue, resolution, and performance impact.

To further one more goal, I've built the lab into a tsql script but also a new Azure Data Studio SQL notebook. Either offer a step-by-step introduction and demonstration. New to SQL Notebooks? No worries, they were released generally in March 2019 as a new feature of Azure Data Studio (which can be launched from SSMS 18+), which itself was made GA in September 2018.

A SQL Notebook is another way to combine text instructions with step-by-step, modular executed SQL statements in a single file. Notebooks are a popular form of mixed media, combining text, proofs, queries, and their live interactive output. They are already quite popular tools for data scientists to package methodology, analysis and live output. SQL Notebooks include kernels for querying SQL, PySpark, Spark, Python with multiple languages, and now in preview for SQL 2019 CTP 3.0, working with big data clusters. SQL Notebooks could also be used by DBA's and developers as rich, well-documented runbooks for complex exercises deployment or disaster recovery exercises.

SQL Notebooks are easy to learn and use, consider a SQL Notebook for your next detailed query breakdown or summary data analysis. Maybe you'll learn two things at once!

Github links to the SparkhoundSQL toolbox for the Untrusted FK's lab: .sql .ipynb

PS Check it out, Github even parses the notebook JSON correctly to display a (non-interactive) view of the notebook outside of ADS with the saved resultsets. Neat!

UPDATE: Refined my terminology a little bit to favor "SQL Notebooks" not "ADS Notebooks"

Restoring Toggle Results Window as Ctrl+R in SSMS

Recent versions of SQL Server Management Studio have unmapped Control+R as the toggle for the Results window in a typical query window. Each time I work on a new SSMS environment or refresh my laptop, I find myself seeking out how to restore this setting. This blog post is as much a reference for my future self as anything.

Instead of the results pane, you may instead see at the bottom left corner of SSMS the following messages:
"(Ctrl+R) was pressed. Waiting for second key of chord..." or then "The key combination (Ctrl+R, Ctrl+R) is not a command."

Here's how to remap the Results pane to Ctrl+R, including a picture below.

1. In SSMS, go to Tools, Options.
2. Under Environment, click on Keyboard.
3. In the Show Commands containing box, you're looking for "Window.ShowResultsPane".
4. Change the "Use new shortcut in" dropdown to "SQL Query Editor." 
• Note: This step is important - mapping Ctrl+R to "Global" won't work.
5. Click in the "Press shortcut keys" field and hit Ctrl+R on your keyboard. Click the Assign button.
6. Click OK.
7. In your query windows, you can now Ctrl+R to toggle the results window all you like.

Updated: 20180105 because again I needed to this blog post as a reference myself, added the chord messages to improve SEO.

Painful or Helpful? No SSMS Multiserver Results if One Instance Errors

I've been going back and forth with this question for a few days now.

If one instance in a multiserver query presents an error, no resultsets from any of the other instances are displayed, even if n "row(s) affected)" is displayed in the Messages tab.

This is particularly painful when querying dynamic management objects across server groups, because new DMV's and new DMV columns are being introduced in every version, sometimes with service packs.

• Is this a feature preventing you from using code that won't work against the group as a whole, saving you from accidentally assuming that all instances returned data?
• Or, is this a pain that causes you to need version-specific registered server groups to get at data from some instances?

There is no right answer to this question, only the opportunity to be aware of this behavior.

There is an old MSConnect item from 2007 for a similar issue that was marked as "won't fix" in 2011, so it is not a bug. I am not sure if there is any beneficial intent from MS to prevent you from getting any data when one instance in a group has a syntax error.

Imagine if, within a large list of production SQL Servers, a handful are of a prior SQL version where some utility scripts won't work.

Should I then create another registered server group for instances that can run sys.dm_os_volume_stats, or a group for instances that support the new columns added to sys.dm_exec_query_stats? No correct answer here either, in the absence of an option to allow for partial resultsets to be returned from multiserver queries, you will need to consider what suits your needs best.

Here's the steps to reproduce for a simple scenario:

1. Add a SQL 2008 instance and a SQL 2008 R2 instance to a new Registered Servers group.
2. Create a new multiserver query.
3. Attempt to query a DMF or DMV that was introduced in SQL 2008 R2, such as sys.dm_os_volume_stats. (Sample script below.)
4. The message table includes one error and one rows returned message, like below. No Results tab is returned, so the rows are not visible even though the query worked successfully on the SQL 2008 R2 instance.

sqldemo1\sql2008(domain\user): Msg 208, Level 16, State 1, Line 2Invalid object name 'sys.dm_os_volume_stats'.sqldemo1\sql2008r2(domain\user): (2 row(s) affected)

Sample script:

select distinct
vs.volume_Mount_point,
file_system_type,
   drive_size_GB = convert(decimal(19,2), vs.total_bytes/1024./1024./1024. ) ,
   drive_free_space_GB = convert(decimal(19,2), vs.available_bytes/1024./1024./1024. ),
   drive_percent_free = CONVERT(DECIMAL(9,2), vs.available_bytes * 100.0 / vs.total_bytes)
FROM
   sys.master_files AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.database_id, f.file_id) vs

I have confirmed this behavior in Microsoft SQL Server Management Studio 12.0.2000.8, 11.0.3128.0 and 10.50.4000.0.

It May Be Necessary Re-enable Service Broker After Baremetal Restore

After the restore of a database, or perhaps a bare-metal restore of a Windows server running SQL Server, unique IDs in the Service Broker settings may have changed, causing the broker to fail when starting. So, you will need to reactivate Service Broker.

You may first notice this because Database Mail gives you an error message about the Service Broker not being enabled in the MSDB database.

For example:
"...Either Service Broker is disabled in msdb, or msdb failed to start. ... Bring msdb online, or enable Service Broker."

Clicking OK to this prompt will cause SQL Server Management Studio to hang or freeze up. You need to do this in a T-SQL script.

Similarly,

ALTER DATABASE MSDB SET ENABLE_BROKER

will never complete.

It may be necessary to run

ALTER DATABASE MSDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE

to reset the service broker's unique ID on MSDB and then

ALTER DATABASE MSDB SET ENABLE_BROKER

will complete successfully.

Try a Database Design Exercise At Your Next SQL User Group

Tried something different for the January 2013 meeting of the Baton Rouge SQL Server User Group (brssug.org), which meets every second Wednesday at the Louisiana Technology Park (latechpark.com).

I think this was one of the most enjoyable meetings we've ever had, and certainly the most interactive.  As the chapter president, this took very little preparation (printing, writing implements) and really required me to do nothing more than lead the discussion.  My most excellent user group colleagues did all the heavy lifting, with lots of smiles.

I took my Relational Database Design exercise from the October 2012 Regional AITP Conference, printed out a bunch of copies, and handed them out to the attendees.  I'd recommend this to anyone looking for a change-up in their user group.  We couldn't find a speaker for January, so I thought this would be good content for our first meeting of the year.

For about 20 minutes, they worked with pens (not pencils, we're professionals!) on scratch paper for rough drafts of their design.  I reminded them that knowledge of American college football was not required, and that if you were making design decisions based on sports knowledge, you're probably not on the right track.

I hooked up my laptop to the big screen TV and started up a blank database diagram in SQL Server Management Studio 2012, which many of them had not seen.  The Database Diagrams tool built into SSMS is perfect for an exercise like this.  I hit "New Table" and said, "What table is first?"  Later, "Any more columns we need here?"  "What's the next table?" "What would be the best data type for this?" While on the keyboard, I tried to make as few decisions as possible, and encourage discussion of everyone's ideas and suggestions.  A couple times, we backed up the design after changing our minds.  No worries!

The 15 of us got through most (not all) of the design in about 80 minutes, time which flew by.  We had a great mix of very friendly folks.  Some had experience back to SQL 4.2, some with lots of Business Intelligence experience, and some .net devs with only accidental SQL experience.  We covered a ton of topics that were educational for everyone, and stumbled across too many design choice point-counterpoint discussions to remember.  

Do we need audit fields?  What attributes do we need to store per year, per player, or per game?  Are attributes dependent on the primary key?  How should we handle static student data vs student data that is measured annually?  The storage of games, schedules and rosters was a big source of conversation - always polite and professional and with lots of jokes and side conversations.  While most of our design conversation was vendor agnostic, we also touched on data types and indexes in SQL Server.

Along the way, added indexes, unique constraints, computed columns (to record if our football team won/lost/tied), foreign keys, identity columns and more, using only the Management Studio Database Diagrams.

The point of the user group meeting was not to complete the exercise - there wasn't enough time - but to raise all the kind of database design decisions that would come up in "real life."  With more time, we could have completed the design, filled in sample data, or even created some basic reports and a data warehouse to serve them.  

Here is a link to the problem statement for the contest, which I suppose now I can't ever use again for a conference competition:

Here's a second panoramic I took, with much less success.  My apologies to the folks whose heads were vanished by the Pano app on my android phone.

Thanks to everyone who attended, see you in February!

Management Studio Database Diagram Owners

If you're working in an environment as a developer without sysadmin privileges, and you are creating database diagrams using Management Studio underrated diagram tool, but not the database owner or a sysadmin, you'll see your created diagrams look like this

where the diagram is owned by the developer using the sql login 'jdoe'.  With many diagrams created by multiple developers, this can be ugly, confusing and just nonsensical.  Sysadmins don't have this problem, which is why like me you may have used Database Diagrams for years without encountering this issue.

There is no way to change this or rename it from the Management Studio GUI, but a simple script can fix the problem.

Find the diagram you want to rename, and the new principal you want to be the "owner".

select * from dbo.sysdiagrams

select * from sys.database_principals

(abbreviated results shown below)

name principal_id diagram_id

Diagram_0 1 1

name principal_id type type_desc

dbo 1 S SQL_USER

jdoe 6 S SQL_USER

This script can be executed by the developer to change the owner of the diagram from jdoe to dbo.

  update dbo.sysdiagrams

  set  principal_id  = 1 --dbo

where  principal_id  = 6 --jdoe

and    diagram_id    = 1

And now the diagram isn't owned by one of your developers.

"Heterogeneous queries and use of OLEDB providers are not supported in fiber mode."

This is a classic.


I've seen this in environments where someone is trying desperately to resolve SQL Server performance issues.  Out of the blue, linked server/DTC queries will begin to fail with the following error message:


Heterogeneous queries and use of OLEDB providers are not supported in fiber mode.