Uvod v SQL Server 2005
Matjaž Perpar, Microsoft
Tadej Vidmar, Microsoft
Podatkovna zbirka
Kaj je podatkovna zbirka?
Mesto za shranjevanje podatkov
Kako se razlikuje od navadne podatkovne
datoteke?
Boljša organiziranost
Lahko definiramo povezave med podatki
podatkov ne dobimo direktno iz zbirke, ampak
preko aplikacije, ki dostopa do zbrke
Aplikacija predstavi podatke v razumljivi obliki
Podatkovna zbirka
Uporabnik
Rezultat
Poizvedba
OLTP
OLAP
Aplikacija
Sistem za upravljanje s
podatkovnimi zbirkami
Microsoft SQL Server 2005
Arhitektura SUPB
Arhitektura SUPB
Instanca SUPB
Sistemske podatkovne zbirke
Instanca SUPB
Uporabniške podatkovne zbirke
Instanca SUPB
Instance SUPB
Default Instanca
Uporabimo ime računalnika
Samo ena na računalnik
Named Instanca
Uporabimo ime računalnika in ime instance
Aplikacije potrebujejo klientno komponento za
dostop
Več instanc...
Ena default in več named instanc
Vsaka deluje ločeno (kot na več računalnikih)
Tipi podatkovnih zbirk
Sistemske podatkovne zbirke
master
model
tempdb
msdb
pubs
Northwind
User1
Uporabniške podatkovne zbirke
distribution
Kako dostopamo do SQL strežnika
Grafično okolje
SQL Server Management Studio
Command Prompt orodje
SQLCMD
Prikaz okolja (Management Studio, SQLCMD)
Prikaz procesa startup/shutdown
Prikaz instanc
Arhitektura podatkovne zbirke
Arhitektura podatkovne zbirke
Fizična arhitektura
Logična arhitektura
Podatkovna zbirka
Podatkovna zbirka
Datoteka .mdf in .ldf
Tabela
Extent
Stored procedure
Stran (Page)
...
Kako so shranjeni podatki
Database
Data (file)
Log (file)
.mdf or .ndf
.ldf
Tables, Indexes
Extent
(8 contiguous
8-KB pages)
Data
Page (8 KB)
Maximum row size = 8060 bytes
Arhitektura podatkovne zbirke
Fizična struktura podatkovne zbirke
Pages and Extents
Database Files and Filegroups
Primary data files
Secondary data files
Log files
Arhitektura podatkovne zbirke
Logična struktura podatkovne zbirke
Objekti v pod. zbirki
Tabela
Podatkovni tip
Pogled (view)
Stored procedure
Function
Index
Constraint
Rule
Default
Trigger
Collations
Logins
Users
Roles
Groups
Izdelava nove podatkovne zbirke
Parametri podatkovne zbirke
Nadzor nad velikostjo pod. zbirke
Brisanje podatkovne zbirke
Kako deluje transaction log
1 Spremembe podatkov
v aplikaciji
Buffer Cache
2 Podatkovne strani se
naložijo v pomnilnik
3 Vsak stavek, ki spremeni
podatke se zapiše v TL
Disk
Disk
4 Checkpoint proces,
na določen interval,
Zapiše potrjene
(Commited)
Transakcije v PZ
Database Objects
PK
EmpNum
integer
Clustered Index
Anderson
Anderson
Barr
...
LastName
FirstName
FK
Stored
Procedure
UpdatePhone
Trigger
CtryCode
Extension
LastMod
longstring
varchar(20)
char(2)
char(6)
longstring
10191
Labrie
Angela
FR
x19891
\HR\KarlD
10192
Labrie
Eva
FR
x19433
\HR\KarlD
10202
Martin
Jose
SP
x21467
\HR\AmyL
EmployeePhoneView
SELECT lastname, firstname, extension
FROM employee
Check
x#####
Izdelava tabele, dodajanje podatkov
Funkcije

Aggregate Functions
SELECT AVG (UnitPrice) FROM Products

Scalar Functions
SELECT DB_NAME() AS 'database'

Rowset Functions
SELECT *
FROM OPENQUERY
(OracleSvr, 'SELECT ENAME, EMPNO FROM SCOTT.EMP')
Transakcije
Zagotovljena integriteta podatkov
Spremembe znotraj transakcije se:
Hkrati potrdijo(Commit)
Zavržejo (Rolled Back)
BEGIN TRANSACTION
UPDATE savings SET amount = (amount - 100)
WHERE custid = 78910
… <Rollback transaction if error>
UPDATE checking SET amount = (amount + 100)
WHERE custid = 78910
… <Rollback transaction if error>
COMMIT TRANSACTION
Transakcijska zaščita
BEGIN TRANSACTION
UPDATE savings
SET balance = (amount – 100)
WHERE custid = 78910
IF @@ERROR <> 0
BEGIN
RAISERROR ('Transaction not completed due to
savings account problem.', 16, -1)
ROLLBACK TRANSACTION
END
UPDATE checking
SET balance = (amount + 100)
WHERE custid = 78910
IF @@ERROR <> 0
BEGIN
RAISERROR ('Transaction not completed due to
checking account problem.', 16, -1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
Transact-SQL
Data Control Language stavki
GRANT
DENY
REVOKE
Data Definition Language stavki
CREATE
ALTER
DROP
Data Manipulation Language stavki
SELECT
INSERT
UPDATE
DELETE
Sistemski podatkovni tipi
Numerični
Integer
Natančni numerični
Približni numerični
Denarni
Datum in čas
Znaki, unicode znaki
Binarni
Ostali
Pogledi
Employees
EmployeeID
LastName
Firstname
Title
1
2
3
Davolio
Fuller
Leverling
Nancy
Andrew
Janet
~~~
~~~
~~~
USE Northwind
GO
CREATE VIEW dbo.EmployeeView
AS
SELECT LastName, Firstname
FROM Employees
EmployeeView
Lastname
Firstname
Davolio
Fuller
Leverling
Nancy
Andrew
Janet
Pogled
Prednosti pogledov
Zbrani podatki za uporabnika
Fokus na primernih in potrebnih
podatkih
Omejen dostop do zaupnih podatkov
Zmanjšamo kompleksnost
Skrivanje kompeksnosti
Poenostavljene poizvedbe
Poenostavljen nadzor nad dovoljenji
Izboljšane performance
Organizacija podatkov za izvoz
Primer
Orders
OrderID
10663
10827
10427
10451
10515
Customers
CustomerID RequiredDateShippedDate
BONAP
BONAP
PICCO
QUICK
QUICK
1997-09-24
~~~
1998-01-26
~~~
1997-02-24
~~~
1997-03-05
~~~
1997-05-07
~~~
1997-10-03
1998-02-06
1997-03-03
1997-03-12
1997-05-23
CustomerID CompanyName
BONAP
PICCO
QUICK
USE Northwind
GO
CREATE VIEW dbo.ShipStatusView
AS
SELECT OrderID, RequiredDate, ShippedDate,
ContactName
FROM Customers c INNER JOIN Orders o
ON c.CustomerID = O.CustomerID
WHERE RequiredDate < ShippedDate
ContactName
Bon app'
Laurence Lebihan
Piccolo und mehr Georg Pipps
QUICK-Stop
Horst Kloss
ShipStatusView
OrderID ShippedDate ContactName
10264
10271
10280
1996-08-23
1996-08-21 Laurence Lebihan
1996-08-30
1996-08-29 Georg Pipps
1996-09-12
1996-09-11 Horst Kloss
Izdelava pogleda v podatkovni zbirki
Kako SQL server dostopa do
podatkov
Kako so shranjeni podatki?
Vrstice so v podatkovnih straneh (data pages)
Kopice(Heaps) so zbirka podatkovnih strani (data
pages) za dotično tabelo
Kako dostopamo do podatkov?
Iskanje po vseh podatkih v tabeli
Uporaba indeksa, ki kaže na podatek na strani
Podatkovne strani (Data Pages)
Page 4
Con
Funk
White
...
...
Page 5
...
...
...
...
...
Rudd
White
Barr
...
...
Page 6
...
...
...
...
...
Akhtar
Funk
Smith
Martin
...
Page 8
Page 7
...
...
...
...
...
Smith
Ota
Jones
...
...
...
...
...
...
...
Martin
Phua
Jones
Smith
...
Page 9
...
...
...
...
...
Ganio
Jones
Hall
...
...
...
...
...
...
...
Indeksi
Zakaj izdelati indeks:
Pohitritev dostopa do podatkov
Unikatnost vrstic
Zakaj ne izdelati indeks:
Rabi prostor in čas
Overhead
Tipi indeksov
Clustered indeks
Non-clustered indeks
Iskanje brez uporabe indeksov
sysindexes
id indid = 0
IAM
Extent
…
127
128
129
130
…
Heap
Extent 127
01
Con
……
01
Rudd
01
Akhtar
02
01
Funk
Smith
……………
02
01
White
Con
02
01
Funk
Rudd
03
02
White
01
Ota
Akhtar
……………………
03
02
Barr
01
Funk
Smith
03
02
Smith
White
04
03
Durkin
02
Jones
Funk
......………………
…04
03
...
02
White
Ota
03
Martin
05
Lang
03
…...Barr
Smith
...............………
…………
...
03
Jones
...... ...Martin
...... ......
………04
…
...
…
...... ...
…… ......
......
… ...
Extent 128
01
Dunn
……
01
Rudd
01
Akhtar
02
01
Randall
Smith
……………
02
01
White
Con
02
01
Funk
Rudd
03
02
Ota
01
Ota
Akhtar
……………………
03
02
Barr
01
Funk
Smith
03
02
Smith
White
04
03
Slichter
02
Jones
Funk
......………………
…04
03
...
02
White
Ota
03
Martin
05
LaBrie
03
…...Barr
Smith
...............………
…………
...
03
Jones
...... ...Martin
...... ......
………04
…
...
…
...... ...
…… ......
......
… ...
First IAM
Bit Map
1
1
0
1
Extent 129
01
Seattle
……
01
Rudd
01
Akhtar
02
01
Paris
Smith
……………
02
01
White
Con
02
01
Funk
Rudd
03
02
Tokyo
01
Ota
Akhtar
……………………
03
02
Barr
01
Funk
Smith
03
02
Smith
White
04
03
Atlanta
02
Jones
Funk
......………………
…04
03
...
02
White
Ota
03
Martin
…………
...
03
…...Barr
Smith
...............………
...
03
Jones
………04
...... ...Martin
... ...
………
...... … ...............
…… ......
......
Extent 130
01
Graff
……
01
Rudd
01
Akhtar
02
01
Bacon
Smith
……………
02
01
White
Con
02
01
Funk
Rudd
03
02
Koch
01
Ota
Akhtar
……………………
03
02
Barr
01
Funk
Smith
02
Smith
White
……03
03
...
02
Jones
Funk
......………………
03
...
02
White
Ota
03
Martin
……04
……
...
03
…...Barr
Smith
...............………
...
03
Jones
………04
...... ...Martin
... ...
………
...... … ...............
…… ......
......
Iskanje z uporabo clustered indeksa
sysindexes
id
indid = 1
root
Clustered Index
Akhtar
…
Martin
Page 140 - Root
Akhtar
SELECT
lastname, firstname Martin
Ganio
Smith
FROM
member
…
…
WHERE
lastname
=
'Ota'
Page 141
Page 145
Akhtar 2334
Barr
5678
Con
2534
Funk 1334
Funk 1534
...
...
Page 100
...
...
...
...
...
...
Ganio 7678
Hall
8078
Jones 2434
Jones 5978
Jones 2634
...
...
Page 110
...
...
...
...
...
...
Martin 1234
Martin 7778
Ota
5878
Phua 7878
Rudd 6078
...
...
Page 120
...
...
...
...
...
...
Smith 1434
Smith 5778
Smith 7978
White 2234
White 1634
...
...
Page 130
...
...
...
...
...
...
Iskanje v kopici z uporabo nonclustered indeksa
sysindexes
id
indid = 2
root
Akhtar
...
Martin
SELECT lastname,
firstname
Page 37
Page
28
Page 12 - Root
AkhtarFROM member
Martin
Ganio
Smith
WHERE
lastname
...
...
Page 41
Akhtar 4:706:01
Barr 4:705:03
Con 4:704:01
Funk 4:706:02
Funk 4:704:02
Non-Leaf
Level
Non
Clustered
clustered
Index
BETWEEN 'Masters' AND 'Rudd'
Page 51
Ganio 4:709:01
Hall 4:709:04
Jones 4:709:02
Jones 4:708:03
Jones 4:707:03
Page 61
Martin 4:708:01
Matey 4:706:04
Matey
Ota 4:707:02
Ota
Phua 4:708:02
Rudd 4:705:01
Page 71
Leaf Level
Smith 4:706:03 (Key Value)
Smith 4:708:04
Smith 4:707:01
White 4:704:03
White 4:705:02
Heap
Page 704
01 ... Conn
02 ... Funk
03 ... White
... ... ...
... ... ...
File ID #4
Page 705
01 ... Rudd
02 ... White
03 ... Barr
... ... ...
... ... ...
Page 706
01 ... Akhtar
02 ... Funk
03 ... Smith
04 ... Matey
... ... ...
Page 707
01 ... Smith
02 ... Ota
02
03 ... Jones
... ... ...
... ... ...
Page 808
01 ... Martin
02
02 ... Phua
03 ... Jones
04 ... Smith
... ... ...
Page 709
01 ... Ganio
02 ... Jones
03 ... Hall
... ... ...
... ... ...
Iskanje v clustered indeksu z uporabo nonclustered
indeksa
sysindexes
Nonclustered
Index on
First Name
id
indid = 2
root
Non-Leaf
Level
Aaron
...
Jose
Aaron
Jose
SELECT
lastname, firstname,
phone
Deanna
Nina
FROM …
member
…
WHERE firstname = 'Mike'
Aaron
Adam
Amie
…
Con
Barr
Baldwin
…
Daum
Hall
Hampton
…
Jose
Judy
Mike
…
Leaf Level
Lugo
(Clustered
Kaethler
Key Value)
Nash
…
Barr
Kim
Nagata
O’Melia
Clustered Index
On Last Name
Barr
Cox
Daum
…
Deanna
Don
Doug
…
Adam
Arlette
Deanna
…
…
…
…
…
Kim
Kobara
LaBrie
…
Shane
Linda
Ryan
…
…
…
…
…
Nagata
Nash
Nixon
…
Susanne
Mike
Toby
…
…
…
…
…
Izdelava indeksov
Stored Procedure
Skrivanje detajlov sheme pod. zbirke
Varnostni mehanizem
Večje performanse
Zmanjšan omrežni promet
Izdelava Stored Procedur
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE()
AND ShippedDate IS Null
GO
Vračanje podatkov
Izdelava
CREATE PROCEDURE dbo.MathTutor
@m1 smallint,
@m2 smallint,
@result smallint OUTPUT
AS
SET @result = @m1* @m2
GO
Izvajanje
DECLARE @answer smallint
EXECUTE MathTutor 5,6, @answer OUTPUT
SELECT 'The result is: ', @answer
Rezultat
The result is:
30
Stored procedure
Arhiviranje in restavracija pb
Zakaj rabimo arhiviranje?
Nastavitev modela restavracije pz
Full Recovery model
Bulk_Logged Recovery model
Simple Recovery model
SQL Server arhiviranje
Med arhiviranjem pod. zbirka deluje
Shrani originalne datoteke in lokacijo le-teh
Zajame vse aktivnosti, ki so se dogajale
med procesom arhiviranja
Kam shraniti arhiv pod. zbirke?
Trdi disk
Tračne enote
Kdaj narediti arhiv pod. zbirk?
Sistemskih pod. zbirk
Uporabniških pod. zbirk
Tipi arhiviranja pod. zbirk
Full Database Backup
Differential Backup
Transaction Log Backup
Database File ali Filegroup Backup
Full Database Backup
Osnovno arhiviranje
Arhivira originalne datoteke, objekte,
podatke
Arhivira del Transaction Log-a
USE master
EXEC sp_addumpdevice 'disk', 'NwindBac',
'D:\MyBackupDir\NwindBac.bak'
BACKUP DATABASE Northwind TO NwindBac
D:\
Northwind
Data
Log
Backup
NwindBac
Differential Backup
Za pod. Zbirke, ki se pogosto spreminjajo
Potrebuje Full Database Backup
Arhivira spremembe pod. Zbirke od
prejšnjega Full Database Backup arhiviranja
Prihranimo čas v procesu arhiviranja in
restavriranja
BACKUP DATABASE Northwind
DISK = 'D:\MyData\MyDiffBackup.bak'
WITH DIFFERENTIAL
Transaction Log Backup
Potrebuje Full Database Backup
Arhivira vse spremembe na pod. Zbirki od
zadnjega ukaza BACKUP LOG do konca
trenutnega Transaction Log-a
Skrajša Transaction Log
USE master
EXEC sp_addumpdevice 'disk', ‘NwindBacLog',
'D:\Backup\NwindBacLog.bak'
BACKUP LOG Northwind TO NwindBacLog
Full Database Backup Strategy
Created Database
and Performed Full
Database Backup
Data
Log
Sunday
Full Database Backup
Data
Log
Monday
Full Database Backup
Data
Log
Tuesday
Full Database and Transaction Log Backup Strategy
Full Database
Backup
Data
Log
Log
Sunday
Full Database
Backup
Log
Log
Log
Monday
Data
Log
Differential Backup Strategy
Full Database
Backup
Data
Log
Differential
Backup
Log
Log
Monday
Log

Data
Log
Differential
Backup
Log
Log
Log
Tuesday

...
Database File or Filegroup Backup Strategy
Full Database
Backup
Data
Log
Log
Monday
Log
Data
File 1
Log
Tuesday
Log
Data
File 2
Log
Log
Wednesday
Data
File 3
Log
Thursday
Log
Arhiviranje in restavracija pod. zbirke
Distribucija, replikacija podatkov
Približa podatke uporabnikom
Neodvisnost lokacije
Ločitev operacij
Online transaction processing (OLTP)
Aplikacije z veliko branji
Lahko zmanjša konflikte
Kriteriji za uporabo distribucije ali replikacije
Replikacija
UPDATE
Distribuirane transakcije
Kriteriji
Čas/Latenca
Avtonomija
Transakcijska
konsistenca
UPDATE
UPDATE
Methods to Distribute Data
Merge Replication
Snapshot Replication
Snapshot Replication with
Immediate or Queued
Updating Subscriptions
Transactional Replication
Transactional Replication with
Immediate or Queued
Updating Subscriptions
Distributed Transactions
Manjša avtonomija
Manjša latenca
Višja avtonomija
Večja latenca
Avtentikacija prijave
AVTENTIKACIJA
Windows
Group or User
Windows OS
ALI
SQL Server
Login Account
SQL strežnik
preveri uporabniško
ime in geslo
Uporabniški računi in vloge (Roles)
Windows
Group User
SQL strežnik
preveri uporabniško
ime in geslo
Windows
OS
ALI
Uporabnik PB
SQL
Server
Vloga PB (Role)
SQL Server
Prijavni račun
(Login Account)
SQL strežnik
preveri uporabniško
ime in geslo
Tipi vlog
Fiksne strežniške vloge
Group administrative privileges at the server
level
Fiksne vloge podatkovne zbirke
Group administrative privileges at the
database level
Vloge definirane s strani uporabnika
Represent work defined by a group of
employees within an organization
Varnost
Kaj je XML?
Vse bolj pomemben standard za zapis
podatkov na internetu
Tekstovna datoteka z določenimi
oznakami, ki definirajo strukturo datoteke
Za izmenjavo podatkov med različnimi
sistemi
XML Shema – določi strukturo XMLa
XML v podatkovni zbirki
FName
LName
Addr
Phone
Fax
XML
XML
XML podatkovni tip
Relacijski podatki -> XML
XML -> Relacijski podatki
XML
Integracija standardov XML
XML podatkovni tip, sheme, indeksi
Shranjevanje in upravljanje z delno
strukturiranimi in hierarhičnimi podatki
Povezava jezikov XQuery in DML
Omogoča dostop do shranjenih podatkov XML
Delni popravki XML podatkov
Ni potrebno zamenjevati celotnih podatkov XML
Prednosti za končne uporabnike
Možna izvedba novih scenarijev (obdelava,
shranjevanje dokumentov, ...)
Dostop do podatkov XML
SQL
XML
XML
Podatkovni
tip
FName
LName
Addr
Phone
Fax
XML
Delo z XML v SQL 2005
.Net koda v podatkovni zbirki
UDT
UDF
UDP
UDT
Razvijalska izkušnja
VB,C#,C++
Projekt
VS.NET
Zbir
“TaxLib.dll”
create
create
create
create
create
Gostitelj CLR
v procesu
SQL Server
select sum(tax(sal,state))
from Emp
where county = ‘King’
Prevod
assembly …
function …
procedure …
trigger …
type …
Dodajanje zbira .NET v SQL strežnik
Vprašanja?
© 2004 Microsoft Corporation. All rights reserved.
Descargar

www.fri.uni