Rollback

Azt már mindenki tudja, h PS-ben lehet kiválóan scriptet generálni a SQL entitásokról a Script() metódus segítségével. Felvetődött egy kérdés vkiben, hogy hogyan lehet csak DB sémát backupolni, és felvetődött erre a Powershell, meg a get-contentitem|%{$_.Script()}, mint alapgondolat. Ja, ez ezt adja vissza egy új táblámra:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Szóval jól néz ki, nem? Nem. addig néz ki jól, amíg nem jut eszembe, hogy van primary key is a táblán. Meg még további indexek. Amiket persze megint ki lehet scriptelni: ˘

%{$_.Indexes}|%{$_.script()}

Meg még ki tudja, mi marad ki. Na mindegy, tuti megoldást nem tudok, de ez olyan lámának tűnt, hogy elkezdtem nézegetni a metódusokat, és találtam egy olyat, hogy EnumScript():

PS > gci |%{$_.EnumScript()}

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_KeynoteDaily] PRIMARY KEY CLUSTERED
(
        [site] ASC,
        [day] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ix_test] ON [dbo].[KeynoteDaily]
(
        [failures] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ix_test2] ON [dbo].[KeynoteDaily]
(
        [measurements] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Vagyis ő megcsinálja nekünk a rekurziót. Szerintem aranyos kényelmi szolgáltatás.
Ja, a fentiekhez a teljes path a következő volt: PS SQLSERVER:\SQL\MYCOMPUTER\DENALI\Databases\MyDatabase\Tables> . Csak a tisztánlátás kedvéért.

Az egyik újdonság a Denali-ban a szekvencia. Eddig ilyen volt Oracle-ben, PostgreSQL-ben, most már van MSSQL-ben is. Eddig MSSQL-ben volt IDENTITY, de az egy kicsit más. Az IDENTITY egy adott tábla sémájához kötött autoincrementes oszlop, megadott kezdőértékkel és növekedési értékkel. Értéket neki nem adunk, hanem ő magától kitölti, kivéve ha azt mondjuk, hogy SET IDENTITY_INSERT tabla ON. Az általunk utoljára felhasznált értéket a @@IDENTITY változó illetve SCOPE_IDENTITY() függvény megmondja, ha szeretnénk. Ez jó. Viszont olyan mértékben a sémához van kötve, hogy az identity tulajdonság nem változtatható. Nem lehet se rátenni egy meglévő oszlopra, se levenni róla. Ez igen fájdalmas tud lenni időnként, és én még nem tudtam feldolgozni, hogy ez miért ilyen béna.
Continue reading ‘Denali I. – SEQUENCE vs IDENTITY’ »

Keresgéltem a BOL-ban, és belebotlottam egy függvénybe: fn_my_permissions(). Ez igen kedvesen megmondja, hogy milyen jogaim vannak. Néhány példa:

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
SELECT * FROM fn_my_permissions ('dbo.Mytable', 'OBJECT');

És mivel van kiváló impersonation is, sysadminként más jogait is lekérhetem:

EXECUTE AS LOGIN = 'GipszJakab'
SELECT * FROM fn_my_permissions ('dbo.Mytable', 'OBJECT');
REVERT

Időnként meglepve tapasztalom, hogy emberek ignorálják az SQL szerverbe bekerült dolgokat, és nekik az SQL Server pont olyan, mint mikor megismerték. Oké, az Enterprise Manager már nincs meg, de azért más is változott. Múltkor egy fejlesztő az Oracle ROWNUM okosságát hiányolta, mert ablakozó lekérdezést akart csinálni, a fenti virtuális oszlop pedig sorszámozza a visszatérő recordsetet, és lehet azt mondani, hogy where rownum < 10. Mondtam, hogy van ilyen.

Gyártsunk játszós táblát:

use tempdb

create table rankdemo (
ugyfel int,
uzletkoto int,
datum smalldatetime,
osszeg float
)

insert into rankdemo values
(1001,1,’2010-01-01′,1234),
(1002,2,’2010-01-02′,1245),
(1003,2,’2010-01-04′,6578),
(1004,3,’2010-01-03′,765),
(1005,3,’2010-01-10′,44236),
(1001,3,’2010-01-11′,75),
(1001,1,’2010-01-10′,1434),
(1002,4,’2010-01-01′,94848),
(1001,1,’2010-01-02′,44236),
(1006,1,’2010-01-31′,4876),
(1001,4,’2010-01-09′,987)

Ebben álságos szerződéskötési adatok vannak, csak a minta kedvéért, valós üzleti vállalkozáshoz a sémát nem ajánlom. És most nézzük meg az úgynevezett ranking függvényeket gyorsan:

Először is, a már emlegetett ROW_NUMBER():

select ugyfel, osszeg, ROW_NUMBER() OVER (order by osszeg desc) as sorszam
FROM rankdemo

Egyszerű, mint a szög. A hozzá hasonló RANK() a rendezés alapjául szolgáló érték "helyezését" mondja meg, azaz itt van holtverseny.
Continue reading ‘ROW_NUMBER, RANK – T-SQL ‘újdonságok’’ »

Ideje, hogy valami szakmait is írjak, mert kezd uncsi lenni a blog. Szóval van egy szép hosszú listám arról, hogy mi mindent nem mondtam el a Millenárisban tartott beszédemben, és ebből mazsolázgatok. Kezdjük is el!

Az első egy kicsi, de hasznos apróság: az OUTPUT clause vagy ahogy Serény tanár úr írná, a klóz. Mire jó? Tipikus jelenet, hogy kell update-elnem rekordokat egy OLTP adatbázisban, azaz az adat változik alattam, de szeretném tudni, hogy pontosan melyeket update-eltem meg. Erre a gyenge megoldás a

select primary_key into #temptabla from tabla
where status = 'NEW'

update tabla where primary_key in (select primary_key from #temptabla)
set status = 'ACTIVE'

drop table #temptabla

Ez egy egész jó megközelítés, két feltétel teljesülése esetén:

1. Nulla konkurrencia lehetséges, azaz pl. nem akar egyszerre két alkalmazásszerver dolgozni az adatbáziban, ekkor ugyanis ők lelkesen dolgozzák fel a rekordokat – egyszerre. Ezt nem részletezném, hogy miért gáz.
2. 2008-at vagy kevesebbet írunk. Azóta ugyanis van OUTPUT clause.

A dolog roppant egyszerű: bele van dugva egy belső trigger gyakorlatilag, ami a következőt tudja: ha beletömjük az OUTPUT-ot az INSERT/UPDATE/DELETE utasításba, akkor lesz inserted és/vagy deleted táblánk, és lehet kiszipkázni az adatokat, akár csak kiechózni, akár betenni táblaváltozókba. Az OUTPUT helye a WHERE feltétel előtt van még, erre figyeljetek. Valahogy így:

UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key
WHERE status = 'NEW'

Valljuk be, hogy jobban néz ki. Ha el akarjuk menteni az outputot későbbi reszeléshez vagy csak nyakonvágni a teljesítményt, akkor pedig a fenti példa így néz ki:

DECLARE @tablacska table (primary_key int);
UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key INTO @tablacska
WHERE status = 'NEW';
SELECT primary_key FROM @tablacska;

Ez egyébként fokozható, például ha már így előjött a konkurrencia, mint probléma, kiválóan megoldható a sok alkalmazás egy táblát túr probléma ezzel meg egy kis locking hinteléssel:

UPDATE tabla WITH (READPAST)
SET status = 'ACTIVE'
OUTPUT inserted.*
WHERE status = 'NEW'

Így ha több szerver dolgoz fel egy queue-t, hogy szépen mondjam, nem kell nagyon foglalkozniuk a másikkal. Egy konkrét szerver odamegy, kivesz annyi rekordot, amennyit akar (mert lehet TOP-ot is mondani az update-ben), ha valaki más is éppen update-el, akkor az általa lockolt rekordokat átugorja, és keres olyat, ami nincs lockolva. Ez a READPAST ajándéka: átugorja a lockolt rekordokat, így nem akadnak fenn egymáson a párhuzamos processzek.

(disclaimer: épp nincs SQL szerverem, ezért a példákat fejből írtam, ha nem működnek, bocsi, kijavítom :)