Student Academic blog

Author : Aamir Hasan

Microsoft has positioned SQL Server 2005 as the back-end data platform for the Microsoft Office System and most of its enterprise products, with the notable exception of Microsoft Exchange. The database engine serves up data; Integration Services (SSIS) provides extracts, transformations, and loading of data; Analysis Services provides business intelligence; and Reporting ServicesService Broker allows for an HTTP-based messaging system to enable service-oriented applications (SOAs), and Replication allows for disconnected recordsets and high availability along with Clustering and several other technologies. And these are just the delivered services and features, not what you can create with them. Each of these features has multiple components in their individual architectures, and keeping everything straight can be a challenge.

Objects

The database engine, along with the other processes, is designed to move data back and forth to and from a client. To facilitate a fast, safe environment, SQL Server 2005 uses several objects to store and optimize the data. I classify everything in SQL Server that is not a process to be an object.

Database

The first object is the database. The database is the outermost collection object, which means that it contains other objects. Databases have logical files defined in it that point to the location of physical files on the storage subsystem.

Schema

SQL Server 2005 uses an abstraction object called a schema. A schema is simply a naming object within a particular database, which in turn "owns" other objects. Users are granted rights to the schema so that they can access the objects the schema owns. This allows the database to have multiple tables called "product," for instance, and by using a separate schema name you can keep them separate. It also allows for objects to remain independent of users, so that when user accounts change, the object does not have to.

You are limited to 2,147,483,647 objects in any database, and schemas come out of that limit. In other words, if you had only one table and one user, you could have roughly 2,147,483,645 schemas.

Table

The next object within a database is the table. A table is an entity in a relational system that provides columns and rows of data. You can think of a single spreadsheet as a table.

View

A view is a T-SQL set of code that selects data from one or more "base" tables, and the result looks like a new table. For instance, if you took the customers and orders tables I mentioned earlier, and joined them with a T-SQL statement as a view called cust_orders, you could query the cust_orders view as if it were a real table. I show you an example of this in the "Take Away" section that follows.

Stored Procedure

A stored procedure is similar to a view, in that it is a bunch of code that runs on the server and returns a result. The difference is that a view is unchanging. Whatever you have entered as the code for a view is that way until you delete (or drop) and re-create it. A stored procedure can accept parameters from users or code. A stored procedure can return data, a status, or perform some other work in the database. Stored procedures can be used like a view to hide the complexity of a table or to slice and dice the data.

Function

A function is yet another set of T-SQL or CLR code, similar to a stored procedure that returns a value to the user. It falls under the same 2,147,483,647 objects per database limit.

Functions can return a table, and can even be used recursively in a query. This makes them very powerful for developing server-side code. SQL Server 2005 contains several system functions, and you can create your own user-defined functions (UDFs).

Index

An index is really another table but contains ranges of data and the physical location where the data is found. It is like an index in a book, showing where the data is located. SQL Server uses B-Tree indexes, which sequentially branch off of the ranges of data in an upside-down tree fashion.

Job

A job is a special set of entries into the msdb database. Jobs contain steps, which are made up of tasks such as T-SQL queries, ActiveX scripts, operating system calls, and more. Steps have conditions, such as success or fail, which you can string together to loop or jump over one step to another to form simple logic.

Alert

An alert is a condition that is raised based on an event, such as a full database file or other system and database-related action in SQL Server 2005. Developers can code them to receive system responses in their programs. Microsoft delivers several system alerts right out of the box, mostly dealing with system-level events, and you can make more alerts for just about anything that happens on your server. The system events are triggered by the server engine, and user-defined alerts can be called by a server condition or directly in code.

Operator

An operator is a set of identification information such as a name, network login, e-mail addresses, and pager number. SQL Server can send messages to operators, alerts and jobs can both notify operators, and automated maintenance plans can send an operator a message containing the results of system maintenance.

Communication Processes

Architecture, SQL 2005 & 2008 asp.net, architecture