NULLIF kontra ISNULL valamint a nullával való osztás kezelése

A NULLIF és az ISNULL ránézésre hasonlónak tűnhetnek, viszont meglehetősen eltérő módon viselkednek.

NULLIF

Szintaxis: NULLIF(expression, expression)

https://msdn.microsoft.com/en-us/library/ms177562.aspx

Ha a két kifejezés értéke különbözik, akkor visszaadja az első kifejezést. Ha viszont megegyeznek, akkor NULL-t ad vissza. Egyszerűbb SQL utasítás érhető el vele, mintha a CASE-t használnánk.

NULLIF és CASE összehasonlítására példa az Books Online-ból (BOL):

USE AdventureWorks2012; 

GO 

SELECT ProductID, MakeFlag, FinishedGoodsFlag,  

   NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal' 

FROM Production.Product 

WHERE ProductID < 10; 

GO 

 

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' = 

   CASE 

       WHEN MakeFlag = FinishedGoodsFlag THEN NULL 

       ELSE MakeFlag 

   END 

FROM Production.Product 

WHERE ProductID < 10; 

GO 

ISNULL

Szintaxis: ISNULL(check_expression, replacement_value)

https://msdn.microsoft.com/hu-hu/library/ms184325.aspx

Ha a vizsgálandó első paraméter nem NULL, akkor azt adja vissza, ellenkező esetben a helyettesítő értéket. Kiválóan alkalmas olyan esetekben például, amikor valamilyen alapértelmezett értéket szeretnénk használni, ha egyébként NULL lenne. A COALESCE utasítás egy speciális esetének is felfogható, amikor csak két paramétert kapott és abból kell visszaadnia az első nem NULL értéket.

Jó példa az aggregáló műveletekre szintén az MSDN-ről:

USE AdventureWorks2012; 

GO 

SELECT AVG(ISNULL(Weight, 50)) 

FROM Production.Product; 

GO 

Az aggregáló függvényeknél erősen ajánlott végiggondolni, hogy kellene-e használni, mert ezeknél a függvényeknél, ha legalább egy elem NULL, akkor az eredmény is NULL lesz.


Közös példa: nullával való osztás 
A kettő kombinálására egy jó példa a nullával való osztás kezelése. Először a NULLIF segítségével kezeljük, hogy ha nullával osztanánk, akkor ne dobjon hibát, ekkor ugyanis NULL lesz az eredmény.
SELECT @osztando / NULLIF( @oszto, 0 ) AS value

Majd erre hívjuk meg az ISNULL-t, hogy ilyenkor nullát adjon vissza és kész is:

SELECT ISNULL( @osztando / NULLIF( @oszto, 0 ), 0) AS value

Entity Frameworkből UDT paraméterű tárolt eljárás futtatása

Az Entity Framework alaphangon nem támogatja a saját SQL típust, vagyis a User Defined Type-ot. Ha egy olyan tárolt eljárást szeretnénk importálni az EF-fel, ami UDT típusú paramétert vár, akkor ugyan nem fog hibát dobni, de nem is fogja legenerálni a hozzátartozó kódot.

Ennek áthidalására egy jó módszer az EntityFrameworkExtras nevű NuGettel is elérhető csomag, aminek segítségével típusosan lehet ilyen tárolt eljárást futtatni. A bekötéséhez az alábbi néhány lépés szükséges.

1. UDT létrehozása MS SQL Server adatbázisban

CREATE TYPE [dbo].[TEMP_IDTABLE] AS TABLE(

       [ID] [int] NULL

)

GO

2. Ezt a típust paraméterként használó tárolt eljárás létrehozása

CREATE PROCEDURE [dbo].[DummyStoredProcedure]

       @myValues [Temp_IDTABLE] READONLY

AS

BEGIN

       -- értelmes logika

       SELECT * FROM @myValues

END

3. A használt EF verziónak megfelelő EntityFrameworkExtras hozzáadása a projekthez

• EF 5: EntityFrameworkExtras.EF5
• EF 6: EntityFrameworkExtras.EF6

4. Létre kell hozni egy osztályt, ami majd az 1. lépésben elkészült UDT-t fogja reprezentálni

[UserDefinedTableType("TEMP_IDTABLE")]

public class TempIdTable

{

    [UserDefinedTableTypeColumn(1, Name = "ID")]

    public int? ID { get; set; }

}

Természetesen az osztály és a mezők neve bármi lehet, mivel az attribútumokkal lesz beállítva, hogy az adatbázisban mire kell majd leképezni.

5. Az előbbihez hasonlóan a tárolt eljáráshoz kell egy osztály

[StoredProcedure("DummyStoredProcedure")]

public class DummyStoredProcedure

{

    [StoredProcedureParameter(SqlDbType.Udt, ParameterName = "myValues")]

    public List<TempIdTable> MyValues { get; set; }

}

6. Ezek után már csak meg kell hívni az eljárást. Ehhez az EntityFrameworkExtras tartalmaz Extended Methodokat, amik a DbObjectre illetve az ObjectContextre akadnak rá, és olyan objektumokat várnak, amik el vannak látva a StoredProcedure attribútummal.

ObjectContext oc = new ObjectContext("ConnectionString");

var sp = new DummyStoredProcedure

{

    MyValues = new List<TempIdTable>

    {

        new TempIdTable { ID = 1 },

        new TempIdTable { ID = 2 }

    }

};

IEnumerable<int> results = oc.ExecuteStoredProcedure<int>(sp);

SSMS IntelliSense Cache frissítése

Időnként előfordul, hogy miután létrehoztam, módosítottam esetleg töröltem valamilyen objektumot, az SSMS hibát jelez olyan SQL utasításokban, amik az érintett objektumra hivatkoznak. Annak ellenére, hogy az SQL script sikeresen lefutna, eléggé zavaró, amikor bemutatásnál vagy megbeszélésen piros hibajelzések tarkítják a kódot. Ezt az okozza, hogy az SSMS-ben lévő IntelliSense Cache még nem frissült a változtatás óta.

Szerencsére többféleképpen is ki lehet kényszeríteni, hogy frissüljön:

• Gyorsgombok segítségével: CRTL+SHIFT+R
• Menüben kikeresve: Edit / IntelliSense / Refresh Local Cache

Hasznos MSSQL infók

Megosztom veletek az elmúlt napok tapasztalatát az MSSQL világából, hátha hasznos lesz nektek is:

Dinamikus SQL

Dinamikus SQL utasításba táblaváltozót (@ prefix) nem lehet átadni kimeneti paraméterként, viszont léteznek megkerülő megoldások. 

• Az egyik a temp tábla (# prefix) használata, ami addig létezik, amíg az adott session tart vagy el nem dobtuk, emiatt elérhető a dinamikus SQL scope-jában is

CREATE TABLE #t ( id INT ) DECLARE @q NVARCHAR(MAX) = 'insert into #t values(1),(2)' EXEC (@q) SELECT * FROM #t

• Táblaváltozót használunk amibe beleszúrjuk a dinamikus SQL futási eredményét

DECLARE @t TABLE ( id INT ) DECLARE @q NVARCHAR(MAX) = 'declare @t table(id int)
                            insert into @t values(1),(2)
                            --itt a lényeg:                            select * from @t'INSERT INTO @t EXEC(@q) SELECT * FROM @t

Nem mindegy, hogy az EXEC (@SQL) vagy az EXEC @SQL utasítást használjuk. Első esetben a @SQL változó tartalmát utasításként értelmezi, ezzel szemben az utóbbinál pedig a @SQL változó tartalmának megfelelő nevű tárolt eljárást akarja futtatni.

Konkatenálás vs Concat

Az SQL nem végez automatikus típuskonverziót konkatenációnál, ami nem baj, de nem árt fejben tartani, mert körülményes lehet utólag átírni egy dinamikus SQL kifejezést a Concat függvényre egy int változó miatt. 

Linked Server

Linked serveren keresztül nem lehet ki-/bekapcsolni az IDENTITY_INSERT tulajdonságot. Ha mégis elkerülhetetlen, akkor létre kell hozni egy tárolt eljárást a célrendszeren, ami beállítja és azt kell meghívni távoli eljárásként. Egy másik korlátozás, hogy nem használható az OUTPUT clause.

SQL Server Alias

SQL alias beállításánál a SERVER-nek hiába van a gépnév mellett megadva a kívánt SQL instance neve is, mivel a gépnév + portszám párost veszi figyelembe. Ezen infó nélkül, ha úgy állítjuk be az aliasokat, hogy RemoteHost\Instance1, RemoteHost\Instance2 és mindkettő az 50000 porton figyel, akkor minden gond nélkül mindkét esetben ugyanarra fog mutatni. A port beállításához leírás itt található.

Cursor

Egyazon Cursort többször is fel lehet használni, ha korábban már le lett zárva és fel lett szabadítva.

Hiányzó indexek lekérdezése MS SQL-ben

Az MS SQL Server 2005+ nyomon követi, hogy szerinte milyen indexek hiányoznak, amiket ki is lehet nyerni belőle. Az alábbi SQL utasítás különböző statisztikai adatokat listáz valamint le is generálja a szükséges CREATE scripteket.

SELECT

migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

mid.database_id,

DB_NAME(mid.database_id) AS database_name,

mid.[object_id],

'CREATE INDEX [missing_index_' 

+ CONVERT (varchar, mig.index_group_handle) + '_' 

+ CONVERT (varchar, mid.index_handle) + '_' 

+ LEFT (PARSENAME(mid.statement, 1), 32) + ']' 

+ ' ON ' + mid.statement 

+ ' (' + ISNULL (mid.equality_columns,'') 

+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

migs.*

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Vannak hiányosságai is ennek a funkciónak:

• nem veszi figyelembe az index létrehozási költségét
• sosem ajánl partícionálást megoldásként
• nem mond semmit az ideális sorrendről a több mezőt magába foglaló indexnél

Bővebb leírás és elemzés itt található.

Lapozás SQL-ben

Többféle modell létezik a lapozott adatok megjelenítéséhez:

1. Egyszerű lekérdezése lapozás nélkül, majd pedig a megjelenítő alkalmazás feladat megoldani, hogy lapozhatóan legyen a megjelenítés
2. Lapozott lekérdezés a ROW_NUMBER használatával
3. Lapozott lekérdezés az OFFSET és FETCH NEXT használatával 

Mindkét esetben, amikor már eleve lapozva olvastatjuk fel az adatokat, érezhető a javulás. Célszerű az OFFSET és FETCH NEXT módszert alkalmazni nagy adatmennyiségnél az SQL SERVER 2012+ verziókban. Bővebb leírás és sebességteszt itt található.

Példakódok a lapozáshoz:  

"ROW_NUMBER" használata:

DECLARE @PageNumber AS INT, @RowspPage AS INTSET @PageNumber = 2SET @RowspPage = 10 

SELECT * FROM (             SELECT ROW_NUMBER() OVER(ORDER BY ID_EXAMPLE) AS Numero,                    ID_EXAMPLE, NM_EXAMPLE , DT_CREATE FROM TB_EXAMPLE               ) AS TBLWHERE Numero BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)ORDER BY ID_EXAMPLE
GO

"OFFSET" és "FETCH NEXT" használata (SQL SERVER 2012):

DECLARE @PageNumber AS INT, @RowspPage AS INTSET @PageNumber = 2SET @RowspPage = 10

SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATEFROM TB_EXAMPLEORDER BY ID_EXAMPLEOFFSET ((@PageNumber - 1) * @RowspPage) ROWSFETCH NEXT @RowspPage ROWS ONLY
GO