- Introduction
- Structured Query Language (SQL) Support
- Analysis Services (OLAP)
- General Enhancements
- What Could Have Been Done Better?
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Structured Query Language (SQL) Support
As a relational database, Microsoft SQL Server 2000 uses SQL as the base standard for accessing and manipulating both data and database objects.
The American National Standards Institute (ANSI) has defined ANSI-SQL 92 as the standard for SQL. Most database vendors conform (generally) to ANSI-SQL 92; however, it is a little limited, especially when it comes to getting the most bang for your buck out of your RDBMS.
ANSI-SQL 92
As an international standard for SQL, ANSI-SQL 92 incorporates the syntax used for Data Manipulation Language (DML), INSERT, UPDATE, and DELETE; as well as Data Definition Language (DDL), CREATE, DROP, and ALTER statements.
SQL Server 2000 allows you to set the compliance options at the database level, so you can have multiple databases in different compatibility modes. This forces all SQL to conform to the ANSI-SQL standard. This option therefore allows SQL statements to be transported to another RDBMS that supports ANSI-SQL. (By default, this option is not enabled.)
Transact SQL (T-SQL)
Transact SQL (T-SQL) extends the basic functionality of ANSI-SQL 92 by offering the ability to leverage the full power of SQL Server with these features:
Allowing data to be returned from stored procedures
Ability to create updateable views
Allowing developers to create user-defined functions
Support for three new datatypes (table, bigint, sql_variant)
Extended cursor options
T-SQL allows developers to create rich and full applications by empowering them to encapsulate functionality that would normally be exposed.
With T-SQL and Query Analyzer you can set up a debugger, allowing you to step through your SQL statements as you would with rich debugging applications such as Visual Studio .NET.
Other features of T-SQL allow full administration of an installation of SQL Server, including backup and restore, setting configuration options, and running database consistency checks (DBCC).
T-SQL also has a wide range of support for extended functionality (built-in functions) provided by SQL Server 2000. These are broken into the following categories:
- Aggregate functions
- Configuration functions
- Cursor functions
- Date and time functions
- Mathematical functions
- Metadata functions
- Rowset functions
- Security functions
- String functions
- System functions
- System statistical functions
- Text and image functions
These functions can do just about anything and everything you could possibly need to manage and leverage your installation of SQL Server 2000. In fact, some of these functions relate to our next section on analysis services and allow you to configure and utilize the best from OLAP.