MS SQL Server

Commands

Anbei ein paar SQL Server spezifische Commands.

/* Entspricht SET CURRENT SQLID von DB2 */
USE [MYDATABASE]

/* Resultate einschränken */
SELECT TOP 1000 *
  FROM [dbo].[MyTable]

/* Insert into */
USE [MYDB];
INSERT INTO [dbo].[MYTable] (
       [Col1]
      ,[Col2]) SELECT [Col1], [Col2] FROM [dbo].[MyTable2]

/* Read uncommited */
SELECT *   FROM dbo.MyTable WITH(READUNCOMMITTED)

/* Spalten-Alias */
SELECT [MyAlias1] = [Col1], 
       [MyAlias1] = [Col2]
                FROM dbo.MyTable

/* EXISTS Beispiel. Die beiden Abfragen sind semantisch identisch */
USE AdventureWorks2008R2 ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE EXISTS
(SELECT * 
    FROM HumanResources.Employee AS b
    WHERE a.BusinessEntityID = b.BusinessEntityID
    AND a.LastName = 'Johnson');
GO
USE AdventureWorks2008R2 ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE a.LastName IN
(SELECT a.LastName
    FROM HumanResources.Employee AS b
    WHERE a.BusinessEntityID = b.BusinessEntityID
    AND a.LastName = 'Johnson');
GO

CTE Common Table Expressions

Eignen sich für rekursive Queries.

;WITH Calculate_CTE (Datum, WeekAdded)
AS
(
    -- Anchor 
    SELECT Datum     = dbo.fnDateOf(GETDATE()),
           WeekAdded = 0
    

    UNION ALL

    -- Recursion    
    SELECT Datum     = DATEADD(week, 1, CTE.Datum),
           WeekAdded = CTE.WeekAdded + 1
    FROM Calculate_CTE CTE
    WHERE CTE.WeekAdded < 10
)
SELECT Datum     = CTE.Datum,
       WeekAdded = CTE.WeekAdded
FROM Calculate_CTE CTE;

Vom Blatt zur Wurzel in einer Hierarchie:

;WITH Abteilung (Id, ParentId, LEVEL)
AS
(
    -- Anchor 
    SELECT ORG.OrgUnitID, ORG.ParentId, 0
    FROM dbo.XOrgUnit ORG
    WHERE ORG.OrgUnitID = 10
 
    UNION ALL
 
    -- Recursion
    SELECT ORG.OrgUnitID, ORG.ParentId, LEVEL + 1
    FROM dbo.XOrgUnit ORG
    INNER JOIN Abteilung ABT ON ABT.ParentId = ORG.OrgUnitID 
           
)
SELECT ABT.Id,
       ABT.ParentId,
       LEVEL
FROM Abteilung ABT;

Von der Wurzel zum Blatt:

;WITH Abteilung (Id, ParentId, LEVEL)
AS
(
    -- Anchor 
    SELECT ORG.OrgUnitID, ORG.ParentId, 0
    FROM dbo.XOrgUnit ORG
    WHERE ORG.ParentID IS NULL
 
    UNION ALL
 
    -- Recursion
    SELECT ORG.OrgUnitID, ORG.ParentId, LEVEL + 1
    FROM dbo.XOrgUnit ORG
    INNER JOIN Abteilung ABT ON ABT.Id  = ORG.ParentId
           
)
SELECT ABT.Id,
       ABT.ParentId,
       LEVEL
FROM Abteilung ABT;

Temporäre Tabellen

lokale temporäre Tabellen

CREATE TABLE #people 
( 
    id INT, 
    name VARCHAR(32) 
)

Scope ist aktuelle Session. Tabelle wird physisch auf Disk angelegt. Tabelle wird gelöscht, wenn Session beeendet wird, trotzdem sollte man Daten aufräumen.

globale temoräre Tabellen

CREATE TABLE ##people 
( 
    id INT, 
    name VARCHAR(32) 
)

Können von allen Sessions benutzt werden. Wird gelöscht, wenn kreierende Session beendet wird.

table Variable

DECLARE @people TABLE 
( 
    id INT, 
    name VARCHAR(32) 
)

Ist eventuell nicht auf Disk. Nur solche können in UDFs verwendet werden.

Resultat einer SP in einem temp Table ablegen

DECLARE @ABT TABLE 
( 
    id INT
)
INSERT INTO @ABT (id) EXEC dbo.spOrgUnitsOfUser 10;

Skripting

Variable deklarieren:

DECLARE @myVar int;

SELECT TOP 1 @myVar = SomeId
FROM dbo.SomeTable X
WHERE X.SomeCol = 'SomeVal'
PRINT CONVERT(varchar,@myVar );

Cursor:

declare @myText varchar(1000)
declare myCurser CURSOR FAST_FORWARD FOR
  select [Text] from dbo.SomeTable
open myCurser
while (1=1) begin
  fetch next from myCurser into @myText
  if @@FETCH_STATUS < 0 break
  print @myText
end

close myCurser
deallocate myCurser

If then else:

DECLARE @compareprice money, @cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @compareprice OUT, 
    @cost OUTPUT
IF @cost <= @compareprice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

Indices

Clustered indices

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

Non Clustered indices

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Transaktionen

BEGIN TRAN UpdateFaLei;
...
COMMIT TRAN UpdateFaLei;

Identity

Identity holen:

DECLARE @TID INT; 
INSERT INTO XLangText (LanguageCode, Text) VALUES (1, @LOVText);
SET @TID = SCOPE_IDENTITY();

Identity ausschalten oder einschalten

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Try Catch

SET NOCOUNT ON;

DECLARE @ErrorMessage VARCHAR(MAX);
SET @Err= NULL;

BEGIN TRY
    -- CODE HERE
    PRINT ('dONE');
END TRY
BEGIN CATCH
  SET @Err= ERROR_MESSAGE()  
  RAISERROR ('Error: %s.', 18, 1, @Err);
  RETURN;
END CATCH;

Umbenennen

Spalte umbenennen:

EXEC sp_rename
    @objname = 'Tablename.OldColumnname',
    @newname = 'NewColumnName',
    @objtype = 'COLUMN'

Tabelle umbenennen:

EXEC sp_rename 'OldTableName', 'NewTableName'

Case Select

SELECT Text = CASE
                WHEN  TXT.[Text] IS NULL THEN 'please translate'
                ELSE  TXT.[Text]
              END
FROM Table TXT

Metadaten

Wie folgt kann man die Foreign-Keys herausfinden:

SELECT f.name AS ForeignKey,
       OBJECT_NAME(f.parent_object_id) AS TableName,
       COL_NAME(fc.parent_object_id,
       fc.parent_column_id) AS ColumnName,
       OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
       COL_NAME(fc.referenced_object_id,
       fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
     INNER JOIN sys.foreign_key_columns AS fc
       ON f.OBJECT_ID = fc.constraint_object_id;

Resourcen

• SQL SERVER 2008 REFERENZ
• Temporäre Tabellen
• # versus @ tmp tables
• stored procedures in assemblies