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.