- What are Constraints or Define Constraints ?
- Not Null
- Primary Key
- Unique Key
- Foreign Key
- What is the difference between Primary Key and Unique Key ?
- By default PK defines Clustered Index in the column where as UK defines Non Clustered Index.
- PK doesn’t allow NULL Value where as UK allow ONLY ONE NULL.
- You can have only one PK per table where as UK can be more than one per table.
- PK can be used in Foreign Key relationships where as UK cannot be used.
- What is the difference between Delete and Truncate ?
- Truncate is Faster where as Delete is Slow process.
- Truncate doesn’t log where as Delete logs an entry for every record deleted in Transaction Log.
- We can rollback the Deleted data where as Truncated data cannot be rolled back.
- Truncate resets the Identity column where as Delete doesn’t.
- We can have WHERE Clause for delete where as for Truncate we cannot have WHERE Clause.
- Delete Activates TRIGGER where as TRUNCATE Cannot.
- Truncate is a DDL statement where as Delete is DML statement.
- What are Indexes or Indices ?
- Types of Indices in SQL ?
- Non Clustered
- How many Clustered and Non Clustered Indexes can be defined for a table ?
Non Clustered – 999
What is Transaction in SQL Server ?
- Types of Transactions ?
- Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit. No need to specify Explicitly.
- Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. PFB an Example for Explicit transactions.
- What is the Max size and Max number of columns for a row in a table ?
- What is Normalization and Explain different normal forms.
1. Organizing data to minimize redundancy.
2. Isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
2. All fields are dependent on the whole of the primary key, or a relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on each candidate key of the relation. If an attribute depends on only part of a multi‐valued key, remove it to a separate table.
2. Transitive dependencies must be eliminated. All attributes must rely only on the primary key. If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
for every one of its non-trivial functional dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof. If there are non‐trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
For example, if you can have two phone numbers values and two email address values, then you should not have them in the same table.
5NF: Isolate Semantically Related Multiple Relationships
A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys. There may be practical constrains on information that justify separating logically related many‐to‐many relationships.
- What is Denormalization ?
It is sometimes necessary because current DBMSs implement the relational model poorly.
In some cases, de-normalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.
A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De‐normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
- Query to Pull ONLY duplicate records from table ?
GROUP BY specificColumn
HAVING COUNT(*) > 1
- Types of Joins in SQL SERVER ?
- Inner Join
- Outer Join
- Cross Join
- Right Outer Join
- Left Outer Join
- Full Outer Join.
- What is Table Expressions in Sql Server ?
- Derived tables
- Common Table Expressions.
- What is Derived Table ?
- What is CTE or Common Table Expression ?
- Non Recursive
- Difference between SmallDateTime and DateTime datatypes in Sql server ?
- DateTime occupies 4 Bytes of data where as SmallDateTime occupies only 2 Bytes.
- DateTime ranges from 01/01/1753 to 12/31/9999 where as SmallDateTime ranges from 01/01/1900 to 06/06/2079.
- What is SQL_VARIANT Datatype ?
- What is Temporary table ?
- What are the differences between Local Temp table and Global Temp table ?
- Both are stored in tempdb database.
- Both will be cleared once the connection,which is used to create the table, is closed.
- Both are meant to store data temporarily.
- Local temp table is prefixed with # where as Global temp table with ##.
- Local temp table is valid for the current connection i.e the connection where it is created where as Global temp table is valid for all the connection.
- Local temp table cannot be shared between multiple users where as Global temp table can be shared.
- Whar are the differences between Temp table and Table variable ?
- Table variables are Transaction neutral where as Temp tables are Transaction bound. For example if we declare and load data into a temp table and table variable in a transaction and if the transaction is ROLLEDBACK, still the table variable will have the data loaded where as Temp table will not be available as the transaction is rolled back.
- Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
- Table variables don’t participate in transactions, logging or locking. This means they’re faster as they don’t require the overhead.
- You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.
- What is the difference between Char,Varchar and nVarchar datatypes ?
- What is the difference between STUFF and REPLACE functions in Sql server ?
- What are Magic Tables ?
The deleted table contains all records that have been deleted from deleted from the trigger table.
Whenever any updation takes place, the trigger uses both the inserted and deleted tables.
- Explain about RANK,ROW_NUMBER and DENSE_RANK in Sql server ?
First lets create some sample data :
CREATE TABLE Salaries
– insert data
INSERT INTO Salaries SELECT
‘A’,5000 UNION ALL SELECT
‘B’,5000 UNION ALL SELECT
‘C’,3000 UNION ALL SELECT
‘D’,4000 UNION ALL SELECT
‘E’,6000 UNION ALL SELECT
– Test the data
SELECT Names, Salary
For that I’ll write a query like this :
,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
,rank () OVER (ORDER BY salary DESC) as RANK
,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
So question is which one to use?
Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don’t leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)
- What are the differences between WHERE and HAVING clauses in SQl Server ?
2.Where applies to each and single row and Having applies to summarized rows (summarized with GROUP BY).
3.In Where clause the data that fetched from memory according to condition and In having the completed data firstly fetched and then separated according to condition.
4.Where is used before GROUP BY clause and HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query.
- Explain Physical Data Model or PDM ?
- Specification all tables and columns.
- Foreign keys are used to identify relationships between tables.
- Specying Data types.
- Includes all entities and relationships among them.
- All attributes for each entity are specified.
- The primary key for each entity is specified.
- Foreign keys (keys identifying the relationship between different entities) are specified.
- Normalization occurs at this level.
- Includes the important entities and the relationships among them.
- No attribute is specified.
- No primary key is specified.
- What are the advantages of database normalization ?
- No need to restructure existing tables for new data.
- Reducing repetitive entries.
- Reducing required storage space
- Increased speed and flexibility of queries.
- What are Linked Servers ?
- The ability to access data from outside of SQL Server.
- The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
- The ability to address diverse data sources similarly.
- Can connect to MOLAP databases too.
- What is the Difference between the functions COUNT and COUNT_BIG ?
- Count returns INT datatype value where as Count_Big returns BIGINT datatype value.
- Count is used if the rows in a table are less where as Count_Big will be used when the numbenr of records are in millions or above.
- Count – Select count(*) from tablename
- Count_Big – Select Count_Big(*) from tablename
- How to insert values EXPLICITLY to an Identity Column ?
- How to RENAME a table and column in SQL ?
- How to rename a database ?
- What is the use the UPDATE_STATISTICS command ?
- How to read the last record from a table with Identity Column ?
WHERE ID = IDENT_CURRENT(‘TABLE’)
WHERE ID = (SELECT MAX(ID) FROM TABLE)
- What is Worktable ?
- What is HEAP table ?
- What is ROW LOCATOR ?
- What is Covering Index ?
where col3 = Value
group by col4
order by col5
- What is Indexed View ?
- What is Bookmark Lookup ?
What are the Isolation levels in SQL
Isolation levels :- Isolation level is required to isolate a resource and protect it from other transactions. This
is achieved with the help of locks but what locks are needed and how they can be established is decided on the
isolation level set on the database level. If low level of Isolation is set, it allows multiple users to access
the resources concurrently but it may result in many concurrency related problems like phantom reads, dirty reads
etc. If higher levels of Isolation is set then it eliminate the concurrency related problem but it results in less
number of concurrent access and it may result in data blocking
Dirty reads:- This situation happens when a transaction tries to read a data by some other concurrent transaction
which is not committed yet. There is a risk, that this other transaction may never be committed, leaving the
original transaction with wrong data.
Lost updates:- It happen when more than one transaction tries to update the same data. In Lost update, a
successfully updated value of a data written in the database is overrides accidentally by the some another
transaction updating the same data concurrently.
Repeatable reads:- Repeatable reads condition occur when a transaction tries to read a data multiple times and and
between the two reads, another transaction modified that data. Therefore when the original transaction tries to
read that data second time, it find different value for that data. In other words, the original transaction reads
two different values for the same data.
Phantom reads:-This condition happen when a transaction needs to execute a same query twice(or multiple times) and
it gets different set of rows from what it get earlier due to the execution of the query first time. This happens
if some another transaction add or delete rows between the two executions of the query and these added/deleted
rows are the part of the record set reruns by the execution of the query.
There are the five Isolation levels (from lower level to higher level) defined in the SQL Server.
Read Uncommitted:- In this Isolation level, a transaction can read the data which is modified by some other
transactions but still not committed. This Isolation level do not issue shared locked to prevent data modification
which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from
reading data which is modified from other transaction by not committed yet. It may results in problem like dirty
read, lost updates, phantom reads etc. It is the least restrictive Isolation level.
Read Committed:- It is the default Isolation set by the SQL Server for any Database. This Isolation level prevents
the transaction from reading data which are modified by some other transactions but still are not committed yet.
Thus it eliminates the problem of Dirty read. But it do not eliminates the occurrence of Phantom reads and
Repeatable Read:- This isolation level is higher than the previous two mention isolation level and it does not
allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also
it doesn't allow any transaction to modify a data if it is being read by some other transaction until the
transaction reading that data complete its operation. This way it eliminates the dirty read and Repeatable reads
problem but it doesn't eliminates the Phantom reads.
Serializable:- This Isolation level do not allow any transaction to read the data unless the other transactions
completed their data modification operation. Also it doesn't allow other transactions to modify the data until the
current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock
(if only read operation) or write lock (for insert,delete,update) for the entire range of records that the
transaction is going to affect. For example, if the query is "Select * from employees" then the transaction will
acquire the read lock for whole table and no other transaction is allowed to add new rows or delete the rows until
the current transaction releases its lock. Similarly, if the query is "Select * from Employee where
country='India", then the current transaction will acquire the read lock for all the records of the table where
country is India, and no other transaction is allowed to add or delete new rows until the current transaction
releases its read lock. Thus, if we executes the same query twice , then each time it will get the same set of
rows of a data and therefore it eliminates the Phantom read problem.
Snapshot:-In this isolation level, a transaction recognise only data which is committed before the start of the
transaction. Any modification of the data after the transaction is begin, is not visible to any statements of the
currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the
help of row version where a separate version of each modified row is maintain in the temp db database dedicated to
the transactions. This isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads