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

Variant Datentyp

Typ auslesen:

SQL_VARIANT_PROPERTY(Value1.Value, 'BaseType')

Mögliche Werte sind int,nvarchar ...

Metadaten

Wie folgt kann man die Foreign-Keys herausfinden:

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

Auf welcher DB bin ich?

So kann ich herausfinden, auf welcher DB ich bin:

SELECT DB_NAME();

Wer ist auf dem DB Server?

SELECT DB_NAME(dbid) AS [DB Name], hostname AS [Host Name], loginame AS [Login Name] 
FROM sys.sysprocesses 
WHERE dbid > 0 
--  AND db_name(dbid) = 'KiSS_BSS_Dev' 
ORDER BY DB_NAME(dbid), hostname, loginame

Escaping a quote

Anführungszeichen werden mit doppel Quoten escaped.

EXEC('SELECT DB_NAME(), FieldName FROM SomeTabl WHERE SomeCol = ''Someval'';');

APPLY

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

SELECT D.deptid, D.deptname, D.deptmgrid
    ,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

Und die Funktion:

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) 
    RETURNS @TREE TABLE
(
    empid   INT NOT NULL
    ,empname VARCHAR(25) NOT NULL
    ,mgrid   INT NULL
    ,lvl     INT NOT NULL
)
AS
BEGIN
  WITH Employees_Subtree(empid, empname, mgrid, lvl)
  AS
  ( 
    -- Anchor Member (AM)
    SELECT empid, empname, mgrid, 0
    FROM Employees
    WHERE empid = @empid

    UNION all
    
    -- Recursive Member (RM)
    SELECT e.empid, e.empname, e.mgrid, es.lvl+1
    FROM Employees AS e
      JOIN Employees_Subtree AS es
        ON e.mgrid = es.empid
  )
  INSERT INTO @TREE
    SELECT * FROM Employees_Subtree;

  RETURN
END
GO

Notice that each row from the Departments table is duplicated as many times as there are rows returned from fn_getsubtree for the department's manager.

Also, the Gardening department does not appear in the results. Because this department has no manager, fn_getsubtree returned an empty set for it. By using OUTER APPLY, the Gardening department will also appear in the result set, with null values in the deptmgrid field, as well as in the fields returned by fn_getsubtree.

Insert binary

UPDATE FileType
SET Icon =
  (SELECT *
   FROM OPENROWSET (BULK N'c:\Upload\Quicktime-icon.png', SINGLE_BLOB) AS [image])
WHERE Extension = '.mov'

Connection Strings

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;

Excel Export

= "INSERT INTO @MyTmpTable"
& "("
&  "KOA,"
&  "Ausgabe,"
&  "Einname,"
& ")"
& "VALUES"
& "("
&    "'" & A2 & "',"
&    "'" & C2 & "', "
&    "'" & D2 & "'"
& ");"