SQL Select gesucht - IP Adresse zwischen IP Min und IP Max

[jensw_2000]

Wenn es Dir um die Datenbankgröße geht, dann nimm BigInt (feste Größe 4 Byte).
VarChar(n) benötigt einen Speicherplatz von (2 * n Byte) + 2 Byte.

Wenn es nicht um die DB Größe geht ist das reine Geschmackssache.
Überhaupt in der DB speichern? Ja klar. Immer rein damit.

Bei den Minigrößen ist es performanceseitig absolut egal ob BigInt oder VarChar. Auch das bisschen Rechnen in den Funktionen macht den MSSQL garantiert nicht lahm.

Ich würde die Werte vermutlich nicht als BigInt speichern, wenn die IP Adressen aus der Anwendung oft abgerufen und dargestellt werden müssen.

Du kannst meine SQL Funktion IPStringToInt verwenden, um IP Adressen lesbar per String Parameter zu übergeben und als BigInt zu speichern (oder Dir eine eigene, bessere UDF bauen).


PS:
Huch, falsch...
NVarChar(n) benötigt (2 * n Byte) + 2 Byte. Bei VarChar(n) ist es nur (n Byte) + 2 Byte.

[jobo]

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

BigInt, VarChar, NVarChar, VarChar

Also wenn man sich schon darüber Gedanken macht, möchte ich dann noch den guten alten Char vorschlagen und mindestens von NVarchar abraten, das wäre wirklich Verschwendung. (Außer man komm in die Verlegenheit, die IP in chinesischen Zahlzeichen notieren zu müssen)

[jensw_2000]

Zitat:

Zitat von jobo (Beitrag 1282808)

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

BigInt, VarChar, NVarChar, VarChar

Also wenn man sich schon darüber Gedanken macht, möchte ich dann noch den guten alten Char vorschlagen und mindestens von NVarchar abraten, das wäre wirklich Verschwendung. (Außer man komm in die Verlegenheit, die IP in chinesischen Zahlzeichen notieren zu müssen)

Richtig. NVarChar ist Unsinn, stand nie wirklich zur Debatte.
Char(15) wäre bei mir aber auch sofort durchgefallen, weil dort jeder abgerufene Datensatz manuell durch die Trim Funktion laufen müsste, bevor die Daten richtig nutzbar werden.

[Dejan Vu]

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

Wenn es Dir um die Datenbankgröße geht, dann nimm BigInt (feste Größe 4 Byte).

Hüstel. 8.

Wenn über IP-Adressen gesucht wird, und auch über -Bereiche, schreit das erst einmal nach int, CHAR(4) oder BINARY(4). Allerdings würde ich CHAR wirklich nicht empfehlen, da es -abhängig von der Codepage und der Collation bei der Darstellung zu Problemen führen kann. BINARY und INT werden dagegen nicht interpretiert.

INT hat den Vorteil, das man auch auch (bit-)maskieren kann. BINARY hat den Vorteil, das man eine Ordnung aufbauen kann (Index, ORDER BY) und das in der Darstellung (reines SELECT auf dem Server) die Octets sichtbar werden, da im SSMS dieser Datentyp in hex dargestellt wird. Ich tendiere zur Speicherung als BINARY(4), weil das dem Grundformat am nächsten kommt (4 Bytes und eben kein Integer)

Hier eine T-SQL Funktion, die einen String in ein Binary(4) umwandelt:

Code:

create function IPToBinary (@IP VarChar(15))
returns binary (4)
as
begin
  declare @b1 tinyint, @b2 tinyint, @b3 tinyint, @b4 tinyint
  set @b4= cast(PARSENAME(@IP, 4) as tinyint)
  set @b3= cast(PARSENAME(@IP, 3) as tinyint)
  set @b2= cast(PARSENAME(@IP, 2) as tinyint)
  set @b1= cast(PARSENAME(@IP, 1) as tinyint)
  return cast(char(@b4)+char(@b3)+char(@b2)+char(@b1) as binary(4))
end

In deiner Tabelle deklarierst Du dann z.B. so:

Code:

CREATE TABLE [dbo].[MyComputers](
   [IPAddress]  AS ([dbo].[IPToBinary]([IPAddressString])),
   [IPAddressString] [varchar](15) NULL
) ON [PRIMARY]

Du findest dann alle PC im Bereich '123.123.123.0' und '123.124.123.255' mit

Code:

select * 
  from MyComputers
  where IPAddress between dbo.IPToBinary('123.123.123.0') and dbo.IPToBinary ('123.124.123.255')

[jensw_2000]

Ja. Char(4) ist wegen der Codepage und vieler anderer Sachen indiskutabel.
INT ist zu klein. Da passen laut Adam Ries keine 4 Byte rein.

Bei BIGINT vs BINARY(4) musst Du mir mal helfen.
Die sind ja beide exakt 4 Byte groß. Platz spart man damit also nicht (auch wenn die Speicherplatzdiskussion eigentlich nur ein Randthema ist).
Was kann das BINARY Format belegbar besser als das BIGINT Format?
Schneller indizierbar? Schneller abrufbar? Schneller konvertierbar? Besser lesbar?
Da stehe ich ein bisschen auf dem Schlauch ...
Sag mal, warum Du das bevorzugst.

[Zacherl]

Hat er doch schon geschrieben :stupid:

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

Bei BIGINT vs BINARY(4) musst Du mir mal helfen.
Die sind ja beide exakt 4 Byte groß. Platz spart man damit also nicht (auch wenn die Speicherplatzdiskussion eigentlich nur ein Randthema ist).

Zitat:

Zitat von Dejan Vu (Beitrag 1282812)

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

Wenn es Dir um die Datenbankgröße geht, dann nimm BigInt (feste Größe 4 Byte).

Hüstel. 8.

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

Was kann das BINARY Format belegbar besser als das BIGINT Format? [..] Besser lesbar?

Zitat:

Zitat von Dejan Vu (Beitrag 1282812)

BINARY hat den Vorteil, das man eine Ordnung aufbauen kann (Index, ORDER BY) und das in der Darstellung (reines SELECT auf dem Server) die Octets sichtbar werden, da im SSMS dieser Datentyp in hex dargestellt wird. Ich tendiere zur Speicherung als BINARY(4), weil das dem Grundformat am nächsten kommt (4 Bytes und eben kein Integer)

[jensw_2000]

Zitat:

Zitat von Zacherl (Beitrag 1282815)

Hat er doch schon geschrieben :stupid:

Ja, ich brauche noch einen Kaffee.

Dann ist der Vorteil also, dass die binär gespeicherte IP Adresse der abgefragten Ergebnismenge in einigen SQL Editoren als A3.8B.23.F4 angezeigt wird, in Anderen als BLOB(binary) und in wieder Anderen als "1100100...101011101"?
Sortieren und Indizieren mann man Integer, glaube ich, auch ganz gut :stupid:.

[Dejan Vu]

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

INT ist zu klein. Da passen laut Adam Ries keine 4 Byte rein.

:gruebel: Bist Du dir da wirklich sicher? ;-)

Zitat:

Was kann das BINARY Format belegbar besser als das BIGINT Format?

(BigInt=8 Bytes, siehe Link :mrgreen:) Ich muss hier ja eigentlich nichts belegen, aber... IP-Adressen sind als 4 einzelne Bytes definiert, nicht als eine Zahl. Und für die Repräsentation liegt es daher nahe, genau diese 4 einzelnen Bytes abzulegen und eben nicht eine Zahl. Ob das Format irgend etwas 'belegbar' (oder unbelegbar) besser kann, ist mir hier egal, denn auf beiden Formaten kann ich eine totale Ordnung aufbauen, ergo Bereiche abfragen, sortieren usw.

Wenn Dir Int (bzw. BigInt) lieber ist, bitte sehr. Ich schrieb doch bereits, warum mir der binary-Datentyp hier eher zusagt. Oder vielleicht nicht: INT ist eine Interpretation der 4 Bytes, Binary(4) dagegen nicht. Vielleicht wird es jetzt klarer: Ein rein ästhetischer Grund. Mich persönlich stört bei 'int' das Vorzeichen. Was hat ein '-' mit einer IP-Adresse am Hut? :stupid:

Du kannst auch NChar(2) nehmen, wenn Du es lustig findest. Oder SmallDateTime. Oder Float(1)...Float(24), geht auch (müssten auch 4 Bytes sein). Such Dir was aus, das sind alles Interpretationen von 4-Byte großen Informationen.
Ich würde Binary nehmen. So wie ich Milch und Zucker zum Kaffee nehme (wo wir schon beim Thema sind).

[jensw_2000]

Wirklich, mir ist es auch egal für welche Lösung er sich entscheidet oder ob er überhaupt eine davon nimmt.
Du hast auch recht damit, dass Binary(4) das effektivste Format ist, um die 32 Bit große IP in der DB zu speichern.
Das Vergleichen gegen ein extrem teuer indizierbares berechnetes Feld ist aber ineffektiver Bockmist.

Zitat:

Zitat von Dejan Vu

In deiner Tabelle deklarierst Du dann z.B. so:
markieren Code:

Code:

CREATE TABLE [dbo].[MyComputers](
   [IPAddress] AS ([dbo].[IPToBinary]([IPAddressString])),
   [IPAddressString] [varchar](15) NULL
) ON [PRIMARY]

Zitat:

Zitat von Dejan Vu

Du findest dann alle PC im Bereich '123.123.123.0' und '123.124.123.255' mit
markieren Code:

Code:

select * 
  from MyComputers
  where IPAddress between dbo.IPToBinary('123.123.123.0') and dbo.IPToBinary ('123.124.123.255')
[/QUOTE]

Zudem glaube ich (ohne das jetzt prüfen oder nachlesen zu wollen), dass Delphi ein Binary Feld nicht direkt auslesen kann. Falls die Daten mal außerhalb der DB, in seiner Delphi Anwendung genutzt werden sollen, dann wird er das Feld vermutlich (wie ein Blob) krampfig über einen Stream auslesen müssen. BigInt ginge direkt.

Ich werde mein DP Konto nach diesem Post löschen.
Jedesmal wenn ich mir die Arbeit mache, um für irgendwelche Fragestellungen eine funktionierende Lösung zu erarbeiten und zu posten, habe ich danach stundenlange Klarstellungen und Längenvergleiche am Hals. Seit Jahren findet sich immer jemand, der in allen Posts irgendwelche Bagatellfehler sucht und die investierte Arbeit danach ins Absurdum dreht.
Ich will meine Zeit zukünftig nicht mehr so sinnlos verschwenden.

Ein paar kleine Sachen noch:

Zitat:

Zitat von Dejan Vu (Beitrag 1282857)

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

INT ist zu klein. Da passen laut Adam Ries keine 4 Byte rein.

:gruebel: Bist Du dir da wirklich sicher? ;-)

Zitat:

Zitat von Dejan Vu (Beitrag 1282857)

Mich persönlich stört bei 'int' das Vorzeichen. Was hat ein '-' mit einer IP-Adresse am Hut? :stupid:

Zitat:

Zitat von Dejan Vu (Beitrag 1282857)

(BigInt=8 Bytes, siehe Link :mrgreen:) Ich muss hier ja eigentlich nichts belegen, aber... IP-Adressen sind als 4 einzelne Bytes definiert, nicht als eine Zahl.

Du hast den richtigen MSDN Artikel gefunden, hast gesehen das der INT Datentyp 4 Byte groß ist, hast auch gesehen INT nicht vorzeichenlos ist, und, Dir ist bewusst, dass IP Adressen per se nicht negativ werden können.

Dann ist deine Aussage "INT geht" fachlich einfach falsch, wenn Du nicht zeitgleich auf die passenden Shift Funktionen verweist.
Der INT hat 2 Byte im positiven und 2 Byte im negativen Bereich.
In dem 2 Byte großen positiven Bereich kann man nun mal keine 4 Byte große positive Zahl hineinschreiben.
Probiere es aus, schau Dir die Exception an und lerne.

Wenn Dein "4 Byte Binary passt natürlich easy in einen 4 Byte INT Datentyp" ein Einsteiger liest, dann bringst Du den zum Verzweifeln. Der glaubt er macht was Anderes falsch, wenn der SQL Server ihm eine "Value out of Range" Exception an den Kopf wirft.
Und ehrlich, alle berechneten Werte nochmal shiften, damit sie doch irgendwie in den INT hineinpassen ... Das ist für mich eher eine Notlösung als eine Lösung.

So, zerreiß meine Klarstellung in der Luft. Ich bin dann mal weg.

[Piro]

Danke an alle.
Ich werde die Lösung von jensw_2000 nehmen, da ich nämlich tatsächlich sehr oft auf die IP Werte in lesbarer Form zugreifen muss.

Auch wenn jensw_2000 leider weg ist, finde ich diese Forum sehr hilfreich.

Schönen Abend noch.