Microsoft SQL Server 2000 Partitionen
Da Wochenende war kam bei mir wiedermal der Spieltrieb durch.
Ich wollte mir schon immer mal die Partitionierung von Tabellen im SQL Server 2000 näher anschauen.
Vorweg sei zu erwähnen das die Paritionen erst mit SQL 2005 halbwegs vernünftig implementiert sind.
Partitionen in SQL 2000 (oder gar SQL 7) haben folgende (gravierende Nachteile):
- Es müssen viele einzelene Tabellen angelegt bzw. verwaltet werden.
- Bei INSERT-Anweisungen müssen immer alle Felder angegeben werden, auch wenn diese Default-Werte haben.
- Die Tabellen dürfen keine Spalte vom Typ “timestamp” haben.
- Die Tabellen dürfen keine Autoinkrement (Identitäts)-Spalte haben.
- Weitere Nachteile (zBspl. bei UPDATE) sind dem MSDN zuentnehmen.
Ok, das waren die Nachteile, evtl. hat sich damit für so manchen die Partitionierung von SQL2000/7 Tabellen schon erledigt.
Nichts destotrotz gehts weiter mit etwas Theorie:
Die Partitionierung funktioniert über mehrere Tabellen, die nur definierte Daten beinhalten dürfen. Diese “Definition” wird über eine CHECK Einschränkung sichergestellt. Hat man die Tabellen angelegt wird eine Sicht erstellt die sich über alle Tabellen erstreckt und in etwa wie folgt aussieht:
SELECT * FROM dbo.part0
UNION ALL
SELECT * FROM dbo.part1
UNION ALL
SELECT * FROM dbo.part2
....
Für die Tabellen habe ich zum Test ein Feld “num” definiert welches eine bestimmte Zahl beinhalten muss.
Es gibt, in meinem Test, vier Tabellen (part0 .. part3). Jede Tabelle hat folgende Felder: num, id, text. Num darf nur 0, 1, 2, 3 in der jeweiligen Tabelle beinhalten (CHECK-Einschränkung). Das Feld id ist irgendeine eindeutige Zahl. Das Feld text hat keine Bedeutung. num und id sind Primärschlüssel.
Hier die CREATE Anweisung für Tabelle “part0″:
CREATE TABLE [dbo].[part0] (
[num] [int] NOT NULL ,
[id] [int] NOT NULL ,
[text] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PART0]
GO
ALTER TABLE [dbo].[part0] WITH NOCHECK ADD
CONSTRAINT [PK_part0] PRIMARY KEY CLUSTERED
(
[num],
[id]
) ON [PART0]
GO
ALTER TABLE [dbo].[part0] ADD
CONSTRAINT [CK_part0] CHECK ([num] = 0)
GO
Der Primärschlüssel muss so gewählt sein das er in jeder Tabelle immer Eindeutig ist.
Nachdem die Tabellen angelegt wurden ist noch die bereits oben beschriebene Sicht zu erstellen:
CREATE VIEW dbo.part
AS
SELECT *
FROM dbo.part0
UNION ALL
SELECT *
FROM dbo.part1
UNION ALL
SELECT *
FROM dbo.part2
UNION ALL
SELECT *
FROM dbo.part3
Die jeweilige Anwendung verwendet die Sicht “part” um Daten abzurufen, anzulegen, zu löschen bzw. zu Modifizieren:
SELECT * FROM part WHERE num = 1
INSERT INTO part (num, id, text) VALUES (1, 2, 'text..')
UPDATE part SET text = 'mehr ' + text WHERE num = 1 AND id = 2
DELETE FROM part WHERE num = 1 AND id = 2
Soweit so gut. Nun stellt sich natürlich die Frage: “Was bringts?”.
Um das zu beantworten gilt es den “SQL Query Analyzer” zu befragen:
Es sieht hier so aus als ob der SQL Server alle Tabellen anfragen würde. Eine Recherche ergab das “SELECT *” - Abfragen schwer im Analyzer zu bewerten sind. Abhilfe schafft hier “SELECT TOP 100 PERCENT *”:
Man sieht genau das was zu erwarten war. Es wird nur eine Tabelle (part1 - “num = 1″) abgefragt statt alle.
Aber die Frage “was es bringt” ist immer noch nicht beantwortet.
Aufgrund der Tatsache das die Tabellen nach dem Erstellen keine bzw. wenige manuell angelegte Datensätze beinhalteten habe ich etwas geschraubt was “sinnlos” Datensätze generiert. Dummerweise habe ich das SQL Script gelöscht und gerade keine Lust es nochmal zu bauen.
Die schiere Menge der Daten, die nach 2 Tagen Laufzeit, generiert wurden bitte ich dem folgenden Screenshot zu entnehmen:
Wieviele Datensätze genau vorhanden sind kann ich nicht wirklich beantworten weil ich keine Lust habe Stunden zu warten bis ein “SELECT num, count (*) as Anzahl FROM part GROUP BY num” fertig ist.
Was ich mit Sicherheit sagen kann ist das die Tabelle “part1″ genau zwei Datensätze beinhaltet und SQL-Abfragen gegen diese Tabelle “..WHERE num = 1..” gewohnt schnell ablaufen.
Fazit:
Partitionen - in SQL 2000 - sind toll, haben einen hohen Spiel-Faktor aber reinpraktisch gesehen keinen Sinn.
Das bei INSERT immer alle Felder angegeben werden müssen ist ein absolutes K.O.-Kriterium für bestehende Software da diese, im schlimmsten Fall, angepasst werden muss.
Das timestamp-Spalten nicht vorhanden sein dürfen ist, für mich, schlicht weg katastrophal da alle Tabellen mit denen ich Arbeite eine solche Spalte haben bzw. haben müssen.