SQL Transactions Flashcards
SQL TRANSACTIONS
Transactions group a ** set of tasks into a single execution unit**.
Each transaction begins with a specific job and ends when all the tasks in the group successfully completed. If any of the tasks fail, the transaction fails.
Therefore, a transaction has only two results: success or failure.
Properties of Transaction
Atomicity: The outcome of a transaction can either be completely successful or completely unsuccessful. The whole transaction must be rolled back if one part of it fails.
Transakcja jest traktowana jako niepodzielna całość, jeśli jedna część zwróci failure, wszystkie zwrócą
Consistency: Transactions maintain integrity restrictions by moving the database from one valid state to another.
Transakcje muszą przenosić bazę danych z jednego spójnego stanu do innego
Isolation: Concurrent transactions are isolated from one another, assuring the accuracy of the data.
Operacje jednej transakcji są izolowane od innych transakcji. Oznacza to, że zmiany dokonane w ramach transakcji są niewidoczne dla innych transakcji, dopóki transakcja nie zostanie zatwierdzona (commit).
Durability: Once a transaction is committed, its modifications remain in effect even in the event of a system failure.
Po zakończeniu transakcji (commit), jej efekty są trwale zapisane w bazie danych i nie zostaną utracone
How to Implement Transactions Control Command using SQL?
BEGIN TRANSACTION Command
It indicates the start point of an explicit or local transaction.
BEGIN TRANSACTION transaction_name ;
**SET TRANSACTION **Command
The values for the properties of the current transaction, such as the transaction isolation level and access mode, are set using the SET TRANSACTION Statement in MySQL.
SET TRANSACTION [ READ WRITE | READ ONLY ];
COMMIT Command
If everything is in order with all statements within a single transaction, all changes are recorded together in the database is called committed. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.
ROLLBACK Command
If any error occurs with any of the SQL grouped statements, all changes need to be aborted. The process of reversing changes is called rollback. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
SAVEPOINT Command
SAVEPOINT creates points within the groups of transactions in which to ROLLBACK.
A SAVEPOINT is a point in a transaction in which you can roll the transaction back to a certain point without rolling back the entire transaction.
RELEASE SAVEPOINT Command
This command is used to remove a SAVEPOINT that you have created.
Syntax:
RELEASE SAVEPOINT SAVEPOINT_NAME
Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.
It is used to initiate a database transaction and used to specify characteristics of the transaction that follows.
PL/SQL
DECLARE
– blok deklaracji (opcjonalnie)
BEGIN
– blok programu
EXCEPTION
– obsługa wyjątków (opcjonalnie)
END
/* Przykładowy komentarz
w wielu liniach… */
declare
liczba1 number(2);
liczba2 number(4,2) := 17.25;
text1 varchar2(12) := ‘Waldek’;
data1 date := SYSDATE; – obecna data i czas.
begin
SELECT numer_ulicy
into liczba1
from address
where name = text1;
end;
SQL SERVER – Input and Output Parameter For Dynamic SQL
An Input Parameter can influence the subset of rows it returns from a select statement within it.
A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter.
DECLARE @inPar INT = 345;
DECLARE @outPar INT;
A parameter whose value is sent out of the stored procedure/function module and back to the calling PL/SQL block is known as an output parameter.
A variable, not a constant, must be used as an output parameter. It can only be found on the left-hand side of a module assignment. Outside of the module’s body, you can’t provide a default value for an OUT parameter.
Input Parameter
The caller can send a data value to the stored procedure or function via input parameters.
The stored method can return a data value or a cursor variable to the caller using output parameters. The caller receives an integer return code from every stored procedure.
DECLARE @inPar INT = 345;
DECLARE @test NVARCHAR(MAX) = N’SELECT TOP 1 [OId]
FROM TestOrders WHERE PersonId = @id_in’;
EXEC sys.sp_executesql @test, N’@id_in INT’, @inPar;
GO
Co oznacza litera N
Litera N przed ciągiem tekstowym w SQL oznacza, że ciąg ten jest traktowany jako ciąg Unicode (czyli w kodowaniu NVARCHAR). W SQL Server przedrostek N pozwala na przechowywanie tekstu, który może zawierać znaki specjalne lub międzynarodowe, takie jak znaki z innych alfabetów (np. polskie znaki diakrytyczne: ą, ę, ś, ł).
N’Some text with Unicode characters: ł, ś, ć’
Bez N – Ciąg znaków będzie kodowany jako VARCHAR. W przypadku tekstu bez znaków specjalnych lub znaków spoza ASCII nie będzie widocznej różnicy, ale tekst Unicode (np. z polskimi znakami diakrytycznymi) może zostać źle zapisany lub ucięty.
Z N – Ciąg znaków będzie kodowany jako NVARCHAR, co pozwala na przechowywanie znaków Unicode i jest rekomendowane przy pracy z międzynarodowym tekstem lub tekstem o różnych alfabetach.
Varchar a Nvarchar
Kodowanie znaków:
VARCHAR przechowuje tekst w formacie ASCII (lub zgodnym z określonym zestawem znaków) i nie obsługuje Unicode. Nadaje się do przechowywania tekstu w językach, które nie wymagają znaków spoza ASCII, np. angielskiego.
NVARCHAR przechowuje tekst w formacie Unicode (UTF-16), dzięki czemu może przechowywać znaki z różnych języków, które wymagają Unicode (np. polskie znaki diakrytyczne, azjatyckie skrypty itp.).
Wymagania pamięci:
VARCHAR zajmuje 1 bajt na każdy znak.
NVARCHAR zajmuje 2 bajty na każdy znak, ponieważ wykorzystuje Unicode. Oznacza to, że ten typ danych wymaga więcej miejsca, ale umożliwia przechowywanie znaków z różnych języków.
VARCHAR jest dobry do prostego tekstu bez znaków diakrytycznych lub specjalnych, co może być użyteczne w przypadku tekstów, które mają być przechowywane w mniejszych kolumnach lub nie wymagają Unicode.
NVARCHAR powinno się stosować zawsze, gdy baza danych obsługuje tekst w różnych językach, co jest istotne w aplikacjach międzynarodowych lub przy przechowywaniu nazw, adresów, opisów itp.
Rozmiar przechowywania:
Dla VARCHAR, maksymalna długość to 8000 znaków (dla VARCHAR(MAX) może być większa).
Dla NVARCHAR, maksymalna długość to 4000 znaków (dla NVARCHAR(MAX) również może być większa), ale w pamięci zajmuje więcej miejsca niż VARCHAR przy tej samej liczbie znaków.
Having vs Where Clause in SQL
The difference between the having and where clause in SQL is that the where clause cannot be used with aggregates, but the having clause can.
The where clause works on row’s data, not on aggregated data. Let us consider below table ‘Marks’.
SELECT Student, Score FROM Marks WHERE Score >=40
SELECT Student, SUM(score) AS total FROM Marks GROUP BY Student
HAVING total > 70
Transactional Control Commands
Transactional control commands are only used with the DML Commands such as - INSERT, UPDATE and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. Following commands are used to control transactions.
COMMIT − to save the changes.
ROLLBACK − to roll back the changes.
SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
SET TRANSACTION − Places a name on a transaction
What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
EXEC procedure_name;
Stored Procedure With One Parameter
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
EXEC SelectAllCustomers @City = ‘London’;
Stored Procedure With Multiple Parameters
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
EXEC SelectAllCustomers @City = ‘London’, @PostalCode = ‘WA1 1DP’;
How to create a variable?
DECLARE
@myvar
as
int
= 2 // optional
SET @myvar = @myvar + 1
Bits number- tinyint, int, bigint
tinyint- 1 (values from 0 to 255)
smallint-2
int- 4
bigInt-8
mathematical overflow error
numeric(9,7) what does it mean
it means the precision, up to 7 digits and then it rounds and the scale is 9
Precyzji 9: liczba może mieć maksymalnie 7 cyfr (łącznie przed i po przecinku).
Skali 7: dwie cyfry są przeznaczone na część po przecinku.
money and smallmoney
represent monetary unit
float and real
are approximately data types
real is float(24) since it takes the same amout of bytes
char/varchar and nchar/nvarchar
char are ASCII range- 1 byte
vchar are UNICODE range - 2 byte
char
uses single quotation marks
Len and Datalenght
len returns the length, Datalenght returns the Datalength
varchar storage size
actual length of data + 2 bytes
how to store a nvarchar
use N’ ‘
what is <>
is it contraty to =, something different from =
What mean non-deterministic
If i run the query 100, i can get diffrent order
how to create and call a view
create view VIEWNAME as …
select * from VIEWNAME
ALTER view VIEWNAME as ..
DROP view VIEWNAME
What is an index in SQL?
**An index is used to speed up searching in the database. **
Indexes are used to find rows with specific column values quickly
Indexes are used to find rows with specific column values quickly.
Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.
The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data.
This is much faster than reading every row sequentially.
An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table to find exact matches. If you don’t have indexes on any column in the WHERE clause, the SQL server has to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.
What is an Execution Plan?
an execution plan in SQL Server Management Studio is a g**raphical representation of the various steps that are involved in fetching results from the database tables. **
Once a query is executed, the query processing engine quickly generates multiple execution plans and selects the one which returns the results with the best performance. There are two types of execution plans to be specific –
Estimated Execution Plan – As the name suggests, this type of execution plan is just a guess by the query processor about how the specific steps that are to be involved while returning the results. It is often generated before the query has been executed
Actual Execution Plan – The Actual Execution Plan is generated after the query has been executed. It shows the actual operations and steps involved while executing the query. This may or may not differ from the Estimated Execution Plan
Jak stworzyć indeks?
to taki spis treści
CREATE INDEX IDX_Nazwisko
ON Pracownicy (Nazwisko);
Indeksy są przydatne, gdy:
- Często szukasz danych w konkretnej kolumnie (WHERE, JOIN).
- Kolumna ma dużo unikalnych wartości (np. Nazwisko, NumerTelefonu).
Clustered Index (indeks klastrowany)
Jak działa:
Clustered Index sortuje i przechowuje dane tabeli fizycznie w kolejności indeksu.
Oznacza to, że wiersze w tabeli są fizycznie rozmieszczone zgodnie z kluczem indeksu klastrowanego.
Każda tabela może mieć tylko jeden indeks klastrowany, ponieważ dane mogą być posortowane tylko w jeden sposób.
Przykład: Załóżmy, że mamy tabelę Pracownicy i tworzymy indeks klastrowany na kolumnie ID:
CREATE CLUSTERED INDEX IDX_ID
ON Pracownicy (ID);
Efekt: Wiersze w tabeli zostaną fizycznie posortowane według kolumny ID. Wyszukiwanie według ID będzie bardzo szybkie, bo SQL wie, gdzie szukać (jak w posortowanej książce telefonicznej).
Non-Clustered Index (indeks nieklastrowany)
Jak działa:
Non-Clustered Index tworzy osobną strukturę danych (taką jak dodatkowa “tablica”) zawierającą klucz indeksu i wskaźnik do rzeczywistego wiersza w tabeli.
Dane w tabeli nie są sortowane ani przechowywane w kolejności indeksu.
Możesz mieć wiele indeksów nieklastrowanych na tej samej tabeli.
Kiedy używać jakiego indexu?
Clustered Index:
Dla kolumn, które są unikalne i często używane do sortowania (np. klucz główny).
Zapewnia szybki dostęp do danych w określonej kolejności.
Non-Clustered Index:
Dla kolumn, które często są używane w filtrach (WHERE, JOIN) lub sortowaniach, ale nie są kluczem głównym.
Pozwala na przyspieszenie zapytań bez zmieniania fizycznej kolejności danych.
What are types of DML triggers?
-Instead of
-After/for
Trigger DML w SQL
Trigger DML (ang. Data Manipulation Language Trigger) to specjalny rodzaj wyzwalacza w SQL, który automatycznie uruchamia się w reakcji na operacje manipulacji danymi w tabeli lub widoku. Operacje te obejmują:
INSERT – dodawanie nowych wierszy,
UPDATE – aktualizowanie istniejących wierszy,
DELETE – usuwanie wierszy.
Jak działa trigger DML?
Trigger przypisuje się do konkretnej tabeli.
Działa automatycznie – nie musisz wywoływać go ręcznie.
Może działać:
-> BEFORE operacji – przed wykonaniem operacji.
-> AFTER operacji – po wykonaniu operacji.
W triggerach masz dostęp do specjalnych tabel logicznych:
INSERTED – zawiera dane, które są wstawiane lub zmieniane.
DELETED – zawiera dane, które zostały usunięte lub zastąpione.
Składnia tworzenia triggera DML
CREATE TRIGGER trigger_name
ON table_name
AFTER [INSERT, UPDATE, DELETE]
AS
BEGIN
– Akcje wykonywane przez trigger
END;
Przykłady triggerów DML
Zapisuje historię wstawianych rekordów do tabeli logów.
CREATE TRIGGER trg_AfterInsert
ON Pracownicy
AFTER INSERT
AS
BEGIN
INSERT INTO LogiPracownicy (Akcja, DataZmiany, Szczegoly)
SELECT ‘INSERT’, GETDATE(), CONCAT(‘Dodano rekord: ‘, Nazwisko)
FROM INSERTED;
END;
Po każdej operacji INSERT na tabeli Pracownicy, trigger dodaje szczegóły o nowym rekordzie do tabeli LogiPracownicy.
INSTEAD OF Trigger w SQL
INSTEAD OF Trigger to rodzaj wyzwalacza w SQL, który zastępuje standardowe działanie operacji DML (INSERT, UPDATE, DELETE) na tabeli lub widoku.
Oznacza to, że gdy operacja jest wywoływana, trigger przejmuje kontrolę i wykonuje alternatywną logikę zamiast tej domyślnej.
Zastępuje standardową operację DML:
Jeśli np. wykonasz DELETE, to zamiast usunięcia wiersza z tabeli, SQL uruchomi kod w triggerze.
Umożliwia kontrolę nad operacjami:
Możesz zmodyfikować, zablokować lub przekierować logikę, np. zapisać dane do innej tabeli zamiast je usuwać.
Działa zarówno na tabelach, jak i na widokach:
Jest szczególnie przydatny w przypadku widoków, które normalnie nie obsługują operacji DML.
FOR / AFTER Trigger
Kiedy się uruchamia?
Uruchamia się po zakończeniu operacji DML na tabeli (ale zanim operacja zostanie zatwierdzona w transakcji).
Działa tylko na tabelach.
Nie można go używać na widokach.
Nie zastępuje działania operacji DML.
Operacja (np. INSERT, UPDATE, DELETE) zostanie wykonana, a następnie trigger wykona dodatkową logikę.
INSTEAD OF Trigger
Kiedy się uruchamia?
Uruchamia się zamiast operacji DML, którą wyzwoliłeś.
Działa zarówno na tabelach, jak i na widokach.
Zastępuje domyślną operację DML.
Operacja (INSERT, UPDATE, DELETE) nie zostanie wykonana domyślnie, a zamiast tego zostanie wykonany kod w triggerze.
