361
SQL Server Interview Question and Answer
| What is normalization? | Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization. |
| What is a Stored Procedure? | Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. |
| What is a trigger? | Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database. |
| What is bit datatype and what’s the information that can be stored inside a bit column? | Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL. |
| How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? | One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table |
| 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 the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.A key formed by combining at least two or more columns is called composite key |
| What is a view? | If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time. |
| What is denormalization and when would you go for it? | Denormalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced |
| What is an Index? | When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index |
| 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 is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them. |
| What are the types of indexes available with SQL Server? | There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered. |
| What is a deadlock and what is a live lock? How will you go about resolving deadlocks? | Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. |
| What is the basic difference between clustered and a non-clustered index? | The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db. |
| What kind of User-Defined Functions can be created? | There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.Scalar User-Defined Function A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.Inline Table-Value User-Defined Function An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables. Multi-statement Table-Value User-Defined Function |
| What are cursors? | Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values. |
| When do we use the UPDATE_STATISTICS command? | This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly. |
| What are constraints? Explain different types of constraints. | Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY |
| Can you tell me the difference between DELETE & TRUNCATE commands? | Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command |
| Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan? | Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. |
| Can we use Truncate command on a table which is referenced by FOREIGN KEY? | No. We cannot use Truncate command on a table with Foreign Key because of referential integrity. |
| What is a self join? Explain it with an example. | Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.CREATE TABLE emp ( empid int, mgrid int, empname char(10) )INSERT emp SELECT 1,2,’Vyas’ INSERT emp SELECT 2,3,’Mohan’ INSERT emp SELECT 3,NULL,’Shobha’ INSERT emp SELECT 4,2,’Shridhar’ INSERT emp SELECT 5,2,’Sourabh’ SELECT t1.empname [Employee], t2.empname [Manager] Here’s an advanced query using a LEFT OUTER JOIN that even returns the SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager] |
| What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? | Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. |
| What do you mean by COLLATION? | Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary – case insensitive and Binary. |
| What’s the difference between a primary key and a unique key? | Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only. |
| What is a Join in SQL Server? Can you explain the types of Joins that we can have with Sql Server? | Join actually puts data from two or more tables into a single result set.There are three types of joins: Inner Join, Outer Join, Cross Join |
| What is database replicaion? What are the different types of replication you can set up in SQL Server? | Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:* Snapshot replication * Transactional replication (with immediate updating subscribers, with queued updating subscribers) * Merge replication |
| When do you use SQL Profiler? | SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly. |
| Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach? | Indexes in SQL Server are similar to the indexes in books. They help SQL 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 the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes 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, depending up on the absence or presence of clustered index on the table.If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used |
| Can you explain the role of each service? | SQL SERVER – is for running the databases SQL AGENT – is for automation such as Jobs, DB Maintanance, Backups DTC – Is for linking and connecting to other SQL Servers |
| What are types of sub-queries? | Single-row subquery, where the subquery returns only one row. Multiple-row subquery, where the subquery returns multiple rows,.and Multiple column subquery, where the subquery returns multiple columns. |
| How do you troubleshoot SQL Server if its running very slow? | First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes |
| 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 table locks). 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-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server. |
| What are the authentication modes in SQL Server? | Windows mode and mixed mode (SQL & Windows). |
| What are user defined datatypes and when you should go for them? | User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables. |
| Where do you think the users names and passwords will be stored in sql server? | They get stored in master db in the sysxlogins table. |
| What is SQL server agent? | SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts. |
| Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow? | For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER –m which will basically bring it into the maintenance mode after which we can restore the master db. |
| What is BCP? When do we use it? | BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same |
| What should we do to copy the tables, schema and views from one SQL Server to another? | We have to write some DTS packages for it. |
| List few advantages of Stored Procedure. | Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data. |
| How do you know which index a table is using? | |
| How do you measure the performance of a stored procedure? | |
| What is RAID and what are different types of RAID configurations? | RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. |
| What are the different ways of moving data/databases between servers and databases in SQL Server? | There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT…SELECT, SELECT…INTO, creating INSERT scripts to generate data |
| What are the steps you will take to improve performance of a poor performing query? | There could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables. |
| What are the steps you will take, if you are tasked with securing an SQL Server? | Here are some things you could talk about: Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc. |
| What is blocking and how would you troubleshoot it? | Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. |
| How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode? | SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode |
| What are different normalization forms? | 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table. 3NF: Eliminate Columns Not Dependent On 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 BCNF: Boyce-Codd Normal Form 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. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation. DKNF: Domain-Key Normal Form A model free from all modification anomalies.Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database. |
| What are the different index configurations a table can have? | A table can have one of the following index configurations:No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered indexes |
| What is a NOLOCK? | Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking). |
| Difference between Function and Stored Procedure? | UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations. |
| What types of Joins are possible with Sql Server? | Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. |
| What is sub-query? Explain properties of sub-query. | Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.Properties of Sub-Query A subquery must be enclosed in the parenthesis. A subquery must be put in the right hand of the comparison operator, and A subquery cannot contain a ORDER-BY clause. A query can contain more than one sub-queries. |
| Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible? | Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels. |
| What is @@ERROR? | The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it. |
| What is Raiseerror? | Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log. |
| What is the difference between a local and a global variable? | A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time. |
| What are the different types of replication? Explain. | The SQL Server 2000-supported replication types are as follows:Transactional Snapshot Merge Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected. |
| What is the STUFF function and how does it differ from the REPLACE function? | STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string. |
| How to rebuild Master Database? | Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory. In the Rebuild Master dialog box, click Browse. In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK. Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases. Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK. In the Rebuild Master dialog box, click Rebuild to start the process. The Rebuild Master utility reinstalls the master database. To continue, you may need to stop a server that is running. |
| What is data integrity? | Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications. |
| What is a Scheduled Jobs or What is a Scheduled Tasks? | Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution. |
| What is Cross Join? | A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price. |
| How do SQL server 2000 and XML linked? Can XML be used to access data? | FOR XML (ROW, AUTO, EXPLICIT) You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.OPENXML OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment |
| What is an execution plan? When would you use it? How would you view the execution plan? | An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again. |
| 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 the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.A key formed by combining at least two or more columns is called composite key. |
| Can you have a nested transaction? | Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT |
| How to write optimized queries in SQL SERVER? | Following are some checklistDon’t use heavy calculation in SQL Query. Write correctly formed queries. Return only the rows and columns needed. Avoid expensive operators such as NOT LIKE. Know the performance and scalability characteristics of queries. Avoid explicit or implicit functions in WHERE clauses. Use locking and isolation level hints to minimize locking. Use stored procedures or parameterized queries. Minimize cursor use. Avoid long actions in triggers. Use temporary tables and table variables appropriately. Limit query and index hints use. Fully qualify database objects. |
| Database mirroring concept in SQL server | Database mirroring concept provides the failover support to database centric applications. Database mirroring continuously streams transactions from a principal server to a mirror server. This type of configuration can be used in situations with moderately large amounts of data, a network with sufficient bandwidth to support the transaction load, and the need for physically dispersed backup servers.The principal server |
