Simon Worth’s SQL Server Weblog

Quick post related to another post I am currently working on.

In SSMS, if you have a query – such as

SET FMTONLY ON
GO

SELECT
       *
  FROM
       sys.columns

Then execute the code – you’ll get a similar resultset to the figure shown below

Now, if you hit CTRL + L to see the estimated execution plan – you will only get

(0 Row(s) affected)

Fine, that makes sense.  FMTONLY ON is only getting meta data so there is no estimated execution plan to show.  That’s all fine.  But now, if you hit F5 to execute the commands you will get this

Notice anything weird?  Exactly, it returned 2 query results instead 1.  Now, hit CTRL + L a couple times in a row and hit F5 again.  However many times you hit CTRL + L and then run the query, you will get that many resultsets back.

No idea if this is a bug or not.  Haven’t looked through connect yet to see if it’s there, but will at some point this weekend.

I can probably come up with a half baked type answer as to why it is doing this with FMTONLY ON, but if anyone else actually knows why, I’d much rather hear from them.