Facebook
From BM1, 1 Year ago, written in Plain Text.
This paste is a reply to Re: SQL Notes from BM - go back
Embed
Viewing differences between Re: SQL Notes and Re: Re: SQL Notes
Transaction Notes lost - Make it again or read from dot net tutorials

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 Joins Transactions are classified into two types such as

ANSI format JOINS
NON-ANSI format JOINS

ANSI - 
Inner join
Outer join
Cross join
 Further, the outer join is divided into 
three types types, they are as follows
    Left outer join
    Right outer join
    Full outer join
    
NON 
follows

Auto Commit Transaction Mode (default) 
ANSI
EQUI join
NON-EQUI join
SELF-join
Natural Join

Note :- Default JOIN in 
SQL is Inner join

INDEX:-

Note - In a table, we can use a maximum 
serve takes care of 1000 indexes (1 Clustered Index plus 999 Non-Clustered Index).

When an index is created, a balanced tree which has root, non-leaf 
beginning and leaf nodes are created. Hence ending the search becomes faster.

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 
transaction. If Trans success commit or if fails rollbacks
Implicit Transaction Mode - SQL server begins, developer commits or rollback
Explicit Transaction Mode - Most used. Developer begins 
and in the case of a non-clustered index, the leaf node actually points to the leaf node of a clustered index or points to the heap table if the table does not have any clustered index.

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
ends


CREATE PROCEDURE #GlobalProcedue
PROC SPAddCustommer
AS
BEGIN
  PRINT 'This is Temporary Temporary Procedure'\n   BEGIN TRANSACTION
      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

GLobal SP:-
CREATE 
savpoints vs rollbacks

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 ##GlobalProcedue
spDivideTwoNumber
@Number1 INT, 
@Number2 INT
AS
BEGIN
  PRINT 'This is Global Temporary Procedure'
END

FUNCTIONS :-

User defined Scalar function:- Meaning it returns only one value
  CREATE FUNCTION AddTwoNumber(@firstNo INT, @second INT)
  RETURNS 
DECLARE @Result INT
  AS
SET @Result = 0
  IF(@Number2 = 0)
  BEGIN
  DECLARE @result INT\n      SET @result = @firstNo + @second\n      RETURN @result\n    RAISERROR('Second Number Cannot be zero',16,1)
  END
  We can call using
  Select dbo.AddTwoNumber 
     or
  DECLARE @result INT
  
ELSE
  BEGIN
    
SET @result @Result (Select dbo.AddTwoNumber(10,15))
  
@Number1 / @Number2
  END

  IF(@@ERROR <> 0)
  BEGIN
    
PRINT @result
  
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)
'Error Occurred'
  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 @Table
    SELECT ID, Name, Cast(DOB AS Date)
    FROM Employee
  Return
End

Calling:-
employees
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 MSTVF_GetEmployees()
       
Functions VS Procedures

https://dotnettutorials.net/lesson/multi-statement-table-valued-function-in-sql-server/

Deterministic function
DELETED
  SELECT * FROM INSERTED
END

What is Logon Trigger in SQL Server?
The Deterministic functions Logon Triggers in SQL Server always return are the same result every time they special kind of stored procedure or we can also say a special type of operation which fire or executed automatically in response to a LOGON event and moreover, we can define more than one Logon trigger on the server.

The Logon triggers 
are called with a specific set of input values and give fired only after the same state of successful authentication but before the database. For examples: SQUARE(), Power(), Sum(), AVG(), and Count(). All aggregate functions user session is actually established. If the authentication is failed then the logon triggers will not be fired.


Popup you see when u 
are deterministic functions. Let us see some examples.

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 
not able to connect to the database state that they access remains database, it comes from the same. For examples: GetDate() and Current_Timestamp
logon triggers.