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.

How do you use the TOP keyword in a SELECT statement?

Vaibhav Kothia May 26, 2023 0 Comments

The TOP keyword is used to limit the number of records returned in a SELECT statement.

For example, if you wanted to select the top 5 records from a table called ‘Customers’, you would use the following query:

SELECT TOP 5 * FROM Customers;

What are the different types of joins in SQL Server?

Vaibhav Kothia May 26, 2023 0 Comments

1. INNER JOIN: This is the most common type of join used in SQL. It returns rows when there is at least one match in both tables.

Example:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2;

2. LEFT JOIN: This join returns all rows from the left table, even if there are no matches in the right table.

Example:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2;

3. RIGHT JOIN: This join returns all rows from the right table, even if there are no matches in the left table.

Example:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2;

4. FULL OUTER JOIN: This join returns all rows from both tables, even if there are no matches in either table.

Example:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2;

5. CROSS JOIN: This join returns the Cartesian product of both tables.

Example:

SELECT *
FROM table1
CROSS JOIN table2;

What are the differences between a primary key and a unique key?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a field in a table that uniquely identifies each record in the table. It is a combination of a unique index and a not null constraint. For example, a customer table may have a primary key of customer_id.

A unique key is a field in a table that uniquely identifies each record in the table. It does not have to be the primary key, but it must contain unique values. For example, a customer table may have a unique key of email address.

What is the purpose of the FOR XML clause in SQL Server?

Vaibhav Kothia May 26, 2023 0 Comments

The FOR XML clause in SQL Server is used to return query results in XML format. It is useful for applications that require data in XML format.

For example, the following query returns a list of customer names and their respective orders in XML format:

SELECT c.Name, o.OrderNumber
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
FOR XML AUTO, ELEMENTS;

The output of this query would be something like this:

John Smith
12345

Jane Doe
54321

What is the purpose of the DDL trigger?

Vaibhav Kothia May 26, 2023 0 Comments

A DDL trigger is a special type of trigger that fires in response to a Data Definition Language (DDL) event. It can be used to audit and control changes to the database schema, such as when a table is modified, or when a user attempts to create or drop a table.

For example, you could create a DDL trigger to log any changes to the database schema, by logging the SQL command that was executed, or by sending an email to the DBA. You could also create a DDL trigger to block certain users from creating or dropping tables, by raising an error when the command is attempted.