SQLServer , MS-SQL T-Sql specialitások

Ideiglenes táblák: memóriában tárolódik, speciális szegmens tárolja

- tranzakció szintű
- session szintű
- lokális
- globális: ##nev

(A tábla neve előtt # szerepel).

Fizikai tábla: fizikai, permanens tábla:

- heap: a rekordok folytonos egységben tárolódnak
- klaszter: egységekre bontott a tábla, van egy hasító függvény, amely a kulcsa alapján
megadja a tároló csoport helyét. Következmény: az azonos kulcsúak egy helyre kerülnek. 

A klaszter csoportok több különböző tábla rekordjait is tudja tárolni.

Táblák létrehozása: 

•  permanens tábla: CREATE TABLE nev (…) ON filegroup 
•  lokális ideiglenes tábla: CREATE TABLE #tablanev (…) 
•  globális ideiglenes tábla: CREATE TABLE ##tablanev(…) 
•  memóriabeli tábla (táblaváltozó): DECLARE @tablavaltozo TABLE(…) 

MERGE Céltábla frissítése (beszúrás, módosítás, törlés) egy forrás tábla alapján.  

MERGE <target> USING 

(SELECT <expression> FROM <source>) AS <alias> ON <intersection> 

WHEN MATCHED THEN <UPDATE | DELETE >  

WHEN TARGET NOT MATCHED [AND <conditions>] THEN <INSERT> 

WHEN SOURCE NOT MATCHEND [AND <conditions>] THEN <UPDATE | DELETE> 

AL-SELECT használata

Korrelálatlan:   nincs hivatkozás a külső(első) SELECT-re

WHERE db > (SELECT avg(db) …);

Korrelált:  van hivatkozás a külső (első) SELECT mezőre
SELECT fo.aru, (SELECT count(*) FROM aruk al WHERE al.db > fo.db)
FROM aruk fo;

 A  SELECT parancs speciális elemei: CASE, WITH, IDENTITY 

CASE Feltételes kifejezés 

• SELECT CASE kif WHEN e1 THEN k1... ELSE k END 
• SELECT CASE WHEN kif1 THEN k1… ELSE k END … 

Első esetben a kifejezés értékét vizsgálja a meglévő értékekre, míg a második esetben nem fix 

értékkel hasonlítunk össze. 

NULL érték kezelése: 

-  IS NULL: ha az ’a’ argumentum üres, akkor a ’b’-t adja vissza. Ha nem üres, akkor 

önmagát. 

• SELECT IS NULL(3,2) -> 3-t ad, 
• SELECT IS NULL(null,2) -> 2-t 

-  NULL IF: akkor ad vissza null értéket, ha a két érték megegyezik. 

•  SELECT  NULLIF(5,5) -> null, 
•  SELECT NULLIF(3,2) -> 3 

WITH Ideiglenes változók létrehozására. SQL parancs hatáskörében jön létre. 

Aliast rendel a táblához, ez felhasználható az utána lévo SELECT-ekben. 

• WITH tnev (mezok) AS (SELECT…) SELECT … FROM tnev… 

IDENTITY 

Ha explicite szerepel, akkor azt használja, ha nincs megadva, akkor ő fogja generálni. 

Mező típus. Származtatott kulcs. Auto increment key. 

•  IDENTITY(n,m);        kod int identity(1,1) 

With-es SQL szerkezet alkalmazása(Hierarchikus kapcsolat lekérdezés ):

----definició
WITH dinamikus_tabla AS
(
  SELECT CAST('2011-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte 
  WHERE   DateValue + 1 <= '2030-12-31'
)
----- használat
SELECT  YEAR(datevalue) ev,
        MONTH(datevalue) honap,
--DAY(datevalue) nap,
        CONVERT(date, min(datevalue)) honap_eleje,
        CONVERT(date, max(datevalue)) honap_vege,
        CONVERT(numeric(9,0), COUNT(datevalue)) honap_napok_db
--INTO #YearMonthTempTable
FROM   dinamikus_tabla 
group by YEAR(datevalue), MONTH(datevalue) --,DAY(datevalue)
order by YEAR(datevalue), MONTH(datevalue) --,DAY(datevalue)
OPTION (MAXRECURSION 0)
;



  Az --horgony tagnál adjuk meg a rekurzió kezdeti pontját meghatározó
rekordot. A  --rek_tag  definiálja a rekurzív algoritmust. Az INNER JOIN
segítségével a WITH által definiált kifejezés ismételten meghívódik.

WITH TempOrg([ID], [Name], ParentID) AS 
( 
  --horgony
  SELECT O.ID, O.Name, O.ParentID 
  FROM Organization O 
  WHERE ParentID IS NULL 
   ---------
  UNION ALL 
 ----------
  --rek_tag
  SELECT O.ID, O.Name, O.ParentID 
  FROM Organization O 
  INNER JOIN TempOrg TMP ON 
    TMP.ID = O.ParentID 
) 
SELECT * FROM TempOrg 

Oracle LISTAGG függvény ms-sql megvalósítása:

SELECT FieldA , STUFF(( SELECT  ','+ FieldB FROM TableName a
WHERE b.FieldA = a.FieldA FOR XML PATH('')),1 ,1, '')  Members
FROM TableName b
GROUP BY FieldA;

SQL DATEADD – SQL DATEDIFF funkciók használata

• SELECT CURRENT_TIMESTAMP — 2012-01-05 07:02:10.577
• SELECT DATEADD(month,2,‘2012-12-09’) — 2013-02-09 00:00:00.000
• SELECT DATEDIFF(day,‘2012-12-09’,‘2013-02-09’) — 62
• SELECT DATENAME(month, ‘2012-12-09’) — December
• SELECT DATENAME(weekday, ‘2012-12-09’) — Sunday
• SELECT DATEPART(month, ‘2012-12-09’) — 12
• SELECT DAY(‘2012-12-09’) — 9
• SELECT GETDATE() — 2012-01-05 07:02:10.577
• SELECT GETUTCDATE() — 2012-01-05 12:02:10.577
• SELECT MONTH(‘2012-12-09’) — 12
• SELECT YEAR(‘2012-12-09’) — 2012

Egyedi dátum formátum összerakása (YYYY_MM_DD)

select CurrentDate=rtrim(year(getdate())) + ‘_’ +

right(‘0’ + rtrim(month(getdate())),2) + ‘_’ +

right(‘0’ + rtrim(day(getdate())),2)

Gyűjtő más néven aggregáló függvények: 

• AVG: a sorok egy kijelölt csoportjának átlagát számolja ki.
• COUNT: megszámlálja azokat a sorokat, amelyek adatokat tartalmaznak (NOT NULL)
• MIN: a sorok egy csoportjában lévő legkisebb értéket adja meg.
• MAX: a sorok egy csoportjában lévő legnagyobb értéket adja meg.
• SUM: a sorok egy csoportjában lévő adatok összegét adja vissza.

Például:

SELECT nev1, COUNT(1) as DB 

FROM tablam 

GROUP BY nev1 

HAVING COUNT(1) > 1 

ORDER BY nev1

Tárolt eljárás, függvény előnyei: 

-  kisebb hálózati forgalom
-  gyorsabb SQL végrehajtás
-  centralizált kezelés
-  hatékonyabb karbantartás
-  védhető adatbázis objektum
-  klienst tehermentesíti
-  egymást hívhatják
-  ütemezhetőek
-  lehet benne függvény is

Eljárás : 

Nincs visszatérési értéke, de lehet kimeneti paramétere
CREATE PROCEDURE nev(@e1 [=ert1] tip1 …) 
WITH ENCRYPTION | EXECUTE AS user 
AS 
BEGIN TRY 
… 
END; 
Futtatása: EXEC nev p1, p2; 

Függvény :

A visszatérési értéke lehet érték és táblázat is
CREATE FUNCTION fnev(@p1 tip1…) RETURNS tipus 
WITH ENCRYPTION | EXECUTE AS user 
AS BEGIN 
… 
 RETURN ertek; 
END; 
Futtatása: SELECT * FROM nev(p1, p2) 

CREATE FUNCTION (param) RETURNS @ret TABLE(m1 tip1, …) AS 
BEGIN 
 … 
 INSERT INTO @ret VALUES(…); 
 RETURN; 
END; 

Kurzor kezelés  

Deklaráció: DECLARE knev CURSOR FOR SELECT…
Nyitás: OPEn knev
Lekérdezés: FETCH poz knev INTO @v1…
Ciklus: WHILE @@FETXH_STATUS = 0
Lezárás: CLOSE knev
Felszabadítás: DEALLOCATE knev
Információ lekérdezés: @@CURSOR_ROWS

BEGIN 
 DECLARE @v char(29) 
 DECLARE c1 CURSOR FOR SELECT nev FROM tablam; 
 OPEN c1; 
 FETCH NEXT FROM c1 INTO @v; 
 WHILE @@FETCH_STATUS = 0 BEGIN 
  SELECT @v; 
  FETCH NEXT FROM c1 INTO @v; 
 END; 
 DEALLOCATE c1; 
END; 

Triggerek 

• Automatikusan meghívódik egy tábla vagy adatbázis objektum eseményre. 
• A végrehajtás sebessége gyorsabb, előre le van fordítva. 
• Garantálja a kapcsolódó tevékenységet, nagyobb biztonság, megbízhatóság. 
• Figyelni kell a triggerek közötti függőségre, melyik trigger mit hív. 
• Tábla insertnél meglehet hívni saját magát a triggert. 

CREATE TRIGGER triggernev ON tablam 
[WITH ENCRYPTION | EXECUTE AS user] 
FOR UPDATE | INSTEAD OF | INSERT | UPDATE | DELETE 
AS 
BEGIN 
 … 
END; 

XML fájl előállítása aadatbázis adatokból: 

-  SELECT … FROM … FOR XML RAW (’tagnev’): nincs gyökér, egyszintű, minden
rekord egy tag; minden mezo egy attribútum
-  SELECT … FROM … FOR XML RAW, ELEMENTS: minden mező egy gyerekelem
-  SELECT … FROM … FOR XML AUTO: minden forrástáblához külön gyerekelem;
minden mező attribútum
-  SELECT … FROM … FOR XML AUTO, ELEMENTS: minden mező gyerekelem
-  SELECT … FOR XML PATH(’tagnev’), ROOT (’nev’): részletes kontroll, egyedi ki-
alakítás; minden mezőre külön XPath beállítás; gyökérelem is kijelölhető
-  SELECT … FOR XML EXPLICIT(’tagnev’), ROOT(’nev’): legnagyobb kontroll,
egyedi kialakítás; mezőkre külön kiterjesztett XPath beállítás

 Kivétel, hibakezelés 

BEGIN TRY 
 … 
END TRY 


BEGIN CATCH 
 ERROR_NUMBER() 
 ERROR_MESSAGE() 
 ERROR_LINE() 
 ERROR_SEVERITY() 
END CATCH; 

RAISEERROR(szoveg, kod1, kod2) 

Adatbázis-szintű objektumkatalógus (object catalog): 

•  sys.check_constraints: az adatbázisban érvényes érték-jellegű kényszerek. 
•  sys.columns: azadatbázisban található oszlopok (a rendszertáblák oszlopai nincsenek listázva).
•  sys.events: azon események, amelyek értesítést vagy triggert indítanak el. 
•  sys.foreign_keys: az adatbázisban levő oszlopok, amelyeken külső kulcs kényszer van definiálva. 
•  sys.foreign_key_columns: az adatbázisban levő azon oszlopok, amelyek külső kulcsok. 
•  sys.identity_columns: az adatbázisban lévő egyediség kényszerrel ellátott oszlopok. 
•  sys.indexes: az adatbázisban levő indexstruktúrák. 
•  sys.index_columns: az adatbázisban levő indexelt oszlopok listája. 
•  sys.key_constraints: az adatbázisban érvényes elsődleges-kulcs kényszerek. 
•  sys.objects: az adatbázisban levő összes objektum (táblák, indexek, kényszerek stb.) 
•  sys.partitions: az adatbázishoz tartozó tábla- illetve indexpartíciók. 
•  sys.procedures: az adatbázishoz tartozó tárolt eljárások. 
•  sys.schemas: az adatbázisban létrehozott sémák. 
•  sys.synonyms: az adatbázisban tárolt szinonimák. 
•  sys.tables: az adatbázisban tárolt táblák (a rendszertáblák nincsenek listázva) 
•  sys.triggers: a tábla- illetve adatbázis-szintű triggerek. 
•  sys.types: a szerveren érvényes típusok 
•  sys.views: az adatbázisban tárolt nézetek  

Adatbázisok és adatfájlok katalógus (database and files catalog): 

•  sys.databases: a szerveren található adatbázisok. 
•  sys.database_files: az adatbázishoz tartozó fájlok 
•  sys.filegroups: az adatbázishoz tartozó fájlcsoportok 
•  sys.master_files: a szerveren található összes adatbázishoz tartozó fájlok  

Biztonság és engedélyezés: 

•  sys.database_permissions: az összes adatbázis-szintű engedély.
•  sys.database_principals: az adatbázis-szintű hozzáférők (user, role).
•  sys.server_permissions: az összes szerver-szintű engedély.
•  sys.server_principals: a szerver-szintű hozzáférők (login, role).
•  sys.sql_logins: a szerveren érvényes loginok

Dinamikus menedzsment 

•  sys.dm_db_index_usage_stats: szerver-szintű indexhasználat. 
•  sys.dm_exec_cached_plans: a szerver által cache-elt végrehajtási tervek. 
•  sys.dm_exec_query_stats: a cache-elt végrehajtási tervek teljesítmény-statisztikái. 
•  sys.dm_exec_connections: a szerver által létesített kapcsolatok. 
•  sys.dm_exec_requests: a szerveren végrehajtás alatt levő kérések. 
•  sys.dm_exec_sessions: a szerveren futó sessionök. 
•  sys.dm_tran_active_transactions: a szerveren futó tranzakciók. 
•  sys.dm_tran_current_transaction: információk a nézetet lekérdező tranzakcióról (1 soros). 
•  sys.dm_tran_database_transactions: az aktuális adatbázisban futó tranzakciók. 
•  sys.dm_tran_locks: a szerver zárkezelőjéhez beérkezett zárkérések és a már lefoglalt zárak  

•  sys.messages: a szerver lehetséges hibaüzenetei. 
•  sys.server_events: azon szerver-szintű események, amelyek értesítést vagy triggert aktiválnak. 
•  sys.server_event_sessions: a külön sessionben futó eseménykezelő eljárások. 
•  sys.servers: a helyi szerver illetve azon távoli szerverek tulajdonságai, amivel a helyi szerver összeköttetésben van