SQL Interview....Part 1

1.What Does ACID Stands For?

A-Atomicity

C-Consistency

I-Isolation

D-Durability

Atomicity Means that each transaction is treated as all or nothing.that is either is committed or aborted.

Consistency ensures that a transaction wont allow the system to arrive at an incorrect logical state.The data must always be logically correct,means all the rules and constraints in the system are honored even in the event of system failure.

Isolation separates concurrent transactions from the updates of other incomplete transactions.

After a transactions commits,the durability property ensures that the effects of transaction persists even if a system failure occurs.

2.What are Lost Updates?

Lost updates occur when two processes read the same data both manipulate the data,changing its value, and then both try to update the original data to the new value.the second value might overwrite the first update completely.

3.What are dirty Reads?

Dirty Reads Occur when a process reads a uncommitted data.if one process has changed the data but not yet committed the change, another process reading the data will read it in an inconsistent state.

4.What are Nonrepeatable reads?

A read is nonrepeatable read if a process might get different values when reading same data in two separate reads within the same transaction.

5.What are Phantoms?

A phantom occurs if a select operation using a same predicate in the same transaction returns a different number of rows.

Concurrency

Concurrency can be defined as the ability of multiple processes to access the shared data at the same time.

SQL Server Small Thigs-- But Overlooked

1.Count(*) is the only aggregate that considers null values in the group. Consider the column Quantity with values {10,20,30,40,NULL,50}
Count(*) will give 6
Count(Quantity) will give 5

--More to Come..

SQL Server Best Practise -- Writing Queries.

1.Always Refer objects with their schema qualified name.It avoids overhead in resolving the names.
Example:Consider a tables called orders under the sales schema
if you execute the query given below
select * from orders
SQL Server first try to find the table under the default schema of the user logged in , then it will search in remianing objects.if the same object is present in two different schemas then query returns an error.
Why give this overhead.
Always use two part identifies when referring to objects in the database.
Like select * from sales.orders

2.it is good practice to add semicolon at the end of each statement.Not All the statement in SQL Server requires a semicolon at the end of each statement.But some statements in SQL Server (like the statement before the CTE).

3.Don't use the follwing aliasing technique.
select Column_Name Alias_Name from table
It is always good practice to use AS keyword when aliasing or = .They make the code more readable and less prone to errors
4.It is recommended that you list all the columns in the select list instead of using Asteric (*) even if you want all the columns from the table for the following reasons
(i) To avoiding errors caused by schema changes
(ii) In terms of performance listing all the columns is better than using "*" although it is very minimal to negligible comared to total cost of the query.
Any ways it is a win win situation using column names instead od Asteric
5.T sql allows you to specify in th ORDER BY clause ordinal positions of the columns based on the order in whihc they appea in the select list.
you can use
SELECT id,qty FROM orders ORDER BY id,qty
or
SELECT id,qty FROM orders ORDER BY 1,2
This is a bad programming practice for two reasons
(i) Relational model does not guarantee column order in a set.(but T-SQL Does)
(ii) if you choose to modify the select list and forgot to modify the order by clause this will throw an error.





More to be Posted....