The rule of transaction in SQL Server tells that either all the statements in the transaction should be executed successfully or none of those statements to be executed.
What is the thumb rule of Transaction in SQL Server?
The SQL Server
ANSI format JOINS
NON-ANSI format JOINS
ANSI -
Inner join
Outer join
Cross join
Further, the outer join is divided into
Left outer join
Right outer join
Full outer join
NON
Auto Commit Transaction Mode (default) -
EQUI join
NON-EQUI join
SELF-join
Natural Join
Note :- Default JOIN in
INDEX:-
Note - In a table, we can use a maximum
When an index is created, a balanced tree which has root, non-leaf
Clustered index is created by default only when primary key is present for the table.
In Non clustered,the leaf node actually holds the data
Implicit Transaction Mode - SQL server begins, developer commits or rollback
Explicit Transaction Mode - Most used. Developer begins and
What is a Covering query?
If all the columns that we have requested in the select clause of the query are present in the index, then there is no need to look up the table again. The requested column data can simply be returned from the index. This is called Covering Query.
Aspect | RANK() Function | DENSE_RANK() Function |
-------------------------|--------------------------|--------------------------|
Rank Assignment | Assigns unique ranks | Assigns unique ranks |
Handling Tied Rows | Skips ranks for tied rows | No gaps in rank sequence |
Tied Rows | Receive different ranks if tied | Receive the same rank if tied |
Rank Values | Non-consecutive (may have gaps) | Consecutive (no gaps) |
Example SQL | RANK() OVER (ORDER BY Column) | DENSE_RANK() OVER (ORDER BY Column) |
Use Case | When gaps in ranks are acceptable | When consecutive ranks are preferred for tied rows |
Window Functions:-
Aggregate Functions :- Avg, min, max, count, sum
Ranking funtions :- rank, dense_rank, row_number, ntile
value functions :- lag, lead, first_value, last_value, nth_value
SELECT
Date,
Price,
Price - LAG(Price, 1) OVER (ORDER BY Date) AS DailyChange,
LAST_VALUE(Price) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EndOfWeekPrice
FROM
StockPrices;
Stored procedure:-
When we execute a sql query it goes through 3 process:Syntax Checked,Plan Selected,Query Execution.
When we create a stored procedure, the syntaxes are checked while creating the procedure or we can say at the design. When we execute the procedure for the first time, the best execution plan is selected and is cached in memory. And after that whenever we call the stored procedure, the query execution plan is taken from the cache rather than creating again and again and executed
NOTE:-So, in SQL Server by using the return values, we can return only one integer. It is not possible, to return more than one value using return values, whereas in output parameters, we can return any data type and a stored procedure can have more than one output parameter.
Temporary/private Stored procedure
CREATE
AS
BEGIN
INSERT INTO Customer VALUES(3, 'CODE_3', 'Pam')
INSERT INTO Customer VALUES(4, 'CODE_4', 'Sara')
IF(@@ERROR > 0)
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
CREATE
BEGIN TRANSACTION
SAVE TRANSACTION SavePoint1
INSERT INTO Customer VALUES (1, 'Code_1', 'Ramesh')
INSERT INTO Customer VALUES (2, 'Code_2', 'Suresh')
SAVE TRANSACTION SavePoint2
INSERT INTO Customer VALUES (3, 'Code_3', 'Priyanka')
INSERT INTO Customer VALUES (4, 'Code_4', 'Preety')
SAVE TRANSACTION SavePoint3
INSERT INTO Customer VALUES (5, 'Code_5', 'John')
INSERT INTO Customer VALUES (6, 'Code_6', 'David')
Rollback Transaction SavePoint2;
ACID:-
A- Each transaction is considered as a single unit. Either a transaction should pass or if it fails then it should be rollbacked
C-The Consistency Property of a Transaction in SQL Server ensures that the database data is in a consistent state before the transaction started and also left the data in a consistent state after the transaction is completed
I-The Data modifications made by one transaction must be isolated from the data modifications made by all other transactions.
D-The Durability Property of a Transaction in SQL Server ensures that once the transaction is successfully completed, then the changes it made to the database will be permanent
Exception handling:-
Raiseerror and @@error
ALTER PROCEDURE
@Number1 INT,
@Number2 INT
AS
BEGIN
END
FUNCTIONS :-
User defined Scalar function:- Meaning it returns only one value
CREATE FUNCTION AddTwoNumber(@firstNo INT, @second INT)
RETURNS
IF(@Number2 = 0)
BEGIN
END
Select dbo.AddTwoNumber
or
DECLARE @result INT
BEGIN
SET
END
IF(@@ERROR <> 0)
BEGIN
User defined Inline Table Valued function
It returns a whole table and its return type is TABLE
CREATE FUNCTION FN_GetStudentDetailsByID
(
@ID INT
)
RETURNS TABLE
AS
RETURN (SELECT * FROM Student WHERE ID = @ID)
We can call it using just like a table But with ()
SELECT * FROM FN_GetStudentDetailsByBranch(‘CSE’)
User defined Multi-statement Table Valued Function
-- Multi-statement Table Valued function: In this we create our own return table definition
CREATE FUNCTION MSTVF_GetEmployees()
RETURNS @Table Table (ID int, Name nvarchar(20), DOB Date)
END
ELSE
BEGIN
PRINT 'RESULT IS :'+CAST(@Result AS VARCHAR)
END
END
Try catch throw:-
ALTER PROCEDURE spDivideBy2(@No1 INT, @No2 INT)
AS
BEGIN
DECLARE @Result INT
SET @Result = 0
BEGIN TRY
IF @No2 = 1
THROW 50001,'DIVISOR CANNOT BE ONE', 1
SET @Result = @No1 / @No2
PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR)
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH
END
DIff raiserror vs try catch throw
if we use any of the two statements in a program for raising a custom error without try and catch blocks, the RAISERROR statement after raising the error will still continue the execution of the program whereas the throw statement will terminate the program abnormally on that line. But if they are used under try block both will behave in the same way that it will jump directly to catch block from where the error got raised.
Views::::----
The views in SQL Server are nothing more than a compiled SQL query. We can also consider the Views as virtual tables. As a virtual table, the Views do not store any data physically by default.
There are two types of views in SQL Server, they are
Simple Views - Views which are dependent on a single table and does not contain any complex operations such as group by having clause etc
Complex Views- Views which are dependent on multiple tables.
We can perform DML operations on Views and similar operation will be done on original table as well. If its a complex view then operation may not perform successfully on all the tables
Can we drop a table that has dependent views on it?
Yes, you can drop a table even if any dependent views are associated with it, but the views that are associated with it will not be dropped. They will still execute in the database only with the status as inactive object and all those views become active and start functioning provided the table is recreated.
Can we create a view based on other views?
Yes, It is possible in SQL Server to create a view based on other views.
Can we update the views in SQL Server?
Yes, in SQL server views can be updated. However, updating a view that is based on multiple tables, may not update the base tables correctly. To correctly update a view that is based on multiple tables we can make use of INSTEAD OF triggers in SQL Server.
Advantages:-
Hiding the complexity
Implementing Row and Column Level Security.
Presenting the aggregated data by hiding the detailed data.
Disadvantage:-
We cannot pass parameters to SQL Server views
We cannot use an Order By clause with views without specifying FOR XML, OFFSET, or TOP
The Views cannot be created based on Temporary Tables in SQL Server
We cannot associate Rules and Defaults with SQL Server views
Index view
Indexed View is a virtual table whose result set (output of a select statement) is persistent on the hard disk. In general, when we create a view, the view does not store any data by default. So, when we query a view, it actually queries the underlying base table and gets the data. But we can change this default behavior in SQL Server i.e. the SQL Server Views can store the data physically. In order to do this, first, you need to create an index on the view.
Note: Indexed View is not always suggested for all situations. For example, if your table is highly transactional (i.e. the base table having lots of Insert, Update and Delete operations), then the indexed view is not suggested. This is because the synchronization between the base table and the indexed view is a time-consuming task.
CREATE VIEW vwTotalSalesPriceByProduct
WITH SCHEMABINDING
AS
SELECT Name,
COUNT_BIG(*) AS TotalTransactions,
SUM(ISNULL((QuantitySold * UnitPrice), 0)) AS TotalSalesPrice
FROM dbo.ProductSales prdSales
INNER JOIN dbo.Product prd
ON prd.ProductId = prdSales.ProductId
GROUP BY Name
Rules for creating an Index view in SQL Server:
The view should be created with the SCHEMABINDING option
If an Aggregate function is used in the SELECT LIST which references an expression, and if there is a possibility for that expression to become NULL, then, a replacement value should be specified. In this example, we are using ISNULL() function, to replace NULL values with ZERO with the expression SUM(ISNULL((QuantitySold * UnitPrice), 0)) AS TotalSalesPrice
If the GROUP BY clause is specified, then the view select list must contain a COUNT_BIG(*) expression
The base tables in the view should be referenced with 2 part name. In this example, Product and ProductSales are referenced using dbo.Product and dbo.ProductSales respectively.
TRIGGERS:-
Triggers are nothing but they are logic’s like stored procedures that can be executed automatically before the Insert, Update or Delete happens in a table or after the Insert, Update, or Delete happens in a table
There are two types of triggers. They are as follows:
1)Instead of Triggers: The Instead Of triggers are going to be executed instead of the corresponding DML operations. That means instead of the DML operations such as Insert, Update, and Delete, the Instead Of triggers are going to be executed.
2)After Triggers: The After Triggers fires in SQL Server execute after the triggering action. That means once the DML statement (such as Insert, Update, and Delete) completes its execution, this trigger is going to be fired.
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | [INSERT,] UPDATE | [INSERT,] DELETE}
ON table_name
[FOR EACH ROW] -- This clause is optional and indicates that the trigger is row-level.
[WHEN (condition)] -- This clause is optional and specifies a condition that must be met for the trigger to execute.
BEGIN
-- Trigger logic here
END;
NOTE:- You cannot create After Trigger on views.
CREATE TRIGGER TRIGGEREXAMPLE
on Employees
FOR INSERT, UPDATE, DELETE
AS
BEGIN
PRINT 'DML operations are not allowed'
ROLLBACK TRANSACTION
END
INSERT INTO
SELECT ID, Name, Cast(DOB AS Date)
FROM Employee
Return
End
Calling:-
Values(21,'Rahul','IT',50000)
OUTPUT-- DML operations are not allowed
Msg 3609, Level 16, State 1, Line 44
The transaction ended in the trigger. The batch has been aborted.
Magic tables:-
Magic tables are a concept in SQL, specifically in the context of triggers. They are not an official term but are often informally used to refer to special pseudo-tables that are available within the scope of a trigger in some database management systems (DBMS)
==> INSERTED and DELETED
-- Create Update Trigger
CREATE TRIGGER trUpdateEmployee
ON Employee
FOR UPDATE
AS
BEGIN
SELECT * FROM
Functions VS Procedures
https://dotnettutorials.net/lesson/multi-statement-table-valued-function-in-sql-server/
Deterministic function
SELECT * FROM INSERTED
END
What is Logon Trigger in SQL Server?
The
The Logon triggers are
Popup you see when u are
Non deterministic function
The Non-deterministic functions in SQL Server may return different results each time they are called with a specific set of values even if