BLOGas.lt
Sukurk savo BLOGą Kitas atsitiktinis BLOGas

Upsert operacija su SQL Server 2005

Parašė Sergejus | 2011-09-25 11:34

Upsert – tai operacijų update or insert junginys. Kaip tikriausiai supratote, ji veikia tokiu principu: jeigu įrašas egzistuoja – atliekama operacija update, priešingu atveju – operacija insert. Upsert turi dvi variacijas: update or insert jeigu tikimybė, kad įrašas jau egzistuoja didesnė arba atvirkščiai, insert or update jeigu tikimybė, kad įrašo nėra didesnė. Nuo SQL Server 2008 upsert aprašomas konstrukcijos Merge pagalba. Kuriant mobilią apklausos aplikaciją Agile Turui 2011 man prisireikė upsert’o, bet SQL Server 2005 nieko panašaus neturi, tad teko improvizuoti:

CREATE PROCEDURE [dbo].[InsertFeedback]
	@id NVARCHAR(50),
	@answers NVARCHAR(MAX)
AS
BEGIN
	BEGIN TRANSACTION Upsert

	UPDATE Feedback WITH (SERIALIZABLE)
	SET Id=@id, Answers = @answers
	WHERE Id=@id

	IF @@rowcount = 0
		INSERT INTO Feedback (Id, Answers)
		VALUES (@id, @answers) 

	COMMIT TRANSACTION Upsert
END

Tai nėra 100% upsert atitikmuo, bet ko gero geriausia ką galima gauti su SQL Server 2005.

Rodyk draugams

Tikslios SQL klaidos gavimas iš SqlException

Parašė Sergejus | 2010-05-27 22:52

Dirbant su duomenų bazėmis, kartais svarbu žinoti kokia tiksliai SQL klaida įvyko serveryje. Iš T-SQL pusės tai sužinoti labai paprasta – SELECT @@ERROR. Iš C# pusės mes gauname SqlException, bet savybė ErrorCode nėra @@ERROR atitikmuo.

Jeigu jums, pavyzdžiui, reikia apdoroti SQL klaidą ‘Delete statement conflicted with column reference…’, mes pirma turime sužinoti šios klaidos numerį SQL serveryje. Tai daroma užklausos SELECT * FROM master.sys.sysmessages pagalba. Prieš tai minėtos klaidos kodas yra 547. Toliau rašome paprastą C# praplėtimo metodą IsSqlDeleteConflictException:

public static class ExceptionExtensions
{
    public static bool IsSqlDeleteConflictException(this Exception exception)
    {
        var errorNumber = 547;
        var e = exception as SqlException;

        return e != null &&
               e.Errors.Cast<SqlError>().Any(err => err.Number == errorNumber);
    }
}

Rodyk draugams

Atsarginis SQL Express duomenų bazių kopijavimas

Parašė Sergejus | 2008-12-08 22:38

Praeitą savaitę vienam klientui reikėjo sukonfigūruoti kasnaktinį atsarginį duomenų bazės kopijavimą. Tai padaryti yra paprasta SQL Server Standard ir aukštesnėse versijose. Problema buvo tame, kad klientas turėjo SQL Server Express versiją, kuri neturi SQL agento atsakingo už atsarginių kopijų darymą.  Laimei, T-SQL palaiko duomenų bazės kopijavimo komandas, todėl nors SQL Express duomenų bazių atsarginį kopijavimą nėra paprasta sukonfigūruoti, bet vis dėlto įmanoma.



Tikiuosi, sutaupysiu jums kažkiek laiko išvardinęs pagrindinius žingsnius, reikalingus SQL Express atsarginiam kopijavimui sukonfigūruoti. Kaip ir visada, pavyzdyje naudosiu Northwind duomenų bazę.




  1. Windows vartotojo, atsakingo už atsarginį kopijavimą sukūrimas. Tam nueiname į Computer Management langą (compgmt.msc iš Run lango) ir atliekame žemiau nurodytus veiksmus:

  2. Northwind duomenų bazės atsarginio kopijavimo skripto aprašymas. Atsarginiam kopijavimui naudojama T-SQL komanda BACKUP DATABASE, o tokio skripto pavyzdys galėtų atrodyti taip:

    Detaliau apie BACKUP DATABASE komandą galite pasiskaityti čia.

  3. SQL vartotojo sukūrimas ir reikalingų teisių suteikimas. Prieš tai sukurtam Windows vartotojui reikia sukurti SQL vartotoją su galimybe jungtis prie serverio:


    Turint SQL vartotoją, reikia jam sukurti duomenų bazės vartotoją reikalingoje duomenų bazėje ir jį įtraukti į db_backupoperators grupę:

  4. Suplanuotos užduoties (angl. Scheduled Task) sukūrimas. Paskutiniu žingsniu reikia sukurti suplanuotą užduotį. Kadangi Scheduled Tasks buvo perdarytas Windows Vista, aš pateiksiu geriau pažįstamo Windows Server 2003 langą:

    Kaip matyti, suplanuota užduotis turi paleisti SQLCMD komandinės eilutės programą, -S parametro pagalba nurodant lokalų SQL Express serverį ir –i parametro pagalba – kelią iki prieš tai aprašyto SQL skripto. Saugumo sumetimais, duomenų bazės atsarginio kopijavimo suplanuota užduotis turi būti vykdoma prieš tai sukurto Windows vartotojo vardu.

Norint automatizuoti atsarginį duomenų bazių kopijavimą, SQL Express reikalauja pakankamai daug papildomo konfigūravimo, todėl jeigu jums ypač svarbus lankstumas ir patikimumas – laikas pagalvoti apie Standard versiją…

Rodyk draugams

SQL užklausų greitaveikos stebėjimas

Parašė Sergejus | 2008-05-26 20:30

Tikriausiai daugeliui laikas nuo laiko tenka optimizuoti SQL užklausas. Kaip žinia, pagrindinis optimizacijos tikslas - sumažinti užklausų vykdymo laiką. O kaip išmatuoti tą vykdymo laiką? Žemiau pateiksiu keletą paprastų patarimų, kuriuos naudoju kasdieniniame darbe.



  1. Prieš užklausų vykdymą išvalyti buferį ir procedūrų tarpinę atmintį (cache):

  2. Jeigu norima sužinoti kiekvienos užklausos vykdymo laiką, galima įjungti laiko statistiką:

  3. Jeigu norima sužinoti bendrą vykdymo laiką, užtenka pasirašyti tokį kodo fragmentą:

O gal jus dar žinote kokių patarimų?  Rašykite!

Rodyk draugams

Įrašų kiekio lentelėje gavimas be count(*)

Parašė Sergejus | 2007-09-02 23:04

Dažna užduotis dirbant su SQL yra įrašų kiekio lentelėje gavimas. Visą laiką aš naudojausi visiems gerai žinomą count(*) funkcija. Pasirodo, sisteminėse lentelėse (tiksliau vaizduose) irgi saugoma tokia informacija (su sąlyga, kad lentelėje apibrėžtas pirminis raktas). Tokiu būdu įrašų kiekio gavimas tampa žymiai efektyvesnis, o pati užklausa atrodo taip:

kur Imones - lentelės pavadinimas.

Rodyk draugams

Kaip gauti vartotojiškų SQL išsaugotų procedūrų sąrašą

Parašė Sergejus | 2007-08-14 19:51

Šiandien susiduriau su viena įdomia problema: reikėjo gauti visą vartotojiškų SQL išsaugotų procedūrų sąrašą, bet daugeliui gerai žinomas SQL sakinys nedavė laukiamų rezultatų:

Šalia vartotojiškų, sąraše buvo ir sisteminės išsaugotos procedūros, skirtos darbui su diagramomis

Taigi tam, kad gauti laukiamus rezultatus, pradinę užklausą reikėjo modifikuoti tokiu būdu:

Tikiuosi jums tai sutaupys porą paieškos valandų.

Rodyk draugams

Raidžių generavimas su T-SQL

Parašė Sergejus | 2007-07-09 12:55

Šiandien man prisireikė atlikti skaičių ir raidžių generavimą. Pasirodo, raidžių generavimas gali pareikalauti net 5 funkcijų panaudojimo! Kaip pavyzdį pateiksiu tokį uždavinį: “Sugeneruoti raidę iš intervalo A-Z“.

Kadangi reikia generuoti raides, tai teks pasinaudoti funkcija RAND(), kuri grąžina skaičių intervale nuo 0 iki 1. Tam, kad susieti raides su skaičiais pravers funkcija UNICODE(), grąžinanti nurodyto simbolio Unicode kodą. Jeigu įvykdyti du SQL sakinius: SELECT UNICODE('A') ir SELECT UNICODE('Z') matyti, kad raidės A kodas yra 65, o Z - 90. Iš to seka, kad raidžių A-Z generavimas yra skaičių generavimas intervale nuo 65 iki 90. Tai daroma tokiu būdu:

RAND()*100 - generuoja slankaus kablelio skaičius iš intervalo [0;100]

ROUND(RAND()*100), 0) - generuoja sveikus skaičius iš intervalo [0;100], bet duomenų tipas vis tiek nėra INT

CAST(ROUND(RAND()*100), 0) AS INT) - generuoja INT tipo sveikus skaičius iš intervalo [0;100]

CAST(ROUND(RAND()*100), 0) AS INT) % 25 - generuoja INT tipo sveikus skaičius iš intervalo [0;25]

CAST(ROUND(RAND()*100), 0) AS INT) % 25 + 65 - generuoja INT tipo sveikus skaičius iš intervalo [65;90]

Belieka konvertuoti gautą skaičių į Unicode raidę. Tai daroma NCHAR() funkcijos pagalba:

NCHAR(CAST(ROUND(RAND()*100), 0) AS INT) % 25 + 65)

O kad kodas būtų visai universalus, kodus 25 ir 65 pakeisime funkcijomis UNICODE('Z')-UNICODE('A') ir UNICODE('A'). Galutinis sakinys atodys taip:

NCHAR(CAST(ROUND(RAND()*100), 0) AS INT) % (UNICODE('Z')-UNICODE('A')) + UNICODE('A'))

Rodyk draugams