Pages

SQL Server

1. List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:

Atomicity. Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.

Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.

Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.

Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

2. What is UNION in SQL Server? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

3. What is SQL Profiler?

SQL Server Profiler is an interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. OR

SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.

INNER JOIN (a.k.a. “simple join”): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

FULL JOIN (or FULL OUTER JOIN): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.

CROSS JOIN: Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

5. What is Self Join and why is it required?

Self Join is the act of joining one table with itself. Self Join is often very useful to convert a hierarchical structure into a flat structure.

In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:
Example -

Given the above query results, what will be the result of the query below?

SELECT count(*) AS cust_not_123_total FROM orders WHERE customer_id <> '123'

The obvious answer is 85 (i.e, 100 - 15). However, that is not necessarily correct. Specifically, any records with a customer_id of NULL will not be included in either count (i.e., they won’t be included in cust_123_total, nor will they be included in cust_not_123_total). For example, if exactly one of the 100 customers has a NULL customer_id, the result of the last query will be:

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BYis used:
- The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
- The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

8. What is CHECK constraint?

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

9. What is sub query and its properties?

A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as

A sub query should not have order by clause

A sub query should be placed in the right hand side of the comparison operator of the main query

A sub query should be enclosed in parenthesis because it needs to be executed first before the main query

More than one sub query can be included

10. What are the types of sub query?

There are three types of sub query –

Single row sub query which returns only one row

Multiple row sub query which returns multiple rows

Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.

11. What is COALESCE in SQL Server? and what is difference between COALESCE and ISNULL?

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

In SQL Server (Transact-SQL), the COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

SelectCOALESCE(empno,empname,salary)fromemployee;

ISNULL() Function

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.

1. The COALESCE() functionis based on the ANSI SQL standard whereas ISNULL function is a Transact-SQL function.

2. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

3. The ISNULL() function contains only two parameters. The COALESCE() function contains multiple parameters. If we use more than two parameters with the ISNULL function then we must use nested ISNULL functions.

SelectCOALESCE(managerId,'bhanu',199)from [Practic].[dbo].[employee1]

SelectISNULL(managerId,'bhanu')from [Practic].[dbo].[employee1]

In above queries managerId is 'int' type. so ISNULL will give error because of data type mismatch but

COALESCE non null records.

12. What is Index? Define Clustered and Non-Clustered index.An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

A table or view can contain the following types of indexes: For more Index

Clustered

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

13. How to delete Duplicate Records?

With CTE AS (

select*,RN =ROW_NUMBER()over(partitionbyid Orderby id)from Employee1

)

deletefrom CTE where RN>1

id - is column by which u find duplicatesORFollowing code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records.DELETEFROM MyTableWHERE ID NOT IN(SELECT MAX(ID)FROM MyTableGROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

16. What is the difference between the RANK() and DENSE_RANK() functions? Provide an example?

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

17. Write query to select all the Even and Odd number records from a table.

To select all the even number records from a table:

Select * from table where id % 2 = 0

To select all the odd number records from a table:

Select * from table where id % 2 != 0

18. What is difference between Truncate, Delete and Drop?Truncate -
1. Truncate is used to delete the content of Table and free the space.
2. This Action can not be rolled back.

Delete -
1. Delete is used to delete records from a Table.
2. This action can be rolled back.

Drop -
1. Drop is used to remove the object from Database.
2. can not rolled back.