Tag: "access"

Access Indexverhalten imitieren

Posted on Jul 25, 2005 von in SQL Server

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?

Posted on Jan 18, 2005 von in SQL Server

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)

IIF in T-SQL

Posted on Jul 13, 2004 von in SQL Server

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

Ganze Geschichte »

2 Kommentare »