SQL Server Skripts für „Die Where-Klausel“

Der Gleichheitsoperator

Künstliche Schlüssel

Das folgende Skript erstellt die EMPLOYEES Tabelle mit 1000 Einträgen.

Eine Kombination aus einem View und Funktion ermöglicht es das „alle Funktionen müssen deterministisch sein“ feature von SQL Server zu umschiffen.

CREATE TABLE employees (
    employee_id   NUMERIC       NOT NULL,
    first_name    VARCHAR(1000) NOT NULL,
    last_name     VARCHAR(900)  NOT NULL,
    date_of_birth DATE                   ,
    phone_number  VARCHAR(1000) NOT NULL,
    junk          CHAR(1000)             ,
    CONSTRAINT employees_pk
       PRIMARY KEY NONCLUSTERED (employee_id)
);
GO

IF OBJECT_ID('rand_helper') IS NOT NULL
   DROP VIEW rand_helper;
GO

CREATE VIEW rand_helper AS SELECT RND=RAND();
GO

IF OBJECT_ID('random_string') IS NOT NULL
   DROP FUNCTION random_string;
GO

CREATE FUNCTION random_string (@maxlen int)
   RETURNS VARCHAR(255)
AS BEGIN
   DECLARE @rv VARCHAR(255)
   DECLARE @loop int
   DECLARE @len int

   SET @len = (SELECT CAST(rnd * (@maxlen-3) AS INT) + 3
                 FROM rand_helper)
   SET @rv = ''
   SET @loop = 0

   WHILE @loop < @len BEGIN
      SET @rv = @rv 
              + CHAR(CAST((SELECT rnd * 26
                             FROM rand_helper) AS INT )+97)
      IF @loop = 0 BEGIN
          SET @rv = UPPER(@rv)
      END
      SET @loop = @loop +1;
   END

   RETURN @rv
END
GO

IF OBJECT_ID('random_date') IS NOT NULL
   DROP FUNCTION random_date;
GO

CREATE FUNCTION random_date (@mindays int, @maxdays int) 
   RETURNS VARCHAR(255)
AS BEGIN
   DECLARE @rv date
   SET @rv = (SELECT GetDate() 
                   - rnd * (@maxdays-@mindays)
                   - @mindays
                FROM rand_helper)
   RETURN @rv
END
GO

IF OBJECT_ID('random_int') IS NOT NULL
   DROP FUNCTION random_int;
GO

CREATE FUNCTION random_int (@min int, @max int)
   RETURNS INT
AS BEGIN
   DECLARE @rv INT
   SET @rv = (SELECT rnd * (@max) + @min
                FROM rand_helper)
   RETURN @rv
END
GO

WITH generator (n) AS
( SELECT 1
   UNION ALL
  SELECT n + 1 FROM generator
WHERE n < 1000
)
INSERT INTO employees (employee_id
                     , first_name, last_name
                     , date_of_birth, phone_number, junk)
select n employee_id
     , [dbo].random_string(11) first_name
     , [dbo].random_string(11) last_name  
     , [dbo].random_date(20*365, 60*365) dob
     , 'N/A' phone
     , 'junk' junk
  from generator
OPTION (MAXRECURSION 1000)
GO

UPDATE employees 
   SET first_name='Markus', 
       last_name='Winand'
 WHERE employee_id=123;

exec sp_updatestats;
GO

Bemerkungen:

• Die JUNK-Spalte wird verwendet, um eine realistische Zeilenlänge zu erhalten. Im Gegensatz zu VARCHAR belegt der CHAR-Datentyp immer den angegebenen Platz ‒ 1000 Byte in diesem Fall. Ohne diese Spalte wäre die Tabelle unrealistisch klein und viele Beispiele würden nicht funktionieren.

• Die Tabelle wird mit Zufallsdaten gefüllt. Danach wird mein Eintrag durch ein update geändert.

• Die Tabellen und Index Statistiken werden erstellt, sodass der Optimizer einen Überblick über den Tabelleninhalt hat.

• SQL Server 2008R2 hat eine Beschränkung von 900 byte, daher wurde die länge der LAST_NAME Spalte auf 900 Byte reduziert.

Zusammengesetzte Schlüssel

Das nächste Skript ändert die EMPLOYEES Tabelle so, dass sie die Situation nach der Zusammenlegung mit „Sehr Große Firma“ widerspiegelt.

ALTER TABLE employees ADD subsidiary_id NUMERIC;
GO
UPDATE      employees SET subsidiary_id = 30;
GO
ALTER TABLE employees ALTER COLUMN subsidiary_id 
                                   NUMERIC NOT NULL;
GO

ALTER TABLE employees DROP CONSTRAINT employees_pk;
GO
ALTER TABLE employees ADD  CONSTRAINT employees_pk 
      PRIMARY KEY NONCLUSTERED (employee_id, subsidiary_id);
GO


WITH generator (n) as
( select 1
union all
select n + 1 from generator
where N < 9000
)
INSERT INTO employees (employee_id
                     , first_name, last_name
                     , date_of_birth, phone_number
                     , junk, subsidiary_id)
SELECT n employee_id
     , [dbo].random_string(11) first_name
     , [dbo].random_string(11) last_name  
     , [dbo].random_date(20*365, 60*365) dob
     , 'N/A' phone
     , 'junk' junk
     , [dbo].random_int(1, (n*29)/9000) subsidiary_id
  FROM generator
OPTION (MAXRECURSION 9000)
GO

CREATE UNIQUE NONCLUSTERED INDEX 
       employees_pk_tmp 
       on employees (employee_id, subsidiary_id);
GO
ALTER TABLE employees DROP CONSTRAINT employees_pk;
GO
ALTER TABLE employees ADD CONSTRAINT employees_pk
      PRIMARY KEY NONCLUSTERED (employee_id, subsidiary_id);
GO
DROP INDEX employees_pk_tmp ON employees;
GO

exec sp_updatestats;
GO

Bemerkungen:

• Der Primärschlüssel wurde um die SUBSIDIARY_ID Spalte erweitert. Die EMPLOYEE_ID Spalte bleibt an erster Stelle.

• Die neuen Einträge werden zufällig auf die Töchter 1 bis 29 aufgeteilt.

• Die Tabelle und Indizes werden neu analysiert, damit der Optimizer das neue Mengengerüst kennt.

Das nächste Skript erstellt den Index auf SUBSIDIARY_ID, um Abfragen nach allen Angestellten eines bestimmten Subunternehmens zu unterstützen:

CREATE NONCLUSTERED INDEX 
       emp_sub_id ON employees (subsidiary_id);

exec sp_updatestats;

Bemerkungen:

• Die Tabelle und Indizes werden erneut analysiert.

Obwohl der Index gute Antwortzeiten ermöglicht, ist es besser den Primärschlüssel-Index umzubauen, sodass er diese Abfrage ebenfalls unterstützen kann:

ALTER TABLE employees DROP CONSTRAINT employees_pk;
GO
ALTER TABLE employees ADD  CONSTRAINT employees_pk 
      PRIMARY KEY NONCLUSTERED (subsidiary_id, employee_id);
GO

DROP INDEX emp_sub_id ON employees;

exec sp_updatestats;

Bemerkungen:

• Die Tabelle bleibt temporär ohne Primärschlüssel.

  Das Bedeutet, dass das Procedere nicht online durchgeführt werden kann.

• Der Index auf SUBSIDIARY_ID ist nun redundant und kann gelöscht werden.

Funktionen

In der Standardkonfiguration ignoriert SQL Server die Groß- und Kleinschreibung automatisch, daher ist in diesem Fall ein gewöhnlicher Index ausreichend:

CREATE INDEX emp_name ON employees (last_name);