August 30, 2019 at 1:47 am
I cannot find a good explanation about how these 3 things differ from each other.
Can anybody explain the difference between these things?
August 30, 2019 at 2:09 pm
These aren't terms that are often used. There are system tables, which are really materialized when queried. There used to be real system tables in SQL Server, and these were base tables as opposed to views. For compatibility reasons, some of the tables are still exposed as tables, even though I think everything is a DMV (view) now.
Base tables typically are tables, as opposed to views, which query base tables or other views.
August 30, 2019 at 10:23 pm
I think I see it now. I created two diagrams. One to show how I think the System Catalog Views, System Views, System Catalog and System tables are related.
Another one to show how how the various types of tables are related.
Based on these two attached diagrams, do I have this right?
August 31, 2019 at 1:21 am
You can check lots of them here: https://www.microsoft.com/en-us/download/details.aspx?id=39083, but you have the idea in the general sense
August 31, 2019 at 1:56 am
Microsoft actually does make a distinction between "System Base Tables" and "System Tables". Please see the articles at the following links for more information...
There are also "System Views" and they are broken into many categories. As Steve points out, there are Dynamic Managment Views (affectionately referred to simply as "DMVs"), but those aren't the only "System Views". Rather, they are a category of "System Views". See the article at the following link for more links of "System Views" by category.
https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017#find-system-views
As for "Base Tables"... I use that term as in "The base tables of a view". The term seems to come from the INFORMATION_SCHEMA.Tables view, specifically the TABLE_TYPE column, which lists tables as "Base Table" and views as "View".
With all that being said, I'll have to say that your drawings aren't actually correct in their labeling.
Also, not all "System Tables" are available in every database. For example, MSDB has a wad of "System Tables" that appear in no other databases. Even more confusing is that sp_help will list them as "User Tables" (which I think is a better term than calling tables in general "Base Tables") but have the is_ms_shipped bit set to 1 in sys.objects.
To add to the confusion, sys.diagrams (if present in a user database) is generally considered to be a "System Table" but does not sport a 1 for is_ms_shipped (apparently because it's created only when a uses starts the very first diagram). And for even MORE confusion, that's the desired effect when you use SYS.SP_MS_MARKSYSTEMOBJECT to create an object in the Master database to allow a proc to be executed from any database as if it lived in whatever database is current.
The bottom line is that I agree with Steve. The use of such terms are fairly infrequent and, I'll add that when they are used, they are frequently used incorrectly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply