How does SQL Server handle deadlocks?

Vaibhav Kothia May 30, 2023 0 Comments

SQL Server handles deadlocks by automatically choosing one of the sessions as a deadlock victim and aborting the transaction. In order to avoid unnecessary deadlocks, SQL Server implements a deadlock detection and resolution process.

For example, consider two sessions, A and B, that are attempting to update the same row in a table. Session A has acquired a shared lock on the row, while Session B has acquired an exclusive lock on the same row. When Session A attempts to acquire an exclusive lock on the same row, a deadlock is detected. SQL Server then chooses one of the sessions as the deadlock victim and aborts the transaction. In this case, Session B is chosen as the deadlock victim and its transaction is aborted.

What is the difference between a trigger and a stored procedure?

Vaibhav Kothia May 30, 2023 0 Comments

A trigger is a block of code that is executed automatically when a specific event occurs in a database, such as when a record is inserted, updated, or deleted. Triggers are often used to implement complex business rules, maintain data integrity, or audit changes to data.

Example of a Trigger:
CREATE TRIGGER tr_Employee_Update
ON Employee
AFTER UPDATE
AS
BEGIN
UPDATE Employee
SET LastUpdated = GETDATE()
WHERE EmployeeID =
(SELECT EmployeeID FROM deleted)
END

A stored procedure is a precompiled set of SQL statements that can be executed multiple times with different parameters. Stored procedures are often used to encapsulate complex business logic and are used to improve application performance by reducing the amount of code that needs to be executed.

Example of a Stored Procedure:
CREATE PROCEDURE GetEmployeeInfo
@EmployeeID int
AS
BEGIN
SELECT *
FROM Employee
WHERE EmployeeID = @EmployeeID
END

What is the main purpose of a stored procedure?

Vaibhav Kothia May 30, 2023 0 Comments

The main purpose of a stored procedure is to allow for efficient reuse of code. It is a set of SQL statements that are stored in the database and can be called from other programs or applications.

For example, a stored procedure might be used to retrieve customer information from a database. The stored procedure can then be called from a web page, allowing the web page to display customer information without having to write the same code multiple times.

What is the purpose of normalization in database design?

Vaibhav Kothia May 30, 2023 0 Comments

Normalization in database design is the process of organizing data into tables in such a way that the results of using the database are always unambiguous and as intended. It is a systematic approach of decomposing tables to eliminate data redundancy (repetition) and undesirable characteristics such as Insertion, Update and Deletion Anomalies.

For example, consider a table that stores both customer information and order information. If the data is not properly normalized, the customer information may be repeated in each row of the orders table. This could lead to data integrity issues, such as incorrect customer information being updated in one row but not the other. Normalization would break this table into two separate tables, one for customer information and one for order information, and create a relationship between them. This would ensure that customer information is only stored once and is consistent across all orders.

What is the purpose of triggers in SQL Server?

Vaibhav Kothia May 26, 2023 0 Comments

Triggers in SQL Server are special stored procedures that are automatically executed in response to an event such as an INSERT, DELETE, or UPDATE statement on a given table. They are used to enforce business rules, maintain data integrity, and to audit changes to data.

For example, a trigger could be used to prevent a user from deleting a record from a table if it is referenced in another table. The trigger would check if the record is referenced in another table and if so, it would raise an error and not allow the delete to occur.

What is the purpose of a stored procedure?

Vaibhav Kothia May 26, 2023 0 Comments

A stored procedure is a set of SQL statements that can be stored in a database and reused as a single unit. It is commonly used to encapsulate a set of operations that can be used over and over again in multiple contexts.

For example, a stored procedure could be used to insert data into a table. This procedure could be used every time new data needs to be added to the table, without having to write the same code over and over again. The procedure could be called with a single line of code, which would then execute all the necessary steps to insert the data.

What is the difference between a primary key and a unique key?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a special type of unique key that is used to identify a single row in a table. It is usually composed of one or more columns that contain only unique values, and cannot be NULL. For example, a table of employees may have an Employee ID column as its primary key.

A unique key is any combination of columns that contains only unique values. It is used to enforce data integrity and can be composed of one or more columns. For example, a table of customers may have a combination of first and last name columns as its unique key, ensuring that no two customers have the same name.

What is the difference between clustered and non-clustered indexes?

Vaibhav Kothia May 26, 2023 0 Comments

Clustered indexes are physical structures that determine the order in which data is stored in a table. They are used to improve the speed of data retrieval from the table. For example, if you have a table of customer orders, you can create a clustered index on the order date field to quickly locate orders within a certain date range.

Non-clustered indexes are logical structures that create a separate copy of the data from the table. They are used to improve the speed of data retrieval from the table. For example, if you have a table of customer orders, you can create a non-clustered index on the customer name field to quickly locate orders by customer name.

How does SQL Server use indexes?

Vaibhav Kothia May 26, 2023 0 Comments

SQL Server uses indexes to quickly locate data without having to search every row in a table every time a query is run. Indexes can be created using one or more columns of a table, providing the basis for both rapid random lookups and efficient access of ordered records.

For example, if you had a table of customer orders, you could create an index on the customer name and order date columns. This would allow you to quickly find all orders for a particular customer, or all orders placed on a particular date.

What is the purpose of the SQL Server Management Studio?

Vaibhav Kothia May 26, 2023 0 Comments

The SQL Server Management Studio (SSMS) is a graphical user interface (GUI) used to manage, configure, and administer all components within Microsoft SQL Server. It provides a comprehensive set of tools for managing, developing, and administering databases and objects within an instance of SQL Server.

For example, SSMS can be used to create and manage databases, tables, views, stored procedures, and functions. It can also be used to manage users and security, as well as to monitor the performance of the SQL Server instance. Additionally, SSMS can be used to develop and debug Transact-SQL scripts, as well as to deploy and configure SQL Server objects.