Welcome to the first SQL Seven column. Each month, I'll present my top seven SQL Server-related technical tips, resources, and other interesting SQL Server information. I'll cover a wide range of topics including data-access techniques, Transact SQL (T-SQL) tips, useful knowledge-base articles, and integration with Visual Studio. In typical David Letterman fashion, I'll start with number seven and work my way up to number one. Considering the recent release of SQL Server 7.0, it seems fitting that this first column should begin with my favorite new features in SQL Server 7.0.
7. Distributed Merge Replication
SQL Server 7.0's new distributed merge replication significantly strengthens its ability to support mobile computing implementations by letting a remote system periodically connect to a central server and synchronize table changes. When used with Windows 9x, distributed merge replication is a good solution for mobile computing and small branch-office scenarios.
6. Query Analyzer
Like its predecessor, ISQL_w, the Query Analyzer lets you build stored procedures and perform interactive queries. But the Query Analyzer also supports color-coded keywords, and it can graphically display the execution path the query processor used.
5. SQL Profiler
The new SQL Profiler replaces the SQL Trace facility. The Profiler is a powerful debugging tool that lets you track the execution of an application's SQL statements and monitor the SQL Server system's activity. The Profiler also can capture event data and replay it on other SQL Server systems.
4. Dynamic Cost-Based Locking
SQL Server 7.0 implements a new dynamic locking mechanism that's based on an internal execution cost. The query analyzer uses a combination of required CPU and I/O resources to determine this cost. This method allows online transaction processing (OLTP) applications to use granular row-level locking and batch operations to use the better-performing page- or table-based locks.
3. OLAP Services
SQL Server 7.0's OLAP Services opens data warehousing, and decision-support appli- cations to many businesses that might never have considered using this type of tool. OLAP Services enables high-performance ad hoc queries for "what-if" decision-support applications.
2. Data Transformation Services
Data Transformation Services (DTS) supersedes SQL Server 6.5's bulk copy program (bcp) utility, but comparing DTS to bcp is like comparing the Model T to a new Lexus. DTS can import, transform, and export data. It has a graphical interface and you can use it to build packages that you can execute from any Object Linking and Embedding (OLE)-compliant tools such as Windows Scripting Host (WSH) and Visual Basic (VB).
1. Dynamic Resource Allocation
Facilitating hands-off management, dynamic resource allocation lets server disk and memory usage scale up and down to meet changing database demands. Dynamic resource allocation makes SQL Server easier to manage. Getting rid of SQL Server 6.5's outdated devices is a bonus.