High level comparison of IB6 v/s MsSql7 features.I had to rescue this article from the Mers list where I posted it. Some people where interested in a general comparison of features. You can also read the article I posted on conversion guidelines from MsSql7 to IB6, including data types and functionality (I donated it to IBPhoenix).
Let's try to compare with Sql7 documentation: « Trigger Enhancements Recursive triggers Multiple triggers per INSERT, UPDATE, or DELETE statement You can now append multiple triggers of the same type to a single table. For example, a single table can have one delete trigger, three insert triggers, and two update triggers. This enhancement allows you to put different business rules into different triggers. A database option allows triggers to call themselves recursively. » IB had that facility several years ago, the only difference is Bill Gates is able to make the world believe he is the first with his products. Sql7 still doesn't have BEFORE/AFTER triggers. Uncle Bill Gates, are you reading? You can steal the missing code when IB goes public. IB doesn't allow calling explicitly triggers, but it can be done in some limited ways. I have to see the value of that first, tho. « 8 KB page, 64 KB extent » IB can have this page size, too. « Support for native operating-system files » IB has external files since years ago. IB doesn't use a database device, it writes files (gdb's) on top of the filesystem directly. Sql7 discovered that idea only 15 months ago compared to IB, 15 years ago. « The maximum number of bytes in a row is now 8060 bytes, and the limit on character and binary data types is 8000 bytes, increased from 255 bytes. Tables can now have 1024 columns, a significant increase over the 250 columns supported previously. SQL Server also now supports Unicode data types. » Interesting. Cahr/varchar in IB can go to 32 KB, what are the news, Mr. Gates? Binary data? IB blobs give you a run for your money. 1024 columns? Nothing new to IB. Unicode? IB has support for several combinations of charsets and collation orders and this is not exclusive of IB6. Speaking of these numbers, is wise to have a table with 1024 columns? Also, if Sql7 allows for 8KB in each row, each column would have to be 8 bytes of length in average if I use 1024 columns. « Differential backup and recovery » This is a thing that would be attractive in IB. Programming Interfaces « OLE DB/ADO, ODBC and SQL-DMO » It would be a shame if they don't support their own technologies. We'll have OLE/DB and ADO as a natural extension from Jason. We'll have ODBC from Jim. We can't have SQL-DMO because it's specific to SqlServer, but we have GDML. « SQL Server 7.0 for Microsoft Windows® 95/98 and Windows NT Workstation is a fully-featured RDBMS targeted for workstation and mobile applications. » It seems IB always had ran on W9X since it got the first 32-bit version with IB4.1. Nothing impressive here. It only means MS found a way to not consume all the resources on the W9X machines while running hungry-Sql (tm). « SQL Server now uses index intersection and index union to take advantage of multiple indexes in a single query. Earlier versions of SQL Server employed no more than one index per table in a query. » How does IB go here? « Replication Enhancements Microsoft’s goals for SQL Server 7.0 are to provide leadership in distributed solutions, for a large variety of applications for replication. » In the practical scenarios where I was asked to setup replication with Sql7, it worked but produced a lot of error messages in the log file. IB-Replicator is a good SW in its duties, not so ambitious, but it works. « Databases now reside on operating-system files instead of on SQL Server logical devices. You can create a database and all its files with a single CREATE DATABASE statement or use SQL Server Enterprise Manager to create new databases and modify (alter) existing databases. Database files expand automatically, eliminating the need for administrators to issue an additional ALTER statement. A new CREATE DATABASE statement syntax incorporates this file-based implementation. » This sounds like reading the IB manual. In Sql6.X, the size was fixed, now it's dynamic. IB databases grow dynamically but they don't shrink, because expanding a db when the space is allocated internally is faster than asking the OS for more space. « Database files can automatically grow from their originally specified size. When you define a file you can specify a growth increment. Each time the file fills, it increases its size by the growth increment. » That's interesting and I would love to see this option added to IB: when more space is asked to the OS, instead of one page, the number of pages to get could be configurable. « Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. This feature is especially useful when SQL Server is used as a database embedded in an application where the user does not have ready access to a system administrator. » And when all space on disk is used, what happens? Crash, rollback, the next disk is reformatted automatically to have more space? See the second phrase, PLEASE... is Sql7 a kind of embedded database that doesn't need a DBA? « Dynamic Locking, in Which Locking Granularity is Based on Costing Full row-level locking is the default. Dynamically scales to page or table locking if needed » Not much to say, really, IB doesn't need too much effort on locking. It only needs to detect when two transactions conflict while writing data. « The query processor has been redesigned to support the large databases and complex queries found in decision support, data warehouse, and OLAP applications. The query processor includes several new execution strategies that can improve the performance of complex queries. » This is where IB lags behind totally. « SQL Server 7.0 supports parallel execution of a single query across multiple processors. A CPU-bound query that must examine a large number of rows often benefits if portions of its execution plan are run in parallel. SQL Server 7.0 determines automatically which queries will benefit from parallelism and generates a parallel execution plan. If multiple processors are available when the query begins executing, the work is divided across the processors. Parallel query execution is enabled by default. » Instead, IB starts jumping from processor to processor and as result, performance degrades. This is an issue with NT symmetric multiprocessing, because it leaves the task of load balancing to the applications and doesn't care to play ping-pong with a non-SMP-aware application. Sun's SMP support seems better. « New Transact-SQL Statements ALTER PROCEDURE ALTER TRIGGER ALTER VIEW ALTER TABLE BULK INSERT COMMIT WORK [...] » Apart from BULK INSERT, what's the news? « Distributed Queries and Updates SQL Server to SQL Server data sources SQL Server to other OLE DB data sources » Let's take the idea for IB, then. The Sql7 doc explains that the query processor communicates with the storage through OLE/DB, so it can query several OLE/DB repositories. « SELECT [ ALL | DISTINCT ] [ TOP n [PERCENT] [ WITH TIES] ] » This is interesting. If IB has the functionality internally, then stop hiding it! Let's make it available through SQL. « Bytes per index = 900 » No doubt, IB is shorter here, 255 bytes. « Database size 1,048,516 TB » Are they willing to test with 1024 PetaBytes? Probably Oracle, DB/2 and Teradata outperform Sql7 too much at that size? What's about the Sql Server than holds the US maps? « Bytes in source text of a stored procedure = 250 MB » A bit more than IB stored procs. :-) « Files per database = 32,767 File size (data) = 32 TB File size (log) = 4 TB » Files per database is not impressive. Log size doesn't matter, because the only log file in IB is the one that tracks the errors in the server and can be viewed direclty or obtained through the Services API. However, I think finally I found what Luis claimed: if the db can grow dynamically and maximum file size is 32 TB, it's a fact of relying on special support in NT for large files, using 64-bit filepointers. However, I wonder: how many people are using IB store above 300 GB to make this change a high priority one? Sincerely, if I have to choose, I prefer a multi-platform server that asks for 2 GB files instead of an only-NT solution that offers 32 TB files and that doesn't run in any other platform, without saying even that several platforms probably don't support these huge files yet. C. -- --------- Claudio Valderrama C. IT Engineer - Independent consultant - CHILE Owner of the Interbase WebRing http://members.tripod.com/cvalde Alegria, Luis wrote in message <732180E59B22D311B4BB0004AC4CCAE60168CC9B>... > > >For what I know of SQL7 can grow and grow with not limitations >
|
|
This page was last updated on 2000-12-22 04:35:04 |