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