mssql varchar als pk -gefahren

tenim

ich bin gestern über eine eigenart des ms-ssql servers gestolpert, die es in sich hat:

wenn man ein varchar-feld als primärschlüssel definiert und eine fremdschlüsselbziehung zu einer anderen tabelle damit aufbaut, unterscheidet mss-sql NICHT zwischen z.b.

"A" und "A "

d.h. jedes nicht druckbare zeichen wird ignoriert und 2 felder gelten immer als gleich wenn ausschliesslich deren druckbare zeichen in ihrer reihenfolge übereinstimmen. ich finde dieses verhalten extrem scheisse, vor allem weil ich in meiner db varchar pk´s brauche.

wusste das jemand von euch? ist das bei anderen rdbms auch so? was für einen sinn macht das?

muemmel

Hi Tenim,

tenim schrieb:

d.h. jedes nicht druckbare zeichen wird ignoriert und 2 felder gelten immer als gleich wenn ausschliesslich deren druckbare zeichen in ihrer reihenfolge übereinstimmen.

Nicht ganz, "A A" ist nicht gleich "AA".
Varchar-Felder sind nun mal so angelegt, dass nur die benötigzten Zeichen abgelegt werden und nicht wie bei Char mit Leerzeichen aufgefüllt wird.
Also wird sinnierweise das Feld vor dem Vergleichbilden getrimmt.
Wenn Du unbedingt das komplette Feld brauchst, dann schreib es in ein weiters und hänge ein konstantes Endekennzeichen dran, z.B. "|" und nimm das dann als Index.
Ich würde mich aber nie darauf verlassen, dass Leerzeichen am Ende beachtet werden.
Eventuell kannst Du aber mit Strkomp oder len noch was an zusätzlichen iNformationen raubekommen.

Gruß Mümmel

hustbaer

tenim schrieb:

wusste das jemand von euch?

Ja, hier, *winkwink*.

tenim schrieb:

ist das bei anderen rdbms auch so?

Vermutlich, weil...

tenim schrieb:

was für einen sinn macht das?

...der SQL Standard schreibt das so vor. Siehe z.B. https://support.microsoft.com/en-us/kb/316626

Die Regel stammt vermutlich aus Zeiten wo viele Server noch keine varchar Felder unterstützt haben bzw. die Verwendung von varchar einfach noch "unüblich" war. Und wenn man kein varchar hat, ist die Regel einfach sehr angenehm. (Bzw. wäre es umgekehrt sehr lästig wenn es die Regel NICHT gäbe.)

Nebenbei bemerkt gibt es auch noch weitere Dinge bei String-Vergleichen die den einen oder anderen überraschen könnten. Probier' z.B. folgendes

DECLARE @x VARCHAR(100);
DECLARE @y VARCHAR(100);

SET @x = 'müss';
SET @y = 'muß';

IF (@x COLLATE Latin1_General_CI_AI) = (@y COLLATE Latin1_General_CI_AI) PRINT 'EQUAL' ELSE PRINT 'NOT-EQUAL';

hustbaer

ps: Wenn du keine "String-Vergleiche" brauchst, sondern mit binären Vergleichen auskommst, dann könntest du einfach varbinary statt varchar verwenden.

Was nebenbei auch ne Grössenordnung schneller beim Vergleichen/Sortieren/Joinen ist (erforderliche CPU-Leistung, wenn die Query disk-bound ist wird es natürlich nicht so viel Unterschied machen).

tenim

danke für das andere beispiel. man lernt nie aus. aber ohne collate funktioniert´s ja zum glück.

wegen dem varbinary-ersatz: geht leider nicht, da in meiner tabelle proben-id´s gespeichert werden, welche spezielle gewebe,blut o.ä. proben von patienten darstellen. das format ist immer gleich. z.b. "H099-ADFG-T1-D1". das wäre dann projekt "H099", patient "ADFG" (pseudonymisiert), "T1"=gewebe, "D1"=erste dna-extraktion.
jede probe ist eindeutig und kommt in der tabelle "samples" exakt einmal vor (probeneingang). wenn jetzt mit den proben gearbeitet wird, kommen die in eine andere tabelle (welche die eigenheiten des arbetsschritts abdeckt). dabei können die user die proben aus der tabelle "samples" auswählen, aber auch über direkteingabe oder excel-import (das hat das problem mit dem leerzeichen verursacht) proben der neuen tabelle hinzufügen. und zwischen diesen beiden tabellen hab ich die PK<->FK beziehnung, damit die leute eben keine proben in die zweite tabelle reinhauen können, die es in der samples-tabelle nicht gibt. den excel-import hatte ich bisher nicht gefiltert/geprüft weil ich eben dem fk-constraint vertraute. hab jetzt überall einen syntax-check eingebaut, so das es jetzt kein problem mehr darstellt.

hustbaer

tenim schrieb:

danke für das andere beispiel. man lernt nie aus. aber ohne collate funktioniert´s ja zum glück.

Nicht wenn die Collation deines Servers Latin1_General_CI_AI ist.

tenim schrieb:

wegen dem varbinary-ersatz: geht leider nicht, da (...)

Naja, so wie du das beschreibst ist das mit den Leerzeichen dann ja sowieso kein Problem. Würde mich zumindest wundern wenn Whitespaces in diese Keys ala "H099-ADFG-T1-D1" überhaupt erlaubt wären.
Oder geht es dir darum dass in der FK Spalte nicht "H099-ADFG-T1-D1 " stehen darf wenn "H099-ADFG-T1-D1" gemeint ist? Das liesse sich einfach vermeiden indem du die Werte RTRIM()st bevor du sie einfügst.

tenim

bei der probenbezeichnung sind leerzeichen nicht erlaubt. und ich prüfe vor einfügen in die samples-tabelle (master-tabelle) auch mit einem regulären ausdruck ob der probencode stimmt. das aber hatte ich vor einfügen in die fk-tabelle unterlassen, weil ich halt dachte der fk-constraint verhindert das (da ja in der fk tabelle nur proben reinkommen können die auch in der samples tabelle vorkommen). jetzt prüfe ich jede art des datenimports vorher ab.