SQL V6.5 Question


Thread Starter

Jason Burda

I am using SQL V6.5. My v.b. script failed and caused identical records to be inserted into a table, however other code requires that there only be one record in the table. I have since solved the problem by modifying the table and attached a random id field so that each time the insert is done there is something unique in each record. Is there a system/internal variable in SQL that would allow me to distinguish between identical records? Say you have 3 rows of data in a table, how in version 6.5 do you only look at the second or any row in the middle (not first or last)? I know with version 7.0 you can look at specific rows..but I have not yet located info to do this in v6.5

Thanks in advance.


Bill Hullsiek

Actually SQL-2000 is little better at solving this class of problems since it allows you to index views.

To identify duplicate rows
SELECT keys, count(*) followed by a GROUP BY primary_keys HAVING
count(*) > 1

The GROUP and HAVING statements will allow you to identify records with a duplicate primary keys.

From a database schema perspective, look at using a key with a global unique identifier (UID). Check the SQL-Server Books On-line for
ROWGUIDCOL property. I am not too sure if this is supported in v6.5.

- bill hullsiek

Curt Wuollet

Often though, at the lowest level there is some kind of a unique key stored to resolve this and order otherwise identical records. This was always done with C-ISAM files and databases with a C API often allow you to see these, eg ESQL. High level interfaces often do not. This lets the dbms deal with the difference between theory and reality. Take this with a grain of salt as I am not a PE nor do I play one on TV. If your database came with source code the behaviour could be predicted. With your database, only God and MS know.



Michael R. Batchelor

Refusing to store identical records is only correct behavior if there is a unique index or primary key (which is really the same thing) defined for the table. If not, then the table *SHOULD* allow duplicate records. The database should do what you tell it.

Frankly, MS SQL server 6.5 works pretty well. We've got a system in one plant with 11 Intellution HMI's and 8 VB and MS Access desktops which has 247 tables and currently has approximatly 800K records scattered around the various tables. It's running MS SQL Server 6.5 on NT4 SP6a. To date, over 3 years now, there has never been a single failure that can be attributed to the SQL server software.

Michael R. Batchelor - Industrial Informatics & Instrumentation, Inc.
Linux is like a wigwam...
No windows, no gates.
Apache inside.

Anthony Kerstens

Correction Jiri.

"databases should refuse to store identical records" only if a key has been defined for the table, and if that key is set to require

Anthony Kerstens P.Eng.

Hullsiek, William"

SQl Server 6.5 does support unique Primary Keys -- and will raise an exception if you insert identical records. However, that is a database schema (configuration) issue.

For software defects, Microsoft does publish a list of "Known" problems and issues. Generally there is a fix / patch available, othertimes there are defined work arounds.

Check the Microsoft website for more information.

- bill hullsiek
William Hullsiek:
> SQl Server 6.5 does support unique Primary Keys -- and will raise an
> exception if you insert identical records. However, that is a database
> schema (configuration) issue.

One can only surmise that the program was either written by, or intended for, amateurs. In that case, it's the fault of the person who selected the product and then failed to configure it to compensate for this shortcoming.

The database repair tools should certainly handle duplicate primary keys gracefully, if they somehow get in there; but there should be no legal way to insert them.

Jiri Baum <[email protected]> http://www.csse.monash.edu.au/~jirib
MAT LinuxPLC project --- http://mat.sf.net --- Machine Automation Tools

Bill Hullsiek

This is not an issue of closed source vs. open source. This is not an issue of operating system (Posix, Unix, Linux, NT, XP, of whatever). This is not an issue of vendor (Ingres, Postgres, SQL Server, Sybase, mySQL, rBASE, SQL-Anywhere) This is not an issue of API (C-ISAM vs ESQL vs
COM+ vs. CORBA).

This is an issue of relational database design. (Logical vs. Physical).

Suggest people read the definitive works of CODD or CJ Date.
Before suggesting a product or a design is defective.

You will get the same behaviour, irregardless of the vendor or which operating system it is located on.

As usual, your mileage may vary : )

- Bill Hullsiek