SQL Server Features
The features, processes and specific uses of an SQL Server
We will first discuss the features of SQL server 2000 and later look at the SQL Server 2005 enhancements. SQL Server's Enterprise Manager console serves as its administrative GUI tool. The Enterprise Manager may be used to view all the SQL Server installations and databases on the network and perform high level administrative functions that involve one or more servers. Common backups, restores and other maintenance related tasks may also be scheduled through the Enterprise Manager. This GUI allows Database Administrators (DBAs) to make changes to individual databases by adding or modifying data objects such as tables. The SQL server Query Analyzer may be used to run SQL queries and obtain results. The query analyzer has an endless potential as a development tool for new reports, analyses, stored procedures, and so on. The Enterprise Manager and Query Analyzer have been consolidated into SQL Server 2005's Enterprise Management studio.
The SQL Server Program Group
The SQL Profiler is a performance monitoring tool. DBAs may observe and record database and query efficiency in real time through custom views and 'traces' that capture the effect of various commands and events on the system in log files. The Service Manager is used to control the main SQL Server processes - MSSQLServer, MSDTC (Microsoft Distributed Transaction Coordinator), and SQLServerAgent (the Job and Event Scheduler) processes. You can see these processes if you press ctrl-alt-del and click on the 'Process' tab of the Task Manager. The icon for these services is usually present in the system tray on the bottom right hand side of the screen. The service manager (from the control panel) may be used to start or stop these processes.
Data Transformation Services (DTS) is one of the most powerful and flexible features of SQL Server 2000. DTS allows developers to perform complicated data import, export and a variety of transformations.
Analysis Services provides tools that allow viewers to specify dimensions and factual data for large data warehouses. Such dimensions add many dimensions to two dimensional database tables and transform them into data 'cubes'. A wide variety of qualitative and quantitative analyses may be performed on the data (data mining) through these cubes. The cubes may be secured for use by specific users with specific roles.
The above are the main component level features of the SQL Server. DBMS features on a finer level of granularity include the incorporation of user-defined functions, indexed views, distributed partitioned views, INSTEAD OF and AFTER triggers, new datatypes like bigint and xml, cascading RI constraints, multiple instances, XML support, and log shipping. Let us look at these features in detail.
Functions are used within queries, stored procedures, and other T-SQL commands. They return the end product of a mathematical or statistical formula or expression. SQL Server contains a number of built-in functions such as abs (to return the absolute value of a number), sin (to return the trigonometric sine of a given number) and so on. SQL Server 2000 and 2005 allow users to write custom functions to perform user-specified calculations that take zero or more input parameters and return either a single value or a set of rows.
A view is a custom way of looking at the data. Normally, data from two or more tables or summaries and aggregations are consolidated via complex SQL queries and displayed in an easy-to-comprehend view. Running underlying queries every time a view is invoked by a user (views may be used just like tables) is expensive. SQL Server 2000 and 2005 allow users to define indexes on views - a query for an indexed view is a lot more efficient. Essentially, the result of the query is indexed and stored in the database.
A trigger is an SQL code that is executed every time a certain event (such as an insert into a table, a delete from another table etc.) occurs. SQL Server 2000 and higher versions allow users to write triggers that are executed instead of the triggering event. Further, each table may have multiple 'after' triggers that are executed after performing updates, insertion, and deletion events.
Large 8-Byte integers may be stored in tables using the datatype 'bigint'. A variable sized column that adapts itself to a variety of data types such as int, varchar and so on may be defined using the data type sql_variant. Further, a table datatype may be defined to temporarily hold sets of rows of tabular data. Several SQL server instances (an instance corresponds to a SQLServer process) with custom configurations and sets of databases, may run on a single computer.
Extensible Markup Language (XML) is a data description and display standard. XML is rapidly becoming the tool of choice for data needs. SQL Server provides native support for XML and allows users to obtain query output in XML format and retrieve data from XML documents as if they were SQL Server tables. Further, data in SQL Server tables may be directly viewed on the Internet using XML tools and IIS (Internet Information Server).
A transaction log records every command that is issued to a DBMS. Log shipping allows a transaction log for a specific time interval to be applied to another database. That is, all the commands in the log for the time period are applied to the other database. This mechanism may be used to perform incremental backups (i.e. backup changes rather than the entire databases) or to restore a database that has been recovered from a backup and bring it up to date. That is, if a backup is performed at 5:00am on Saturday and the system crashes at 5:00pm on Saturday, the transaction log from 5:00am to 5:00pm may be executed or applied on the backup to bring it up to date and get the database running.
In addition to the above, the latest versions of SQL Server support several enhancements to Full-Text search such as change tracking and image filtering (for documents saved in image columns). Versions 2000 and above provide built-in clustering support, which means that several computers may be 'clustered' together to form a fail-safe DBMS server. Secure and differential or incremental backups are also broadly supported.
In SQL server 2005, DTS is transformed to a more comprehensive SSIS or SQL Server Integration Services that provides many more transformations and extensive programming support. SQL server 2005 also supports Ad Hoc reporting through Reporting Services and the new Business Intelligence Development Studio (a development tool for Analysis Services, SSIS etc.).
