Velká kniha T-SQL by Zoner Press

E N C Y K L O P E D I E

SQL Server 2005 KOMPENDIUM ZNALOSTÍ PRO ZAČÁTEČNÍKY I PROFESIONÁLY

Velká kniha T-SQL & SQL Server 2005 je určena všem programátorům webových aplikací a administrátorům, kteří potřebují pracovat s Transact SQL a SQL Serverem 2005. Zahrnuje totiž prakticky všechny oblasti, se kterými se můžete ve své praxi setkat. Kniha vás formou přehledných návodů vede od naprostých začátků (jako je např. zvládnutí příkazů select, insert, update a delete) k transakcím, tabulkám, indexům, pohledům, uloženým procedurám, uživatelsky definovaným funkcím, typům a triggerům, aby samozřejmě neopomenula různá pokročilá témata jako jsou webové služby, možnosti pro zpracování chyb, integrace s CRL, účastníci, šifrování, optimalizace databází, zrcadlení databází, distribuované dotazy či ladění výkonu. Nechybí také důležité informace o konfiguraci SQL serveru, či témata věnovaná zálohování a obnově databáze.

JOSEPH SACK je nezávislý konzultant z Minneapolis ve státě Minnesota. Od roku 1997 zajišťuje zákaznickou podporu a vývoj aplikací v prostředí SQL Serveru pro klienty z různých oblastí. Patří mezi ně například finanční služby, distribuce multimédií, informační technologie, výroba zboží či nákup a prodej nemovitostí. Je držitelem certifikátu MCDBA (Microsoft Certified Database Administrator).

Pod tímto logem vycházejí publikace určené pro každého, kdo se zajímá o tvorbu webových stránek. Od ryze praktických příruček a průvodců až po komplexní publikace o všem, co potřebuje webdesignér při každodenní práci. Na vydavatelský plán a výhody, které můžete získat, se informujte na adrese vydavatelství.

www.zoner.cz Fotografie z nabídky fotobanky HELLER.CZ

E N C Y K LO P E D I E W E B D E S I G N E R A

Věrným čtenářům je určen výhodný PRÉMIOVÝ PLUS PROGRAM. DOPORUČENÁ CENA: 670 KČ KATALOGOVÉ ČÍSLO: ZR618

Zoner Press tel.: 532 190 883 fax: 543 257 245 e-mail: knihy@zoner.cz http://www.zonerpress.cz

ISBN

978-80-86815-57-2

ZONER software, s.r.o., Nové sady 18, 602 00 Brno

VELKÁ KNIHA

O autorovi

Joseph Sack

VELKÁ KNIHA kompendium pro začátečníky i profesionály

T-SQL &

ZONER software, s.r.o. významný producent software v oblasti digitální fotograﬁe, počítačové graﬁky a multimédií, poskytovatel internetových služeb, souvisejících s prezentací na internetu a e-komercí, a nakladatelství odborné literatury.

T-SQL & SQL Server 2005

ENCYKLOPEDIE WEBDESIGNERA

Joseph Sack

V E L K Á K N I HA

W E B D E S I G N E R A

T-SQL &

SQL Server 2005 KOMPENDIUM ZNALOSTÍ PRO ZAČÁTEČNÍKY I PROFESIONÁLY

Joseph Sack

www.zonerpress.cz 9 7 8 8 0 8 6

8 1 5 5 7 2

VELKÁ KNIHA

T-SQL & SQL Server 2005 KOMPENDIUM ZNALOSTÍ PRO ZAČÁTEČNÍKY I PROFESIONÁLY

Joseph Sack

SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach Joseph Sack Original English language edition published Apress L.P., 2560 Ninth Street, Suite 219, Berkeley, CA 94710 USA. Copyright © 2005 by Apress L.P. Czech language edition copyright © 2007 by ZONER software, s.r.o. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from Apress L.P. Originální anglické vydání vydal Apress L.P., 2560 Ninth Street, Suite 219, Berkeley, CA 94710 USA. Copyright © 2005 Apress L.P. České vydání vydal ZONER software, s.r.o., copyright © 2007. Všechna práva vyhrazena. Žádná část této publikace nesmí být reprodukována nebo předávána žádnou formou nebo způsobem, elektronicky ani mechanicky, včetně fotokopií, natáčení ani žádnými jinými systémy pro ukládání bez výslovného svolení Apress L.P.

Velká kniha T-SQL & SQL Server 2005 kompendium znalostí pro začátečníky i profesionály Autor: Joseph Sack Copyright © ZONER software, s.r.o. Vydání první v roce 2007. Všechna práva vyhrazena. Zoner Press Katalogové číslo: ZR618 ZONER software, s.r.o. Nové sady 18, 602 00 Brno Překlad: RNDr. Jan Pokorný Odpovědný redaktor: Miroslav Kučera Šéfredaktor: Ing. Pavel Kristián DTP: Filip Božoň © Cover foto: Jiří Heller, HELLER.CZ s.r.o, www.heller.cz Informace, které jsou v této knize zveřejněny, mohou byt chráněny jako patent. Jména produktů byla uvedena bez záruky jejich volného použití. Při tvorbě textů a vyobrazení bylo sice postupováno s maximální péčí, ale přesto nelze zcela vyloučit možnost výskytu chyb. Vydavatelé a autoři nepřebírají právní odpovědnost ani žádnou jinou záruku za použití chybných údajů a z toho vyplývajících důsledků. Všechna práva vyhrazena. Žádná část této publikace nesmí být reprodukována ani distribuována žádným způsobem ani prostředkem, ani reprodukována v databázi či na jiném záznamovém prostředku či v jiném systému bez výslovného svolení vydavatele, s výjimkou zveřejnění krátkých částí textu pro potřeby recenzí. Veškeré dotazy týkající se distribuce směřujte na: Zoner Press ZONER software, s.r.o. Nové sady 18, 602 00 Brno tel.: 532 190 883, fax: 543 257 245 e-mail: knihy@zoner.cz http://www.zonerpress.cz

ISBN 978-80-86815-57-2

Stručný obsah Kapitola 1

SELECT

Kapitola 2

INSERT, UPDATE, DELETE

Kapitola 3

Transakce, uzamykání, blokování a mrtvé zámky

121

Kapitola 4

Tabulky

149

Kapitola 5

Indexy

203

Kapitola 6

Fulltextové vyhledávání

223

Kapitola 7

Pohledy

243

Kapitola 8

Funkce SQL Serveru

263

Kapitola 9

Podmíněné zpracování, řízení toku, kurzory

313

Kapitola 10

Uložené procedury

333

Kapitola 11

Uživatelsky deﬁnované funkce a typy

353

Kapitola 12

Triggery

381

Kapitola 13

Integrace s CLR

411

Kapitola 14

XML

431

Kapitola 15

Webové služby

453

Kapitola 16

Zpracování chyb

471

Kapitola 17

Účastníci

487

Kapitola 18

Subjekty a povolení

517

Kapitola 19

Šifrování

549

Kapitola 20

Service Broker

579

Kapitola 21

Konﬁgurace a prohlížení voleb SQL Serveru

617

Kapitola 22

Vytváření a konﬁgurace databází

623

Kapitola 23

Integrita a optimalizace databáze

679

Kapitola 24

Údržba databázových objektů a závislosti objektů

699

Kapitola 25

Zrcadlení databáze

705

Kapitola 26

Momentky databáze

727

Kapitola 27

Propojené servery a distribuované dotazy

735

Kapitola 28

Vylaďování výkonu

753

Kapitola 29

Zálohy a zotavení

801

Obsah O autorovi

O odborném recenzentovi

Poděkování

Úvod

Zdrojové soubory

Kapitola 1

SELECT

Základní tvar příkazu SELECT

25 25

Výběr konkrétních sloupců pro všechny řádky

Výběr všech sloupců pro všechny řádky

Selektivní dotazy se základní klauzulí WHERE

Použití WHERE pro speciﬁkaci řádků, které se mají vrátit v sadě výsledků

Kombinace vyhledávacích podmínek

Negace vyhledávací podmínky

Klauzule WHERE pište jednoznačně

Jak se používají operátory a výrazy

Použití operátoru BETWEEN pro hledání data od-do

Porovnávání

Kontrola hodnot NULL

Vracení řádků na základě seznamu hodnot

Zástupné symboly s operátorem LIKE

Řazení výsledků

Klauzule ORDER BY

Řazení výsledků s využitím klíčového slova TOP

Seskupování dat

Klauzule GROUP BY

Klauzule GROUP BY ALL

Selektivní získávání skupin pomocí HAVING

Techniky pro klauzuli SELECT

Odstranění duplicitních hodnot pomocí DISTINCT

DISTINCT v agregačních funkcích

Aliasy sloupců

Použití SELECT pro vytvoření skriptu

Spojování řetězců v dotazu

Dotaz SELECT, který vytvoří seznam hodnot oddělených čárkami

Klauzule INTO

Poddotazy

Testy existence shodujících se řádků pomocí poddotazů Dotazy na více zdrojů dat

53 54

Vnitřní spojení

Vnější spojení

Kartézské součiny

Samospojení

Odvozené tabulky

Kombinování sad výsledků s UNION

Vyvolání tabulkové funkce pro každý řádek pomocí APPLY

Klauzule CROSS APPLY

Klauzule OUTER APPLY

Pokročilé techniky pro zdroje dat Vrácení náhodných řádků pomocí TABLESAMPLE

67 67

Jak se s PIVOT vyrábějí kontingenční tabulky

Normalizování dat s UNPIVOT

Návrat odlišných nebo shodujících se řádků s EXCEPT a INTERSECT

Souhrny dat

Souhrny dat s WITH CUBE

Použití funkce GROUPING s WITH CUBE

Souhrny dat s WITH ROLLUP

Potlačení plánu vykonání dotazu

Pokyny pro operaci spojení

Použití pokynů pro dotaz

Pokyny pro tabulku

Společné tabulkové výrazy

Nerekurzivní společné tabulkové výrazy

Rekurzivní společné tabulkové výrazy

Kapitola 2

INSERT, UPDATE, DELETE

INSERT

95 95

Vkládání řádků do tabulky

Vložení řádku pomocí výchozích hodnot

Explicitní vkládání hodnot do sloupce s atributem IDENTITY

Vložení řádku do tabulky se sloupcem datového typu uniqueidentiﬁer

100

Vkládání řádků příkazem INSERT...SELECT

101

Vkládání dat prostřednictvím uložené procedury

102

UPDATE

104

Aktualizace jediného řádku

104

Aktualizace řádků na základě klauzulí FROM a WHERE

105

Aktualizace sloupců, které mají datové typy pro objemná data

107

Vkládání nebo aktualizace souboru s obrázkem s OPENROWSET a BULK

109

DELETE

112

Odstraňování řádků

112

Vyprázdnění tabulky

114

Klauzule OUTPUT Klauzule OUTPUT v příkazech INSERT, UPDATE a DELETE

115 115

Modiﬁkace dat po etapách pomocí TOP

118

Odstraňování řádků po etapách

119

Kapitola 3

Transakce, uzamykání, blokování a mrtvé zámky

Řízení transakcí

121 121

Explicitní transakce

123

Zobrazení nejstarší aktivní transakce s DBCC OPENTRAN

126

Uzamykání

128

Prohlížení aktivity zámků Transakce, uzamykání a souběžné zpracování Příkaz SET TRANSACTION ISOLATION LEVEL Blokování

130 132 134 138

Identiﬁkace a vyřešení blokujících procesů

139

Příkaz SET LOCK TIMEOUT

142

Mrtvé zámky

143

Identiﬁkace mrtvého zámku pomocí indikátorů sledování

143

Nastavení priority mrtvého zámku

147

Kapitola 4

Tabulky

Základy práce s tabulkami

149 149

Vytvoření tabulky

152

Přidání nového sloupce do existující tabulky

153

Změna deﬁnice sloupce

154

Vytvoření vypočítávaného sloupce

155

Odstranění sloupce tabulky

156

Získání informací o tabulce

157

Odstranění tabulky

157

Základy kolace

158

Prohlížení metadat kolace

158

Vyznačení kolace sloupce

160

Klíče

160

Vytvoření tabulky s primárním klíčem

162

Přidání omezení primárního klíče do existující tabulky

163

Vytvoření tabulky s odkazem cizího klíče

163

Přidání cizího klíče do existující tabulky

164

Vytváření rekurzivních cizích klíčů

165

Povolení kaskádových změn v cizích klíčích

167

Náhradní klíče

169

Použití IDENTITY během vytváření tabulky

170

Zobrazení a oprava hodnot IDENTITY s DBCC CHECKIDENT

171

Vlastnost ROWGUIDCOL

173

Omezení

174

Omezení UNIQUE

174

Přidání omezení UNIQUE do existující tabulky

175

Omezení CHECK

176

Přidání omezení CHECK do existující tabulky

177

Vypínání a zapínání omezení

178

Přidání omezení DEFAULT během vytváření tabulky

180

Přidání omezení DEFAULT do existující tabulky

181

Odstranění omezení z tabulky

182

Dočasné tabulky a tabulkové proměnné

182

Dočasná tabulka pro opakované vyhledávání v dávce

184

Vytvoření tabulkové proměnné, která obsahuje dočasnou sadu výsledků

185

Jak zvládnout velmi rozsáhlé tabulky

186

Implementace horizontálního členění tabulky na oblasti

189

Určení oblasti, ve které se nacházejí data

192

Přidání nové oblasti

194

Odstranění oblasti

196

Přesun oblasti do jiné tabulky

197

Odstraňování funkcí a schémat členění

199

Umístění tabulky do skupiny souborů

199

Kapitola 5

Indexy

Přehled indexů

203 203

Vytvoření indexu tabulky

206

Vynucení jedinečnosti ve sloupcích, které nejsou částí klíče indexu

208

Vytvoření složeného indexu

209

Určení směru řazení sloupce indexu

210

Prohlížení metadat indexu

211

Vypnutí indexu

212

Odstraňování indexů

213

Změna existujícího indexu s DROP_EXISTING

214

Řízení výkonu a souběžného zpracování při budování indexu

214

Přechodné vytváření indexů v Tempdb

215

Řízení plánu paralelního vykonání pro vytvoření indexu

215

Přístup k tabulce během vytváření indexu

216

Volby indexu

216

Index s vloženými sloupci

217

Volby PAD_INDEX a FILLFACTOR

218

Vypnutí uzamykání stránek a řádků indexu

219

Správa velmi velkých indexů

220

Vytvoření indexu ve skupině souborů

220

Jak se implementuje členění indexu do oblastí

221

Kapitola 6

Fulltextové vyhledávání

Fulltextové indexy a katalogy

223 223

Vytvoření fulltextového katalogu

224

Vytvoření fulltextového indexu

225

Modiﬁkace fulltextového katalogu

227

Modiﬁkace fulltextového indexu

229

Odstranění fulltextového katalogu

231

Odstranění fulltextového indexu

232

Výpis metadat fulltextového katalogu a indexu

232

Základy fulltextového vyhledávání Prohledávání fulltextově indexovaných sloupců pomocí FREETEXT

234 234

Hledání slov pomocí CONTAINS Pokročilé vyhledávání

235 236

Hledání CONTAINS se zástupnými symboly

237

Hledání gramatických tvarů slova s CONTAINS

237

Hledání CONTAINS na základě blízkosti slov

238

Hledání s přidělováním pořadí

239

Vrácení výsledků vyhledávání s pořadím podle významu

239

Vrácení vážených výsledků vyhledávání podle přiděleného pořadí

241

Kapitola 7

Pohledy

Normální pohledy

243 244

Vytvoření normálního pohledu

244

Dotazy na deﬁnici pohledu

246

Výpis informací o pohledech uložených v databázi

247

Aktualizace deﬁnice pohledu

249

Modiﬁkace pohledu

249

Odstranění pohledu

250

Modiﬁkace dat prostřednictvím pohledu

250

Zašifrování pohledu Jak na zašifrování pohledu Indexované pohledy

251 252 252

Vytvoření indexovaného pohledu

253

Jak donutit optimalizátor, aby u indexovaného pohledu použil index

256

Členěné pohledy Vytvoření distribuovaného členěného pohledu

Kapitola 8

Funkce SQL Serveru

Agregační funkce

257 257

263 263

Výpočet aritmetického průměru

264

Výpočet počtu řádků

264

Nalezení nejmenší a největší hodnoty výrazu

265

Výpočet součtů hodnot

266

Statistické agregační funkce

266

Matematické funkce Jak se používají matematické funkce Funkce pro práci s řetězci

267 268 269

Převod znaků na kódy ASCII a zpět

271

Převody mezi celočíselnými a znakovými hodnotami Unicode

271

Nalezení počáteční pozice řetězce v jiném řetězci

272

Nalezení počáteční pozice řetězce v jiném řetězci pomocí zástupných symbolů

272

Určení podobnosti řetězců

273

Získání části řetězce zleva nebo zprava

274

Určení počtu znaků nebo bajtů v řetězci

275

Nahrazení části řetězce jiným řetězcem

275

Vsunutí řetězce do jiného řetězce

276

Převod písmen řetězce na malá, velká, nebo první ve slovech velká

277

Odstranění vedoucích a koncových mezer

279

Opakování výrazu

280

Opakování mezer

280

Výstup výrazu s opačným pořadím znaků

281

Vrácení části znakového výrazu

281

Jak se pracuje s hodnotami NULL

282

Náhrada hodnot NULL alternativní hodnotou

282

Flexibilní hledání pomocí ISNULL

282

Vrácení první hodnoty, která není NULL

284

Vrácení NULL při shodě dvou výrazů (jinak vrátit první výraz)

284

Funkce pro práci s datem a časem

285

Získání aktuálního data a času

286

Přičítání k datu, odečítání od data

286

Rozdíl mezi dvěma daty

288

Zobrazení řetězce vyjadřujícího část data

288

Zobrazení celočíselné hodnoty vyjadřující část data pomocí DATEPART

289

Zobrazení celočíselných částí data pomocí YEAR, MONTH a DAY

290

Převody datových typů pomocí Convert a Cast

290

Převody datových typů

291

Převody hodnot vyjadřujících datum

291

Je výraz datum nebo číslo?

293

Funkce přidělující pořadí

293

Očíslování řádků

294

Vrácení řádků podle přidělených pořadí

295

Vrácení řádků podle souvislé řady přidělených pořadí

297

Funkce NTILE

298

Systémové funkce pro sondování serveru, nastavení serveru a připojení

299

Nastavení pro první den týdne SQL Serveru

299

Jazyk používaný v aktuální relaci

300

Výpis a nastavení prodlevy pro uvolnění zámků v aktuální relaci

300

Zobrazení úrovně vnoření kontextu uložené procedury

301

Název aktuální instance SQL Serveru a verze SQL Serveru

301

ID relace aktuálního připojení

302

Počet otevřených transakcí

302

Kolik řádků ovlivnil předchozí příkaz?

303

Systémové statistické funkce

304

Jak se zobrazí nastavení databáze a SQL Serveru

306

Získání ID a názvu aktuální databáze

306

Získání ID a názvu databázového objektu

307

Jak se zjistí aplikace a hostitel aktuální uživatelské relace

307

Výpis informací o aktuálním uživateli a přihlašovacím kontextu

308

Prohlídka voleb uživatelského připojení

309

Funkce pro práci s IDENTITY a uniqueidentiﬁer

309

Zjištění poslední hodnoty ve sloupci s atributem IDENTITY

309

Zjištění hodnot základu a přírůstku sloupce s atributem IDENTITY

311

Vytvoření nové hodnoty datového typu uniqueidentiﬁer

311

Kapitola 9

Podmíněné zpracování, řízení toku, kurzory

Podmíněné zpracování

313 313

Vyhodnocení jediného vstupního výrazu s CASE

314

Vyhodnocování booleovských výrazů s CASE

315

Příkaz IF...ELSE

317

Řízení toku

319

Návrat s RETURN

319

Příkaz WHILE

321

Příkaz GOTO

323

Příkaz WAITFOR

325

Kurzory

327

Vytváření a používání kurzorů Transact-SQL

Kapitola 10

Uložené procedury

Základní informace o uložených procedurách

329

333 333

Vytvoření jednoduché uložené procedury

335

Vytvoření parametrizované uložené procedury

336

Výstupní parametry uložené procedury

339

Modiﬁkace uložené procedury

340

Odstraňování uložených procedur

341

Automatické vykonávání uložených procedur při startu SQL Serveru

341

Výpis metadat uložené procedury

343

Dokumentace uložených procedur

344

Uložené procedury a bezpečnost

344

Zašifrování uložené procedury

345

Speciﬁkace bezpečnostního kontextu procedury s EXECUTE AS

346

Rekompilace a ukládání do cache

349

Překompilování uložené procedury vždy, když se vykoná

349

Vyprázdnění cache procedur

351

Kapitola 11

Uživatelsky deﬁnované funkce a typy

Základy uživatelsky deﬁnovaných funkcí Skalární uživatelsky deﬁnované funkce

353 353 354

Přímé tabulkové uživatelsky deﬁnované funkce

358

Vícepříkazové uživatelsky deﬁnované funkce

360

Modiﬁkace uživatelsky deﬁnovaných funkcí

364

Prohlížení metadat UDF

366

Odstraňování uživatelsky deﬁnovaných funkcí

366

Zisky plynoucí z uživatelsky deﬁnovaných funkcí

367

Udržování opětovně využitelného kódu ve skalárních UDF

367

Využití skalárních UDF pro křížové odkazy na hodnoty přirozených klíčů

369

Vícepříkazové UDF místo pohledů

373

Základy uživatelsky deﬁnovaných typů

375

Vytváření a používání uživatelsky deﬁnovaných typů

375

Identiﬁkace sloupců a parametrů, které používají uživatelsky deﬁnované typy

378

Odstraňování uživatelsky deﬁnovaných typů

379

Kapitola 12

Triggery

Triggery DML

381 382

Trigger DML typu AFTER

383

Trigger DML typu INSTEAD OF

387

Triggery DML a transakce

390

Řízení triggerů DML na základě modiﬁkovaných sloupců

393

Výpis metadat triggeru DML

394

Triggery DDL

395

Vytvoření triggeru DDL, který sleduje databázové události

396

Vytvoření triggeru DDL, který sleduje události na úrovni serveru

399

Prohlížení metadat triggeru DDL

400

Správa triggerů

401

Modiﬁkace triggeru

401

Zapínání a vypínání triggerů

402

Limit pro hloubku vnořování triggerů

404

Řízení rekurze triggeru

405

Nastavení pořadí odpalování triggeru

406

Odstranění triggeru

408

Kapitola 13

Integrace s CLR

411

Přehled CLR

412

Kdy používat assembly (a kdy ne)

413

Přehled objektů CLR

414

Vytváření databázových objektů CLR

415

Zapnutí podpory CLR v SQL Serveru 2005

415

Napsání assembly pro uloženou proceduru CLR

416

Kompilace assembly do souboru DLL

419

Načtení assembly do SQL Serveru

420

Vytvoření uložené procedury CLR

421

Vytvoření skalární uživatelsky deﬁnované funkce CLR

423

Vytvoření triggeru CLR

426

Administrace assembly

428

Výpis metadat assembly

428

Modiﬁkace povolení assembly

428

Odstranění assembly z databáze

429

Kapitola 14

XML

431

XML a příbuzné technologie

432

Práce s nativním XML

434

Vytváření sloupců datového typu XML

434

Vkládání dat XML do sloupce

436

Ověřování platnosti dat XML pomocí schémat

437

Získávání dat XML

439

Modiﬁkace dat XML

442

Indexy XML

443

Konverze mezi dokumenty XML a relačními daty

445

Klauzule FOR XML

445

Příkaz OPENXML

450

Kapitola 15

Webové služby

453

Technologie webových služeb

454

Koncové body HTTP

455

Vytvoření koncového bodu HTTP

458

Správa bezpečnosti koncového bodu HTTP

461

Modiﬁkace koncového bodu HTTP

463

Odstranění koncového bodu HTTP

465

Rezervace jmenných prostorů

465

Vytvoření klienta .NET, který používá webovou službu

Kapitola 16

Zpracování chyb

Systémové a uživatelsky deﬁnované chybové zprávy

466

471 471

Výpis informací, které poskytují systémové chybové zprávy

471

Vytvoření uživatelsky deﬁnované chybové zprávy

473

Odstranění uživatelsky deﬁnované chybové zprávy

475

Příkaz RAISERROR Vyvolání chybové zprávy pomocí RAISERROR Příkaz TRY...CATCH

475 476 478

Staromódní zpracování chyb

480

Zpracování chyb s TRY...CATCH

482

Zpracování chyb s TRY...CATCH, aniž se musí přepsat uložená procedura

483

Vnořené konstrukce TRY...CATCH

484

Kapitola 17

Účastníci

Účastníci Windows

487 487

Jak se vytvoří přihlášení Windows

489

Výpis přihlášení Windows

490

Změny v přihlášení Windows

490

Jak se odstraní přihlášení Windows

492

Jak se uživateli Windows nebo skupině Windows odepře přístup k SQL Serveru

493

Účastníci SQL Serveru

493

Jak se vytvoří přihlášení SQL Serveru

495

Výpis přihlášení SQL Serveru

496

Změny v přihlášení SQL Serveru

496

Jak se odstraní přihlášení SQL

499

Správa členů serverových rolí

499

Výpis informací o ﬁxních serverových rolích

500

Databázoví účastníci

502

Vytváření databázových uživatelů

503

Výpis informací o databázových uživatelích

504

Modiﬁkace databázového uživatele

505

Odstranění databázového uživatele z databáze

506

Oprava osiřelých databázových uživatelů

506

Výpis informací o ﬁxních databázových rolích

508

Správa členství ve ﬁxních databázových rolích

510

Správa uživatelsky deﬁnovaných databázových rolí

511

Správa aplikačních rolí

513

Kapitola 18

Subjekty a povolení

Přehled povolení Výpis povolení SQL Serveru 2005, která lze přiřazovat

517 518 519

Subjekty a povolení s oborem server

522

Správa serverových povolení

524

Subjekty a povolení s oborem databáze

525

Správa databázových povolení

527

Subjekty a povolení s oborem schéma

528

Správa schémat

531

Správa povolení schématu

532

Povolení objektů Správa povolení na úrovni objektů Správa povolení přes obory subjektů

534 538 539

Zjištění povolení aktuálního připojení k nějakému subjektu

539

Výpis povolení účastníka podle oboru subjektů

541

Změna vlastnictví subjektu

544

Jak povolit přihlášením SQL přístup ke zdrojům mimo SQL Server?

546

Kapitola 19

Šifrování

549

Šifrování podle šifrovací fráze

549

Šifrování podle šifrovací fráze pomocí funkce Hlavní klíče

550 552

Záloha a obnova hlavního klíče služby

553

Vytvoření, regenerace a odstranění hlavního databázového klíče

554

Záloha a obnova hlavního databázového klíče

555

Odstranění šifrování hlavním klíčem služby z hlavního databázového klíče

557

Šifrování asymetrickým klíčem

558

Vytvoření asymetrického klíče

558

Výpis informací o asymetrických klíčích v aktuální databázi

559

Změna hesla pro privátní klíč asymetrického klíče

560

Šifrování a dešifrování dat pomocí asymetrického klíče

560

Odstranění asymetrického klíče

563

Šifrování symetrickým klíčem

563

Vytvoření symetrického klíče

564

Výpis symetrických klíčů, které jsou v aktuální databázi

565

Změna způsobu šifrování symetrického klíče

565

Šifrování a dešifrování symetrickým klíčem

567

Odstranění symetrického klíče

571

Šifrování certiﬁkátem

571

Vytvoření databázového certiﬁkátu

571

Prohlížení certiﬁkátů uložených v databázi

572

Zálohování a obnova certiﬁkátu

573

Správa privátního klíče certiﬁkátu

574

Šifrování a dešifrování pomocí certiﬁkátu

576

Kapitola 20

Service Broker

579

Ukázkový scénář: online knihkupectví

580

Vytvoření základní aplikace Service Broker

580

Zapnutí aktivit Service Broker v databázích

581

Vytvoření hlavního databázového klíče pro šifrování

582

Typy zpráv

582

Vytváření kontraktů

584

Vytváření front

586

Vytváření služeb

588

Zahájení dialogové konverzace

590

Dotaz na příchozí zprávy ve frontě

592

Získání zprávy a odpověď na ni

593

Ukončení konverzace

595

Vytvoření uložené procedury pro zpracování zpráv Vytvoření uložené procedury Implementace Service Broker na vzdálených serverech

597 598 601

Bezpečnost transportu

603

Bezpečnost dialogové konverzace

606

Vytvoření tras a vazeb vzdálené služby

608

Notiﬁkace událostí Zachycování přihlašovacích příkazů

Kapitola 21

Konﬁgurace a prohlížení voleb SQL Serveru

Prohlížení konﬁgurace SQL Serveru Změny konﬁguračních nastavení SQL Serveru

Kapitola 22

Vytváření a konﬁgurace databází

Vytváření, modiﬁkace a odstraňování databází

612 612

617 617 620

623 623

Vytvoření databáze s výchozí konﬁgurací

624

Prohlížení informací o databázi

624

Vytvoření databáze pomocí voleb souborů

626

Vytvoření databáze s uživatelsky deﬁnovanou skupinou souborů

629

Nastavení uživatelského přístupu k databázi

632

Přejmenování databáze

634

Odstranění databáze

636

Odpojení databáze

636

Připojení databáze

638

Konﬁgurace databázových voleb

639

Výpis databázových voleb

640

Konﬁgurace voleb ANSI SQL

641

Konﬁgurace automatických voleb

643

Vytvoření nebo modiﬁkace databáze tak, aby umožňovala externí přístup

645

Speciﬁkace jiné kolace pro databázi, než je výchozí kolace serveru

647

Konﬁgurace voleb kurzoru

648

Volba DATE_CORRELATION_OPTIMIZATION

650

Modiﬁkace chování parametrizace v databázi

651

Jak se zapne konzistentní čtení pro transakci

654

Konﬁgurace modelů zotavení databáze

656

Jak se nakonﬁguruje kontrola datových stránek

657

Řízení přístupu k databázi a vlastnictví

659

Změna stavu databáze na online, oﬄine nebo emergency

659

Změna vlastníka databáze

660

Správa databázových souborů a skupin souborů

662

Přidání datového souboru nebo souboru protokolu do existující databáze

662

Odstranění datového souboru nebo souboru transakčního protokolu z databáze

664

Změna umístění datového souboru nebo souboru transakčního protokolu

665

Změna logického názvu souboru

666

Zvýšení velikosti databázového souboru a modiﬁkace jeho voleb růstu

667

Přidání skupiny souborů do existující databáze

668

Nastavení výchozí skupiny souborů

669

Odstranění skupiny souborů

670

Nastavení databáze nebo skupiny souborů pouze ke čtení

671

Prohlížení a správa prostoru, který zabírá databáze na disku

672

Jak databáze využívá prostor na disku

672

Jak se srazí velikost databáze nebo databázového souboru

674

Kapitola 23

Integrita a optimalizace databáze

Kontroly databáze

679 679

Kontrola konzistence struktur pro alokaci místa na disku s DBCC CHECKALLOC

680

Kontrola alokace a strukturální integrity objektů databáze s DBCC CHECKDB

682

Tabulky a omezení

685

Kontrola alokace a strukturální integrity všech tabulek ve skupině souborů s DBCC ...

685

Kontrola integrity dat tabulek a indexovaných pohledů s DBCC CHECKTABLE

687

Kontrola integrity tabulky příkazem DBCC CHECKCONSTRAINTS

690

Kontrola konzistence systémových tabulek s DBCC CHECKCATALOG

692

Údržba indexů

692

Přebudování indexů

693

Defragmentace indexů

696

Kapitola 24

Údržba databázových objektů a závislosti objektů

Údržba databázového objektu

699 699

Změna názvu uživatelsky vytvořeného databázového objektu

699

Změna schématu objektu

701

Závislosti objektu

702

Výpis informací o závislostech databázového objektu

702

Prohlížení deﬁnice objektu

703

Kapitola 25

Zrcadlení databáze

705

Zrcadlení databáze v kontextu

706

Architektura zrcadlení databáze

707

Příprava pro zrcadlení databáze

708

Vytvoření koncových bodů zrcadlení

709

Vytvoření zálohy hlavní databáze a její obnova

713

Vytvoření databázové relace se zrcadlením

716

Shrnutí příprav pro zrcadlení databáze

719

Tři módy zrcadlení databáze

720

Změna operačního módu

721

Přepínání rolí hlavní a zrcadlové databáze

722

Jak se pozastaví a opět rozběhne relace se zrcadlením

723

Jak se zastaví relace se zrcadlením a odstraní koncové body

723

Monitorování a konﬁgurace voleb

724

Monitorování stavu zrcadla

724

Zkrácení doby potřebné na přepnutí databází

725

Nastavení prodlevy připojení

726

Kapitola 26

Momentky databáze

Základní informace o momentkách

727 727

Jak se vytvoří momentka databáze a jak se na ni kladou dotazy

728

Odstranění momentky databáze

730

Zotavení dat pomocí databázové momentky

730

Kapitola 27

Propojené servery a distribuované dotazy

Základní informace o propojených serverech Vytvoření propojeného serveru k jiné instanci SQL Serveru

735 736 736

Konﬁgurace vlastností propojeného serveru

738

Výpis informací o propojeném serveru

739

Odstranění propojeného serveru

740

Přihlášení propojeného serveru

740

Jak se mapuje přihlášení na propojený server

741

Výpis propojených přihlášení

742

Odstranění mapování na propojený server

743

Vykonávání distribuovaných dotazů

743

Vykonání distribuovaných dotazů nad propojeným serverem

743

Vytvoření a používání aliasu čtyřdílného názvu propojeného serveru

745

Vykonávání distribuovaných dotazů s OPENQUERY

746

Vykonávání jednorázových dotazů s OPENROWSET

747

Čtení dat ze souboru pomocí OPENROWSET BULK

748

Kapitola 28

Vylaďování výkonu

753

Tipy pro vyšší výkon dotazů

754

Zachycení a vyhodnocení výkonu dotazu

756

Jak se nástrojem SQL Server Proﬁler zachytí pomalé dotazy

756

Zachycení vykonávajících se dotazů se sys.dm_exec_requests

760

Graﬁcký plán vykonání dotazu

761

Zobrazení odhadnutého plánu vykonání dotazu s příkazy Transact-SQL

765

Vynucené použití konkrétního plánu dotazu v SQL Server 2005

769

Výpis informací o vykonání dotazu

771

Výpis výkonových statistik plánů uložených do cache

774

Statistiky

775

Ruční vytváření statistik

776

Aktualizace statistik

777

Generování a aktualizace statistik pro všechny tabulky

778

Výpis informací o statistikách

780

Odstraňování statistik

781

Vylaďování indexů

781

Výpis fragmentace indexů

783

Výpis využití indexů

786

Nástroj Database Engine Tuning Advisor

788

Různé další techniky Alternativa k dynamickému SQL

794 794

Použití pokynů, aniž by se musela modiﬁkovat aplikace SQL

Kapitola 29

Zálohy a zotavení

796

801

Vytvoření plánu záloh a zotavení

801

Zálohy

803

Základní úplná záloha

806

Pojmenujte a popište své zálohy a zálohovací média

809

Konﬁgurace doby, po kterou se zálohy uchovávají

810

Zálohování po pruzích

812

Pojmenované zálohovací zařízení

813

Zrcadlení sad záloh

815

Zálohování transakčního protokolu

817

Sady záloh vytvářené s volbou COPY_ONLY

819

Diferenční zálohy

820

Zálohování jednotlivých souborů nebo skupin souborů

820

Částečné zálohování

823

Výpis metadat zálohy

824

Obnova databáze

827

Obnova databáze z úplné zálohy

827

Obnova databáze ze zálohy transakčního protokolu

832

Obnova databáze z diferenční zálohy

836

Obnova souboru nebo skupiny souborů

837

Obnova po částech

838

Obnova stránky

840

Rejstřík

843

O autorovi JOSEPH SACK je nezávislý konzultant z Minneapolis ve státě Minnesota. Od roku 1997 vyvíjí a podporuje prostředí SQL Serveru pro klienty z různých oblastí. Patří mezi ně například finanční služby, distribuce multimédií, informační technologie, výroba zboží či nákup a prodej nemovitosti. Joseph získal titul bakaláře v oboru psychologie na Minnesotské univerzitě. Je autorem publikace SQL Server 2000 Fast Answers for DBAs and Developers a je také držitelem certifikátu MCDBA (Microsoft Certified Database Administrator). Chcete-li se ho na něco zeptat, nebo s ním něco konzultovat, je dosažitelný na joe.sack@gmail.com.

O odborném recenzentovi EVAN TERRY už přes 15 let pracuje na různých pozicích v odvětví informačních technologií (pro státní i privátní sektor) jako programátor analytik, systémový inženýr, konzultant softwaru, vývojář, analytik dat a architekt dat. Upřímně věří, že máme-li jako profesionálové z oblasti IT uspět při vývoji složitých systémů, musíme dokonale rozumět věcné problematice procesů, které podporujeme. Evan se také snaží přemostit propast dělící odborníky od laické veřejnosti, protože chápe perspektivy obou skupin a pomáhá jim, aby jejich vzájemná komunikace byla efektivní. Evan je také spoluautorem publikace Beginning Relational Data Modeling vydavatelství Apress.

Poděkování Tato kniha je věnována Davidu Hatchovi, protože díky tomu, že mi neúnavně pomáhal, dodával důvěru, poskytoval cenné rady a podporu, se všechno dotáhlo do úspěšného konce. Děkuji Tony Davisovi za jeho schopnost dívat se na věci s nadhledem, za jeho vhled a jasná stanoviska, a že mi také pomohl najít vhodný vypravěčský styl. Tony mi dodal opravdovost, takže výsledkem je to nejlepší, čeho jsem byl schopen. Díky zasluhuje také Evan Terry, který věnoval neskutečně mnoho péče tomu, aby se kladl důraz nejen na odbornou stránku knihy, ale také na vše, co je v ní obsaženo. Evan má talent na jasné formulace a umí klást správné otázky. Bylo příjemné vědět, že mě Tony i Evan z povzdálí pozorně sledují. Děkuji také Beth Christmasové za její báječný způsob myšlení, za její nadání umět udržet věci v běhu, a za její promptní reakce na mé naléhavé otázky. Beth se starala právě tak úporně jako já, aby se plán prací na knize dodržel, za což jsem jí moc vděčný. Dík patří také velmi talentované Julii Smithové, která mi pomáhala nacházet správná slova, abych byl důsledný, a aby tok vyprávění plynul hladce. Podobně jako Evan, i Julie dělala daleko víc, než měla v náplni práce, vyhodnocovala text na mnoha úrovních. Díky patří také Katie Stenceové, Kari Brooksové a zbytku týmu Apress – z některými z nich se mi nepodařilo seznámit, ale děkuji za jejich příspěvky, které jsem obdržel. Je příjemné vědět, že na mně záleží tolika lidem. A konečně – děkuji Gary Cornellovi, který zakoupil práva k mé poslední knize, má výbornou reputaci díky svým předchozím vydavatelských závazkům, je čestný, poctivý, a podporuje i mé budoucí projekty. Kéž by všechny vydavatelské firmy zaměstnávaly lidi jako je Gary! Bylo by mnohem více spokojených autorů odborných publikací!

Úvod Když dojde na lámání chleba a začnete skutečně prakticky řešit své každodenní úlohy SQL Serveru, odborná dokumentace vám obvykle sděluje mnohem víc, než potřebujete vědět. Řekněme, že jste v časové tísni a potřebujete do nějaké tabulky přidat nějaké omezení na jedinečnost dřív, než někdo do tabulky vloží duplicitní hodnotu. Kde najdete, jak na to? Jednou z prvních voleb jsou online knihy o SQL Serveru (SQL Server Books Online). Vzhledem k tomu, jak obrovské množství užitečných informací obsahují, bezpečně víte, že odpověď na vaši otázku tam někde je. Bohužel – než se vám ji podaří najít, pravděpodobně budete nějakou dobu bezradně klikat sem a tam, nebo se rovnou vydáte po falešné stopě. Až se pak konečně dostanete na správnou stránku, možná zjistíte, že potřebná informace je zasazena do rozsáhlého bloku syntaxe a že se popisovaná funkcionalita, a ani uvedené příklady nevztahují k tomu, co hledáte. Tuto knihu jsem psal se záměrem, abychom se jednou provždy vypořádali s potřebou nacházet, získávat a používat informace co nejrychleji. Jednotlivá témata se prezentují ve formě úloh. U každého tématu získáte nezbytné základy, abyste mohli začít pracovat, pak uvidíte jeden nebo více praktických příkladů, a nakonec se stručně a výstižně vysvětlí, jak to celé funguje. Ať už si potřebujete oprášit nějaké téma, které jste nechali nějakou dobu ležet ladem, nebo prostě teprve začínáte s Transact-SQL, budete moci snadno a rychle vytipovat a zvolit oblasti, v nichž potřebujete něco udělat nebo vylepšit. Tato kniha zahrnuje základní i pokročilejší témata a předvádí složitější techniky, které nabízí hit SQL Serveru 2005, Transact-SQL. Chcete se dozvědět více o tom, co je nového v SQL Serveru 2005? Nové schopnosti Transact-SQL i funkcionalita zaváděná nově v SQL Serveru 2005 se v této knize předvádějí také, takže si budete moci nové schopnosti otestovat rychle a s minimálním úsilím. Protože je kniha napsaná tak, aby byla přívětivá k přímému přístupu k informacím v ní obsažených, nemusíte ji číst od začátku, jednu kapitolu za druhou. Ať už jste začátečníci, mírně pokročilí, nebo příležitostní uživatelé SQL Serveru, věřím, že tato kniha vám poskytne bleskově všechna fakta, která potřebujete, abyste mohli snadno vyřešit své každodenní úkoly.

Zdrojové soubory Zdrojové soubory k této knize je možné stáhnout z následující adresy: http://www.zonerpress.cz/download/transact-sql.zip

Velikost souboru je 58 kB.

Kapitola 3

Transakce, uzamykání, blokování a mrtvé zámky V předchozích dvou kapitolách jsem probral jazyk modifikace dat (Data Modification Language) a poskytl návody k příkazům SELECT, INSERT, UPDATE a DELETE. Než přejdeme k jazyku definice dat (Data Definition Language, který slouží pro vytváření, úpravy a odstraňování tabulek, indexů, pohledů a dalších věci), proberu v této kapitole návody, v nichž uvidíte, jak se zpracovávají transakce, monitorují zámky, a jak se dá vypořádat s blokováním a s mrtvými zámky. Předvedu vám novou úroveň izolace v SQL Serveru 2005 (tzv. "momentka" neboli snapshot) a také dynamické správní pohledy (dynamic management views), jimiž se monitorují mrtvé zámky a pomáhají řešit problémy s blokováním.

Řízení transakcí Transakce jsou integrální součástí relačního databázového systému a pomáhají definovat jednolitou jednotku práce. Ta se může skládat z jednoho nebo několika příkazů Transact-SQL, které se buď potvrdí, nebo anulují (jako jediný celek). Tato funkcionalita "všechno nebo nic" pomáhá zabránit částečným aktualizacím a nestabilním stavům. K částečným aktualizacím dochází, když se některá z vzájemně závislých částí nějakého procesu anuluje nebo stornuje, aniž by se anulovaly (resp. vrátily zpět) i všechny ostatní části procesu, jež jsou na sobě závislé. Transakce se popisuje čtyřmi charakteristickými rysy, na které se odkazuje zkratkou ACID, z anglických termínů Atomicity, Consistency, Isolation (nebo Independence) a Durability:

• Atomicity (nerozložitelnost) znamená, že je transakce nedělitelnou entitou – vykonají se buď všechny její kroky, nebo žádný z nich.

• Consistency (důslednost) zajišťuje, že jsou data platná před transakcí i po ní. V platném stavu se stále musí udržovat integrita dat (například odkazy na cizí klíče) i interní datové struktury.

• Isolation (izolovanost) je požadavek na to, aby transakce nebyly závislé na jiných transakcích, které se mohou případně vykonávat souběžně (buď ve stejné době nebo se překrývat). Daná transakce nemůže vidět průběžná (nestabilní) data jiné transakce – vidí je buď v takovém stavu, v jakém byla, než transakce začala, nebo poté, co transakce skončila.

• Durability (stálost) znamená, že účinky transakce jsou trvalé, jakmile se transakce potvrdí, veškeré změny přečkají i výpadky systému. V této kapitole předvedu a proberu mechanismy a funkcionalitu SQL Serveru 2005, jimiž se zajišťuje soulad s principy ACID, konkrétně uzamykání a transakce.

122

Kapitola 3 – Transakce, uzamykání, blokování a mrtvé zámky

V SQL Serveru 2005 existují tři možné typy transakcí: potvrzované automaticky, implicitní a explicitní. Transakce s automatickým potvrzováním (autocommit) je výchozí chování SQL Serveru 2005, kdy se každý separátní příkaz Transact-SQL potvrzuje automaticky ihned poté, co skončí. Pokud například vykonáte za sebou dva příkazy INSERT, z nichž ten první se nezdaří a druhý uspěje, změny provedené druhým příkazem se uchovají, protože každý příkaz INSERT je automaticky obsažen ve své vlastní transakci. Přestože tento mód uvolňuje vývojáři ruce, protože se nemusí starat o explicitní transakce, může být závislost na tomto druhu transakčních činností chybou. Máte-li například dvě transakce, z nichž jedna připisuje na účet nějakou částku, a druhá z téhož účtu nějakou částku odepisuje, a ta první se nezdaří, z ničeho nic jste dlužníkem. To by jistě potěšilo banku, ale už asi ne zákazníka, jemuž z účtu náhle zmizí finanční prostředky! Automatické potvrzování je dokonce poněkud nebezpečné i pro jednoúčelové administrativní změny — pokud například omylem odstraníte všechny řádky z nějaké tabulky, bude už pozdě, až na to konečně přijdete, protože nemáte možnost transakci vrátit zpět (anulovat její účinky). K implicitním transakcím dochází tehdy, když je nějaká relace SQL Serveru v implicitním transakčním módu, a když se poprvé vykoná kterýkoliv z těchto příkazů: ALTERTABLE

FETCH

REVOKE

CREATE

GRANT

SELECT

DELETE

INSERT

TRUNCATETABLE

DROP

OPEN

UPDATE

Jakmile se vykoná jeden z výše uvedených příkazů, automaticky se vytvoří (otevře) nová transakce a zůstane otevřená tak dlouho, dokud se nevydá buď příkaz ROLLBACK, nebo příkaz COMMIT. Příkaz vyvolávající transakci se považuje za její součást. Implicitní mód se aktivuje v dotazovací relaci příkazem: SET IMPLICIT_TRANSACTIONS ON

Chcete-li tento mód vypnout (a vrátit se do explicitního módu), vydejte příkaz: SET IMPLICIT_TRANSACTIONS OFF

Implicitní mód může být velmi těžko zvládnutelný v rutinním provozu, protože designéři aplikací (i koneční uživatelé) mohou zapomínat transakce potvrzovat, takže je ponechávají otevřené velmi dlouho, čímž blokují jiná připojení (o blokování se více dozvíte v této kapitole později). Explicitní transakce jsou ty, které definujete sami. Je to zdaleka nejdoporučovanější mód práce v situacích, kdy databázová aplikace modifikuje data. Je to proto, že máte explicitní kontrolu nad tím, které jednotlivé modifikační operace patří do jediné transakce, i nad tím, co se má dít, dojde-li k nějaké chybě. Modifikace seskupíte dohromady do jediné entity pomocí svých vlastních instrukcí. Při práci s explicitními transakcemi se používají příkazy a klíčová slova Transact-SQL, která jsou podrobně popsaná v tabulce 3.1: Tabulka 3.1. Příkazy pro explicitní transakce Příkaz

Popis

BEGIN TRANSACTION

Nastavuje počátek explicitní transakce.

Velká kniha T-SQL & SQL Server 2005

123

Tabulka 3.1. Příkazy pro explicitní transakce Příkaz

Popis

ROLLBACK TRANSACTION

Obnoví původní data, která transakce modiﬁkovala. Převede data zpět do takového stavu, v jakém byla na začátku transakce. Prostředky, které měla transakce v držení, se uvolní.

COMMIT TRANSACTION

Ukončí transakci, pokud při ní nedošlo k žádným chybám, a potvrdí natrvalo provedené změny. Prostředky, které měla transakce v držení, se uvolní.

BEGIN DISTRIBUTED TRANSACTION

Umožňuje deﬁnovat počátek distribuované transakce, kterou bude spravovat koordinátor distribuovaných transakcí společnosti Microsoft (MS DTC, Microsoft Distributed Transaction Coordinator). MS DTC musí běžet lokálně a vzdáleně.

SAVE TRANSACTION

SAVE TRANSACTION určuje ukládací bod uvnitř transakce. Umožňuje deﬁnovat místo, kam se může transakce vrátit, jestliže se její část anulovala. Transakce se musí anulovat nebo potvrdit okamžitě poté, co se vrátila zpět do ukládacího místa.

@@TRANCOUNT

Vrací počet aktivních transakcí daného připojení. BEGIN TRANSACTION zvyšuje @@TRANCOUNT o 1, ROLLBACK TRANSACTION a COMMIT TRANSACTION snižují @@TRANCOUNT o 1. ROLLBACK TRANSACTION do ukládacího místa ji neovlivňuje.

Explicitní transakce V tomto návodu si předvedeme, jak se pomocí explicitních transakcí potvrzují nebo anulují modifikace dat v závislosti na tom, zdali se z dávky příkazu vrátí nějaká chyba: -- Zjistíme počet oddělení SELECT COUNT(*) BeforeCount FROM HumanResources.Department -- Sem si uložíme celočíselný kód poslední chyby DECLARE @Error int BEGIN TRANSACTION INSERT HumanResources.Department (Name, GroupName) VALUES ('Accounts Payable', 'Accounting') SET @Error = @@ERROR IF (@Error<> 0) GOTO Error_Handler INSERT HumanResources.Department (Name, GroupName) VALUES ('Engineering', 'Research and Development') SET @Error = @@ERROR IF (@Error <> 0) GOTO Error_Handler COMMIT TRAN Error_Handler:

124

Kapitola 3 – Transakce, uzamykání, blokování a mrtvé zámky

IF @Error <> 0 BEGIN ROLLBACK TRANSACTION END -- Zjistíme, jaký je počet oddělení po transakci SELECT COUNT(*) AfterCount FROM HumanResources.Department

Vrátí se následující: BeforeCount ----------16 (1 row(s) affected) (1 row(s) affected) Msg 2601, Level 14, State 1, Line 15 Cannot insert duplicate key row in object 'HumanResources.Department' with unique index 'AK_Department_Name'. The statement has been terminated. AfterCount ----------16 (1 row(s) affected)

Jak to funguje Prvním příkazem zjistíme, kolik řádků tabulka HumanResources.Department obsahuje. Dozvěděli jsme se, že jich je 16: -- Zjistíme počet oddělení SELECT COUNT(*) BeforeCount FROM HumanResources.Department

Pak vytvoříme lokální proměnnou, do které uložíme hodnotu funkce @@ERROR (ta zachytí nejnovější chybový stav nějakého příkazu SQL): -- Sem si uložíme celočíselný kód poslední chyby DECLARE @Error int

Pak zahájíme explicitní transakci: BEGIN TRANSACTION

Další příkaz (INSERT) se pokusí vložit řádek do tabulky HumanResources.Department. Tabulka obsahuje omezení na jedinečný název oddělení. Protože však dodaný název v tabulce ještě není, nový řádek se vloží a operace skončí úspěšně: INSERT HumanResources.Department (Name, GroupName) VALUES ('Accounts Payable', 'Accounting')

Velká kniha T-SQL & SQL Server 2005

125

Pak tu máme zpracovatele chyb příkazu INSERT: SET @Error = @@ERROR IF (@Error <> 0) GOTO Error_Handler

Tento řádek kódu vyhodnotí funkci @@ERROR. Systémová funkce @@ERROR vrátí celočíselnou hodnotu poslední chyby naposledy vykonaného příkazu uvnitř oboru aktuálního připojení. Příkaz IF říká, že pokud došlo k nějaké chybě, má kód skočit (pomocí GOTO) do sekce Error_Handler kódu.

Poznámka Informace o příkazu GOTO naleznete v kapitole 9. Informace o @@Error jsou v kapitole 16. V kapitole 16 se také seznámíte s novou konstrukcí pro zpracování chyb – TRY...CATCH. GOTO je klíčové slovo, které pomáhá řídit tok vykonávání příkazů. Identifikátor, který uvedete za GOTO, zde Error_Handler, je uživatelsky definovaná sekce kódu. Pak se v kódu pokoušíme vložit další nový

řádek, ale tentokrát se jedná o oddělení, jehož název už v tabulce existuje. Protože tabulka má omezení jedinečnosti na sloupec s názvy (Name), vložit řádek se nám nepodaří: INSERT HumanResources.Department (Name, GroupName) VALUES ('Engineering', 'Research and Development')

Nezdar způsobí, že funkce @@ERROR uvedená za příkazem INSERT se nastaví na nějakou nenulovou hodnotu. Příkaz IF se proto vyhodnotí na TRUE, takže se použije příkaz GOTO, nevykoná se COMMIT TRAN a skočí se rovnou do sekce Error_Handler: SET @Error = @@ERROR IF (@Error <> 0) GOTO Error_Handler COMMIT TRAN

Za návěštím Error_Handler se vykoná ROLLBACK TRANSACTION: Error_Handler: IF @Error <> 0 BEGIN ROLLBACK TRANSACTION END

Poté, co se transakce anuluje, musíme ověřit, kolik řádků je nyní v tabulce oddělení. Zjistíme, že jich je stále jen 16. Je to proto, že oba příkazy INSERT byly součástí jediné transakce a jeden z nich se nezdařil. Protože se transakce chovají ve stylu "všechno nebo nic", nevloží se žádný řádek: -- Zjistíme, jaký je počet oddělení po transakci SELECT COUNT(*) AfterCount FROM HumanResources.Department

Kapitola 12

Triggery V této kapitole budeme prezentovat návody na vytváření a používání triggerů jazyka pro definici dat (Data Definition Language, DDL) i triggerů jazyka pro manipulaci s daty (Data Manipulation Language, DML). Triggery DML obsahují kód Transact-SQL, který reaguje na operace INSERT, UPDATE nebo DELETE vykonávaných nad tabulkou nebo pohledem. Triggery DDL reagují na události serveru nebo databáze, nikoliv na modifikace dat. Například se dá vytvořit trigger DDL, který zapíše informace do nějaké tabulky sloužící pro potřeby pozdějších auditů vždy, když uživatel databáze vydá příkaz CREATE TABLE nebo příkaz DROP TABLE. Když se triggery využívají řádně, poskytují pohodlné automatické reakce na specifické akce. Jsou příhodné v situacích, kdy musíte na věcné úrovni vytvářet reakce na jisté činnosti. Triggery by se neměly používat místo omezení (například místo omezení primárního klíče nebo omezení na jedinečnost hodnot), protože omezení poskytují lepší výkon a lépe se hodí pro operace tohoto druhu. Také byste měli být dobře informováni o tom, jakým kódem Transact-SQL je trigger nadefinovaný, a pečlivě zajistit, aby byl řádně optimalizovaný. Jestliže vykonání triggeru trvá několik sekund pro každý příkaz UPDATE, může to dosti negativně ovlivnit celkový výkon. Podle mých osobních zkušeností se zdá, že kdykoliv dojde na řešení potíží s výkonem, úplně se zapomíná na existenci triggerů. Často slyším stížnosti na to, s jak mizerným výkonem se provádějí modifikace dat a kolik času se strávilo jejich optimalizacemi, aby se nakonec zjistilo, že příčinou všech potíží s výkonem byl pouze nějaký špatně vyladěný trigger. Je to jeden z hlavních důvodů, proč já používám triggery DML pouze zřídka – a když už je použiji, dávám si zatraceně pozor, aby byly rychlé a bez závad. Nicméně požadavky vaší aplikace mohou diktovat, že musíte použít nějaký trigger DML. Nemluvě o tom, že triggery DLL v SQL Serveru 2005 otevírají celý nový rozsah funkcionality, který nebyl k dispozici v předchozích verzích. Triggery totiž poskytují schopnosti, které nejde snadno nahradit jinými databázovými objektovými typy. V této kapitole probereme následující témata:

• Jak se vytvoří trigger AFTER DML. • Jak se vytvoří trigger INSTEAD OF

DML.

• Jak se vytvoří trigger DDL. • Jak se modifikuje nebo odstraní existující trigger. • Jak se zapínají a vypínají triggery.

382

Kapitola 12 – Triggery

• Jak se omezí vnořování triggerů, jak se nastaví jejich pořadí, v němž se mají odpalovat, a jak se řídí rekurze.

• Jak se prohlížejí metadata triggeru. Nejprve ovšem začneme úplně od začátku, základními informacemi o triggerech DML.

Poznámka V této kapitole se dozvíte, jak se triggery vytvářejí pomocí Transact-SQL. V kapitole 13 se ale také probírá, jak se dají triggery vytvářet pomocí nové funkcionality společného runtime jazyků (Common Language Runtime, CLR).

Triggery DML Triggery DML reagují na uživatelské operace INSERT, UPDATE nebo DELETE prováděné nad tabulkou nebo pohledem. Když dojde k nějaké události modifikace dat, trigger vykoná akce, které jsou v něm nadefinované. Triggery se v tomto ohledu podobají uloženým procedurám, protože jsou vytvářeny pomocí jazyku Transact-SQL a umožňují provádět úplný rozsah činností. Trigger DML se dá konkrétně definovat pro aktualizaci, vkládání nebo odstraňování (FOR UPDATE, FOR INSERT, FOR DELETE) nebo pro libovolnou jejich kombinaci. Aktualizační (UPDATE) triggery reagují na modifikace v jednom nebo několika sloupcích tabulky, triggery pro vkládání (INSERT) reagují na nová data, která byla přidaná do databáze, a konečně – odstraňovací (DELETE) triggery reagují na data odstraňovaná z databáze. Existují dva typy triggerů DML, které se vykonají buď "po něčem" (AFTER) nebo "místo něčeho" (INSTEAD OF). Triggery AFTER jsou povoleny pouze pro tabulky a vykonávají se až po dokončené modifikaci dat v tabulce. Triggery INSTEAD OF se vykonávají místo původně plánované modifikace dat a dají se vytvářet pro tabulky i pro pohledy. Triggery DML umožňují reagovat na modifikace dat prováděné v tabulce. Například můžete vytvořit trigger, který plní nějakou tabulku pro audity na základě toho, jaká operace se provedla. Pomocí triggeru také můžete snižovat objem zboží na skladě. Přestože je to velmi mocná schopnost, je tu několik věcí, které bystě měli mít na zřeteli, než začnete v hojné míře triggery používat:

• Triggery se často stávají skrytými, takže se na ně postupně zapomene. Když se pak řeší potíže s výkonem nebo s logikou, administrátoři databáze často zapomenou na to, že na pozadí se vykonávají nějaké triggery. Zajistěte, aby triggery byly ve vaší dokumentaci vždy "dobře vidět".

• Můžete-li zajistit, aby všechny modifikace dat protékaly nějakou uloženou procedurou, důrazně se doporučuje, abyste všechny své činnosti prováděli v takové uložené proceduře, nikoliv pomocí triggeru. Například – potřebujete-li aktualizovat množství něčeho v nějaké tabulce spojené relací poté, co vložíte záznam o prodeji, proč tuto logiku raději nestrčit do uložené procedury? Výhodou je lepší údržba (máte jediné místo, kam je potřeba se dívat) a lepší řešení potíží (máte jediné místo, kde se bude muset něco řešit), až bude zapotřebí danou proceduru modifikovat nebo řešit její neočekávané chování.

Velká kniha T-SQL & SQL Server 2005

383

• Vždycky mějte v prvé řadě na mysli výkon, což znamená psát takové triggery, které se vykonají rychle. Dlouho běžící triggery mohou významně zpomalit modifikační operace dat. Zejména dávejte pozor, umisťujete-li triggery do nějaké databáze, kde se data modifikují velmi často.

• Neprotokolované aktualizace neodpalují triggery DML (například WRITETEXT, TRUNCATE TABLE a dávkové vkládací operace).

• Omezení obvykle běží rychleji než trigger DML, takže lze-li požadavky věcné logiky splnit pomocí omezení, použijte omezení. Triggery AFTER se vykonají až po dokončení modifikací dat, takže nemohou sloužit jako prevence proti porušení stanovených omezení.

• Nedovolte, aby se z triggeru vracely sady výsledků na základě příkazu SELECT. Většina aplikací to nebude umět elegantně zpracovat, nehledě na fakt, že vložené dotazy mohou snižovat výkon triggeru. Budete-li mít při tvorbě triggerů stále na mysli tato všeobecná vodítka a budete-li triggery používat řádně, stanou se z nich vynikající prostředky, jak si v databázi vynutit požadovaná pravidla věcné logiky.

Trigger DML typu AFTER Trigger DML typu AFTER se vykoná až poté, co se nad tabulkou úspěšně dokončí modifikační operace INSERT, UPDATE nebo DELETE. Specifická syntaxe triggeru DML s AFTER vypadá takto: CREATE TRIGGER [ název_schématu . ]název_triggeru ON název:tabulky [ WITH <volba_triggeru_dml> [ ...,n ] ] AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ NOT FOR REPLICATION ] AS { příkaz_sql [ ...n ]}

Argumenty příkazu jsou popsané v tabulce 12.1. Tabulka 12.1. Argumenty pro CREATE TRIGGER Argument

Popis

[ název_schématu . ]název_triggeru

Nepovinný název schématu a povinný uživatelsky deﬁnovaný název nového triggeru.

název_tabulky

Název tabulky, na kterou se trigger bude aplikovat.

<volba_triggeru_dml> [ ...,n ]

Umožňuje specifikovat klauzuli ENCRYPTION a (nebo) klauzuli EXECUTE AS. ENCRYPTION zašifruje definici Transact-SQL triggeru, takže nebude možné jej prohlížet pomocí systémových tabulek. EXECUTE AS umožňuje definovat bezpečnostní kontext, pod kterým se bude trigger vykonávat.

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]

Deﬁnuje, na kterou událost, či jaké události DML bude trigger reagovat. Jsou to INSERT, UPDATE a DELETE. Jediný trigger může reagovat na jednu nebo více těchto akcí nad tabulkou.

384

Kapitola 12 – Triggery

Tabulka 12.1. Argumenty pro CREATE TRIGGER Argument

Popis

NOT FOR REPLICATION

Speciﬁkuje, že trigger se nesmí vykonávat, když se nad tabulkou vykonává replikační modiﬁkace.

příkaz_sql [ ...n ]

V triggeru je povolen jeden nebo více příkazů Transact-SQL, které mohou vykonávat všelijaké akce, jako například ověřit platnost změn DML nebo provádět akce DML nad jinými tabulkami.

Než budeme pokračovat konkrétním návodem, je důležité upozornit na to, že SQL Server vytváří dvě "virtuální" tabulky, které jsou speciálně dostupné triggerům. Je to tabulka odstraněných řádků (deleted) a tabulka vložených řádků (inserted). Tyto dvě tabulky zachycují stav řádků před modifikacemi a po nich. Jak tyto jednotlivé tabulky ovlivňují operace DML ukazuje tabulka 12.2. Tabulka 12.2. Vkládané a odstraňované virtuální tabulky Operace DML

Co obsahuje tabulka vložených řádků

Co obsahuje tabulka odstraněných řádků

INSERT

Vložené řádky

UPDATE

Nové řádky (aktualizované řádky)

Staré řádky (stav před aktualizací)

DELETE

Odstraněné řádky

Pomocí tabulek vložených a odstraněných řádků se dá v triggeru přistupovat k datům ve stavu, v jakém byla před modifikacemi, nebo po modifikacích, které způsobily, že se trigger odpálil. Do těchto tabulek se ukládají data aktualizací zasahujících jediný řádek i více řádků. Nezapomínejte vždy programovat triggery pro oba druhy aktualizací (jediný řádek, více řádků). Například operace DELETE může mít dopad buď na jeden jediný řádek, nebo na padesát řádků, takže dávejte pozor na to, aby trigger byl naprogramován vždy tak, aby správně zvládl oba případy. V tomto návodu předvedu, jak se pomocí triggeru sleduje vkládání řádků a odstraňování řádků z tabulky Production.ProductInventory: -- Sleduje všechny operace Insert a Delete CREATE TABLE Production.ProductInventoryAudit (ProductID int NOT NULL , LocationID smallint NOT NULL , Shelf nvarchar(10) NOT NULL , Bin tinyint NOT NULL , Quantity smallint NOT NULL , rowguid uniqueidentifier NOT NULL , ModifiedDate datetime NOT NULL , InsOrUPD char(1) NOT NULL ) GO -- Vytvoří trigger, který naplní tabulku Production.ProductInventoryAudit CREATE TRIGGER Production.trg_uid_ProductInventoryAudit ON Production.ProductInventory

Velká kniha T-SQL & SQL Server 2005

385

AFTER INSERT, DELETE AS SET NOCOUNT ON -- Vložené řádky INSERT Production.ProductInventoryAudit (ProductID, LocationID, Shelf, Bin, Quantity, rowguid, ModifiedDate, InsOrUPD) SELECT DISTINCT i.ProductID, i.LocationID, i.Shelf, i.Bin, i.Quantity, i.rowguid, GETDATE(), 'I' FROM inserted i -- Odstraněné řádky INSERT Production.ProductInventoryAudit (ProductID, LocationID, Shelf, Bin, Quantity, rowguid, ModifiedDate, InsOrUPD) SELECT d.ProductID, d.LocationID, d.Shelf, d.Bin, d.Quantity, d.rowguid, GETDATE(), 'D' FROM deleted d GO -- Vloží nový řádek INSERT Production.ProductInventory (ProductID, LocationID, Shelf, Bin, Quantity) VALUES (316, 6, 'A', 4, 22) -- Odstraní řádek DELETE Production.ProductInventory WHERE ProductID = 316 AND LocationID = 6 -- Zkontroluje tabulku sloužící pro potřeby auditů SELECT ProductID, LocationID, InsOrUpd FROM Production.ProductInventoryAudit

Vrátí se následující: (1 row(s) affected) (1 row(s) affected) ProductID

LocationID InsOrUpd

----------- ---------- -------316

316

(2 row(s) affected)

Jak to funguje Návod jsme zahájili tím, že jsme vytvořili novou tabulku, která se bude využívat při následných auditech. Budou se v ní sledovat veškeré operace vkládání řádků do tabulky Production.ProductInven-

386

Kapitola 12 – Triggery

tory nebo odstraňování řádků z této tabulky. Schéma nové tabulky se shoduje se schématem původní tabulky, nicméně přidali jsme nový sloupec s názvem InsOrUPD, který indikuje, zdali se v řádku jednalo

o operaci vkládání nebo o aktualizační operaci: CREATE TABLE Production.ProductInventoryAudit (ProductID int NOT NULL , LocationID smallint NOT NULL , Shelf nvarchar(10) NOT NULL , Bin tinyint NOT NULL , Quantity smallint NOT NULL , rowguid uniqueidentifier NOT NULL , ModifiedDate datetime NOT NULL , InsOrUPD char(1) NOT NULL ) GO

Pak jsme příkazem CREATE TRIGGER vytvořili trigger DML typu AFTER. V prvním řádku příkazu jsme vyznačili vlastnické schéma a název nového triggeru: CREATE TRIGGER Production.trg_uid_ProductInventoryAudit

Pak jsme v klauzuli ON uvedli název tabulky (její aktualizace způsobí, že se trigger odpálí): ON Production.ProductInventory

Budeme monitorovat dvě činnosti DML – vkládání a odstraňování: AFTER INSERT, DELETE

Tělo triggeru začíná za klíčovým slovem AS: AS

SET NOCOUNT jsme nastavili na ON, abychom potlačili informativní zprávy "rows affected", které se nyní

nebudou vracet volající aplikaci vždy, když se trigger odpálí: SET NOCOUNT ON

První příkaz vloží nový řádek do sledovací tabulky pro ty řádky, které existují ve virtuální tabulce vložených řádků (inserted): INSERT Production.ProductInventoryAudit (ProductID, LocationID, Shelf, Bin, Quantity, rowguid, ModifiedDate, InsOrUPD) SELECT DISTINCT i.ProductID, i.LocationID, i.Shelf, i.Bin, i.Quantity, i.rowguid, GETDATE(), 'I' FROM inserted i

Druhý příkaz vloží nový řádek do sledovací tabulky pro ty řádky, které existují ve virtuální tabulce odstraněných řádků (deleted), ale nikoliv v tabulce vložených řádků: INSERT Production.ProductInventoryAudit

Velká kniha T-SQL & SQL Server 2005

387

(ProductID, LocationID, Shelf, Bin, Quantity, rowguid, ModifiedDate, InsOrUPD) SELECT d.ProductID, d.LocationID, d.Shelf, d.Bin, d.Quantity, d.rowguid, GETDATE(), 'D' FROM deleted d GO

Poté, co jsme vytvořili trigger, vložili jsme do tabulky Production.ProductInventory jeden řádek, a hned potom jsme zase tentýž řádek odstranili, abychom mohli daný trigger otestovat. -- Vloží nový řádek INSERT Production.ProductInventory (ProductID, LocationID, Shelf, Bin, Quantity) VALUES (316, 6, 'A', 4, 22) -- Odstraní řádek DELETE Production.ProductInventory WHERE ProductID = 316 AND LocationID = 6

Jak jste pak sami viděli – když se vykonal dotaz nad sledovací tabulkou, vrátily se nám dva řádky, protože do tabulky se zaznamenaly operace vložení a odstranění řádku, které jsme provedli nad tabulkou Production.ProductInventory: SELECT ProductID, LocationID, InsOrUpd FROM Production.ProductInventoryAudit

Trigger DML typu INSTEAD OF Triggery DML typu INSTEAD OF se vykonávají místo původní modifikace dat, která trigger odpálila, a jsou povoleny jak pro tabulky, tak i pro pohledy. Triggery INSTEAD OF se často používají ke zpracování modifikací dat u pohledů, které přímé modifikace dat nepovolují (přehled pravidel, jež musí pohled splňovat, aby byl aktualizovatelný, naleznete v kapitole 7). Triggery DML typu INSTEAD OF mají následující syntaxi: CREATE TRIGGER [ název_schématu . ] název_triggeru ON { název:tabulky | název_pohledu } [ WITH <volba_triggeru_dml> [ ...,n ] ] INSTEAD OF { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ NOT FOR REPLICATION ] AS { příkaz_sql [ ...n ] }

Argumenty příkazu jsou popsané v tabulce 12.3.

Kapitola 22

Vytváření a konﬁgurace databází V této kapitole najdete směsici návodů, které se točí okolo vytváření a konfigurace databáze SQL Serveru. Mezi činnosti, které se naučíte dělat s Transact-SQL, patří:

• Vytvoření nové databáze. • Přidávání souborů do databáze a odstraňování souborů nebo skupin souborů z databáze. • Prohlížení a modifikace databázových nastavení (včetně několika nových nastavení zavedených v SQL Serveru 2005).

• Zvýšení nebo snížení velikosti databáze nebo databázového souboru. • Odstranění databáze z instance SQL Serveru. • Odpojení databáze od instance SQL Serveru a její opětovné připojení k instanci. Proberu také všelijaká nastavení stavu – např. jako nakonfigurovat databázi, aby se z ní dalo jen číst, nebo jak přepnout databázi do módu jediného uživatele.

Poznámka Nová funkcionalita zrcadlení databáze (database mirroring) SQL Serveru 2005 se probírá v kapitole 25, databázové momentky (database snapshots) v kapitole 26.

Vytváření, modiﬁkace a odstraňování databází V první sadě návodů proberu, jak se v instanci SQL Serveru vytvoří, modifikuje a odstraní databáze. Konkrétně ukážu, jak se:

• Vytvoří databáze na základě výchozí konfigurace systémové databáze model. • Prohlížejí informace týkající se konfigurace databáze. • Vytvoří databáze pomocí explicitních voleb databázového souboru (což znamená, že nebude závislá na systémové databázi model).

• Vytvoří databáze, která používá uživatelsky definovanou skupinu souborů (filegroup). • Změní název existující databáze.

Kapitola 22 – Vytváření a konﬁgurace databází

624

• Odstraní databáze z instance SQL Serveru. • Odpojí databáze od instance SQL Serveru, takže zbudou jen soubory podkladových dat a protokolů, a jak se pomocí stejných souborů databáze opět připojí. Prvořadé příkazy, které budete používat při vytváření a modifikaci databází, jsou CREATE DATABASE a ALTER DATABASE. Obdobně jako při výkladu příkazu SELECT v kapitole 1, i zde v každém návodu použiji některé relevantní komponenty, které budeme potřebovat k vyřešení aktuální úlohy, a nebudu prezentovat syntaxi v jediném obrovském bloku.

Vytvoření databáze s výchozí konﬁgurací Tento návod předvádí, jak se vytvoří databáze ve své nejjednodušší formě, a to pomocí výchozí konfigurace založené na systémové databázi model. Databáze model je systémová databáze, která se nainstaluje s SQL Serverem a definuje šablonu pro všechny ostatní databáze, jež se budou vytvářet v dané instanci SQL Serveru. Vytvoříte-li databázi, aniž byste specifikovali jiné volby než je název databáze, bude nová databáze založená na systémové databázi model. Syntaxe pro vytvoření databáze na základě šablony model vypadá takto: CREATE DATABASE název_databáze

Příkaz CREATE DATABASE se ve své nejjednodušší formě spokojí jen s jediným argumentem – názvem nové databáze. V tomto návodu vytvoříme novou databázi s názvem BookStore: USE master GO CREATE DATABASE BookStore GO

Jak to funguje V návodu jsme vytvořili novou databázi s názvem BookStore, aniž bychom uvedli nějaké další volby kromě názvu databáze. Když vynecháme podrobnosti, jako umístění, velikost a volby pro růst souborů, vytvoří se nová databáze na základě systémové databáze model. Databáze bude obsahovat všechny uživatelsky definované objekty, které jste dříve umístili do databáze model, a jmenná konvence pro názvy souborů bude založena na zadaném názvu databáze. Přestože se takto vytvoří nová databáze velmi rychle, nemáte při tomto způsobu kontrolu nad několika volbami, které popíšu v průběhu této kapitoly.

Prohlížení informací o databázi Tento návod předvede, jak se vypisují informace o databázových vlastnostech a souborech pomocí systémové uložené procedury sp_helpdb: EXEC sp_helpdb 'BookStore' GO

Velká kniha T-SQL & SQL Server 2005

625

Vrátí se dvě sady výsledků (které jsem musel trochu smrsknout, aby se vešly na šířku stránky knihy): name

db_size

BookStore 1.68 MB

name

owner

dbid created

JOEPROD\ 8 Owner

status

compatibility_level

Sep 30 2005 Status=ONLINE, Upda- 90 teability=READ_WRITE, UserAccess=MULTI_ USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled

fileid filename

filegroup size

maxsize

BookStore 1

C:\Program Files\ Microsoft SQL Server\ MSSQL.1\MSSQL\DATA\ BookStore.mdf

PRIMARY

1216 KB Unlimited

BookStore_log

C:\Program Files\ Microsoft SQL Server\ MSSQL.1\MSSQL\DATA\ BookStore_log.LDF

NULL

504 KB

growth usage 1024 KB data only

2147483648 KB 10%

log only

Jak to funguje Vlastnosti databáze jsme si prohlédli pomocí systémové uložené procedury sp_helpdb. Přebírá jediný nepovinný parametr – v tomto případě je to název databáze: EXEC sp_helpdb 'BookStore'

Pokud bychom název databáze neuvedli, vrátily by se informace o všech databázích na dané instanci SQL Serveru. Systémová uložená procedura sp_helpdb vrací tyto informace:

• Název a vlastníka databáze. • Datum, kdy byla databáze vytvořena. • Různá databázová nastavení a volby, jako jsou výchozí kolace databáze nebo informace, zdali databáze je (nebo není) nakonfigurována tak, aby automaticky aktualizovala statistiky (to je skupina databázových voleb, které proberu v kapitole později).

• Seznam souborů, z nichž se skládá databáze, také jejich velikosti, skupiny souborů, kam patří, a volby růstu.

Kapitola 22 – Vytváření a konﬁgurace databází

626

Výstup také obsahuje úroveň kompatibility databáze. Například – databáze SQL Serveru 2005 má standardně úroveň kompatibility 90. Kompatibilita SQL Serveru 2000 by měla úroveň 80, SQL Serveru 7.0 70 a SQL Serveru 6.5 úroveň 65. Úroveň kompatibility umožňuje udržovat v SQL Serveru 2005 databáze, které zůstanou kompatibilní s předchozími verzemi SQL Serveru. To také znamená, že u databází kompatibilních s SQL Serverem 2000 nemůžete používat ta rozšíření Transact-SQL, která byla zavedena v SQL Serveru 2005. Úroveň kompatibility můžete nastavit systémovou uloženou procedurou sp_dbcmptlevel.

Vytvoření databáze pomocí voleb souborů Vytvářet nové databáze pomocí výchozích voleb systémové databáze model je užitečné, když jednoduše potřebujete co nejrychleji vytvořit nějakou testovací databázi, nicméně v produkčním prostředí budete obvykle klást větší důraz na volby, které se týkají umístění, velikosti a voleb růstu databázových souborů pro data a protokoly. Následující návod proto předvádí, jak se při vytváření databází dají zadávat explicitní volby pro databázové soubory. Zkrácená syntaxe příkazu CREATE DATABASE, jakou budeme používat v tomto návodu, je tato: CREATE DATABASE název_databáze [ ON [ <specifikace souborů> [ ,...n ] ] ] [ [ LOG ON { <specifikace souborů> [ ,...n ] } ] ]

Argumenty této syntaxe jsou stručně popsané v tabulce 22.1. Tabulka 22.1. Argumenty pro příkaz CREATE DATABASE Argument

Popis

název_databáze

Název nové databáze.

[ ON [ <specifikace_souborů> [ ,...n ] ] ]

Vyznačuje pro databázi jeden nebo více explicitně deﬁnovaných datových souborů.

[ LOG ON { <specifikace_souborů> [ ,...n ] } ]

Vyznačuje pro databázi jeden nebo více explicitně deﬁnovaných souborů transakčního protokolu.

Syntaxe argumentu specifikace_souborů pro vytvoření datového souboru a souboru protokolu vypadá takto: [ PRIMARY ] ( [ NAME =logický_název_souboru ,] FILENAME ='fyzický_název_souboru' [ ,SIZE =velikost [ KB | MB | GB | TB ] ] [ ,MAXSIZE ={ maximální_velikost

[ KB | MB | GB | TB ] | UNLIMITED } ]

[ ,FILEGROWTH =inkrement_růstu [ KB | MB | % ] ] )[ ,...n ]

Prvky argumentu specifikace_souborů jsou popsané v tabulce 22-2.

Velká kniha T-SQL & SQL Server 2005

627

Tabulka 22.2. Argumenty speciﬁkace_souborů Argument

Popis

PRIMARY

Nepovinné klíčové slovo. V argumentu specifikace_souborů speciﬁkuje, že se jedná o primární datový soubor (vstupní bod databáze, obsahuje ukazatele na ostatní soubory a typicky má příponu názvu souboru .mdf ). Pro danou databázi může existovat jen jeden primární soubor, a pokud se nevyznačí explicitně, bude se za primární datový soubor považovat první datový soubor uvedený v příkazu CREATE DATABASE.

logický_název_souboru

Logický název databázového souboru.

fyzický_název_souboru

Fyzická cesta a název databázového souboru.

velikost [ KB | MB | GB | TB ]

Počáteční velikost souboru v jednotkách podle speciﬁkovaného atributu (kilobajty, megabajty, gigabajty, terabajty).

MAXSIZE = { maximální_velikost [ KB | MB | GB | TB ] | UNLIMITED }

Maximální povolená velikost souboru. Zvolíte-li UNLIMITED, bude moci soubor růst neomezeně, a může zabrat veškerý dostupný prostor na dané fyzické jednotce.

FILEGROWTH = inkrement_růstu [ KB | MB | % ]

O kolik soubor naroste (zvětší se jeho velikost), když požaduje další prostor. Dá se vyznačit buď počet kilobajtů či megabajtů, nebo procento velikosti existujícího souboru, o které má soubor narůst. Vyberete-li 0, soubor neporoste.

[ ,...n ]

Speciﬁkuje, že se dá deﬁnovat více souborů (až 32 767 na jednu databázi).

V tomto návodu vytvořím novou databázi s názvem BookStoreArchive pomocí voleb příkazu CREATE DATABASE zmíněných výše: USE master GO CREATE DATABASE BookStoreArchive ON PRIMARY ( NAME = 'BookStoreArchive', FILENAME = 'F:\MSSQL\DATA\BookStoreArchive.mdf' , SIZE = 3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ), (

NAME = 'BookStoreArchive2', FILENAME = 'G:\MSSQL\DATA\BookStoreArchive2.ndf' , SIZE = 1MB , MAXSIZE = 30, FILEGROWTH = 5% )

LOG ON (

NAME = 'BookStoreArchive_log',

628

Kapitola 22 – Vytváření a konﬁgurace databází

FILENAME = 'H:\MSSQL\TLOG\BookStoreArchive_log.LDF' , SIZE = 504KB , MAXSIZE = 100MB , FILEGROWTH = 10%) GO

Jak to funguje V tomto návodu jsme vytvořili novou databázi s názvem BookStoreArchive. Klíčovým slovem PRIMARY jsme specifikovali první soubor jako primární datový soubor: CREATE DATABASE BookStoreArchive ON PRIMARY

Následovala v závorkách definice prvního souboru. Logický název souboru bude BookStoreArchive: (

NAME = 'BookStoreArchive',

Fyzický název souboru říká, že soubor bude na jednotce F:. V produkčních scénářích pravděpodobně budete dávat své datové soubory na jednotky s jinými písmeny (možná budete podporovat pole disků RAID 5 nebo RAID 10, Redundant Array of Independent Disks): FILENAME = 'F:\MSSQL\DATA\BookStoreArchive.mdf' ,

Pak jsme nastavili počáteční velikost souboru na tři megabajty: SIZE = 3MB ,

Maximální velikost souboru jsme nastavili na UNLIMITED, což znamená, že bude moci růst tak dlouho, dokud bude na jednotce F: nějaké volné místo: MAXSIZE = UNLIMITED,

Přírůstek pro růst jsme nastavili tak, že soubor se bude zvětšovat vždy o 10 megabajtů. Kdykoliv bude pro soubor třeba přidat další prostor, zvětší se o 10 megabajtů: FILEGROWTH = 10MB ),

Předchozí definice souboru končí čárkou a následuje definice druhého datového souboru: (

NAME = 'BookStoreArchive2', FILENAME = 'G:\MSSQL\DATA\BookStoreArchive2.ndf' ,

Druhý datový soubor dostal jiný logický i jiný fyzický název souboru. Fyzický název souboru má příponu .ndf. Přestože se tato konkrétní přípona souboru nepožaduje, snadněji se podle ní identifikuje účel souborů, budete-li pro primární soubor používat příponu .mdf a pro všechny sekundární datové soubory příponu .ndf. Specifikujete-li více souborů, které navíc rozprostřete po jednotkách s různými písmeny, můžete – za předpokladu, že všechny tyto jednotky podporují pole disků RAID a jsou na separátních kanálech – po nich rozložit vstupní a výstupní operace, a potenciálně tak u rozsáhlejších databází s velkým provozem zvýšit výkon.

Velká kniha T-SQL & SQL Server 2005

629

Velikost druhého souboru jsme nastavili na jeden megabajt, a maximální velikost jsme nastavili na 30 megabajtů. Růst souboru jsme stanovili na pětiprocentní přírůstek, nikoliv v megabajtech jako u prvního datového souboru: SIZE = 1MB , MAXSIZE = 30, FILEGROWTH = 5% )

Za definicí dvou datových souborů uvozují klíčová slova LOG ON definici souboru transakčního protokolu: LOG ON (

NAME = 'BookStoreArchive_log',

Fyzický název souboru dostal příponu .ldf, což je standard pro soubory transakčních protokolů: FILENAME = 'H:\MSSQL\TLOG\BookStoreArchive_log.LDF' ,

Počáteční velikost jsme nastavili na 504 kilobajtů, maximální velikost souboru transakčního protokolu pak na 100 megabajtů s desetiprocentní mírou růstu. SIZE = 504KB , MAXSIZE = 100MB , FILEGROWTH = 10%)

Jakmile se příkaz CREATE DATABASE vykoná, automaticky se na serveru vytvoří patřičné soubory, přičemž databáze je pak připravena a můžete s ní začít pracovat. V pozdějších návodech této kapitoly se dozvíte, jak se dají modifikovat vlastnosti existujících souborů, i jak se přidávají do databáze nové datové soubory nebo soubory transakčních protokolů.

Vytvoření databáze s uživatelsky deﬁnovanou skupinou souborů Databáze musí mít přinejmenším jeden datový soubor a jeden soubor transakčního protokolu. Tyto soubory patří do jediné databáze, a proto nejsou sdíleny s jinými databázemi. Standardně, když se databáze vytvoří, patří datové soubory do primární skupiny souborů. Skupina souborů (filegroup) je pojmenované seskupení souborů pro administrační a rozmisťovací účely. Primární skupina souborů obsahuje primární datový soubor, i další datové soubory, které nebyly explicitně přiřazené do jiné skupiny souborů. Do skupin souborů patří datové soubory (ale už nikoliv soubory transakčního protokolu). Kromě primární skupiny souborů (kterou mají všechny databáze v SQL Serveru) můžete vytvářet sekundární uživatelsky definované skupiny souborů, do nichž pak umisťujete své soubory. Uživatelsky definované skupiny souborů se často používají u velmi velkých databází (VLDB), protože umožňují rozčlenit databázi na několik oblastí. Správa zálohování databáze pak probíhá na úrovni skupiny souborů, nikoliv na úrovni celé databáze.

Kapitola 22 – Vytváření a konﬁgurace databází

630

Poznámka Do skupin souborů se dají umisťovat i jednotlivé tabulky a indexy. To, jak se umístí tabulka do konkrétní skupiny souborů, naleznete v kapitole 4, jak index viz kapitola 5.

V tomto návodu předvedu, jak se vytvoří databáze se soubory umístěnými v uživatelsky definované skupině souborů. Syntaxe, kterou k tomu budeme potřebovat, vypadá takto: CREATE DATABASE název_databáze [ ON FILEGROUP název_skupiny_souborů [ DEFAULT ] <specifikace souborů> [ ,...n ] ] [ [ LOG ON { <specifikace souborů> [ ,...n ] } ] ]

Argumenty této syntaxe jsou popsané v tabulce 22.3. Tabulka 22.3. Argumenty CREATE DATABASE Argument

Popis

název_databáze

Název databáze.

FILEGROUP název_skupiny_souborů [DEFAULT ]

Vyznačuje logický název skupiny souborů. Následuje-li klíčové slovo DEFAULT, bude tato skupina souborů výchozí skupinou souborů databáze (což znamená, že standardně do ní patří všechny objekty zde vytvářené).

<specifikace souborů> [ ,...n ]

Vyznačuje jeden nebo více explicitně deﬁnovaných datových souborů pro databázi.

[ LOG ON { <specifikace souborů> [ ,...n ] } ]

Vyznačuje jeden nebo více explicitně deﬁnovaných souborů transakčního protokolu pro databázi.

V tomto návodu vytvoříme novou databázi s názvem BookStoreInternational, která bude používat dvě skupiny souborů. Jednou z nich bude požadovaná primární skupina souborů, druhou bude nová, uživatelsky definovaná skupina souborů FG2. Obě skupiny souborů vytvoříme jedním příkazem CREATE DATABASE: USE master GO CREATE DATABASE BookStoreInternational ON PRIMARY (

NAME = 'BookStoreInternational', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ BookStoreInternational.mdf' , SIZE = 3MB ,

Velká kniha T-SQL & SQL Server 2005

631

MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ), FILEGROUP FG2 DEFAULT (

NAME = 'BookStoreInternational2', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BookStoreInternational2.mdf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB )

LOG ON (

NAME = 'BookStoreInternational_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BookStoreInternational_log.LDF' , SIZE = 504KB , MAXSIZE = 100MB , FILEGROWTH = 10%)

Jak to funguje V tomto návodu jsme vytvořili novou databázi se dvěma datovými soubory a jedním souborem protokolu o transakcích. První datový soubor jsme vytvořili na primární (PRIMARY) skupině souborů: CREATE DATABASE BookStoreInternational ON PRIMARY (

NAME = 'BookStoreInternational', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BookStoreInternational.mdf' , SIZE = 3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ),

Druhý databázový datový soubor jsme vytvořili pomocí klíčového slova FILEGROUP v nové uživatelsky definované skupině souborů, kterou jsme nazvali FG2. Tuto skupinu souborů jsme označili jako výchozí, takže všechny nově vytvářené objekty v databázi se budou vytvářet v této skupině souborů: FILEGROUP FG2 DEFAULT (

NAME = 'BookStoreInternational2', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BookStoreInternational2.mdf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB )

632

Kapitola 22 – Vytváření a konﬁgurace databází

Protože se protokoly o transakcích neumisťují do skupin souborů, použili jsme v definici specifikace_souborů klíčová slova LOG ON: LOG ON (

NAME = 'BookStoreInternational_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BookStoreInternational_log.LDF' , SIZE = 504KB , MAXSIZE = 100MB , FILEGROWTH = 10%)

V tomto návodu jsme do skupiny souborů FG2 umístili jediný soubor (mohli byste ale do ní umístit i více souborů). Když skupina souborů obsahuje několik souborů, SQL Server plní soubory současně, nikoliv tak, že by nejprve naplnil jeden soubor a pak přešel k dalšímu.

Nastavení uživatelského přístupu k databázi SQL Server 2005 poskytuje tři módy uživatelského přístupu k databázi, které mají vliv na to, jací uživatelé mohou přistupovat k databázi (a kolik jich může být): SINGLE_USER, RESTRICTED_USER a MULTI_USER. Volby SINGLE_USER a RESTRICTED_USER jsou metody, při nichž se "zabouchnou dveře" jiným uživatelům, aby nemohli provádět žádné činnosti v databázi. Často je to prospěšné tehdy, když provádíte takové změny v konfiguraci databáze, u nichž je žádoucí, aby jiní uživatelé v té době nemohli pracovat s databází. Tyto volby se používají také tehdy, potřebujete-li vrátit zpět nějakou změnu v datech, nebo chcete vyloučit uživatele do té doby, dokud se plně nedokončí modernizace systému nebo aplikace. Podrobněji jsou jednotlivé volby popsané v nadcházející tabulce Syntaxe pro modifikaci uživatelského přístupu je následující: ALTER DATABASE název_databáze SET { SINGLE_USER | RESTRICTED_USER | MULTI_USER } [WITH { ROLLBACK AFTER celé_číslo [ SECONDS ] | ROLLBACK IMMEDIATE | NO_WAIT } ]

Argumenty této syntaxe jsou popsané v tabulce 22.4. Tabulka 22.4. Argumenty ALTER DATABASE Argument

Popis

název_databáze

Název existující databáze, ve které chcete modiﬁkovat uživatelský přístup.

Velká kniha T-SQL & SQL Server 2005

633

Tabulka 22.4. Argumenty ALTER DATABASE Argument

Popis

SINGLE_USER | RESTRICTED_USER | MULTI_USER

Vyberete-li SINGLE_USER, bude moci v daném čase přistupovat k databázi jen jediný uživatel. Když se vybere tahle volba, a nepoužijí se ukončovací volby (jejich popis viz další argument), plánované modiﬁkace tohoto jediného uživatele budou blokovány tak dlouho, dokud se z databáze neodpojí všichni ostatní uživatelé. Vyberete-li RESTRICTED_USER, budou moci přistupovat k databázi jen členové skupin s rolí sysadmin, dbcreator nebo db_owner. Při MULTI_USER mohou k databázi přistupovat všichni uživatelé, kteří k tomu mají udělena patřičná povolení.

ROLLBACK AFTER celé_číslo [ SECONDS ]

Ukončovací volby umožňují během příkazu ALTER DATABASE anulovat v databázi nedokončené transakce. Nepoužijete-li žádnou ukončovací volbu, bude váš příkaz ALTER muset čekat možná dost dlouho, než uzamykající připojení dokončí své úkoly. Ukončovací volby se ve skutečnosti mohou používat s jakoukoli klauzulí SET, ovšem nejčastěji se používají, když se přístup k databázi mění na mód SINGLE_USER nebo RESTRICTED_USER.

ROLLBACK IMMEDIATE | NO_WAIT

ROLLBACK AFTER celé_číslo [SECONDS] speciﬁkuje, že otevřené databázové transakce se budou anulovat, až uplyne uvedený počet sekund. ROLLBACK IMMEDIATE anuluje otevřené transakce okamžitě. Uvedete-li NO_WAIT, způsobí to, že příkaz se nezdaří, pokud se nemůže okamžitě úspěšně dokončit (aby mohl příkaz s touto volbou uspět, nesmějí být v databázi otevřeny žádné transakce).

Následující návod předvede, jak se změní mód databáze AdventureWorks na SINGLE_USER, jak se stornují všechny otevřené transakce, a jak se databáze přepne zpět do módu MULTI_USER: SELECT user_access_desc FROM sys.databases WHERE name = 'AdventureWorks' ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE SELECT user_access_desc FROM sys.databases WHERE name = 'AdventureWorks' ALTER DATABASE AdventureWorks SET MULTI_USER SELECT user_access_desc FROM sys.databases WHERE name = 'AdventureWorks'

Vrátí se: user_access_desc

634

Kapitola 22 – Vytváření a konﬁgurace databází

-----------------------------------------------------------MULTI_USER (1 row(s) affected) user_access_desc -----------------------------------------------------------SINGLE_USER (1 row(s) affected) user_access_desc -----------------------------------------------------------MULTI_USER (1 row(s) affected)

Jak to funguje V návodu jsme se nejprve dotázali systémového katalogového pohledu sys.databases na aktuální mód uživatelského přístupu. Pak jsme mód databáze změnili na SINGLE_USER, a použili volbu, která znamenala okamžité anulování všech otevřených transakcí v ostatních uživatelských relacích: ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Opět jsme pohledem sys.databases zkontrolovali uživatelský přístup, a databázi jsme zase přepnuli do módu MULTI_USER: ALTER DATABASE AdventureWorks SET MULTI_USER

Pak jsme přes pohled sys.databases ještě jednou zkontrolovali mód databáze: SELECT user_access_desc FROM sys.databases WHERE name = 'AdventureWorks'

Je důležité připomenout, že budete-li anulovat otevřené transakce tímto způsobem, můžete si ve vaší aplikaci přivodit jisté potíže. Závisí to na tom, jak aplikace zpracovává nedokončené procesy. Vždy, když je to možné, snažte se měnit uživatelský přístup v době, kdy s databází nikdo nepracuje, nebo když nejsou aktivní žádné transakce. Do módu SINGLE_USER je nutné nastavit databázi, chcete-li vykonat jisté operace, jako například volby READ_ONLY a READ_WRITE. Jinou příčinou, proč je potřeba uzavřít všechna aktuální uživatelská připojení, může být například to, že potřebujete napravit nějakou mimořádnou událost, aniž byste se museli potýkat s blokováním nebo s chybami z volající aplikace.

Přejmenování databáze V návodu předvedu, jak se pomocí příkazu ALTER DATABASE změní název existující databáze. Syntaxe: ALTER DATABASE název_databáze MODIFY NAME = nový_název_databáze

Velká kniha T-SQL & SQL Server 2005

635

Dva argumenty tohoto příkazu jsou původní název databáze a nový název databáze. Tento návod konkrétně předvede, jak se změní název databáze BookWarehouse na BookMart. Vykonáme také příkaz ALTER DATABASE...SET SINGLE USER, abychom vymetli jakákoliv jiná souběžná připojení k databázi: USE master GO ALTER DATABASE BookWarehouse SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE BookWarehouse MODIFY NAME = BookMart GO ALTER DATABASE BookMart SET MULTI_USER GO

Vrátí se (výsledky mohou být různé, závisí to na činnostech, které se v databázi prováděly během ukončování připojení): Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. The database name 'BookMart' has been set.

Jak to funguje V tomto návodu jsme přejmenovali databázi z BookWarehouse na BookMart. Než jsme to ale udělali, změnili jsme kontext relace dotazu na databázi master (protože není možné změnit název databáze v připojení k téže databázi): USE master GO

Databázi jsme pak přepnuli do módu jediného uživatele a anulovali všechny otevřené transakce v této databázi (kromě transakcí, které existovaly v aktuální relaci): ALTER DATABASE BookWarehouse SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO

Pak jsme příkazem ALTER DATABASE s klauzulí MODIFY NAME změnili název databáze: ALTER DATABASE BookWarehouse MODIFY NAME = BookMart GO

Přestože jsme databázi přepnuli do módu jednoho uživatele pod starým názvem, zůstane v módu jediného uživatele tak dlouho, dokud ji explicitně nedáte zpět do módu MULTI_USER:

636

Kapitola 22 – Vytváření a konﬁgurace databází

ALTER DATABASE BookMart SET MULTI_USER GO

Odstranění databáze Uživatelskou databázi můžete z SQL Serveru odstranit příkazem DROP DATABASE. Příkaz DROP DATABASE odstraní odkazy na databázi ze systémových tabulek SQL Serveru. Jsou-li podkladové soubory online, odstraní se také fyzické soubory ze stroje SQL Serveru. Syntaxe je následující: DROP DATABASE název_databáze

V tomto návodu odstraníme databázi BookStoreArchive_Czech (vytváří se v jednom z pozdějších oddílů této kapitoly): USE master GO ALTER DATABASE BookStoreArchive_Czech SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DROP DATABASE BookStoreArchive_Czech GO

Jak to funguje Návod jsem zahájil tím, že jsem přepnul aktuální kontext relace dotazu na databázi master, protože databázi nemůžete odstranit v době, kdy jste k ní připojeni. V návodu jsem také přepnul databázi do módu jediného uživatele a vynutil si, aby se okamžitě anulovaly všechny otevřené transakce. A nakonec – v rámci téže relace dotazu, jsem příkazem DROP DATABASE databázi odstranil.

Odpojení databáze Když databázi odstraníte, zmizí z instance SQL Serveru nejen ona, ale i její fyzické soubory. Pokud byste sice odstranili databázi z instance SQL Serveru, ale zachovali její fyzické soubory (kvůli archivaci, nebo pokud chcete s databází migrovat do jiné instance SQL Serveru), nemusíte databázi odstranit – stačí ji odpojit (detach). Databázi můžete také přemístit z jedné instance SQL Serveru na jinou tak, že ji z jedné instance odpojíte a ke druhé připojíte. Chcete-li nějakou databázi odpojit, zavolejte systémovou uloženou proceduru sp_detach_db. Ta má následující syntaxi: sp_detach_db [ @dbname= ] 'název_databáze' [ , [ @skipchecks= ] 'přeskočit_kontroly' ] [ , [ @KeepFulltextIndexFile= ] 'zachovat_soubor_fulltextového_indexu' ]

Parametry procedury jsou popsané v tabulce 22.5.