Soci (Soczó Zsolt) szakmai blogja

2008.02.29.

SQL Server 2008 újdonságok 24. – Multi-input user-defined aggregates

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 16:22

Eddig a saját aggregáló függvények csak 1 bemeneti változón dolgozhattak, most ezt a limitet feloldották. Így megírhatjuk magunk azokat a függvényeket, amelyek más termékekben már benne vannak, és amelyek nevét nem mondjuk ki. :)

Példaként mutatok egy Population Covariance számító aggregáló függvényt (a másik termék doksijából vettem a függvény számítási módját, de nem vállalok rá garanciát, hogy jól írtam át CLR UDF-re):

using System;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(
Format.Native, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //the aggregate of S, {NULL} is the same as aggregate of S
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = true, //optimizer property
IsNullIfEmpty = true)
]
[StructLayout(LayoutKind.Sequential)]
public class PopulationCovariance
{
    double sumOfA;
    double sumOfB;
    double sumOfAMultB;
    ulong count;

    public void Init()
    {
        sumOfA = sumOfB = sumOfAMultB = 0;
    }

    public void Accumulate(SqlDouble a, SqlDouble b)
    {
        if (a.IsNull || b.IsNull)
        {
            return;
        }

        sumOfA += a.Value;
        sumOfB += b.Value;
        sumOfAMultB += a.Value * b.Value;
        count++;
    }

    public void Merge(PopulationCovariance other)
    {
        sumOfA += other.sumOfA;
        sumOfB += other.sumOfB;
        sumOfAMultB += other.sumOfAMultB;
        count += other.count;
    }

    public SqlDouble Terminate()
    {
        if (count == 0)
        {
            return SqlDouble.Null;
        }

        return (sumOfAMultB - sumOfA * sumOfB / count) / count;
    }
}

Mielőtt bemutatnám a függvény használatát előre bocsájtom, hogy nem értek a statisztikai számításokhoz, de feltételezem a függvény kapcsolatot, összefüggést keres a két halmaz között.

create aggregate PopulationCovariance(@a float, @b float) 
returns float
external name SqlClrTest.PopulationCovariance

if object_id('TestOrder') is not null drop table TestOrder

create table TestOrder
(
  Id int not null primary key identity,
  Discount float,
  OrderedAmount int
)

insert TestOrder (Discount, OrderedAmount)
values 
(0, 5),
(10, 23),
(5, 12),
(2, 1),
(14, 42)

select dbo.PopulationCovariance(Discount, OrderedAmount) from TestOrder

truncate table TestOrder
insert TestOrder (Discount, OrderedAmount)
values 
(10, 5),
(1, 23),
(2, 12),
(20, 1),
(4, 42)

select dbo.PopulationCovariance(Discount, OrderedAmount) from TestOrder

truncate table TestOrder
insert TestOrder (Discount, OrderedAmount)
values 
(10, 5),
(0, 23),
(2, 12),
(5, 1),
(14, 42)

select dbo.PopulationCovariance(Discount, OrderedAmount) from TestOrder
73.08
-65.84
30.48

Az első adathalmaz alapján ha nagy a diszkont értéke, többet vesznek az emberek a termékekből. A másodikban pont fordítva hat a diszkont. A harmadikban próbáltam összezagyválni az adatokat, ott kisebb, de pozitív a két halmaz függése. Valaki megszakértheté nekem ezeket a számokat, hogy jól értelmezem-e? Gondolom valahogyan normalizálni kellene a kimenetet, de nem ismerem a szabályokat.

A népszavazásig nem fedi fel titkát az SZDSZ

Filed under: Élet — Soczó Zsolt @ 15:54

Hír itt.

Ebben az a röhej, hogy ezzel már fel is fedték, hogy nagy a szar, még a saját választásunkat is megbundáztuk. :)

2008.02.27.

Webszerver compression – lassít vagy gyorsít?

Filed under: IIS,IIS7,Szakmai élet — Soczó Zsolt @ 22:05

Nem kérdés, hogy a kliens jelentősen gyorsabban hozzájut a tömörített tartalomhoz, pl. a google lapjai többek között ezért gyorsak.
De mekkora hatással van ez a szerverre? Mennyire lassítja azt le? Eddig az volt a fejemben, hogy ez a szükséges rossz, a szerver procija gőzerővel tömörít, ezért bár a kliens megnövelt felhasználó élményt él át, a szerver ki lesz terhelve. De NEM és nem!

Mivel a tömörített tartalmat letárolja az IIS, ezért ha már egyszer lezajlott a tömörítés, a továbbiakban már csak mint statikus fájlt kell kiszolgálni a szervernek, ráadásul sokkal kisebbet, mint az eredeti. Ennek eredményeképpen nem csak, hogy jobb lesz az élmény a kliensen, de még a szerver is örül. Ez a nem semmi, mi? Persze dinamikus tartalom esetén már sokkal jobban meg kell gondolni, ott általában nem megy az újrahasznosítás.

Mindezt lehet kombinálni SSL-lel is, ekkor is örül a szerver, mert kisebb tartalmat kell titkosítani, és ellentétben a tömörítéssel a titkosítás valóban nagyon proci intenzív. Tessék megnézni a linkelt cikket, tanulságos.
Szeressétek a tömörítést, nem kerül semmibe bekapcsolni (IIS7-en, IIS6-ban nincs kivezetve a GUI-ra).

2008.02.26.

Lejtőn a VB, emelkedő ágon a C#

Filed under: .NET,C#,Job,SQL Server,Szakmai élet — Soczó Zsolt @ 22:24

A jó öreg C++ meg még mindig keresettebb, mint az előző kettő. A JAVA-ról csönd. :) (Az a legkeresettebb, legalábbis ezen adatok alapján.)

Forrás.

Az SQL Server is felfelé megy, azt én vettem már hozzá a képhez, bár gyanúsan együtt fut a Javaval, lehet, hogy nem stimmel valami.

Állások száma

Mit jelent a Start, Stop, Recycle az IIS7-nél

Filed under: IIS,IIS7,Szakmai élet — Soczó Zsolt @ 21:16

Starting, stopping and recycling IIS 7.0 Web sites and application pools

2008.02.24.

SQL Server 2008 újdonságok szünet

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 20:49

A februári CTP nem hajlandó muzsikálni, se kép, se hang, ezen bug miatt. Remélem mihamarabb megoldják.

IIS 7.0 – the number one reason customers want Windows Server 2008

Filed under: IIS,IIS7,Szakmai élet — Soczó Zsolt @ 18:26

Érdekes, mi?

IIS7 a nyerő :)

2008.02.21.

SQL Server 2008 februári CTP AdventureWorks

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 18:38

Itt.

Hogyan hagyjunk meg a duplikált sorok közül csak egyet?

Filed under: Adatbázisok,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 17:34

Ha a táblán nincs PK, ez nem is olyan egyszerű.

Érdekes módon a ROW_NUMBER segítségével ez egyszerűen megoldható.

2008.02.20.

Kulturált installer az SQL Server 2008 februári CTP-hez

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 16:55

Csak egy kép, a könyvtárak beállításához. Eddig ugye erről nem kérdeztek minket, de miért is nem?

sql2008ctp6installerdatadirs.png

Ja, és ami érdekes, eddig a teleptők hta alkalmazások voltak (dhtml), ez meg már WinForms (spy-jal szépen látszanak a WinForms ablak classok). Jót tett neki, mert ez már átméretezhető. Mégis csak 2008-at írunk, átméretezhető ablakok meg már vannak 20 éve, csak itt volt az ideje élni a lehetőséggel.

IE history és egyebek törlése parancssorból

Filed under: IE7,Szakmai élet — Soczó Zsolt @ 16:47
RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255

Felettébb hasznos IE fejlesztéseknél.

Visual Studio 2008 Support for SQL Server 2008

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet,VS 2008 — Soczó Zsolt @ 11:03

A VS 2005 és 2008 nem tud kapcsolódni az SQL Server 2008 CTPhez, csak ezen patch után.

Falaz az ügyészség a rendőröknek falazó ügyésznek

Filed under: Élet — Soczó Zsolt @ 10:54

Szomorú, hogy miközben jön le az új SQL Server, és örülök, hogy süt a nap, közben Magyarszágon rendre arról szólnak a hírek, hogy itt bizony még a régi rendszer patkolása folyik, csak demokráciának hívják. Tudom, ez más, mint az volt, akkor aki pofázott, eltűnt. Nem azt sírom vissza, bár nem is éltem benne. Embertelen lehetett egy értelmiségi embernek. Sok birkának persze jó volt, nem kell messze mennem a családban, hogy erről megbizonyosodjak.

De az elkeserít, hogy lehetne ez nálunk másképp, nem szükségszerűen ilyen fura látszat-demokráciában kellene élni, mégha ez engem direkben nem érint. De bármikor érinthet, és a gyerekeimet, szeretteimet is. Ezért nem tetszik, ami itt folyik.

És márciusban nem azért megyek el szavazni, mert zavar a 300 vagy 1000 Ft, hanem azért, mert csak így tudok jelezni a Gyurcsáni bandának, hogy elmentek ti a k. anyátokba. Persze, ettől nem mennek el, de legalább érezték a támogatásomat. :)

Nem, nem a Viktortól várok új menyországot. De bárkit szívesebben látok kormányon, mint ezeket. Ha ló nincs, jó Viktor is. (A szadeszt max. a drogliberalizáció miatt tartom számon, egyébként Ibolyával együtt (akire előzőleg szavaztam) túlságosan balra mentek el. Marad a Jobbik? :)

A nap híre: SQL Server 2008 februári CTP letölthető

Filed under: Adatbázisok,SQL Server,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 10:38

Méghozzá bárkinek. Azért lényeges momentum ez, mert ez a verzió már nagyjából mindent tartalmaz, amit a végleges termék is fog. Pl. ebben már pl. mennek a Filtered Indexek, vagy az Auditing (select is!), az intellisense se lesz már olyan gyagya, stb.

Csak a download site helytartóját kellene meganalizálni egy kapanyéllel, hogy egy retek direkt linket rakjanak már ki, mert így sok ember (mivel nem látja a tényleges címet) nekilát böngészővel direktben letölteni az 1.2 Gigát, ami persze 5-ször meg fog szakadni. Csak a headerekből lehet kinézni a direkt linket, használjátok ezt.

2008.02.19.

MSDeployhoz Vista SP1 kell

Filed under: IIS,IIS7,Szakmai élet,Vista,Windows Server 2008 — Soczó Zsolt @ 23:53

Mert őkelmét Windows 2008-on tesztelték, ami meg azonos modulokat tartalmaz, mint a Vista SP1. Van motivációm, hogy felrakjam az SP1-et.

SQL Server 2008 újdonságok 23. – Ordered SQLCLR table-valued functions

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 13:44

A Table Valued Function-öknél eddig nem lehetett megadni információt a szervernek a függvényből jövő adatok sorrendjére, így hiába jöttek sorban az adatok, ha a TVF-ből jövő lekérdezés eredményét sorrendeztettük order by-jal, akkor az SQL Server újrarendezte az adatokat, ami meglehetősen nagy költséggel járhat. 2008-ban már megjelölhető az adatok előrendezettsége, ami jelentős költségmegtakarítást eredményezhet.
Nézzük pár példát!

Windows Event Log publikálás:

using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
using Microsoft.SqlServer.Server;

public class TVF
{
    [SqlFunction(FillRowMethodName = "FillEventLogRow")]
    public static IEnumerable EventLog(SqlString logName)
    {
        if (logName.IsNull)
        {
            return new int[0];
        }
        else
        {
            return new EventLog(logName.Value).Entries;
        }
    }

    public static void FillEventLogRow(object obj,
        out SqlDateTime timeWritten,
        out SqlString message,
        out SqlString category,
        out SqlString source,
        out SqlInt64 eventId)
    {
        EventLogEntry e = (EventLogEntry)obj;
        message = new SqlString(e.Message);
        timeWritten = new SqlDateTime(e.TimeWritten);
        category = new SqlString(e.Category);
        source = new SqlString(e.Source);
        eventId = new SqlInt64(e.InstanceId);
    }
}
use AdventureWorks;
go
--Kell az external_access-hez
alter database AdventureWorks set trustworthy on
go

exec sp_configure 'clr enabled', '1';
reconfigure with override;
go

if object_id('EventLog') is not null drop function EventLog
if exists(select * from sys.assemblies where name = 'SqlClrTest') drop assembly SqlClrTest

create assembly SqlClrTest from 'C:\sql2008\SqlClrTest\bin\SqlClrTest.dll'
with permission_set = external_access
go

--Létrehozzuk a tábla kimenetű függvényt,
--ami az eventlogot publikálja ki
create function EventLog(@logname nvarchar(100))
returns table 
(
  Time datetime,
  Message nvarchar(max),
  Category nvarchar(max),
  Source nvarchar(max),
  EventId bigint
)
as external name SqlClrTest.TVF.EventLog
go

create function EventLogWithOrder(@logname nvarchar(100))
returns table 
(
  Time datetime,
  Message nvarchar(max),
  Category nvarchar(max),
  Source nvarchar(max),
  EventId bigint
)
order (Time asc)  --itt az újdonság
as external name SqlClrTest.TVF.EventLog
go

Látható, hogy az függvény létrehozásakor meg lehet adni, az adatok milyen sorrendben fognak jönni.
Ha hazudunk a sorrendet illetően, akkor lekérdezéskor így járunk:

Msg 5332, Level 16, State 1, Line 2
The order of the data in the stream does not conform to the ORDER hint specified for the CLR TVF ‘dbo.EventLog’. The order of the data must match the order specified in the ORDER hint for a CLR TVF. Update the ORDER hint to reflect the order in which the input data is ordered, or update the CLR TVF to match the order specified by the ORDER hint.

Én az event loggal jártam így. Elvileg az event log idő szerint sorba van rendezve. Gyakorlatilag azonban amikor az os-t telepítettem, akkor az alapértelmezett -8 órás időzóna volt megadva, amit telepítés után állítottam át. Ettől az idő is visszaállt 9 órával, így az event log elején voltak nem időrendi bejegyzések.

Miért jó megadni a sorrendet? Vessük össze ezt a két lekérdezést és a végrehajtási tervüket:

select *
from dbo.EventLog(N'Application')
order by Time

select *
from dbo.EventLogWithOrder(N'Application')
order by Time

Table Valued Function order-rel vs. order nélkül

A valódi költség persze nem feltétlen így oszlik meg, de biztos jól mérhető a hatás.
Észrevette mindenki az Assert operátort? Ő a hazugságvizsgáló, ő veszi észre, ha mégse jó a beígért sorrend.

A következő példában az eredmények nagyon gyorsan generálódnak, így jobban látszik a rendezés költsége. Egy egyszerű hatványozó, kamatos-kamat számító függvényt mutatok:

[SqlFunction(FillRowMethodName = "FillAustinPowersRow")]
public static IEnumerable AustinPowers(SqlDouble baseNumber, SqlDouble power, SqlInt32 first, SqlInt32 last)
{
    if (baseNumber.IsNull || power.IsNull || first.IsNull || last.IsNull)
    {
        return null;
    }
    if (first < 0)
    {
        throw new ArgumentOutOfRangeException("first",
            "A sorozat első eleme min. 0 kell legyen.");
    }
    if (first > last)
    {
        throw new ArgumentOutOfRangeException("first, last",
            "A sorozat első elemének kisebb vagy egyenlő sorszámúnak kell lenni a utolsó elemnél.");
    }
    return AustinPowersWorker(baseNumber.Value, power.Value, first.Value, last.Value);
}

static IEnumerable AustinPowersWorker(double baseNumber, double power, int first, int last)
{
    double curr = baseNumber * Math.Pow(power, first);
    yield return curr;

    for (int i = first; i < last; i++)
    {
        curr *= power;
        yield return curr;
    }
}

public static void FillAustinPowersRow(object obj, out SqlDouble num)
{
    num = new SqlDouble((double)obj);
}
&#91;/source&#93;

Hát nem imádni való ez a yield? 

&#91;source='sql'&#93;
create function Austin(@base float, @power float, @first int, @last int)
returns table 
(
  power float
)
as external name SqlClrTest.TVF.AustinPowers
go

create function Austin2(@base float, @power float, @first int, @last int)
returns table 
(
  power float
)
order (power asc)
as external name SqlClrTest.TVF.AustinPowers
go
&#91;/source&#93;

Tesztek:
&#91;source='sql'&#93;
select power from dbo.Austin2(4000000, 1.001, 0, 100000)
order by power
go
select power from dbo.Austin(4000000, 1.001, 0, 100000)
order by power
&#91;/source&#93;

Végrehajtási idők: 26000ms vs. 200ms. Ez már jelentős. Ráadásul, ha nem számok jönnek ki a TVF-ből, hanem pl. stringek, akkor a rendezés még sokkal költségesebb. (A méréseknél kikapcsoltam az eredmények megjelenítését a Management Studioban, hogy annak az ideje lehetőleg ne látsszon a kimenetben.)

Ami viszont furcsa, hogy kb. 10 futtatásból egyszer lefut pár 100 ms alatt az első függvény is. Nem tudom, mi lehet az oka.

A következő példa fájlokat listáz egy könyvtárban, amely listát elvileg az NTFS rendezetten ad vissza:

&#91;source='csharp'&#93;
&#91;SqlFunction(FillRowMethodName = "FillDirListRow")&#93;
public static IEnumerable DirList(SqlString startDir)
{
    if (startDir.IsNull)
    {
        return null;
    }
    return Directory.GetFiles(startDir.Value);
}

public static void FillDirListRow(object obj, out SqlString fileName)
{
    fileName = new SqlString((string)obj);
}
&#91;/source&#93;

&#91;source='sql'&#93;
create function DirList(@base nvarchar(300))
returns table 
(
  FileName nvarchar(300)
)
order (FileName asc)
as external name SqlClrTest.TVF.DirList
go
select * from dbo.DirList('c:\windows\system32')
&#91;/source&#93;

Nos, elküldenek a náthás frászba, hogy már megint beígértem egy sorrendet, de nem tartottam be. No, mondom, ez érdekes lesz, ezek szerint mégse rendezetten adja vissza a neveket az NTFS? Nézzük a bibis váltást:

&#91;source='c'&#93;
11/02/2006  01:34 PM             6,656 blbres.dll
11/02/2006  01:34 PM            17,408 blb_ps.dll
&#91;/source&#93;

Hoppá, igaza van, ez tényleg nem stimmel. WTF? Némi guglizás után fény derült a titokra.
<a href="http://blogs.msdn.com/oldnewthing/archive/2005/06/17/430194.aspx">Why do NTFS and Explorer disagree on filename sorting?</a>
<a href="http://blogs.msdn.com/michkap/archive/2005/01/16/353873.aspx">How [case-]insensitive (apologies to Frank Sinatra)</a>

Az első linken Raymond Chen barátunk elmondja, hogy az NTFS nem a mi örömünkre rendez, hanem a saját hatékonysága érdekében, így ne is várjuk tőle, hogy majd mindenféle nyelvi szabályok szerint rendezze a kimenetet, rendezi, ahogy neki jó. Amikor leformázzuk a kötetet, akkor kialakul egy sorrend, amit aztán semmiféle locale váltás már nem befolyásol. Szóval esetünkben más az SQL Server által elvárt sorrend, mint amelyet az NTFS használ rendezéshez. Ok, az NTFS fekete doboz, de milyen sorrendben gondolkodik az SQL Server ebben az esetben? Másképpen fogalmazva, mi a TFV-ünk egyetlen oszlopának a COLLATION-je?
A <a href="http://msdn2.microsoft.com/en-us/library/ms179886.aspx">doksi</a> alapján Coercible-default, azaz "... If the object is declared in a batch, the object is assigned the default collation of the current database for the connection.". Ez vonatkozik ránk. Az adatbázis SQL_Latin1_General_CP1_CI_AS collation-ű, ez az AdventureWorks alapértelmezett elmebeteg collation-je.
Hogyan lehetne más collation-t rámondani a TFV kimenetére? Mi sem egyszerűbb:


create function DirList(@base nvarchar(300))
returns table 
(
  FileName nvarchar(300) collate Latin1_General_CI_AS
)
order (FileName asc)
as external name SqlClrTest.TVF.DirList

Ettől nem lesz jó a függvényünk, de látjuk, hogyan lehet collation-t megadni az oszlop definícióban. :)
A kis-nagybetű mappelés az NTFS-ben a $UpCase nevű system metadata file-ban tárolódik, ha valaki megmutatja, hogyan kell ezt olvasni, megpróbálom kitalálni, van-e hozzá illő collation a szerverben.

Összegezve: ha a TVF által publikált adatok elve valamilyen sorrend szerint vannak rendezve, akkor ezt a rendezettséget kommunikálhatjuk az SQL Server 2008 felé, így a rendezéseket, csoportosításokat vagy a distinct műveleteket jelentősen gyorsíthatjuk.
Emellett (nem trviális és kevésbé ismert infó), ha egy táblába szúrjuk be a TVF-ből kiszelektált adatokat, és a a céltábla clu indexet tartalmaz, ami pont a TVF kimenetének megfelelő sorrendet ír elő, akkor sokkal gyorsabb lesz az insert. Erről már írtam egyszer.

De, hogy itt is lássuk a hatást:

if object_id('tempdb..#Powers') is not null drop table #Powers

create table #Powers
(
  id int not null identity,
  power float not null
)

create clustered index idx1 on #Powers(power);
go

insert #Powers
select power from dbo.Austin2(4000000, 1.001, 0, 100000)
option (recompile)

insert #Powers
select power from dbo.Austin(4000000, 1.001, 0, 100000)
option (recompile)

A tábla pont úgy van rendezve a clu index miatt, mint a TVF kimente. Emiatt a két insert végrehajtási terve is más lesz:

Beszúrás rendezett és rendezetlen alapadatokból

Durva, mi? A Sort visz el igen jelentős költségeket. Konkrétan, a példabeli százezer sor esetén 12.1 vs. 16.7 mp. Az elsőnél a CPU 5.4mp, a másodiknál 8mp volt. De ami nagyon fontos, és ez nem látszik a profiler kimenetben, kutya sok memória kell a sorbarendezéshez, ezért többször el is szállt hibával a második insert, hogy adott időn belül nem tudott memóriát kapni.

Msg 8645, Level 17, State 1, Line 1
A timeout occurred while waiting for memory resources to execute the query in resource pool ‘default’ (2). Rerun the query.

Egy szó mint száz, becsüljétek meg az eleve rendezett adatokat. :)

Ps. Az előbbi timeoutról infó, akit érdekel.

2008.02.18.

Script Elevation PowerToys for Windows Vista

Filed under: PowerShell,Scripting,Security,Szakmai élet,Vista — Soczó Zsolt @ 18:57

Hogy én ezt eddig nem láttam. Pedig jó az UAC ellen. :)

Breaking Changes for ASP.NET 2.0 applications running in Integrated mode on IIS 7.0

Filed under: IIS,IIS7,Szakmai élet — Soczó Zsolt @ 15:50

Lista.

SQL Server 2008 újdonságok 22. – Composable DML (DML table source)

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 11:09

Ez egy jó dolog lesz.
Az OUTPUT kulcsszó már ismerős lehet 2005-ből, egy DML (INSERT, UPDATE, DELETE) művelet által érintett sorokat lehetett kipakolni tábla típusú változóba vagy lokális változókba.

A 2008-ban ezt tovább bővítették, így a kimenet bemenetként szolgálhat egy INSERT utasítás részére, azaz, össze lehet csövezni mindenféle átmeneti tábla nélkül a DML műveleteket. Innen a komponálható DML elnevezés.

Lássunk egy egyszerű példát:

create table t1(col1 int);
create table t2(col1 int);

insert into t1 values (1),(2),(3);

insert into t2(col1)
select col1 from 
(update t1 
set col1 = col1 + 1 
output inserted.col1) as d;

select * from t2
col1
-----------
2
3
4

Egyszerűen nevet kell adni az output kimentének, és máris táblaként kezelhetjük.

Bob barátunktól átemelek egy összetettebb auditálós példát:

CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
CREATE TABLE AuditChanges (Action varchar(6), Stock VARCHAR(6), Qty INT);
GO

INSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
GO

INSERT INTO AuditChanges
SELECT * FROM
(
MERGE Stock S
  USING Trades T
  ON S.Stock = T.Stock
  WHEN MATCHED AND (Qty + T.Delta = 0) THEN
    DELETE
  WHEN MATCHED THEN
    UPDATE SET Qty += T.Delta
  WHEN NOT MATCHED THEN
    INSERT VALUES(Stock, T.Delta)
  OUTPUT $action, T.Stock, inserted.Qty
) tab (action, stock, qty);
GO

select * from AuditChanges
Action Stock  Qty
------ ------ -----------
DELETE BOEING NULL
INSERT GE     3
UPDATE MSFT   15

Aranyos. De azért tudni kell róla, hogy ez csak egy kezdemény még az SQL Serverben, v 0.1, majd a következő verziókban teljesítik ki. Nem használható csak INSERT-ben az eredmény, csak táblába tud beszúrni, view-ba nem, nem lehet a céltáblán trigger, nem lehet rajta fk vagy pk, nem dolgozhat replikációban. Kb. arra jó, amire a második példa is mutatja, logolni bizonyos műveleteket.

2008.02.14.

SQL Server 2008 újdonságok 21. – Row constructor

Filed under: Adatbázisok,SQL Server 2008 — Soczó Zsolt @ 23:47

Amikor pár sornyi beépített adattal kell dolgozni, jól jön, hogy van tábla típusú változó:

DECLARE @Movie TABLE
(
    MovieID INT IDENTITY(1, 1),
    MovieRatingId INT,
    Title VARCHAR(200) NOT NULL
);

Az adatok betöltése azonban egy utasításban kicsit körülményes volt:

INSERT INTO @Movie (MovieRatingId, Title)
SELECT 3, 'SQL the Movie'
UNION ALL
SELECT 4, 'SQL Massacre'
UNION ALL
SELECT 1, 'SQL for Everyone'
UNION ALL
SELECT 4, 'SQL Massacre 2 - The Oracle Returns'

De SQL Server 2008-ban létezik a Row Constructor, azzal sokkal tisztább megoldást érhetünk el:

INSERT INTO @Movie (MovieRatingId, Title)
VALUES
(3, 'SQL the Movie'),
(4, 'SQL Massacre'),
(1, 'SQL for Everyone'),
(4, 'SQL Massacre 2 - The Oracle Returns');

Jó, mi?

Közvetlenül is lehet szelektálni VALUES-zal létrehozott ál-táblából, csak ilyenkor meg kell adni az oszlopok nevét is az AS után, ami érhető követelmény.

SELECT * FROM (
VALUES
(3, 'SQL the Movie'),
(4, 'SQL Massacre'),
(1, 'SQL for Everyone'),
(4, 'SQL Massacre 2 - The Oracle Returns')) AS T(id, text)

Mindez persze megy CTE-ben is:

WITH MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag)
AS
(   
SELECT *
    FROM (VALUES
    (0, 'UR','Unrated',1),
    (1, 'G','General Audiences',1),
    (2, 'PG','Parental Guidance',1),
    (3, 'PG-13','Parental Guidance for Children Under 13',1),
    (4, 'R','Restricted, No Children Under 17 without Parent',0))
        AS MR(MovieRatingId, Code, Description, AllowYouthRentalFlag)
)
SELECT
M.Title,
MR.Code AS RatingCode,
MR.Description AS RatingDescription
FROM @Movie AS M
INNER JOIN MovieRatings AS MR
ON M.MovieRatingId = MR.MovieRatingId;

Ezek után néha már nem is kell tábla típusú változó, elég egy ilyen pszeudo tábla.

Példák innen lopva. Köszönet érte a szerzőnek.

Newer Posts »

Powered by WordPress