Database Objects

An RDBMS like SQL Server contains many objects. Some of these objects with respect to SQL Server 2005 have been briefly described below.

The database itself
The highest-level object within a DBMS is certainly the database itself, its a collection of various other objects such as tables, views, stored procedures, etc.
When SQL Server 2005 is loaded for the first time, it contains the following four system databases:
• master DB – This is the most critical system database present and cannot be deleted. It contains a special set of system tables which keep track of the system as a whole, example each new database created on the server has a corresponding entry in the sysdatabases table in the master db.
• model DB – As the name suggests, its contains the model (or the template) which would be used for any new database created on the server. If one modifies it, then every database would contain the modified characteristic. Its a required database and cannot be deleted.
• msdb DB – This is the database where SQL Agent process stores any system-related tasks. Example if there is any scheduled task like backing up of a database or a sproe execution scheduled, the msdb would have entries relating to them.
• tempdb DB – One of the most interesting system databases, it contains all the temporary objects created while using the SQL Server DBMS. Whenever a large complex query which requires creating interim tables is executed, the interim tables are cretaed in tempdb, and same is the case for any user created temporary table in a query, irrespective of the current database. One more point to note is that this is the only database which is created from scratch every time the SQL Server is started. Kinda cool trivia!

Transaction Log
This is the first point of storage where every database change gets stored, yeah you read it correct, although the data is read from the database file, while writing/updating data, changes do not go directly to the database file rather they get stored in the transaction log and get propagated to the database file when database issues a checkpoint.
The database file has a random access arrangement, but the log is serial in nature thus making the database tracking done in an orderly manner.

Tables
The fundamental object within the database, it contains the domain data (columns) and entity data (rows) in a tabular format. Each tables also contains metadata which tells the type of data allowed in the table.

Indexes
An index is one of those database objects which exists only within the framework of a table or view. It helps in speeding the lookup of information present in a table, just like an index at the back of a book does.
There are two types of indexes:
• Clustered Indexes – There can be only one clustered index on a table and data present in the table is ‘physically’ sorted according to this index. A real world example of this index type would be the page numbers of a book.
• Non-clustered Indexes – These are created to improve performance of frequently used queries not covered by the clustered index. The maximum number of non-clustered indexes that can be created an a table is 999. A real world example would be the index section at the back of a book.

Triggers
This object also exists only within the framework of a table. A trigger is a piece of logical code that is automatically executed each time a certain action occurs such as inserts, updates or deletes on a table. Usually they are used for checking the consistency of the data addition to a table.

Constraints
This object also exists only within the framework of a table. They as the name suggest help in limiting the data in a table to meet certain conditions. Both triggers and constraints help in maintaining data integrity.

Stored Procedures
sprocs are generally an ordered series of T-SQL statements bundled together into a single unit. They have several advantages over individual statements:
• Less network traffic required to run the code as sprocs are called using their short names instead of the entire query text.
• sprocs are ‘pre-optimized’ and ‘pre-compiled’ thus saving time each time they’re run.
• They encapsulate a process/functionality, thus hiding the complexity as well as securing the code
• Can be called from other sprocs, thus making them reusable to a limited extent.

UDFs
User-Defined Functions (UDFs) are similar to sprocs except that they:
• Can return a value of most SQL Server data types.
• Can’t have “side effects”, i.e. they can’t do anything outside their scope, like changing tables or sending e-mails.
UDFs are similar to functions/methods in VB/C# as they take in parameter (s) and return a value, however every parameter being passed is passed by value.

Full-Text Catalogs
These are mappings of data that speed the search for specific blocks of text within columns that have full-text searching enabled. These do not get updated automatically when a database changes.

Source of reference: Beginning SQL Server 2005 Programming by Robert Vieira

Advertisement