Facebook
From BM1, 10 Months ago, written in Plain Text.
This paste is a reply to Re: SQL Notes from BM - view diff
Embed
Download Paste or View Raw
Hits: 445
  1. Transaction Notes lost - Make it again or read from dot net tutorials
  2.  
  3. 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.
  4.  
  5.  
  6. What is the thumb rule of Transaction in SQL Server?
  7.  
  8. The SQL Server Transactions are classified into three types, they are as follows
  9.  
  10. Auto Commit Transaction Mode (default) - SQL serve takes care of beginning and ending the transaction. If Trans success commit or if fails rollbacks
  11. Implicit Transaction Mode - SQL server begins, developer commits or rollback
  12. Explicit Transaction Mode - Most used. Developer begins and ends
  13.  
  14.  
  15. CREATE PROC SPAddCustommer
  16. AS
  17. BEGIN
  18.    BEGIN TRANSACTION
  19.       INSERT INTO Customer VALUES(3, 'CODE_3', 'Pam')
  20.       INSERT INTO Customer VALUES(4, 'CODE_4', 'Sara')
  21.       IF(@@ERROR > 0)
  22.       BEGIN
  23.          ROLLBACK TRANSACTION
  24.       END
  25.       ELSE
  26.       BEGIN
  27.          COMMIT TRANSACTION
  28.       END  
  29. END
  30.  
  31. savpoints vs rollbacks
  32.  
  33. BEGIN TRANSACTION
  34.  
  35.  SAVE TRANSACTION SavePoint1
  36.      INSERT INTO Customer VALUES (1, 'Code_1', 'Ramesh')
  37.      INSERT INTO Customer VALUES (2, 'Code_2', 'Suresh')
  38.  
  39.  SAVE TRANSACTION SavePoint2
  40.      INSERT INTO Customer VALUES (3, 'Code_3', 'Priyanka')
  41.      INSERT INTO Customer VALUES (4, 'Code_4', 'Preety')
  42.  
  43.  SAVE TRANSACTION SavePoint3
  44.      INSERT INTO Customer VALUES (5, 'Code_5', 'John')
  45.      INSERT INTO Customer VALUES (6, 'Code_6', 'David')
  46.      
  47. Rollback Transaction SavePoint2;
  48.  
  49.  
  50.  
  51.  
  52. ACID:-
  53.  
  54. A- Each transaction is considered as a single unit. Either a transaction should pass or if it fails then it should be rollbacked
  55. 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
  56. I-The Data modifications made by one transaction must be isolated from the data modifications made by all other transactions.
  57. 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
  58.  
  59.  
  60. Exception handling:-
  61.  
  62. Raiseerror and @@error
  63.  
  64. ALTER PROCEDURE spDivideTwoNumber
  65. @Number1 INT,
  66. @Number2 INT
  67. AS
  68. BEGIN
  69.   DECLARE @Result INT
  70.   SET @Result = 0
  71.   IF(@Number2 = 0)
  72.   BEGIN
  73.     RAISERROR('Second Number Cannot be zero',16,1)
  74.   END
  75.   ELSE
  76.   BEGIN
  77.     SET @Result = @Number1 / @Number2
  78.   END
  79.  
  80.   IF(@@ERROR <> 0)
  81.   BEGIN
  82.     PRINT 'Error Occurred'
  83.   END
  84.   ELSE
  85.   BEGIN
  86.     PRINT 'RESULT IS :'+CAST(@Result AS VARCHAR)
  87.   END
  88. END
  89.  
  90. Try catch throw:-
  91. ALTER PROCEDURE spDivideBy2(@No1 INT, @No2 INT)
  92. AS
  93. BEGIN
  94.   DECLARE @Result INT
  95.   SET @Result = 0
  96.   BEGIN TRY
  97.     IF @No2 = 1
  98.     THROW 50001,'DIVISOR CANNOT BE ONE', 1
  99.     SET @Result = @No1 / @No2
  100.     PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR)
  101.   END TRY
  102.   BEGIN CATCH
  103.     PRINT ERROR_NUMBER()
  104.     PRINT ERROR_MESSAGE()
  105.     PRINT ERROR_SEVERITY()
  106.     PRINT ERROR_STATE()
  107.   END CATCH
  108. END
  109.  
  110. DIff raiserror vs try catch throw
  111. 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.
  112.  
  113. Views::::----
  114.  
  115. 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.
  116.  
  117. There are two types of views in SQL Server, they are
  118.  
  119. Simple Views - Views which are dependent on a single table and does not contain any complex operations such as group by having clause etc
  120. Complex Views- Views which are dependent on multiple tables.
  121.  
  122. 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
  123.  
  124. Can we drop a table that has dependent views on it?
  125. 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.
  126.  
  127. Can we create a view based on other views?
  128. Yes, It is possible in SQL Server to create a view based on other views.
  129.  
  130. Can we update the views in SQL Server?
  131. 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.
  132.  
  133. Advantages:-
  134.  
  135. Hiding the complexity
  136. Implementing Row and Column Level Security.
  137. Presenting the aggregated data by hiding the detailed data.
  138.  
  139. Disadvantage:-
  140. We cannot pass parameters to SQL Server views
  141. We cannot use an Order By clause with views without specifying FOR XML, OFFSET, or TOP
  142. The Views cannot be created based on Temporary Tables in SQL Server
  143. We cannot associate Rules and Defaults with SQL Server views
  144.  
  145. Index view
  146.  
  147. 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.
  148.  
  149. 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.
  150.  
  151. CREATE VIEW vwTotalSalesPriceByProduct
  152. WITH SCHEMABINDING
  153. AS
  154. SELECT Name,
  155.   COUNT_BIG(*) AS TotalTransactions,
  156.   SUM(ISNULL((QuantitySold * UnitPrice), 0)) AS TotalSalesPrice  
  157. FROM dbo.ProductSales prdSales
  158. INNER JOIN dbo.Product prd
  159. ON prd.ProductId = prdSales.ProductId
  160. GROUP BY Name
  161.  
  162. Rules for creating an Index view in SQL Server:
  163. The view should be created with the SCHEMABINDING option
  164. 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
  165. If the GROUP BY clause is specified, then the view select list must contain a COUNT_BIG(*) expression
  166. 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.
  167.  
  168.  
  169. TRIGGERS:-
  170.  
  171. 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
  172.  
  173. There are two types of triggers. They are as follows:
  174.  
  175. 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.
  176. 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.
  177.  
  178. CREATE [OR REPLACE] TRIGGER trigger_name
  179. {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | [INSERT,] UPDATE | [INSERT,] DELETE}
  180. ON table_name
  181. [FOR EACH ROW] -- This clause is optional and indicates that the trigger is row-level.
  182. [WHEN (condition)] -- This clause is optional and specifies a condition that must be met for the trigger to execute.
  183. BEGIN
  184.    -- Trigger logic here
  185. END;
  186.  
  187. NOTE:- You cannot create After Trigger on views.
  188.  
  189.  
  190. CREATE TRIGGER TRIGGEREXAMPLE
  191. on Employees
  192. FOR INSERT, UPDATE, DELETE
  193. AS
  194. BEGIN
  195.   PRINT 'DML operations are not allowed'
  196.   ROLLBACK TRANSACTION
  197. END
  198.  
  199.  
  200. INSERT INTO employees
  201. Values(21,'Rahul','IT',50000)
  202.  
  203. OUTPUT-- DML operations are not allowed
  204. Msg 3609, Level 16, State 1, Line 44
  205. The transaction ended in the trigger. The batch has been aborted.
  206.  
  207. Magic tables:-
  208.  
  209. 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)
  210. ==> INSERTED and DELETED
  211.  
  212. -- Create Update Trigger
  213. CREATE TRIGGER trUpdateEmployee
  214. ON Employee
  215. FOR UPDATE
  216. AS
  217. BEGIN
  218.   SELECT * FROM DELETED
  219.   SELECT * FROM INSERTED
  220. END
  221.  
  222. What is Logon Trigger in SQL Server?
  223. The Logon Triggers in SQL Server are the 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.
  224.  
  225. The Logon triggers are fired only after the successful authentication but before the user session is actually established. If the authentication is failed then the logon triggers will not be fired.
  226.  
  227.  
  228. Popup you see when u are not able to connect to the database, it comes from the logon triggers.
  229.  
  230.