SQL Interview Question

SQL Interview Question with Answer

Interview Question : What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process ofnormalization. It's the controlled introduction of redundancy in tothe database design. It helps improve the query performance as thenumber of joins could be reduced.

Interview Question : How do you implement one-to-one, one-to-many and many-to-manyrelationships while designing tables?
One-to-One relationship can be implemented as a single table andrarely as two tables with primary and foreign key relationships.One-to-Many relationships are implemented by splitting the data intotwo tables with primary key and foreign key relationships.Many-to-Many relationships are implemented using a junction table withthe keys from both the tables forming the composite primary key of thejunction table.
It will be a good idea to read up a database designing fundamentalstext book.

Interview Question : What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on whichthey are defined. But by default primary key creates a clustered indexon the column, where are unique creates a nonclustered index bydefault. Another major difference is that, primary key doesn't allowNULLs, but unique key allows one NULL only.
What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes byproviding a descriptive name, and format to the database. Take forexample, in your database, there is a column called Flight_Num whichappears in many tables. In all these tables it should be varchar(8).In this case you could create a user defined datatype calledFlight_num_type of varchar(8) and use it across all your tables.
See sp_addtype, sp_droptype in books online.

Interview Question : What is bit datatype and what's the information that can be storedinside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true orfalse). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0and there was no support for NULL. But from SQL Server 7.0 onwards,bit datatype can represent a third state, which is NULL.

Interview Question : Define candidate key, alternate key, composite key.
A candidate key is one that can identify each row of a table uniquely.Generally a candidate key becomes the primary key of the table. If thetable has more than one candidate key, one of them will become theprimary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is calledcomposite key.

Interview Question : What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value issupplied to that column while inserting data. IDENTITY columns andtimestamp columns can't have defaults bound to them. See CREATEDEFUALT in books online.
Back to topSQL Server architecture (top)

Interview Question : What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps mustbe performed or none. ACID stands for Atomicity, Consistency,Isolation, Durability. These are the properties of a transaction. Formore information and explanation of these properties, see SQL Serverbooks online or any RDBMS fundamentals text book.

Interview Question : Explain different isolation levels
An isolation level determines the degree of isolation of data betweenconcurrent transactions. The default SQL Server isolation level isRead Committed. Here are the other isolation levels (in the ascendingorder of isolation): Read Uncommitted, Read Committed, RepeatableRead, Serializable. See SQL Server books online for an explanation ofthe isolation levels. Be sure to read about SET TRANSACTION ISOLATIONLEVEL, which lets you customize the isolation level at the connectionlevel.
CREATE INDEX myIndex ON myTable(myColumn)

Interview Question : What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clusteredindex gets created on the primary key, unless specified otherwise.
What's the maximum size of a row?
8060 bytes. Don't be surprised with questions like 'what is themaximum number of columns per table'. Check out SQL Server booksonline for the page titled: "Maximum Capacity Specifications".

Interview Question : Explain Active/Active and Active/Passive cluster configurations
Hopefully you have experience setting up cluster servers. But if youdon't, at least be familiar with the way clustering works and the twoclusterning configurations Active/Active and Active/Passive. SQLServer books online has enough information on this topic and there isa good white paper available on Microsoft site.

Interview Question : Explain the architecture of SQL Server
This is a very important question and you better be able to answer itif consider yourself a DBA. SQL Server books online is the best placeto read about SQL Server architecture. Read up the chapter dedicatedto SQL Server Architecture.

Interview Question : What is lock escalation?
Lock escalation is the process of converting a lot of low level locks(like row locks, page locks) into higher level locks (like tablelocks). Every lock is a memory structure too many locks would mean,more memory being occupied by locks. To prevent this from happening,SQL Server escalates the many fine-grain locks to fewer coarse-grainlocks. Lock escalation threshold was definable in SQL Server 6.5, butfrom SQL Server 7.0 onwards it's dynamically managed by SQL Server.

Interview Question : What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row getslogged in the transaction log, which makes it slow. TRUNCATE TABLEalso deletes all the rows in a table, but it won't log the deletion ofeach row, instead it logs the deallocation of the data pages of thetable, which makes it faster. Of course, TRUNCATE TABLE can be rolledback.

Interview Question : Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP in SQL Server books online for moreinfomation.
What are the new features introduced in SQL Server 2000 (or the latestrelease of SQL Server at the time of your interview)? What changedbetween the previous version of SQL Server and the current version?
This question is generally asked to see how current is your knowledge.Generally there is a section in the beginning of the books onlinetitled "What's New", which has all such information. Of course,reading just that is not enough, you should have tried those things tobetter answer the questions. Also check out the section titled"Backward Compatibility" in books online which talks about the changesthat have taken place in the new version.
What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the databaseautomatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pagestitled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

Interview Question : Whar is an index? What are the types of indexes? How many clusteredindexes can be created on a table?
I create a separate index on eachcolumn of a table. what are the advantages and disadvantages of thisapproach?
Indexes in SQL Server are similar to the indexes in books. They helpSQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes.When you craete a clustered index on a table, all the rows in thetable are stored in the order of the clustered index key. So, therecan be only one clustered index per table. Non-clustered indexes havetheir own storage separate from the table data storage. Non-clusteredindexes are stored as B-tree structures (so do clustered indexes),with the leaf level nodes having the index key and it's row locater.The row located could be the RID or the Clustered index key, dependingup on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves thequery performance, as the query optimizer can choose from all theexisting indexes to come up with an efficient execution plan. At thesame t ime, data modification operations (such as INSERT, UPDATE,DELETE) will become slow, as every time data changes in the table, allthe indexes need to be updated. Another disadvantage is that, indexesneed disk space, the more indexes you have, more disk space is used.
Back to topDatabase administration (top)

Interview Question : What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to providefault tolerance to database servers. There are six RAID levels 0through 5 offering different levels of performance, fault tolerance.MSDN has some information about RAID levels and for detailedinformation, check out the RAID advisory board's homepage

Interview Question : What are the steps you will take to improve performance of a poorperforming query?
This is a very open ended question and there could be a lot of reasonsbehind the poor performance of a query. But some general issues thatyou could talk about would be: No indexes, table scans, missing or outof date statistics, blocking, excess recompilations of storedprocedures, procedures and triggers without SET NOCOUNT ON, poorlywritten query with unnecessarily complicated joins, too muchnormalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performanceproblems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SETSTATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performancemonitor, Graphical execution plan in Query Analyzer.
Download the white paper on performance tuning SQL Server fromMicrosoft web site. Don't forget to check out sql-server-performance.com

Interview Question : What are the steps you will take, if you are tasked with securing anSQL Server?
Again this is another open ended question. Here are some things youcould talk about: Preferring NT authentication, using server, databseand application roles to control access to the data, securing thephysical database files using NTFS permissions, using an unguessableSA password, restricting physical access to the SQL Server, renamingthe Administrator account on the SQL Server computer, disabling theGuest account, enabling auditing, using multiprotocol encryption,setting up SSL, setting up firewalls, isolating SQL Server from theweb server etc.
Read the white paper on SQL Server security from Microsoft website.Also check out My SQL Server security best practices