Difference between revisions of "MS SQL Server"
(→Connection Strings) |
(→Connection Strings) |
||
Line 376: | Line 376: | ||
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; | Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; | ||
Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True; | Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True; | ||
+ | |||
+ | == Excel Export == | ||
+ | = "INSERT INTO @Steuerung" | ||
+ | & "(" | ||
+ | & "KOA," | ||
+ | & "Ausgabe," | ||
+ | & "Einname," | ||
+ | & "Sanktion," | ||
+ | & "Verrechnung," | ||
+ | & "Rueckerstattung," | ||
+ | & "IstLeistungWsh," | ||
+ | & "IstLeistungWshStationaer," | ||
+ | & "IstMonatsbudgetAktiv," | ||
+ | & "IstGrundbudgetAktiv," | ||
+ | & "Betrifft," | ||
+ | & "Quoting" | ||
+ | & ")" | ||
+ | & "VALUES" | ||
+ | & "(" | ||
+ | & "'" & A2 & "'," | ||
+ | & "'" & C2 & "', " | ||
+ | & "'" & D2 & "', " | ||
+ | & "'" & E2 & "', " | ||
+ | & "'" & F2 & "', " | ||
+ | & "'" & G2 & "', " | ||
+ | & "'" & H2 & "', " | ||
+ | & "'" & I2 & "', " | ||
+ | & "'" & J2 & "', " | ||
+ | & "'" & K2 & "', " | ||
+ | & "'" & L2 & "', " | ||
+ | & "'" & M2 & "' " | ||
+ | & ");" | ||
== Resourcen == | == Resourcen == |
Revision as of 16:41, 26 August 2011
Contents
- 1 Commands
- 2 CTE Common Table Expressions
- 3 Temporäre Tabellen
- 4 Skripting
- 5 Indices
- 6 Transaktionen
- 7 Identity
- 8 Try Catch
- 9 Umbenennen
- 10 Case Select
- 11 Variant Datentyp
- 12 Metadaten
- 13 Auf welcher DB bin ich?
- 14 Wer ist auf dem DB Server?
- 15 Escaping a quote
- 16 APPLY
- 17 Insert binary
- 18 Connection Strings
- 19 Excel Export
- 20 Resourcen
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 @Steuerung" & "(" & "KOA," & "Ausgabe," & "Einname," & "Sanktion," & "Verrechnung," & "Rueckerstattung," & "IstLeistungWsh," & "IstLeistungWshStationaer," & "IstMonatsbudgetAktiv," & "IstGrundbudgetAktiv," & "Betrifft," & "Quoting" & ")" & "VALUES" & "(" & "'" & A2 & "'," & "'" & C2 & "', " & "'" & D2 & "', " & "'" & E2 & "', " & "'" & F2 & "', " & "'" & G2 & "', " & "'" & H2 & "', " & "'" & I2 & "', " & "'" & J2 & "', " & "'" & K2 & "', " & "'" & L2 & "', " & "'" & M2 & "' " & ");"