SQL Server interview question Part 5

1. Ques: Magic tables are nothing but the logical tables maintained by SQL server internally.These are not physical tables. ?
Answer: 
These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.
Two types of Magic tables available in SQL server:

• Inserted
• Deleted

These table can not accessible directly, not even their data-type
The only method to have access to these tables is Triggers operation either After Trigger or Instead of trigger.

2. Ques: What is ACID property ?
Answer:
ACID is stands for Atomicity, Consistency, Isolation, Durability.

ACID property is a transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.

Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.

Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.

3. Ques: What is COALESCE Function in SQL server ? and what is Syntax?
Answer
 COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.
Syntax of COALESCE
COALESCE( expression1, expression2, ... expression_n )
ex:
SELECT COALESCE(NULL, 'Employee', NULL, 'Manager');
Result: 'Employee'


4. Ques: What is difference between  STUFF() function and REPLACE() in SQL server ?
Answer:
STUFF()
This function can be used for delete a certain length of the string and insert a new string in the deleted place.
ex:
STUFF ( InputString, start, length, ReplacedString )

REPLACE()
This function replaces all the occurrences of a string expression with a new string within an input string.
ex:
REPLACE ( InputString , StringToReplace , NewString )


5. Ques: What is Views in sql server? What are the advantages and disadvantages  of Views ?
Answer:
View are virtual table that contains column and data from multiple tables. Views are compiled at run time. Data is in the virtual table, not stored permanently. Views display only selected data. A view hides the complexity of the underlying database schema, or customizes the data and schema for a set of users.

Advantages of Views:

• Views are used for security purpose because it store data in virtual table and hide the data from original table.
• Views provides Query Simplicity with select data from different table and set on single table as per user requirement.
• To hide data complexity.
• To protect the data. If you have a table containing sensitive data in certain columns, 
• you might wish to hide those columns from certain groups of users. 
• Create a view to allow reading of only certain columns from a table.

Disadvantages of views

• Performance : with using Views performance of query goes to slow.

 6. Ques: What are possible way the index combination's of table ?

Answer:

 Indexes can combination's  as in a table

•  A clustered index 
•  A clustered index and many nonclustered indexes 
•  A nonclustered index 
•  Many nonclustered indexes

7. Ques: Difference between Identity column and Primary column?

Answer:

Primary Key: 

1.Primary Key value will be entered by the user. 

2.Can be created more than one column (composite primary key). 

3.All primary key is not an identity column. 

4.Can be update the value 

5.Can be refer by other table as a foreign key.

 Identity Column: 

1.Identity column is auto incremented 

2.Incremented numeric values only 

3.Only one Identity column in table 

4.All identity column is an primary Key 

5.Values cannot be updated 

8. Ques: What id difference between 'Optimistic' and 'Pessimistic' locking ?
Answer:
Pessimistic locking 
In pessimistic locking when user wants to update data it locks the record and till then no one can update data. Other user’s can only view the data when there is pessimistic locking.

Optimistic locking
In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days browser based application is very common and having pessimistic locking is not a practical solution.

9. Ques:  What is Common Table Expression (CTE) ?
Answer:
The Common Table Expression (CTE) was introduced earlier in the SQL Server 2005.
The CTE defines about a temporary view, which can be referenced in the same query just as a view .
The CTE’s can be used and compiled in exactly the same ways that simple Subqueries are being used.
It can be used instead of temp table or table variables in the stored procedures in the circumstances.
CTE’s can also recursively refer to the same table using a union or union all, and this works great for searching an adjacency pairs pattern hierarchy.
The CTE uses the WITH clause, so the syntax can be shown as:

WITH ProductCTE
AS
(
  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)
SELECT * FROM ProductCTE