Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 25, 1998

General Information - Page 8

By Sharon Dooley

25 Jan 98

Nested iteration is still used, particularly for non-equality joins that dont lend themselves to the other methods.

 

Hash Aggregation is used for SUM, etc. In this technique, the algorithm is

Read a row from the input table

Hash the key value to a bucket; if key is already there, compute aggregate. If not, put it in a bucket.

Loop through input

Output key values and computed aggregates

Note: this produces unordered output. This means that you can no longer depend on GROUP BY to produce ordered output with out an ORDER BY clause. (Ive been trying to tell you all for years that you should, by ANSI standard, use the ORDER BY!) There is a mechanism in place so that when you upgrade a 6.5 database you will get ordered results.

There are changes in the I/O strategies as well. First of all, pages are now 8K and extents are 64KB (see the Storage Architecture section for more details). During table scans, I/Os are also 64KB. Unordered scans can do parallel reads from files. Read-ahead works on heaps as well as the clustered index. The QP drives the read-ahead.

Bulk update processing has been improved. The QP does the index maintenance. On insert, update or delete, changes are sorted in index order and applied in a single pass over each index. Constraint processing is integrated into bulk updating as well. This technique is used in DBCC and bcp. Microsoft reports that a typical "data massaging" application ran 10 times faster.

The query processor understands partitioning solutions. This means that if you were to define tables to hold monthly data (Jan, Feb, Mar, etc.) and combine them with a UNIONING view, then just request one month from that view, it will only search that table. It appears that you need to define a check constraint limiting the month table to a particular month for it to be able to do this. SQL Server does not support partitioning by value, that is, spreading a table across several different storage areas based on key values as is done in DB2, for example.

Query optimization is still cost-based. I/O is still the dominant factor.

There is a lot of support for parallel operations on SMP machines. This is automatic, you dont have to use any configuration or hinting directives. The degree of parallelism will be adjusted down on loaded systems or systems with insufficient memory. For a single query, the optimizer runs on one cpu. However, multiple queries can be optimized simultaneously if there are multiple cpus.

There is support for distributed queries. This support uses OLE DB. It accesses SQL Server directly. With the Jet OLE DB provider it can access Jet files, Excel, FoxPro and dbase. With the OLE Provider for ODBC, it can access Sybase, Informix, DB2, etc. Microsoft has written an OLE DB provider for Oracle which it uses to access Oracle 7 & 8. There is also interaction with Active Directory Services (NT 5) and Index Server. It uses something called a "Linked Server" for these. I am not clear on the difference between a Linked Server and the current Remote Server concept. It is also possible to use something called the OpenRowset function to retrieve data from an OLE DB data source without making it a linked server. [Interaction with Index Server was mentioned a few times at the briefing. I got the impression that there was some integration between Index Server and text data types but this was not clearly stated. Perhaps my total lack of knowledge of Index Server interfered with my comprehension.]


 

Many thanks to Sharon for providing these notes - drop her a note at sharond@compuserve.com and tell her thanks!

 



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM