Difference between revisions of "MS SQL Server"
(→Try Catch) |
(→Umbenennen) |
||
Line 261: | Line 261: | ||
<source lang="sql"> | <source lang="sql"> | ||
EXEC sp_rename 'OldTableName', 'NewTableName' | EXEC sp_rename 'OldTableName', 'NewTableName' | ||
+ | </source> | ||
+ | |||
+ | == Case Select == | ||
+ | <source lang="sql"> | ||
+ | SELECT Text = CASE | ||
+ | WHEN TXT.[Text] IS NULL THEN 'please translate' | ||
+ | ELSE TXT.[Text] | ||
+ | END | ||
+ | FROM Table TXT | ||
</source> | </source> | ||
Revision as of 10:38, 10 January 2011
Contents
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