What is the difference between HAVING and GROUP BY?
The HAVING clause is only used with the SELECT statement and is often used with the GROUP BY clause, despite the fact that both specify a search criterion.
When the GROUP BY clause is absent, the HAVING clause just functions as a WHERE clause.
What is a transaction in TSQL?
A collection of one or more SQL statements that communicate with a database is known as a SQL transaction. A whole transaction can be committed to a database as one logical unit or can be rolled back (undone) as one logical unit. Transactions in SQL are crucial for preserving database integrity.
In other words, A group of tasks is combined into a single execution unit using transactions. Each transaction starts with a particular task and is completed once every activity in the group has been properly completed. The transaction fails if any of the tasks are unsuccessful. Therefore, there are only two outcomes for a transaction: success or failure.
What is a stored procedure?
A set of precompiled SQL statements used to carry out a certain task is called a stored procedure.
In other words, A collection of SQL statements that have been produced and saved in the database is known as a stored procedure. A saved procedure will take input parameters, allowing several clients to use the same method over the network with various inputs. A stored method will improve performance while lowering network traffic. All clients will receive the updated stored procedure if we change a stored process.
What is a user-defined function?
SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
What are the benefits of the functions?
- UDFs, support modular programming. Once you create a UDF and store it in a database then you can call it any number of times. You can modify the UDF independently of the source code.
- UDFs reduce the compilation cost of T-SQL code by caching plans and reusing them for repeated execution.
- They can reduce network traffic. If you want to filter data based on some complex constraints then that can be expressed as a UDF. Then you can use this UDF in a WHERE clause to filter data.
What is a trigger?
The trigger enables us to run a batch of SQL statements whenever a table event takes place (INSERT, UPDATE or DELETE command executed against a specific table). Triggers are managed and saved in DBMS. A stored procedure may also be run by it.
In other words, A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.
When do the DML triggers run?
DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not.
What is a cursor?
A cursor in SQL Server is a database object that allows us to retrieve each row at a time and manipulate its data. A cursor is nothing more than a pointer to a row. It’s always used in conjunction with a SELECT statement.
What is the use of the Stored Procedure?
The following are the benefits of employing stored procedures:
- Application performance is improved by stored procedures.
- Because stored procedure execution plans are cached in SQL Server’s memory, server overhead is reduced.
- They are reusable.
- It can contain logic. The stored procedure code can be modified without having an impact on clients.
- They offer your data superior security.
Is it possible to call a stored procedure within a stored procedure?
Yes, we can call a stored procedure within a stored procedure. It is called the recursion property of the SQL server and these types of stored procedures are called nested stored procedures.
Big Data and SQL 