Difference between revisions of "MS SQL Server"

From no name for this wiki
Jump to: navigation, search
(→‎Resourcen)
(→‎Pivot)
 
Line 627: Line 627:
 
FOR COL2 IN ([1], [2])
 
FOR COL2 IN ([1], [2])
 
) AS T
 
) AS T
 +
</source>
 +
 +
== Unipivot ==
 +
<source lang="sql">
 +
SELECT
 +
*
 +
FROM
 +
(
 +
SELECT
 +
Aktiv = CONVERT(VARCHAR, K1.Aktiv),
 +
KundeID =  CONVERT(VARCHAR, K1.KundeID),
 +
PersonIdErfasser =  CONVERT(VARCHAR, K1.PersonIdErfasser),
 +
KundendienstleistungId
 +
FROM dbo.Kundendienstleistung K1
 +
) K
 +
UNPIVOT 
 +
  (FieldValue FOR FieldName IN 
 +
      (KundeId, PersonIdErfasser, Aktiv) 
 +
) AS unpvt
 +
WHERE unpvt.KundendienstleistungId = 1
 +
 
</source>
 
</source>
  

Latest revision as of 19:44, 24 September 2019

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

DDL

Spalte hinzufügen: 

ALTER TABLE [dbo].[MyTable] ADD [WshDauerauftragAktiv] [bit];
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_FaLeistung_WshDauerauftragAktiv]  DEFAULT ((0)) FOR [WshDauerauftragAktiv]

Unique Index with nulls

Ab SQL Server 2008 sind filtered indices möglich: 

CREATE UNIQUE NONCLUSTERED INDEX
UKXXX ON dbo.Mytable
(
  KbZahlungseingangID  
) 
WHERE KbZahlungseingangID IS NOT NULL;
 
-- Droppen
DROP INDEX Mytable.UKXXX

Constraint

Constraint erstellen, so dass nur neue Daten überprüft werden (WITH NOCHECK). 

ALTER TABLE dbo.MyTable  WITH NOCHECK
  ADD CONSTRAINT XXX 
  CHECK(BlaId = 3 OR BlaId = 4 );

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. Indices und dergleichen nur mit #blabla möglich, nicht mit @blabla.

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;

Scripting

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 myCursor CURSOR FAST_FORWARD FOR
  select [Text] from dbo.SomeTable
open myCursor
while (1=1) begin
  fetch next from myCursor into @myText
  if @@FETCH_STATUS < 0 break
  print @myText
end

close myCursor
deallocate myCursor

Überprüfen, ob es Cursor schon gibt: 

    IF Cursor_Status('global','cAusgleichsmeldungen') > 0 
	BEGIN
		DEALLOCATE cAusgleichsmeldungen
	END


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)))+'.'

Rownumber

SELECT FirstName, LastName, ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

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;

Welche Transaktionen sind offen

DBCC OPENTRAN

SELECT * FROM sys.dm_tran_session_transactions;

SELECT  L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName, 
    P.object_id AS LockedObjectId, 
    L.resource_type AS LockedResource, 
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,        
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

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;


Gibt es Datenbank XXX? 

DECLARE @dbname nvarchar(128)

SET @dbname = N'BLABLA'

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
BEGIN
 PRINT 'EXISTS';
END

Stored Prozeduren auflisten (USE DB... wichtig). 

SELECT [name]
FROM sys.objects
WHERE [Type] = 'P';


Columns auflisten 

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'


Fehlende Foreign Keys: 

(SELECT 
TableName = COL.TABLE_NAME,
ColumnName = COL.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS COL
WHERE
COL.COLUMN_NAME IN
(
SELECT 
ColumnName = COL.COLUMN_NAME
from 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY ' 
)
AND COL.TABLE_NAME NOT LIKE 'qry%'
EXCEPT
SELECT 
--FK.name AS ForeignKey,
TableName = OBJECT_NAME(FK.parent_object_id),
ColumnName = COL_NAME(FKCOLS.parent_object_id, FKCOLS.parent_column_id)
--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)
EXCEPT
(
SELECT
TableName = Tab.Table_Name, 
ColumnName = COL.COLUMN_NAME 
FROM 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY '
)

Fehlende Indices wegen Foreign Keys: 

select db_name(), obj.name, obj.type_desc, col.name, fk.name, fk.type_desc,
      'create index IX_'+obj.name+'_'+col.name+' on '+obj.name +' ('+col.name+')'
from sys.objects obj
       inner join sys.columns col on obj.object_id = col.object_id
       inner join sys.foreign_key_columns fkCol on (col.object_id = fkCol.parent_object_id and col.column_id = fkCol.parent_column_id)     
       inner join sys.foreign_keys fk on (fkCol.constraint_object_id = fk.object_id)
 where obj.type_desc = 'USER_TABLE'
   and CONVERT(varchar(11), col.object_id) + '$' + CONVERT(varchar(11), col.column_id) 
       not in -- Clustered Indexes ausschliessen
         (
           select CONVERT(varchar(11), col.object_id) + '$' + CONVERT(varchar(11), col.column_id)
             from sys.objects obj
                  inner join sys.columns col on obj.object_id = col.object_id
                  inner join sys.index_columns idxCol on (col.object_id = idxCol.object_id and col.column_id = idxCol.column_id)
                  inner join sys.indexes idx on (idxCol.object_id = idx.object_id and idxCol.index_id = idx.index_id)
            where obj.type_desc = 'USER_TABLE'
              and idx.type <> 1 )

Auf welcher DB bin ich?

So kann ich herausfinden, auf welcher DB ich bin: 

SELECT DB_NAME();


Statement cache

SELECT 
	a.attrlist,
	EST.text,
	ECP.objtype
	
	
FROM sys.dm_exec_cached_plans ECP
	CROSS  APPLY sys.dm_exec_sql_text(ECP.plan_handle) est
	CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
              FROM   sys.dm_exec_plan_attributes(ECP.plan_handle) epa
              WHERE  epa.is_cache_key = 1
              ORDER  BY epa.attribute
              FOR    XML PATH('')) AS a(attrlist)
WHERE  est.dbid = db_id('Corp_CMS_DM') 	
and ECP.objtype	= 'Prepared'

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'

Cross Join

CROSS JOIN erstellt Kreuzprodukt. 

  INSERT INTO dbo.MyTable 
  (
    COLA, 
    COLB    
  )
  SELECT 
    KTO.COLA, 
    KAT.SOMECOL
  FROM dbo.SomeTAble KTO
    CROSS JOIN @SomeTmpTable KAT -- Kartesisches Produkt mit @SomeTmpTable;

Pivot

;WITH MY_CTE
 AS
 (
	SELECT 
		COL1='Item 1',
		COL2=1,
		COL3=0.5

	UNION ALL
	
	SELECT 
		COL1='Item 2',
		COL2=1,	
		COL3=0.25
		

	UNION ALL
	
	SELECT 
		COL1='Item 3',
		COL2=2,
		COL3=3.0		
		
	UNION ALL
	
	SELECT 
		COL1='Item 4',
		COL2=2,	
		COL3=4.0			
		  
 )
 
 
SELECT 
	'AverageCost',
	[1], [2]
FROM 
	(SELECT COL2,
			COL3
	 FROM MY_CTE) AS CTE
PIVOT (
	AVG(COL3)
	FOR COL2 IN ([1], [2])
) AS T

Unipivot

SELECT
*
FROM 
(
	SELECT
		Aktiv = CONVERT(VARCHAR, K1.Aktiv),
		KundeID =  CONVERT(VARCHAR, K1.KundeID),
		PersonIdErfasser =  CONVERT(VARCHAR, K1.PersonIdErfasser),
		KundendienstleistungId
	FROM dbo.Kundendienstleistung K1
) K
UNPIVOT  
   (FieldValue FOR FieldName IN   
      (KundeId, PersonIdErfasser, Aktiv)  
) AS unpvt
	WHERE unpvt.KundendienstleistungId = 1

OUTPUT

DECLARE @TmpTableTABLE
(
  TheID INT NOT NULL
);

 UPDATE ABC
   SET Column1 = 1,       
   OUTPUT deleted.id
   INTO @TmpTable
 FROM MyTable ABC   


SELECT * FROM  @TmpTable

Datetime

-- Konvertiert DATETIME TO DATE
CREATE FUNCTION dbo.fnDateOf
(
  @DateValue DATETIME
)
RETURNS DATETIME WITH SCHEMABINDING
AS 
BEGIN
  RETURN (CONVERT(DATETIME, CONVERT(VARCHAR, @DateValue, 112), 112));
END;
GO

-- Nächster Monat --
SELECT DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));


-- SQL Server string to date / datetime conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date

-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

 

-- Without century (yy) string date conversion - convert string to datetime function

SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

 

-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

-- 1900-01-01 20:10:44.000

 

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

-- YYYYMMDD ISO date format works at any language setting - international standard

SELECT convert(datetime, '20161023')

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

-- 2016-10-23 20:44:11.500

 

-- Style 126 is ISO 8601 format: international standard - works with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)

-- 2008-10-23 18:52:47.513

 

-- Convert DDMMYYYY format to datetime - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL Server T-SQL string to datetime conversion without century - some exceptions

-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc

SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default

SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.

SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI

SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR

SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German

SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian

SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.

SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.

SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec

SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.

SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan

SELECT convert(datetime, '161023', 12) -- yymmdd ISO

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt

SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

------------

-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss

-- String to datetime - mssql datetime - sql convert date - sql concatenate string

DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)

 

SELECT @DateValue = '20120718',

       @TimeValue = '211920'

SELECT @DateTimeValue =

convert(varchar, convert(datetime, @DateValue), 111)

+ ' ' + substring(@TimeValue, 1, 2)

+ ':' + substring(@TimeValue, 3, 2)

+ ':' + substring(@TimeValue, 5, 2)

SELECT

DateInput = @DateValue, TimeInput = @TimeValue,

DateTimeOutput = @DateTimeValue;

Connection Strings

Beispiel 1: 

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Beispiel 2: 

Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;

Beispiel 3, lokaler Rechner: 

 Server=(local);Database=nhibernate;Trusted_Connection=true;

Excel Export

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

= "UPDATE LANG"
&	" SET strTextF = '" & D2 & "',"
&	    "strTextI = '" & E2 & "'"			
& " FROM dbo.tabLanguage  LANG"
& " WHERE LANG.strLabel = '" & B2 & "';"

Nummer rechts mit Nullen auffüllen: 

= TEXT(123; "0000000000")

Statement Cache

SELECT  TOP 100
         qs.execution_count,
         DatabaseName = DB_NAME(qp.dbid),
         ObjectName = OBJECT_NAME(qp.objectid,qp.dbid),
         StatementDefinition =
                SUBSTRING (
                        st.text,
                        (
                                qs.statement_start_offset / 2
                        ) + 1,
                 (
                                       (
                                               CASE qs.statement_end_offset
                         WHEN -1 THEN DATALENGTH(st.text)
                         ELSE qs.statement_end_offset
                                               END - qs.statement_start_offset
                                       ) / 2
                                ) + 1
                ),
         query_plan,
         st.text, total_elapsed_time
 FROM    sys.dm_exec_query_stats AS qs
         CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
         CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
 WHERE
     st.encrypted = 0
 ORDER BY qs.execution_count DESC

Statement cache leeren: 

USE AdventureWorks2008R2;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

DBCC FREEPROCCACHE;

USE AdventureWorks2008R2;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

http://msdn.microsoft.com/en-us/library/ms174283.aspx

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

deadlocks

--	Here's the code (terrible, but designed to cause a deadlock). I ran the code in three steps.
-- Step 1:
-- Start connection Number 1 using SSMS:

USE [AdventureWorks]
GO
BEGIN TRAN

--Update One: Run 1st
UPDATE sales.[SalesTaxRate]
SET taxrate = taxrate + 0.05
WHERE taxtype = 1;

-- The above starts the first transaction, but leaves it open.
-- Start Connection 2 in SSMS:

USE [AdventureWorks]
GO
BEGIN TRAN


--Update: Run 2nd (both examples)
UPDATE sales.[SalesTerritory]
SET salesytd = salesytd + 1
WHERE territoryID = 1;

--Update
UPDATE sales.[SalesTaxRate]
SET taxrate = taxrate + 0.05
WHERE taxtype = 1;

COMMIT TRAN

-- Run the above code in the second connection, and this code is now blocked and won't complete due to the blocking lock created by the code running in the first connection.
-- Step 3: In the first connection:
-- Update Two: Run 3rd
UPDATE sales.[SalesTerritory]
SET salesytd = salesytd + 1
WHERE territoryID = 1;

COMMIT TRAN

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

binary

print sys.fn_varbintohexstr(CONVERT(VARBINARY,'test'));

CREATE TABLE DOC (Document varbinary(max)) 

INSERT INTO DOC(Document) 
SELECT * FROM 
OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB) SOMEALIAS

Jobs

JobStatus: 

SELECT TOP 100
  sysjobhistory.run_status, run_date, sysjobs.name
FROM
 msdb..sysjobhistory  as sysjobhistory
   Join  msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id   
WHERE
 name like '%pendenz%'   
ORDER BY run_date DESC;

User reaktivieren nach Backup

EXEC sp_change_users_login @action='Report';

EXEC sp_change_users_login @action='Update_One', @UserNamePattern= 'pattern' , @LoginName='loginname';

Doppeleinträge entfernen

BEGIN TRANSACTION;
	;WITH CTE
	AS(
		SELECT	L.lngID,
				L.strLabel, 
				Rank = RANK() OVER (PARTITION BY L.strLabel ORDER BY L.lngId)	   	   
		FROM dbo.tabLanguage L
	)
	
	DELETE FROM dbo.tabLanguage
	WHERE lngID IN
	(
		SELECT lngID FROM CTE
		WHERE Rank > 1
	)
	
	SELECT 
		L.strLabel
	FROM dbo.tabLanguage L
	GROUP BY L.strLabel
	HAVING COUNT(L.lngID) > 1
	
ROLLBACK TRANSACTION;

Merge

Mapping zwischen neu erstellen Id und alter Id, Bulk insert. 

DECLARE @UpdateVariable bit
DECLARE @ChangeResult TABLE (ChangeType VARCHAR(10), Id INTEGER)
DECLARE @Data TABLE (Id integer IDENTITY(1,1), Val VARCHAR(10))
INSERT @Data ([Val]) VALUES ('A');

MERGE @data AS TARGET
USING (SELECT 'A' AS Val UNION ALL SELECT 'B' AS Val) AS SOURCE 
	ON TARGET.Val = SOURCE.Val
WHEN NOT MATCHED THEN
    INSERT ([Val])
    VALUES (SOURCE.Val)
WHEN MATCHED THEN 
    UPDATE SET @UpdateVariable = 1
OUTPUT $action, inserted.Id INTO @ChangeResult;

PRINT @UpdateVariable;
SELECT * FROM @data;
SELECT * FROM @ChangeResult;

Resourcen

Retrieved from "http://www.claude-glauser.ch/mediawiki/index.php?title=MS_SQL_Server&oldid=2297"