SELECT SERVERPROPERTY('Edition');
There is also a server property called EngineEdition that you can inspect, as follows:
SELECT SERVERPROPERTY('EngineEdition');
Return values
- is not a possible value
- Standard or Workgroup,
- indicates that your SQL Server edition is either Enterprise, Enterprise Evaluation, or Developer
- Express Edition
SQL Server 2008, there are three types of system metadata objects
- Dynamic Management Objects- It’s don’t really correspond to physical tables—they contain information gathered from internal structures to allow you to observe the current state of your SQL Server instance
Pseudotables are tables that are not based on data stored on disk but are built as needed from internal structures and can be queried exactly as if they are tables. Like sysprocesses and syscacheobjects
All the catalog views (as well as the Dynamic Management Objects and compatibility views) are in the sys schema, and you must reference the schema name when you access the objects.
definition of sys.tables
SELECT object_definition (object_id('sys.tables'));
System Functions
- SERVERPROPERTY
- COLUMNPROPERTY
- DATABASEPROPERTY
- DATABASEPROPERTYEX
- INDEXPROPERTY
- INDEXKEY_PROPERTY
- OBJECTPROPERTY
- OBJECTPROPERTYEX
- SQL_VARIANT_PROPERTY
- FILEPROPERTY
- FILEGROUPPROPERTY
- TYPEPROPERTY
- CONNECTIONPROPERTY
- ASSEMBLYPROPERTY
i.e SELECT DATABASEPROPERTYEX('msdb', 'Recovery');
To view the recovery models of all our databases, you can use the sys.databases view:
SELECT name, recovery_model, recovery_model_desc FROM sys.databases;
T o find out the database ID
SELECT database_id FROM sys.databases WHERE name = 'AdventureWorks2008';
By Function
SELECT DB_ID('AdventureWorks2008');
The Major Component of SQL Server Engine
As you can see that Protocol layer is the upper most layer, which receive the request and translate it into a form that the relational database can understand and it return the result in a form that a client can understand.
For TSQL, the relational Engine parse, compile and optimizes the request. As a batch executed, if the data is needed, a request sent to storage Engine. The storage Engine Manage all data access, bulk insert and certain DBCC command..
SQLOS Layer handle s the activities that are normally consider to be operating system responsibility such as thread management, synchronize primitives, deadlock detection, memory management including buffer pool.
