SCCM 2012 SQL Views

A while ago, I was looking at MOF edits and importing them into CM12. After some time I noticed that the SQL view name changed between CM07 and CM12.

Perfect, I thought, this is going to be a pain because it will mean that I have to change thousands of CM07 SQL queries to work with CM12!

CM12                                                  CM07

Without thinking about it, I started testing one of the existing SQL queries from Monitor Information Reporting (MIR).

Select

      MIR.ManufactureID0,

Count(*)

from

      dbo.v_GS_Monitor_Information0 MIR

Group by

      MIR.ManufactureID0

order by

      MIR.ManufactureID0           

I think that things are great as I am starting to get results! Maybe I made a mistake when I first looked at this! Then I go looking for the SQL view and I don’t see v_GS_Monitor_Information0 SQL view.

Now this is where I’m banging my head!

I run the query again… and I get results.. but where are the results coming from??? I must be overlooking something obvious, but what???

Note that v_GS_Monitor_Information0 SQL view is not present in the left-hand pane. Also notice that I’m getting results from the query!

Suddenly it hits me like a ton of bricks! I know exactly what the Microsoft product team did, but now I have to prove it!

I run the following query:

SELECT name, xType FROM sysobjects WHERE name like ‘v_GS_Monitor%’

This is the result that I get:

This tells me that they are using an alias for the SQL view or in SQL terms a “Synonym!” How cool is that!!!?!?!?!?! I did some quick checking and the best information on synonyms that I could find were these blogs:

Create Synonym:

http://msdn.microsoft.com/en-us/library/ms177544.aspx

Benefits and limitations of using synonyms in SQL Server 2005:

http://www.mssqltips.com/sqlservertip/1576/benefits-and-limitations-of-using-synonyms-in-sql-server-2005/

What does this mean for SQL queries and best practices?

Well that is for another blog post after I talk to a few people at Microsoft to confirm my suspicions.  My *GUESS* is that you should use the CM12 SQL view names wherever possible, and use the new synonyms if you need to be compatible with CM07.