views:

42

answers:

1

Why has SQL Server to duplicate a hundred of system views from model database into each user-defined database instead of keeping them centralised (for example, as it keeps server objects) and using them in specific context of specific database when asked/needed?

Aren't they (for example, definitions/script of a system view) common between databases?


Update:

I still could not get it:
1)
In order to execute a view in context of another database there is no need to copy view definitions into each database (just extract view def and execute in context of specific database).
So, why to multiplicate copies?
2)
Then, I am still confused what is stored where?

[1] (BOL2005[1a]-2008R2[1b]) tells that:

"In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database"

Also, I would like to understand why SSMS permits to script server objects (for ex., right-click in Object Explorer under Server Objects --> Triggers on syspolicy_server_trigger --> Script Server Trigger as...---> ) while, for ex., system views do not have such possibility.

What does make them different? Where are they, resp., stored?


Cited:
[1] master Database
[1a]
(SQL Server 2008 R2 Books Online)
http://msdn.microsoft.com/en-us/library/ms187837.aspx
[1b]
(SQL Server 2005 Books Online)
http://msdn.microsoft.com/en-us/library/ms187837(v=SQL.90).aspx

+3  A: 

They aren't duplicated.

  • sys.objects and sys.database_principals are stored because they are database specific
  • sys.server_triggers or sys.server_permissions only exist at the server level in master db

SQL Server resolves the sys schema correctly to the database or server as required:

USE MyDB
GO
SELECT * FROM sys.server_permissions   --   <. always from master db 

It is that simple and of no use on a practical day to day level...

Edit: After Martin Smith's comment, from MSDN

gbn
Actually from `mssqlsystemresource` rather than `master` I think.
Martin Smith
@Martin Smith: The public catalog views are in master, the internal hidden *tables* are in mssqlsystemresource which is not accessible. Effectively its master. Unlike SQL Server 2000 and before which really had tables in master
gbn
I copied the mdf for mssqlsystemresource and attached it as a new database. `sys.server_permissions` shows up as a user view in that copy.
Martin Smith
@Martin Smith: it is still effectively master because you can't use the resource db. Note that it becomes a user view not system view here
gbn