- History of SQL Server
- What's New in Version 2000
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
What's New in Version 2000
Several of the key features found in SQL Server 2000 are as follows:
XML Support
Multi-Instance Support
Data Warehousing/Business Intelligence Improvements
Windows 2000 Support
Performance and Scalability Improvements
Wizard Improvements
Query Analyzer Improvements
DTS Improvements
Transact SQL Enhancements
XML Support
XML is becoming the standard in which businesses communicate and share information. SQL Server 2000 provides extensive XML support. XML support is easy to setup and access. Once you are up and running with XML, you can store XML inside a table, query the XML data through Transact-SQL statements, and even join XML data to relational data through a SQL statement.
Multi-Instance Support
Multi-Instance support enables multiple copies of the SQL Server engine to run on the same machine. Microsoft has finally caught up to Oracle on this feature. This is a great feature in that it enables a DBA to combine multiple environments such as development, test, and production onto one machine. Also, this is big win for ASP and ISPs in that they can host multiple applications on the same machine.
Data Warehousing and Business Intelligence (BI) Improvements
Microsoft continues to build out its BI offering through SQL Server 2000 and its Analysis Services (previously named OLAP Services in version 7.0). Analysis Services provides OLAP analysis over the Web through accessing and linking cubes via the Internet. Additionally, SQL Server 2000 includes Data Mining tools and support for Web-related analysis. For example, when SQL Server is combined with Commerce Server 2000, you can perform data mining on click-streams, purchasing patterns, and other types of information.
Windows 2000 Support
SQL Server 2000 is tightly integrated with Windows 2000. SQL Server 2000 automatically registers itself with the Active Directory. This enables someone to search for SQL 2000 servers in an organization, manage databases directly from the Active Directory service, and perform other functions.
SQL Server 2000 is also integrated with Windows 2000 through the Kerberos security mechanism. Furthermore, SQL Server takes advantage of the performance improvements found in Windows 2000. When running Windows 2000 Datacenter and SQL Server 2000 Enterprise Edition, SQL Server can scale up to 32 processors and 64GB of RAM.
Performance and Scalability Improvements
SQL Server 2000 continues to produce record-setting performance numbers on the Intel–based platform. SQL Server 2000 is the first Microsoft product to enable the data tier to be portioned across multiple servers. This is accomplished through distributed partitioned views, which enables the workload to be distributed across multiple servers. Distributed portioned views are a key component of Microsoft's scale-up-and-out strategy.
Also, SQL Server 2000 provides other performance enhancements such as DBCC improvements, indexed views, and index reorganization. DBCC operations can now take advantage of multiple processors (Enterprise Edition only). Indexed views are a big plus for data warehousing. Furthermore, index reorganization can now take place with a minimal performance impact.
Wizard Improvements
SQL Server 2000 provides two very useful wizards: Copy Database Wizard and Log Shipping Wizard (Enterprise Edition only).
The Copy Database Wizard automates the steps to copy a database. This is very useful when setting up test environments, moving databases, and sharing databases.
The Log Shipping Wizard automates log shipping. It enables you to copy and apply transaction logs to a standby server. This is useful when creating a standby server. Previously, the Log Shipping utility was found in the SQL Server 7.0 resource kit and was script based. Now it is directly integrated with the Enterprise Edition and automates the scripting process.
Query Analyzer Improvements
The Query Analyzer is full of productivity enhancements. The following lists my favorite improvements:
Integrated Debugger—Finally, the SQL Server has an integrated debugger! This is great for DBAs and developers who have to debug stored procedures. No longer do you have to use the Visual Studio debugger (which did not always work).
Object Browser—The Object Browser provides a hierarchical view of objects. For example, you can drill into a database, table, column, or other types of objects. After drilling into the object, you can drag and drop the object name into a SQL statement.
Object Search—The Object Search enables keyword searching across databases, tables, stored procedures, and other objects.
Templates— With this feature, you can create custom templates or use the standard templates—for example, whether you always include a code header in your stored procedures, save the code header as a template, or use the template to create the stored procedure. This ensures consistency and reduces cut-and-pasting.
DTS Enhancement
DTS has been improved to preserve primary key and foreign key constraints. This is useful when migrating tables from other RDBMS. Additionally, a DTS package can be saved as a Visual Basic project. By adding the package to SourceSafe, you gain version control for your DTS packages.
Transact-SQL Enhancements
Transact-SQL has been enhanced to support user-definable functions (UDFs). This enables you to store your logic in a common routine. Also, Transact-SQL has been enhanced to support declarative referential integrity.