Frank Kalis

Microsoft Access bietet die Option "Nullwerte ignorieren" bei Indices an. Wie kann man das auf den SQL Server übertragen?...

Die schlechte Nachricht gleich vorweg: Ein direktes Äquivalent existiert im SQL Server nicht! Erstellt man einen Index als UNIQUE, kann dieser genau einmal einen NULL Marker enthalten. Beispiel:

CREATE TABLE Table1
(cID INT PRIMARY KEY
 , c1 INT NULL)
CREATE UNIQUE NONCLUSTERED INDEX ix_test ON Table1(c1)
INSERT INTO Table1 
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL 
SELECT 3,NULL
DROP TABLE Table1

Server: Msg 2601, Level 14, State 3, Line 4
Cannot insert duplicate key row in object 'Table1' with unique index 'ix_test'.
The statement has been terminated.

Einen Index kann man also nicht nutzen. Auch nicht besser sieht es mit einer UNIQUE Einschränkung aus:

CREATE TABLE Table1
(cID INT PRIMARY KEY
 , c1 INT NULL
 CONSTRAINT c_c1 UNIQUE)
INSERT INTO Table1 
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL 
SELECT 3,NULL
DROP TABLE Table1

Server: Msg 2627, Level 14, State 2, Line 4
Violation of UNIQUE KEY constraint 'c_c1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.

Um es abzukürzen - und nochmals zu wiederholen - eine eingebaute Möglichkeit gibt es nicht. Man muß also selber tätig werden. 4 Möglichkeiten stehen hierfür zur Verfügung.

1. Einen Indexed View benutzen.

SET NOCOUNT ON
CREATE TABLE Table1
(cID INT PRIMARY KEY
 , c1 INT NULL)
GO
CREATE VIEW Table1_Unique_Non_NULL
 WITH SCHEMABINDING
 AS
  SELECT c1
   FROM dbo.Table1
   WHERE c1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uc1 ON Table1_Unique_Non_NULL(c1)
INSERT INTO Table1 
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL 
SELECT 3,NULL
SET NOCOUNT OFF
SELECT * FROM Table1_Unique_Non_NULL
DROP VIEW dbo.Table1_Unique_Non_NULL
DROP TABLE Table1

c1          
----------- 
1

(1 row(s) affected)

Wie man sieht, wird der 2.te NULL Marker widerspruchslos in die Tabelle eingefügt. Versucht man jedoch:

INSERT INTO Table1 SELECT 4,1

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'Table1_Unique_Non_NULL' with unique index 'uc1'.
The statement has been terminated.

2. Eine Einschränkung auf eine berechneten Spalte verwenden.

SET NOCOUNT ON
CREATE TABLE Table1 
(cID INTEGER PRIMARY KEY 
 , c1 INTEGER NULL
 , foolme AS (CASE WHEN c1 IS NULL THEN cID END)
 , CONSTRAINT cc1 UNIQUE (c1,foolme)
)

INSERT INTO Table1 
SELECT 1, 1
UNION ALL
SELECT 2, NULL
UNION ALL 
SELECT 3, NULL
SET NOCOUNT OFF
SELECT * FROM Table1
DROP TABLE Table1

cID         c1          foolme      
----------- ----------- ----------- 
2           NULL        2
3           NULL        3
1           1           NULL

(3 row(s) affected)

Und auch hier führt:

INSERT INTO Table1 SELECT 4, 1

Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'cc1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.

3. Einen Trigger verwenden:

SET NOCOUNT ON
CREATE TABLE Table1 
(cID INTEGER PRIMARY KEY 
 , c1 INTEGER NULL
)
GO
CREATE TRIGGER IgnoreNulls
ON Table1 FOR UPDATE, INSERT
AS
  IF EXISTS
   (SELECT *
    FROM Inserted AS I
    JOIN Table1 AS t1
     ON I.c1 = t1.c1 AND I.cID<>t1.cID)
   BEGIN
    ROLLBACK TRAN
    RAISERROR('Nene...is nicht!',16,1)
   END
GO
INSERT INTO Table1 
SELECT 1, 1
UNION ALL
SELECT 2, NULL
UNION ALL 
SELECT 3, NULL
SET NOCOUNT OFF
SELECT * FROM Table1
DROP TABLE Table1

cID         c1          
----------- ----------- 
1           1
2           NULL
3           NULL

(3 row(s) affected)

Und auch hier der vollständigkeithalber die Probe

INSERT INTO Table1 SELECT 4, 1

Server: Msg 50000, Level 16, State 1, Procedure IgnoreNulls, Line 11
Nene...is nicht!

Vereinzelt findet man auch noch eine vierte Methode erwähnt. Die entsprechende Spalte in eine eigene Tabelle zu überführen, sie zum Primärschlüssel dieser Tabelle machen und eine 1:1 Beziehung zwischen beiden Tabelle einrichten. Diese Methode möchte ich hier nicht weiter besprechen. IMHO ist dies nur ein gangbarer Weg, wenn man dies direkt beim Design der Datenbank und der Applikation berücksichtigt. Eine nachträgliche Änderung, um dieses Indexverhalten zu erzielen, ist mit einem nicht zu unterschätzenden Aufwand verbunden.

Die Reihenfolge, in welcher die verschiedenen Ansätze erwähnt wurden, stellt gleichzeitig eine gewisse Wertung dar. IMHO erscheint der 1. Ansatz mit dem Indexed View am geeignetsten. Der Index sollte das Ganze recht schnell machen und ausserdem läßt sich der View wiederverwenden. Der Index auf der berechneten Spalte sollte ebenfalls noch eine recht gute Performance bringen. Das Schlußlicht wird der Trigger sein.

First() und Last() in T-SQL?

Sehr häufig kann man Fragen in SQL Server Communities beobachten von Leuten, die Ihre Access Datenbanken auf den SQL Server portieren und anschließend versuchen, die proprietäre Access SQL Syntax in T-SQL zu konvertieren. Eine der beliebtesten Fragen in diesem Zusammenhang, ist die Frage, nach dem T-SQL Äquivalent zu First() und Last(). Nun, einfache Antwort: Es gibt kein direktes Äquivalent! Aber es gibt einen Workaround, um das gleiche Ergebnis zu erzielen:

USE NORTHWIND
GO
SELECT
 (SELECT
   OrderDate
 FROM
  Orders
 WHERE
  OrderID =
   (SELECT MIN(OrderID)
    FROM Orders)) AS First_Order
 ,
 (SELECT
   OrderDate
  FROM
   Orders
  WHERE
   OrderID =
    (SELECT MAX(OrderID)
    FROM Orders)) AS Last_Order

First_Order                                            Last_Order
------------------------------------------------------ -----------------------
1996-07-04 00:00:00.000                                1998-05-06 00:00:00.000

(1 row(s) affected)

Dies ist eine oft gestellte Frage in SQL Server Communities. Die Antwort lautet...

Ganze Geschichte »