Just Code‎ > ‎

TSQL - Från kursen

posted Nov 2, 2009, 5:15 AM by Peter Henell   [ updated Jan 25, 2010, 11:36 PM ]
select * from customers

-- Vissa Problem med fnuttar kan undvikas genom följande kommando set quoted_identifier off exec ("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 tabell select *--[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 table when 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 all select 2, 'Olof', '' union all select 3 ,'Nils', '' union all select 4, 'Stina', '' union all select 5, 'Pelle', ''

select * from kunder

-- Vi har fått ett gäng nya kunder i stockholm insert NyaKunder
select 1, 'Kajsa', 'Stockholm' union all select 6, 'Peter', 'Stockholm' union all select 2, 'Olof', 'Stockholm' union all select 4, 'Stina', 'Stockholm' union all select 5, 'Pelle', 'Stockholm'

select * from NyaKunder


merge kunder T -- Detta är target, tabellen som skall uppdateras using nyakunder S -- detta är source, tabellen som vi läser ny data ifrån ON t.kundid = s.kundid -- Detta är vilkoret för en Match -- Lägg till nya kunder som inte finns i Kunder when 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 uppdateras when matched then update set t.stad = s.stad

-- Ta bort Kunder som inte finns i NyaKunder when not matched by source then delete ;-- ett merge måste avslutas med semikolon select * from kunder





-- Jämför cursor med en loop select name from sys.tables declare @tblName varchar(100) declare tablecursor CURSOR FOR SELECT name from sys.tables open tablecursor

fetch tablecursor into @tblName while @@FETCH_STATUS = 0 begin print @tblName fetch tablecursor into @tblName end close tablecursor
deallocate tablecursor
--- -- -- -- -- -- -- - -- Slut cursor test declare @id int ,@name nvarchar(255) ,@totalrow int ,@rownum int set @totalrow = (select COUNT(*) from sys.tables) set @rownum = 0 declare @tbl table (name varchar(255)); with t as
( select ROW_NUMBER() over (order by name) as RowNumber
,name
from sys.tables ) while @rownum < @totalrow begin set @rownum = @rownum +1 insert @tbl select name
from t
where t.RowNumber = @rownum end select * from @tbl go create proc peter as
begin try
select 1 / 0 end try

begin catch
select ERROR_MESSAGE() , ERROR_SEVERITY() , ERROR_NUMBER() , ERROR_LINE() , ERROR_PROCEDURE() return -- avsluta om det blivit något fel end catch

go exec 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 nr2 create proc hej(@i int) as

if @i = 10 begin select * from customers
end else begin select * from Employees
end go exec hej 7 go -- Övning --ta ut kunder som finns i länder där det finns leverantörer med fax with 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älld select
MAX(freight)
,EmployeeID
from
orders
group by EmployeeID


-- ta reda på information om den högsta ordrarna för varje anställd select
* 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 gjorda with 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 usa select
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 selectlistan select
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 land select 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); go USE Northwind
go drop table Employee
create table Employee
( namn varchar(500), datum datetime, stad varchar(500), rowid uniqueidentifier default(newid()) ) -- Lägg till i kolumnordning insert Employee values ('Peter', '2009-01-01', 'Stockhoom') -- Med namngivnga kolumner insert Employee(namn, datum, stad) values ('Sofie', '2008-01-01', 'Götet') -- Utelämna en kolumn insert 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 update insert 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 update update 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 snapshottet create 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 table Select
*
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 query select
* 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 index create 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 fel create 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 plan checkpoint -- Skriver alla färdiga transactioner till disk dbcc DROPCLEANBUFFERS
dbcc FREEPROCCACHE

-- Testa med Client Statistics insert NewPersons
select
FirstName,
MiddleName,
LastName, BusinessEntityID from Person.Person

-- Droppa nu ett par index drop index NewPersons.IX_FirstName_LastName

-- Testa igen med Client Statistics insert 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örning create proc hejsan
with recompile as print 'hejsan' -- tvinga recompile på en proc vid en körning exec 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ågan use AdventureWorks2008

select * from Person.Person where FirstName = 'Kenny' -- = ger väldigt fin prestanda jämfört med andra jämförelser select * from Person.Person where FirstName like 'Ken%' -- Kan fortfarande använda index select * 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 som select * from Production.Product where ListPrice between 1000 and 2000 -- intervallprestanda är duglig om det finns index select * 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 OR select * from Production.Product where Weight = null -- jämförelse med null blir alltid false select * from Production.Product where Weight is null select Name, ISNULL(weight, 0) from Production.Product where Weight is null create 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 indexet select 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 cube select
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 c inner join orders o
on c.CustomerID = o.CustomerID


-- 1.13 select * from
Customers c left outer join orders o
on c.CustomerID = o.CustomerID
where o.OrderID is null





-- select * from Products -- Rekursiv CTE WITH 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 årtalen with c as
( SELECT datepart(yy, orderdate) as orderYear, customerid --,EmployeeID from orders where employeeid = 3 ) select COUNT(*), orderyear from c

group by orderyear

go create view v1 as
SELECT datepart(yy, orderdate) as orderYear, customerid --,EmployeeID from orders where employeeid = 3 go

select COUNT(*), orderyear from v1 group by orderyear


-- Ranka anställda efter hur mycket de har sålt with 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 c select * 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 produkten select
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.'
Comments