Datu glabāšana SQL Server

Rakstā tiek pārskriets pāri SQL Server datu glabāšanas struktūrām, sniedzot atsauces uz papildus materiāliem padziļinātai informācijas iegūšanai. Šis raksts ir kā pamats vairākiem turpmāk plānotajiem rakstiem.

Mazliet par to, kas notiek

Sākotnēji gan nebiju domājis šeit rakstīt "par dzīvi", bet šodien sanāca piedalīties pasākumā "Kāpēc vērts izmantot Microsoft SQL Server" un tad jau mājupceļā radās doma par nelielu rakstiņu (gan par pasākumu, gan par to kas vispār notiek).

Izolācijas līmeņi transakcijām

Šajā rakstā aprakstīti SQL Server 2005 transakciju izolācijas līmeņi (isolation level).

Kura nedēļas diena?

Lai noskaidrotu kura nedēļas diena ir šodien, ir jāizmanto funkcija datepart(). Viss būtu skaisti, ja vien visā pasaulē pirmā nedēļas diena būtu pirmdiena. Problēmas garāks skaidrojums un risinājums turpinājumā.

Trigera aizliegšana vienas transakcijas ietvaros

Tāda eksotiska situācija gadījās- ir tabula, kurai ir trigeris. Trigeris rūpējas par biznesa loģiku. Vienmēr tas ir jādara, bet ir viena izņēmuma procedūra, kuras izpildes laikā trigerim nav jāstrādā. Zemāk aprakstīts risinājums.

Determinētu funkciju izmantošana

Rakstā īsi aprakstīts kas ir determinētas un kas ir nedeterminētas funkcijas. Mazliet garāk rādīts un skaidrots kā determinētu funkciju izmantošana uzlabo (var uzlabot) ātrdarbību.

CLR agregātfunkcijas piemērs

Sākot ar SQL Server 2005 ir iespēja veidot agregātfunkcijas izmantojot Visual Studio .NET. Tas nozīmē, ka papildus jau iebūvētām agregātfunkcijām (Sum, Average, Min, Max u.c.), ir iespēja pievienot arī paša veidotas agregātfunkcijas.

Šajā rakstā piemērs, kā izveidot funkciji, kas sarindo vairākus teksta apgabalus vienu aiz otra. Piemēram, ja vienā rindā ir ‘Jānis’ otrā rindā ir ‘Linda’, tad agregātfunkcijas izpildes rezultātā šie abi vārdi tiek savienoti vienā virknē izveidojot teksta virkni ‘Jānis;Linda’.

SQL Server programmēšana

.NET integrācija ļauj programmētājiem veidot jaudīgas lietotāja definētas procedūras, trigerus, lietotāja definētas funkcijas, agregātfunkcijas un tipus SQL Server iekšienē, izmantot to .NET valodu, kura ir izstrādātājam vairāk pazīstama (šobrīd gan var izmantot C#, VB un C++).

CLR 'nekonedara' procedūras izveide

Šeit detalizētāk aprakstīts kā var izveidot CLR procedūru, galvenā uzmanība uz to kā realizēt tehniski, nevis kāda jēga no izdarītā :).

Microsoft Visual studio

Sākot ar SQL Server 2005 kopā ar SQL Server developer edition (SQL Server Express instalācijā šis rīks neietilpst) nāk rīks ar nosaukumu „SQL Server Business Intelligence Development Studio”. Nodaļas virsraksts gan ir Microsoft Visual studio- un tas ir apzināti. Katram, kas ir programmējis ar Visual Studio (c#, Visual Basic, ..) šis rīks būs pazīstams- patiesībā abi šie rīki ir viens un tas pats, bet atšķirība ir tā, ka SQL Server Business Intelligence Development Studio ir Visual Studio ar citiem projektu tipiem un nogrieztām iespējām. Ja lietotājam nav uz datora iepriekš instalēta Visual Studio, tad kopā ar serveri tā tiek uzinstalēta ar ierobežotām iespējām (orientēta strādāšanai ar SQL Server). Savukārt, ja Lietotājs strādā ar VS2005, tad tiek atvērta jau ierastā Visual studio vide.

Rīks: SQL Server Profiler

Ļoti noderīgs rīks SQL Server monitorēšanai. Ar SQL Server profiler var redzēt kādas komandas tiek izpildītas SQL Server, var reģistrēt dažādu notikumu datus (piemēram, resursu bloķēšanas notikumi, strupsaķeres, utt)

SQL Server Management Studio

Sākot ar SQL Server 2005 galvenais rīks, ar ko strādā datu bāzes izstrādātāji, ir SQL Management Studio. Salīdzinot ar iepriekšējām SQL Server versijām šis rīks sevī apvieno iepriekš pieejamos- SQL Server Enterprice Manager un Query Analyzer. Svarīgi ir tas, ka strādājot ar SQL Server Express 2005 un jaunāku (bezmaksas SQL Server versija, kā būtiskākais ierobežojums ir datu bāzes apjoms- šobrīd 4 GB), ir pieejams bezmaksas rīks datu bāzes administrēšanai- SQL Server Management Studio Express. Bezmaksas versijā gan ir ierobežojumi, tomēr, kamēr nav jāveic ļoti sarežģīti uzdevumi, ar to pilnīgi pietiek.

Vaicājuma statistika

Raksta mērķis mudināt paspēlēties ar "Statistics IO" tiem, kas to vēl nav darījuši.

Aprēķinātās kolonnas

Dažkārt ir labi izmantot kolonnas, kuru vērtības tiek aprēķinātas dinamiski, vaicājumam griežoties pie kādas konkrētas kolonnas- tās sauc par aprēķinātajām kolonnām (computed column).

Kāda vērtība tika ielikta Identity laukā?

Daži veidi kā noskaidrot, kāda vērtība tika ievietota Identity laukā (vispār var arī netikt ievietota, ja notiek kļūda vai tiek taisīts Rollback transakcijai):

1) @@Identity- atgriež pēdējo vērtību, kas ģenerēta priekš identity lauka. Ja Tabulā, kurā ievieto datus, ir trigeris, kas raksta datus citā tabulā, tad atgriezīs tās tabulas Identity, kurā datus rakstīja pēdējā. Konekcijas ietvaros!

2) SCOPE_IDENTITY()- atgriež pēdējo ģenerēto Identity vērtību vērtību, bet neskatās, kas notiek trigeros. Konekcijas ietvaros! (šis arī tas, kas tiek izmantots, ja ģenerē .NET SqlDataAdapter- insert komandu)

3) IDENT_CURRENT('TabulasNosaukums')- pēdējo ģenerēto Identity vērtību, kas ģenerēts norādītajā tabulā. Šeit jārēķinās, ka citas konekcijas var iespaidot atgrieztos rezultātus.

4) [2011-05-17 papildināts] Ievietoto datu identifikatoru vērtību iegūšanai var izmantot arī T-SQL Output klauzu.

Labāk šis aprakstīts un ar piemēru ilustrēts šeit: http://msdn.microsoft.com/en-us/library/ms175098.aspx bet arī šeit, jo ik pa laikam šo sanāk meklēt.

Identity kolonnā sev vēlamās vērtības

Šeit aprakstīts, kā panākt, lai Identity kolonnā var ierakstīt sev vēlamās vērtības.

Atkarībā no programmas dažādi vaicājuma rezultāti

Piemēram- ir datu bāze, kurā strādā ar vairākām programmām. Programmas ielasa datus no skatiem. Skatos ir pielikta "where" daļa.
Nepieciešamas izmaiņas- viena no programmām, kas lasīja datus no šiem skatiem ir jāmaina tā, lai tiktu ielasīti pilnīgi visi dati. Tajā pašā laikā saglabājot to, ka pārējās programmās tiek ielasīti dati tieši tāpat, kā to darīja iepriekš.
Ko darīt?
Izveidojam funkciju, kas atgriež vērtību 1, ja jārāda visi dati, 0, ja jārāda tikai pēdējā gada dati- atkarībā no programmas, ar ko lietotājs strādā. Šo funkciju izmantot skatos. Tādā veidā panākot minimālu izmaiņu nepieciešamību programmā.
Funkcija izskatītos apmēram šādi:

Create FUNCTION [dbo].[fn_JaunaProgramma] ()
RETURNS [bit]
AS BEGIN
IF EXISTS (SELECT program_name FROM master.dbo.sysprocesses
    WHERE spid = @@spid AND program_name = 'Nosaukums')
       RETURN 1;
RETURN 0;
END

„Nosaukums” ir programmas nosaukums, ar kuru lietotājam jāslēdzās klāt lai atgrieztu vērtību ‘1’. (Kā uzstādīt programmas nosaukumu)
Savukārt pats vaicājums skatā varētu izskatīties šādi (zvaigznīte konkrētu kolonnu uzskaitījuma vietā netiek rekomendēta):

Select *
From dbo.Tabula
Where dbo.fn_JaunaProgramma() = 1 OR gads > 2008

BET! Kods ir ļoti grūti testējams (kāds saka, ka neatlasa datus, bet programmētājs pieslēdzas ar Management Studio un viss tiek rādīts, jo ir cits programmas nosaukums)! Var rasties daudz mulsinošu problēmu, par kurām neviens nekad neiedomāsies. Šis bija veiksmīgs pagaidu risinājums (daļa programmu strādāja vienā veidā, otra daļa- pavisam citā) pārejas periodam. Pēc tam funkcija vienkārši atgrieza vienu vērtību- „1”. Kā galīgo risinājumu šādu nerekomnedētu.

Programmas nosaukuma norādīšana

Pēc noklusējuma- jebkura izveidota .Net programma Profiler pie „ApplicationName” uzrāda „.NET SQLClient Data Provider”. Ja strādā ar vienu programmu, tad nav nekādu problēmu, bet, ja ar datu bāzi strādā uzreiz vairākas, būtu ērti redzēt skaistu programmas nosaukumu.

Lai to risinātu jāpievieno konekcijai šo: „Application Name=ManaProgramma”, kur ManaProgramma ir programmas nosaukums.
Kopumā tas varētu izskatīties šādi:

„Data Source=SQLServerExpress;Initial Catalog=TestDB;Integrated Security=True; Application Name=ManaProgramma”

Ļoti vienkārša, bet ērta lieta.

Kolonnu saraksta un atbilstošo datu tipu iegūšana

Veidojot aprakstus datu bāzei, kau apskatītu kādi lauki ir tabulā, skatā, funkcijā (kas atgriež tabulu), lai iegūtu kolonnu sarakstu, var veidot diagrammas, otrs variants ir izmantot sp_help procedūru. šādi:

sp_help 'objektaNosaukums'

vai arī- datu bāzes objektu sarakstu iegūst:

select * from sys.all_objects
select * from sys.all_columns

Pēc tam abus var savā starpā sasiet ar object_id palīdzību un izveidot visu ko nu nepieciešams.
Tas gan nestrādā uz vaicājumiem (gribējās dabūt veidu, kā iegūt šādu sarakstu uz jebkuru vaicājumu).

Piezīmes:
Vispār raksta doma bij pilnīgi savādāka nekā beigās sanāca. Sākotnējā doma bija- jebkuram vaicājumam iegūt kolonu sarakstu ar datu tipiem. Bet nekādā elegantā veidā to neizdevās izdarīt. Ja kāds pateiks kā to var izdarīt būšu priecīgs.

Doma kurā virzijos- kad .Net izveido datu adapteri, var nolasīt shēmu no datu bāzes (reāli nenolasot datus). Tas izskatās apmēram tā (dt- jauna datu tabula bez kolonām, sql- vaicājums):

SqlDataAdapter da = new SqlDataAdapter(sql, this.sqlConnection1);
da.FillSchema(dt, SchemaType.Source);

Paskatoties ar profiler tiek izpildīta sekojoša komandu virkne:

SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;
select * from tabula
SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

Atslēga tajā visā ir „SET FMTONLY ON”- ja šis parametrs ir „ON”, tad vaicājums neatgriež datus, bet tikai shēmu.
Respektīvi- paliku pie secinājuma, ka pa tiešo nav ērta veida, kā iegūt visus rezultātus, bet ar .net programmas palīdzību to tā kā varētu sataisīt..

SnapShot izmantošana testēšanā

Strādājot pie izmaiņām ierastais proces ir apmēram tāds- viens vai vairāki cilvēki viec izmaiņas testēšanas vides serverī, tiek laisti daudzi skripti, datu mainīšanas vaicājumi. Vēlāk datu struktūras maiņas skripti tiek apvienoti vienā lielākā, vēlreiz pārbaudīti uz citas testēšanas vides un galu galā tiek laisti produkcijas vidē.

Problēma:
Pēc neveiksmīgas vaicājumu izpildes nepieciešams atjaunot vidi tādu, kāda tā bija- to var darīt ar vaicājumu palīdzību, kas atsauc veiktās izmaiņas (piemēram- nepareizi pievienojam kolonnu, tad nu to pēc tam dzēšam). Tāpat to var darīt ar rezerves kopiju (BackUp) palīdzību. Ja pirmais veids ir ātrs (bet ne vienmēr izpildāms, jo piemēram, netīši izmetot kolonnu atjaunot to ar iepriekšējiem datiem ar vaicājumu palīdzību parasti nav iespējams), tad otrs jau ir lēns un samērā piņķerīgs process.

Risinājums (sākot ar SQL Server 2005):

Izmantot datu bāzes SnapShot (SnapShot pēc idejas ir datu bāzes ‘momentuzņēmums’ tā izveides brīdī- SnapShot’ā datus mainīt nav atļauts).

Tātad izveide:

CREATE DATABASE TDatuBaze_SnapShot ON
(
NAME = TDatuBaze,
FILENAME = 'C:\SQL\TDatuBaze.ss'
)
AS SNAPSHOT OF TDatuBaze;

Kur TDatuBaze_SnapShot- veidojamā ‘momentuzņēmuma’ nosaukums,
„TDatuBaze”- fiziskais faila nosaukums, no kā veido ‘momentuzņēmumu’:

FileName-jaunā SnapShot faila fiziskā atrašanās vieta (kā nu paši izdomājam).
„As SnapShot OF TDatuBaze” – kurai datu bāzei tiek veidots ‘momentuzņēmums’.
Pēc skripta izpildes pie Databases Snapshots varēs atrast jaunizveidoto snapšotu. To var pavisam droši izmantot select vaicājumu izsaukšanai vai datu bāzes iepriekšējās struktūras apskatīšanai.

Atjaunošana:

Use Master
ALTER DATABASE TDatuBaze SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE TDatuBaze from DATABASE_SNAPSHOT = 'TDatuBaze_SnapShot'
ALTER DATABASE TDatuBaze SET MULTI_USER WITH ROLLBACK IMMEDIATE

Šajā skriptā:
· No sākuma pārslēdzam konekciju uz Master datu bāzi (datu bāzei nedrīkst lietot brīdī, kad to atjauno no SnapShot).
· Nežēlīgi atsitam visus citus lietotājus, kas šobrīd varētu lietot datu bāzi.
· Atjaunojam.
· Atļaujam citiem lietotājiem slēgties klāt datu bāzei.

Pēc skripta izpildes datu bāzi ir tieši tāda, kāda tā bija SnapShot izveides brīdī. Galvenā atšķirība no BackUp:
· Strādā stipri ātri (pie izveides dati netiek kopēti- tikai tad, kad pamata datu bāzē tiek veiktas izmaiņas) un ērti
· Var salīdzināt snapshot ar aktuālo datu bāzi

Ierobežojumi:
· SnapShot nevarēs izveidot uz SQL Server Express (vajag Enterprise Edition)
· Vienai datu bāzei šādā scenārijā nevar būt vairāk kā viens SnapShot (nu- principā būt var, tikai atjaunot datu bāzi varēs tikai tad, kad paliks tikai viens snapshot).
· Ja datu bāze, kurai ir izveidots SnapShot atjauno no rezerves kopijas- tad SnapShot vairs nav derīgs.
· Būtu jārēķinās ar papildus slodzi serverim, jo mainot datus jārūpējas arī par SnapShot.