Microsoft SQL Server database administrator, think fast! Without using any documents, write a query that extracts the list of indexes from the SQL Server 2000 system tables, then enumerates the fields in each index, and determines whether the field is sorted in ascending or descending order. You have two minutes. Fast!
If you really stop reading now and start thinking about this thankless task with an essential two minutes, then you're in a big trouble, which involves system indexes, System index keywords, system fields, and some metadata functions, including similar object_name and indexkey_property such as standby information. So far, it's obviously going to take far more than 2 minutes to write such a query.
unfortunate SQL Server 2000 database administrators, who need to browse esoteric system tables, are the worst part of this work using a database management system. system tables, which are usually efficient, but in my mind, they are never designed for user friendliness.
Fortunately, there is a savior in front of you. In SQL Server 2005, the system table is missing. Yes. It's gone. There is no need to operate on unfamiliar bits, nor do we need to find a cryptic coding scheme--which was necessary in the past. For those of you who need to take a look at the remaining references, I know what you're thinking: an endless mechanical upgrade to ensure compatibility with SQL Server 2005. But don't be disgusted with it. There are still objects similar to system tables in the system for backward-compatible purposes. But the table itself-or indeed should be-is forgotten, like 8-tracks and tab, into the dustbin of history.
So where did these watches go? system data in SQL Server 2005 is now stored in the Hidden resources table, which can only be accessed directly by the server itself. Lower-level users (and database administrators) must use a new set of categorized views that show the data obtained from hidden tables and various hidden functions that we cannot see or call. Systems in previous versions of SQL Server were implemented in the form of a series of so-called (and reasonably correct) "compatible views".
Category views and their partners, dynamic management views (explained below) represent a way to process metadata that is completely redesigned and rethinking. Without the mysterious tables that only give the database administrator a little sense of the underlying data, SQL Server now provides a wealth of resources: there are more than 200 categories and management views in SQL Server 2005, replacing approximately 50 system tables in previous versions.
all of these views can be found in the system plan. (The plan is a significantly expanded security feature in SQL Server 2005.) But this is another tip of the story. To see a full list of available views, SQL Server Management Studio extends the system view tree for all databases. Or select a list from the view itself through T-SQL and find friendly, Easy-to-understand names:
SELECT name
from Sys.all_views
WHERE is_ms_shipped = 1
you'll also find no need to browse through the documentation to find clues about what's going on with the data on a relational system. These views have a clear explanation of self.
Some clues about the name of the view are as follows: Those prefixes are dm_ dynamic management view, representing the changing state of the server through information similar to the current session, lock, and system resources. Other views can be considered categorized views. Those prefixes are all_ that contain information about system objects, such as views, and user-defined objects. Information that contains only user-defined objects that do not have the All_ prefix. In those views that include system objects, the Is_ms_shipping field can be used to differentiate between user-defined objects and system objects. If the value of the is_ms_shipped field is 1, the row represents a system object, otherwise the user-defined object.
Finally, let's examine some of the data types you can get from the Category view. For beginners, all the common content is available. For example: Look at the data in the index, use sys.indexes instead of the original sysindexes--it's strange, now called Sys.sysindexes. For constraints, try Sys.check_constraints, sys.default_constraints, or sys.key_constraints. Do you see this trend?
This tip is incomplete even if there is no simple mention of a new dynamic management view. These views are a powerful tool for SQL Server to store new metadata that can help database administrators quickly resolve problems and analyze server performance. Some of these star players, including Sys.dm_exec_query_stats, are used to report how many processor times the query requires; and sys.dm_db_index_usage_stats, to help the database administrator decide which index is most useful, Which is no use.
for this huge collection of metadata views, there is much more to say. But now, take a look at the recently published online version of SQL Server 2005 books by Microsoft. The System view topic provides a holistic description of the capabilities of this powerful new data warehouse.
In addition, the following is the solution to the 2-minute problem. First use the SQL Server 2000 system tables. Second, you can read the translation of the SQL Server 2005 classification view as early as possible.
