I just ran into something that I hadn’t noticed in SQL Server. It struck me as somewhat interesting but is really not too big of a deal.
While demonstrating that the XML plan can be generated when you dump data into a temp table, I happened onto this little ditty. First, let’s take a look at the simple demo script that I created for the temp table dilemma.
SELECT *
INTO #testtemp
FROM sys.databasesIt was thought, by the questioner, that inserting into a temp table just like I am demonstrating would cause an error to be thrown when trying to show the xml plan. I can execute this query and it runs just fine. Now, if I add the showplan_xml to it, like the next script, it will still run fine.
SET showplan_xml ON
go
SELECT *
INTO #testtemp
FROM sys.databases
go
SET showplan_xml OFF
goPretty straight forward and note that I have batched the showplan separately from the code I am testing. This is required by the showplan command. The curious part comes when looking at the results. Well, not entirely the results – but more accurately the name of the results.
I looked at the column name for this output and thought: “wait, I know this is a SQL 2008 instance.” I decided to verify the version and results. Thus, I reran the query below to test.
SELECT @@VERSION
go
SET showplan_xml ON
go
SELECT *
INTO #testtemp
FROM sys.databases
go
SET showplan_xml OFF
goThis query produces the following results.
This seems pretty conclusive to me that I am running SQL 2008 but the showplan still outputs as 2005 XML Showplan. I also checked this on 2008 R2 and get the same results. Microsoft has decided to continue to use the 2005 Showplan schema and did not update that name. Would it be nice to have an updated label? Certainly it would. The flip-side is that the XML Schema does not seem to have changed, so it is merely aesthetic in nature.
If you would like, you can take a look at the schema here. I only checked the last updated date on the current, SQL 2008, and SQL 2005 sp2 schemas and saw that the date was the same for all of them.
