ODosta Inc. Create Your Own Tech World
A database is a collection of data stored in data files on a disk or some removable medium. A database consists of data files to hold actual data. An SQL Server database is made up of a collection of tables that stores sets of specific structured data. A table includes a set of rows (also called as records or tuples) and columns (also called as attributes). Each column in the table is intended to store a specific type of information, for example, dates, names, currency amounts, and numbers. A user can install multiple instances of SQL Server on a computer.
Each instance of SQL Server can include multiple databases. Within a database, there are various object ownership groups called schemas. Within each schema, there are database objects such as tables, views, and stored procedures. Some objects such as certificates and asymmetric keys are contained within the database but are not contained within a schema.
SQL Server databases are stored as files in the file system. These files are grouped into file groups. When people gain access to an instance of SQL Server, they are identified as a login. When people gain access to a database, they are identified as a database user. A user who access to a database can be given permission to access the objects in the database. Though permissions can be granted to individual users, it is recommended to create database roles, add the database users to the roles, and then, grant access permission to the roles. Granting permissions to roles instead of users makes it easier to keep permissions consistent and understandable as the number of users grows and continually change.
- SQL Server 2012 supports three kinds of databases, which are as follows:
System Database
SQL Server uses system databases to support different parts of the DBMS. Each database has a specific role and stores job information that require being carried out by SQL Server. The system databases store data in tables, which contain the views, stored procedures, and other database objects. They also have associated database files (for example, .mdf and .ldf files) that are physically located on the SQL Server machine.
The following table shows the system databases that are supported by SQL Server 2012:
| Database | Description |
| master | The database records all system-level information of an instance of SQL Server. |
| msdb | The database is used by SQL Server Agent for scheduling database alerts and various jobs. |
| model | The database is used as the template for all databases to be created on the particular instance of SQL Server 2012. |
| resource | The database is a read-only database. It contains system objects included with SQL Server 2012. |
| tempdb | The database holds temporary objects or intermediate result sets. |
Modifying System Data
Users are not allowed to directly update the information in system database objects, such as system tables, system stored procedures, and catalog views. However, users can avail a complete set of administrative tools allowing them to fully administer the system and manage all users and database objects.
These are as follows:
Administration Utilities:
From SQL Server 2005 onwards, several SQL Server administrative utilities are integrated into SSMS. It is the core administrative console for SQL Server installations. It enables to perform high-level administrative functions, schedule routine maintenance tasks, and so forth.
SQL Server Management Objects (SQL-SMO) API: Includes complete functionality for administering SQL Server in applications.
[AdSense-B]These use system stored procedures and Transact-SQL DDL statements. The following figure shows a Transact-SQL query window:
Viewing System Database Data
Database applications can determine catalog and system information by using any of these approaches:
System catalogs views
Views displaying metadata for describing database objects in an SQL Server instance.
SQL-SMO
New managed code object model, providing a set of objects used for managing Microsoft SQL Server.
Catalog functions, methods, attributes, or properties of the data API
Used in ActiveX Data Objects (ADO), OLE DB, or ODBC applications.
Stored Procedures and Functions
Used in Transact-SQL as stored procedures and built-in functions.
SQL server 2014 also has been released on 1st April, 2014, Which is include many advance features;
Furthermore: SQL Course
