TSQL - Från kursen
Post date: Nov 2, 2009 1:15:22 PM
select * from customers
-- Vissa Problem med fnuttar kan undvikas genom följande kommandoset quoted_identifier offexec ("select * from customers where customerid = 'ANTON'")select * from Customers where CompanyName = "Bon app'"set quoted_identifier on-- Cross apply-- Kör en funktion per rad i en tabellselect *--[11],[42],[72],[14],[51]from(select
od.UnitPrice, p.ProductName, o.EmployeeID
from
[Order Details] od
inner join Orders o
on od.OrderID = o.OrderId
inner join Products p
on p.ProductID = od.ProductID
) as sales
pivot ( sum(UnitPrice) for ProductName in ( [Chang], [Aniseed Syrup], [Chef Anton's Cajun Seasoning], [Chef Anton's Gumbo Mix], [Grandma's Boysenberry Spread], [Uncle Bob's Organic Dried Pears], [Northwoods Cranberry Sauce], [Mishi Kobe Niku]) ) as pvt
select * from Products
Chai
-- Merge-- Används när man vill skjuta in en tabell med ny data till en tabell med befintlig data.-- Kan göra insert update och delete i samma statement beroende på vad som passar.-- Jobbar med Source och target tablewhen not matched by target
when matched
when not matched By source -- finns i target men inte i source (gammal anställd som inte är med i lönelistan)create table Kunder( KundId int , Namn varchar(50) , stad varchar(500))create table NyaKunder( KundId int , Namn varchar(50) , stad varchar(500))insert Kunder
select 1, 'Kajsa', '' union allselect 2, 'Olof', '' union allselect 3 ,'Nils', '' union allselect 4, 'Stina', '' union allselect 5, 'Pelle', ''
select * from kunder
-- Vi har fått ett gäng nya kunder i stockholminsert NyaKunder
select 1, 'Kajsa', 'Stockholm' union allselect 6, 'Peter', 'Stockholm' union allselect 2, 'Olof', 'Stockholm' union allselect 4, 'Stina', 'Stockholm' union allselect 5, 'Pelle', 'Stockholm'
select * from NyaKunder
merge kunder T -- Detta är target, tabellen som skall uppdaterasusing nyakunder S -- detta är source, tabellen som vi läser ny data ifrånON t.kundid = s.kundid -- Detta är vilkoret för en Match-- Lägg till nya kunder som inte finns i Kunderwhen not matched by target then insert (kundid, namn, stad) values (s.kundid, s.namn, s.stad)
-- Uppdatera med stad från nyaKunder, Alla utom nils bör uppdateraswhen matched then update set t.stad = s.stad
-- Ta bort Kunder som inte finns i NyaKunderwhen not matched by source then delete ;-- ett merge måste avslutas med semikolonselect * from kunder
-- Jämför cursor med en loopselect name from sys.tablesdeclare @tblName varchar(100)declare tablecursor CURSOR FOR SELECT name from sys.tablesopen tablecursor
fetch tablecursor into @tblNamewhile @@FETCH_STATUS = 0begin print @tblName fetch tablecursor into @tblNameendclose tablecursor
deallocate tablecursor
--- -- -- -- -- -- -- --- Slut cursor testdeclare @id int,@name nvarchar(255),@totalrow int,@rownum intset @totalrow = (select COUNT(*) from sys.tables)set @rownum = 0declare @tbl table (name varchar(255));with t as
( select ROW_NUMBER() over (order by name) as RowNumber
,name
from sys.tables)while @rownum < @totalrowbegin set @rownum = @rownum +1 insert @tbl select name
from t
where t.RowNumber = @rownumendselect * from @tblgocreate proc peter as
begin try
select 1 / 0end try
begin catch
select ERROR_MESSAGE() , ERROR_SEVERITY() , ERROR_NUMBER() , ERROR_LINE() , ERROR_PROCEDURE() return -- avsluta om det blivit något felend catch
goexec peter
select
*
from
suppliers
-- Hur tvinga igenom en viss jointyp-- Det handlar om att koppla ihop två tabeller-- Loop join funkar som så att den loopar igenom den lilla tabellen och letar sedan efter matchningar i den stora tabellen.-- används när vi har en liten tabell och en stor tabell-- funkar väldigt bra om den ena tabellen är relativt mycket mindre än den andra.--Merge join --påminner om loop join-- används när tabellerna är ungefär lika stora-- merge join kräver ett index på det vi joinar på.-- Eftersom det finns index på det vi joinar så ligger värdena sorterade och det blir väldigt lätt för servern att hitta alla värden.-- funkar bra.-- Hash join-- Ett tecken på att det saknas index på något av kolumnerna vi joinar på-- Inte så roligt!-- select
EmployeeID
,SUM(p.unitprice) as sumsales
from Orders o
inner join [Order Details] od
on o.OrderID = od.OrderID
inner join Products p
on od.ProductID = p.ProductID
group by EmployeeID
option (Loop Join) -- lägg till option för att välja vilken typ av join som skall göras -- Intersect-- som union men som bara tar resultat där båda resultaten matchar-- ExCept-- tar fram rader ur första frågan och tar bort matchingar som finns i fråga nr2create proc hej(@i int) as
if @i = 10 begin select * from customers
endelse begin select * from Employees
endgoexec hej 7go-- Övning--ta ut kunder som finns i länder där det finns leverantörer med faxwith c as
(select * from Suppliers where Fax is not null)select
*
from
Customers
where Country in (select Country from c) -- ta reda på den högsta fraktkostnaden för varje anställdselect
MAX(freight)
,EmployeeID
from
orders
group by EmployeeID
-- ta reda på information om den högsta ordrarna för varje anställdselect
*FROM Orders o inner join ( select
MAX(freight) as MaxFreight
,EmployeeID
--,OrderID from
orders
group by EmployeeID--, OrderID ) t
on o.EmployeeID = t.EmployeeID and t.MaxFreight = o.Freight
-- Gruppera dessa orders på vilket år och vilken månad dessa är gjordawith c as
( select
employeeid
,YEAR(OrderDate) orderYear, YEAR(shippeddate) ShippingYear
,Month(OrderDate) orderMonth, Month(shippeddate) ShippingMonth
from
Orders
)select EmployeeID, orderYear ,COUNT(*) from c group by EmployeeID, orderYear
-- Ta fram lista med nummrering av alla anställda på personal i usaselect
lastName, firstname ,ROW_NUMBER() over (order by lastname) from Employees where Country = 'USA' --select
*, row_number() over (order by productname)from
products
where discontinued = 1-- det går även att köra rankingfunktioner på columner som inte är med i selectlistanselect
OrderID,
EmployeeID,
RANK() over (order by Orders.Freight)from
orders
-- flera numreringar baserat på en kolumn, exempelvis country-- Tar fram en numrerad lista på kunder med numrering mellan varje landselect CompanyName, Country
,ROW_NUMBER() over(partition by country order by companyname)from customers
SELECT * FROM [Order Details]select
avg(UnitPrice) OVER(PARTITION BY PRODUCTID) ,MIN(UnitPrice) OVER(PARTITION BY PRODUCTID) ,PRODUCTID
from [Order Details]GROUP BY ProductID
USE AdventureWorks
go-- In this example, using the OVER clause is more efficient than using subqueries.SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total' ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg' ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count' ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min' ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659);goUSE Northwind
godrop table Employee
create table Employee
( namn varchar(500), datum datetime, stad varchar(500), rowid uniqueidentifier default(newid()))-- Lägg till i kolumnordninginsert Employee values ('Peter', '2009-01-01', 'Stockhoom')-- Med namngivnga kolumnerinsert Employee(namn, datum, stad) values ('Sofie', '2008-01-01', 'Götet')-- Utelämna en kolumninsert Employee(stad, namn) values ('Kholm','Peter') -- datum blir null-- Ger output av valda kolumner vid en insert-- OUTPUT kan även användas vid delete eller updateinsert Employee(namn, datum, stad) Output inserted.* values ('Sofie', '2008-01-01', 'Götet')delete Employee output deleted.*-- För update så kan vi även se det värde som fanns innan updateupdate Employee set datum = GETDATE() output inserted.*, deleted.*
where namn = ''-- kan vara smidigt att använda vid insert från select så man direkt ser vad man insertat-- SELECT INTO -- är oerhört snabb. Den skapar en kopia men utan index. Inget loggas! Smart att göra till en temporärtabell-- INSERT INTO (Insert Select)-- Loggas i motsats mot Select into, använder även index-- Truncate table -- Loggas inte i transactionsloggen-- Delete-- Loggar i transactionsloggen-- Temporära tabeller-- Kräver endast select behörigheter-- #tmp -- Local, kan endast nås från current session-- ##tmp -- global, kan nås från fler, men den försvinner ändå när man stänger sin session-- recovery mode Simple-- Vid checkpoint så töms transactionsloggen. Det händer vid jämna mellanrum-- ett snapshot är en slags kopia som pekar tillbaka till databasen.-- Den är read only. Det går att läsa tillbaka denna data till den riktiga databasen om man klantar sig vid en stor update tex.-- En snapshot är en fil som är tom från början. När data i ursprungstabellen ändras så kommer ursprungliga värdet att kopieras in i snapshottetcreate database northwind_snap on
( Name='Northwind', Filename='c:\snapshots\snap.ss' ) as snapshot of Northwind
select
Distinct Country
from
Customers
select * from Customers where Country in (select Country from Customers)-- Det går att lägga en select som ett fält i selectsatsen-- Subfråga som ett uttryck-- de 10 dyraste producterna-- Exempel på derived tableSelect
*
from
(select
top 10
ProductName, UnitPrice from
Products
order by
UnitPrice desc) as t
order by ProductName
with t as
(select
top 10
ProductName, UnitPrice from
Products
order by
UnitPrice desc)select * from t order by ProductName;-- efter ett CTE kan det vara bra att slänga in ett semikolon så att servern vet att vi är klara-- det går även att använda en temporär tabell för att lösa problemet-- de kan dock ge sämre prestanda eftersom inga index finnes. -- corelalted sub queryselect
*FROM Orders o inner join ( select
MAX(orderdate) as maxDate, EmployeeID from
orders
group by
EmployeeID
) as maxdates
on o.EmployeeID = maxdates.EmployeeID
select -- radnummer, helt enkelt productName,
ROW_NUMBER() over(order by productName) as radnummer
from
Products
select -- bruten kedja productname, unitprice, RANK() over (order by unitprice desc) as rank
from Products
select -- ingen bruten kedja productname, unitprice, dense_RANK() over (order by unitprice desc) as drank
from Products
select -- Delar upp producter i prisgrupper productname, unitprice, ntile(3) over (order by unitprice desc) as drank
from Products
-- Frågeoptimering-- Klustrade index -- Talar om hur data ligger lagrat i tabellen. -- Håller reda på hela tabellen. -- Behöver inte vara unikt. -- I allmänhet använder man en primärnyckel som är nyckel i det klustrade indexet -- Det klustrade indexet bör vara så litet som möjligt, det används sedan av de övriga index -- Ett klustrat index per tabell -- Ikkeklustrade index -- använder det klustrade indexted för att hitta rader i tabellen -- får finnas massor per tabell -- -- Fördelen med index -- select går snabbare i allmänthet -- Nackdelar -- Tar utrymme. Tar lika mycket utrymme som kolumnen som man indexerat -- Långsamma insättningar och uppdateringar. -- Index -- Komposita och Included Kolumns -- Omfattar mer än en kolumn. -- Kan bli ett täckande index, dvs alla kolumner i frågan ingår i frågan -- Man bör ha index på -- Det man ofta söker på i where-satsen eller våra joins. select FirstName, MiddleName, LastName, BusinessEntityID Into NewPersons
from Person.Person
select
firstname,
lastname
from newpersons
where Firstname = 'Syed' sp_helpindex 'NewPersons' sp_help 'NewPersons'create clustered index cL_beid on NewPersons(BusinessEntityID)select
firstname,
lastname
from newpersons
where Firstname = 'Syed'create nonclustered index IX_FirstName on NewPersons(FirstName)-- Nu kan vi skapa ett täckande index-- Sortera först på förnamn sedan på efternamn-- Detta är ett komposit indexcreate nonclustered index IX_FirstName_LastName on NewPersons(FirstName, LastName)--eller Included kolumns-- Sortera på förnamn, inkludera sedan efternamn-- Detta gör att den bara sorterar på förnamn och behöver då inte sortera på efternamn vid uppdateringar-- Detta är ett included kolumn index, Oops råkade döpa dem felcreate nonclustered index IX_FirstName_Comp on NewPersons(firstname) include (LastName)create nonclustered index IX_Last_Comp on NewPersons(LastName) include (FirstName)select
firstname,
lastname
from newpersons
where Firstname = 'Syed' order by 1, 2 -- det går att rensa query optimizer cachen-- Detta kan vara bra att köra när man sitter och testar lite olika optimeringar så att man inte kör på en gammal execution plancheckpoint -- Skriver alla färdiga transactioner till diskdbcc DROPCLEANBUFFERS
dbcc FREEPROCCACHE
-- Testa med Client Statisticsinsert NewPersons
select
FirstName,
MiddleName,
LastName, BusinessEntityID from Person.Person
-- Droppa nu ett par indexdrop index NewPersons.IX_FirstName_LastName
-- Testa igen med Client Statisticsinsert NewPersons
select
FirstName,
MiddleName,
LastName, BusinessEntityID from Person.Person
-- Testa med att droppa alla index och insert borde gå på några hundra hundradelar-- Index sparas på sidor. Om en sida är full och ett nytt värde måste in där så måste sidan splittas.-- Den nya splittade sidan kommer att hamna konstigt och bli fragmenterade. Därför måste man bygga om sina index.-- Nonclustered index sparar värdena från kolumnerna som ingår i indexet pluss idt på det klustrade indexted där raden ligger.-- Om inget klustrat index finnes så sparas en referens till sidans ID.-- En table scan måste då göras för att hitta sidan i den HEAP som tabellen ligger lagrad i.drop table NewPersons2
select
*into NewPersons2
from
Person.Person
Insert NewPersons2
select * from Person.Person
select
BusinessEntityID
,FirstName, LastName, MiddleName
from NewPersons2
where modifiedDate = '1996-07-24 00:00:00.000' select FirstName, LastName, modifiedDate
from newpersons2
where
--modifiedDate between '1992-01-01' and '1998-12-31' --modifiedDate = '1996-07-24 00:00:00.000' LastName Like 'Robert%' create clustered index CL_Date on NewPersons2(BusinessEntityID)
create nonclustered index IX_Names on NewPersons2(modifiedDate) Include (LastName)-- with recompile kommer göra så att frågan kommer att kompileras om vid varje körningcreate proc hejsan
with recompile asprint 'hejsan'-- tvinga recompile på en proc vid en körningexec sp_ActiveDirectory_Obj with recompile
sp_recompile 'sp_minProcedure' -- markeras för att compileras om vid nästa körning--This example causes the triggers and stored procedures that uses the titles table to be recompiled the next time they are run.--EXEC sp_recompile titles-- Detta händer i denna ordning när en fråga körs Parsning Namnuppslagning Skapa frågeplan -> cachas
-- Om data eller index förändras så kan det vara intressant att tvinga en kompilering av fråganuse AdventureWorks2008
select * from Person.Person where FirstName = 'Kenny' -- = ger väldigt fin prestanda jämfört med andra jämförelserselect * from Person.Person where FirstName like 'Ken%' -- Kan fortfarande använda indexselect * from Person.Person where FirstName like '%en' -- inledande wildcard ger alltid sämre prestanda, tvingar fram table scan select * from Person.Person where FirstName like 'Ken___' -- valfritt tecken vilket somselect * from Production.Product where ListPrice between 1000 and 2000 -- intervallprestanda är duglig om det finns indexselect * from Production.Product where Color in ('Black', 'White') -- insert into table values(''), ('') -- em lista med items som kan skriva mindre kod för att inserta flera rader samtidigt.-- AND går före OR, använd paranteser-- Prestanda på AND är bättre än ORselect * from Production.Product where Weight = null -- jämförelse med null blir alltid falseselect * from Production.Product where Weight is nullselect Name, ISNULL(weight, 0) from Production.Product where Weight is nullcreate table Import
( name varchar(100), weight varchar(100) sparse -- Effektivare på att lagra värden är null om man har väldigt många värden som är null, om det är väldigt få värden som är null så kan den vara mindre effektiv)
select top 10 * from Production.Product
select top 10 Percent * from Production.Product -- Tar de första värdena utifrån det klustrade indexetselect top 10 * from Production.Product order by ListPrice desc -- select top 10 with ties * from Production.Product order by ListPrice -- TIES Får även med delade platser-- när man använder group by kan det vara ett tipps att börja med att skriva group by först, och sedan kopiera upp alla grupper till selectsatsen-- lägg sedan till en eller flera agreggerade funktioner-- where vilkoret kommer att filtrera först, having filtrerar sedan-- group by rollup betyder aggregggerade värden för första kolummen i group by-satsen-- group by CUBE agregerade värden för alla upptänkliga summeringar (inte bara den första)-- group by Grouping Sets((DepartmentName, Gender), (Gender, MaritalStatus), ...) gör flera olika grupperingar på samma resultat (2008)-- datasettet innehåller en blandning av alla grupperingar-- samma som 3 frågor med group by och sedan göra union på resultatet-- cross join är alla upptänkliga combinationer. Tag en rad ur första tabellen och ta ut en rad för varje rad från den andra tabellen-- union slår ihop två frågor, dubletter tas endast med en gång. Kolumnnamn tas från den första frågan-- union all tar även med dubletter use Northwind
select
CategoryID
,SupplierID
,avg(UnitPrice) ,COUNT(*) from
Products
group by
CategoryID, SupplierID
with rollup select
CategoryID
,SupplierID
,avg(UnitPrice) ,COUNT(*) from
Products
group by
CategoryID, SupplierID
with cubeselect
supplierid
, UnitPrice
from
Products
compute sum(UnitPrice), avg(unitprice) -- Skapar ett nytt resultatset med en summering select * from
orders o
inner join [Order Details] od
on o.OrderID = od.OrderID
inner join Products p
on p.ProductID = od.ProductID
select * from
Customers cinner join orders o
on c.CustomerID = o.CustomerID
-- 1.13select * from
Customers cleft outer join orders o
on c.CustomerID = o.CustomerID
where o.OrderID is null
-- select * from Products-- Rekursiv CTEWITH Managers AS
(
SELECT
EmployeeID,
LastName,
ReportsTo, @@NESTLEVEL as ManagerLevel
FROM
Employees
WHERE
EmployeeID = 2
UNION ALL
SELECT
e.employeeID, e.LastName,
e.ReportsTo, @@NESTLEVEL as ManagerLevel
FROM
Employees e
INNER JOIN Managers m
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers
select * from Employees where Country = 'USA'select * from Employees where EmployeeID in (1, 2, 3, 4, 8)select EmployeeID from Employees
select CustomerID from orders where EmployeeID IN (select EmployeeID from Employees where Country = 'USA')
select MAX(orderdate) from orders group by EmployeeID
select orderid, customerid, employeeid, orderdate from Orders
-- vi kan inte gruppera på ett alias och vi vill inte gruppera på själva datumen, bara på årtalen.-- Då gör vi först en cte där vi plockar ut årtalenwith c as
( SELECT datepart(yy, orderdate) as orderYear, customerid --,EmployeeID from orders where employeeid = 3)select COUNT(*), orderyear from c
group by orderyear
gocreate view v1 as
SELECT datepart(yy, orderdate) as orderYear, customerid --,EmployeeID from orders where employeeid = 3go
select COUNT(*), orderyear from v1 group by orderyear
-- Ranka anställda efter hur mycket de har såltwith c as( select
EmployeeID
,SUM(p.unitprice) as sumsales
from Orders o
inner join [Order Details] od
on o.OrderID = od.OrderID
inner join Products p
on od.ProductID = p.ProductID
group by EmployeeID
)select employeeid, sumsales, RANK() over(order by sumsales) as rank from cselect * from orders
-- Ta fram alla order där kunderna har köpt ovanligt lite av en product, -- jämfört med snittet av hur mycket folk brukar köpa av den produktenselect
ProductID
,OrderID
,Quantity
from [Order Details] od1
where Quantity < (select Max(quantity) / AVG(Quantity) from [Order Details] od2 where od1.ProductID = od2.ProductID)-- Kan även skrivas som en join med samma resultat.-- Den inre frågan är nu helt fristående från den yttre.select
od1.ProductID
,OrderID
,Quantity
from [Order Details] od1
INNER JOIN ( select
AVG(quantity) as medel
, ProductID
from
[Order Details] group by ProductID
) od2
on od1.ProductID = od2.ProductID
where od2.medel > od1.Quantity
select *
into MyPersons
from Person.Person
where person.FirstName like 'm%'select *
into APersons
from Person.Person
where person.FirstName like 'a%'select * from Person.Person p
left outer join myPersons m on m.rowguid = p.rowguid
left outer join APersons a on a.rowguid = p.rowguid
where p.Title = 'Mr.'