Sql Server Developer Interview Questions And Answers Pdf
1. What is a deadlock and what is a live lock?
Answer: 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.
2. What is blocking and how would you troubleshoot it?
Answer: 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.
3. Can we write a distributed query and get some data which is located on other server and Oracle Database?
Answer: SQL Server can be lined to any server provided it has an OLE-DB provider from Microsoft to allow a link.
E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.
4. What the different Topologies in which Replication can be configured?
Answer: Replication can be configured in any topology depending on keeping given the complexity and the workload of the entire Replication. It can be any of the following:
Publisher, Distributor, and Subscriber on the same SQL Instance.
Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.
Publisher, Distributor, and Subscriber on individual SQL Instances.
5. What is lock escalation?
Answer: A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.
6. What are the main differences between #temp tables and @table variables and which one is preferred?
SQL Server can create column statistics on #temp tables
Indexes can be created on #temp tables
@table variables are stored in memory up to a certain threshold.
7. What is Checkpoint In SQL Server?
Answer: When we did operation on SQL SERVER that is not committed directly to the database. All operation must be logged in to Transaction Log files after that they should be done on to the main database. A CheckPoint is a point which alerts Sql Server to save all the data to the main database if no checkpoint is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take a long time because Checkpoint is also fired. (E learning Portal)
8. What have included columns when we talk about SQL Server indexing?
Answer: Indexed with included columns were developed in SQL Server 2005 that assists in covering queries. Indexes with Included Columns are non clustered indexes that
have the following benefits:
Columns defined in the include statement, called non-key columns, are not counted in the
several columns by the Database Engine.
Columns that previously could not be used in queries, like varchar(max), can be included
as a non-key column.
A maximum of 1023 additional columns can be used as non-key columns.
9. What is the difference between the 2 operating modes of Database Mirroring?
Answer: High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
High-Performance Mode is to ensure that the Principal database runs faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.
10. What is an execution plan?
Answer: 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 a query execution plan in a separate window when a query is run again.
11. Explain UNION, MINUS, UNION ALL, INTERSECT?
INTERSECT returns all distinct rows selected by both queries.
MINUS – returns all distinct rows selected by the first query but not by the second.
UNION – returns all distinct rows selected by either query.
UNION ALL – returns all rows selected by either query, including all duplicates.
12. How do you troubleshoot errors in a SQL Server Agent Job?
Answer: Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right-click and choose view history from the drop-down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day).
13. What the different types of Replication and why are they used?
Answer: There are 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose depends on the requirements and/or the goals one is trying to achieve. For example, Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table, etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales order tables etc. Merge Replication is more useful in case of remote / distributed systems where the data flow can be from multiple sites, for example, sales are done at a promotional event that might not be connected to the central servers always.
14. Can we hide the definition of a stored procedure from a use?
Answer: YES, while creating a stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.
15. Why we use the OPENXML clause?
Answer: OPENXML parses the XML data in SQL Server in an efficient manner. Its primary ability is to insert XML data into the DB.
16. Can we store Videos inside the SQL Server table?
Answer: YES, we can store Videos inside SQL Server by using the FILESTREAM data type, which was introduced in SQL Server 2008.
17. How can we determine what objects a user-defined function depends upon?
Answer: sp_depends system stored procedure or query the says depends on system table to return a list of objects that a user-defined function depends on upon.
18. Can one drop a column from a table?
Answer: YES, to delete a column in a table, use ALTER TABLE table_name DROP COLUMN column_name.
19. What is Fill Factor?
Answer: Fill Factor is a setting that applies to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created/rebuilt.
20. How SQL Server executes a statement with nested subqueries?
Answer: When SQL Server executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set.
21. What is Data-Warehousing?
Answer: Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
Integrated, meaning that the database contains data from most or all of an organization’s operational applications and that this data is made consistent.
22. What are the different Types of Join?
Answer: 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. A common example is when a company wants to combine each product with a pricing table to analyze each product at each price.
Inner Join A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer joins to specify the unmatched rows to be included:
Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left” table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
Right Outer Join: In Right Outer Join all rows in the second-named table i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
Self Join This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self-join can be of any type, as long as the joined tables are the same. A self-join is rather unique in that it involves a relationship with only one table. The common example is when a company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.
23. Why you need indexing? where that is Stored and what you mean by schema object? For what purpose we are using view?
Answer: We can’t create an Index on Index… The index is stored in the user_index table. Every object that has been created on Schema is Schema objects like Table, View, etc. If we want to share the particular data with various users we have to use the virtual table for the Base table. So that is a view.
Indexing is used for faster search or to retrieve data faster from various tables. Schema containing a set of tables, basically schema means logical separation of the database. The view is created for faster retrieval of data. It’s a customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.
24. How do you open a Cluster Administrator?
Answer: We can’t create an Index on Index. The index is stored in the user_index table. Every object that has been created on Schema is Schema Object From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.
25. What Is the Difference Between OSQL And Query Analyzer?
Answer: OSQL is a command-line tool that executes the query and displays the result same a query analyzer but the query analyzer is graphical and OSQL is a command-line tool. OSQL is quite useful for batch processing or executing remote queries.
26. What is the OSQL utility?
Answer: OSQL is a command-line tool that is used to execute a query and display the result same a query analyzer but everything is in command prompt.
27. What is a correlated subquery?
Answer: When a subquery is tied to the outer query. Mostly used in self joins.
28. What factors you will consider calculating the storage requirement for that view?
Answer: Very tricky, View, doesn’t take space in Database, Views are virtual tables. Storage is required to store Index, in case you are developing an indexed view.
29. What are the pros and cons of putting a scalar function in a queries select list or in the where clause?
Answer: Should be avoided if possible as Scalar functions in these places make the query slow down dramatically.
You are being assigned to create a complex View and you have completed that task and that view is ready to be getting pushed to the production server now. you are supposed to fill a deployment form before any change is pushed to the production server.
One of the Filed in that deployment form asked, “Expected Storage requirement”.
30. How important do you consider cursors or while loops for a transactional database?
Answer: I would like to avoid cursor in the OLTP database as much as possible, Cursors are mainly only used for maintenance or warehouse operations.
31. Is it possible to update the Views? If yes, How, If Not, Why?
Answer: Yes, We can modify views but a DML statement on a join view can modify only one base table of the view (so even if the view is created upon a join of many tables, only one table, the key preserved table can be modified through the view).32. Could you please name different kinds of Joins available in SQL Server? _text]Answer:
OUTER JOIN – LEFT, RIGHT, CROSS, FULL ;
33. WHAT OPERATOR PERFORMS PATTERN MATCHING?
Pattern matching operator is LIKE and it has to use with two attributes
1. % means matches zero or more characters.
34. What is the Difference between COALESCE() & ISNULL()?
Answer: ISNULL accepts only 2 parameters. The first parameter is checked for a NULL value, if it is NULL then the second parameter is returned, otherwise, it returns the first parameter.
COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter,
35. If we drop a table, does it also drop related objects like constraints, indexes, columns, defaults, Views, and Stored Procedures?
Answer: YES, SQL Server drops all related objects, which exists inside a table like, constraints, indexes, columns, defaults, etc. BUT dropping a table will not drop Views and Stored Procedures as they exist outside the table.
36. What are the recovery models for a database?
Answer: There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.
37. How will you go about resolving deadlocks?
Answer: 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.
38. What is the optimal Disk configuration for a database server and what RAID configurations would you use if budget is not a constraint?
RAID 1 for the OS / Applications
RAID 1 for the page file
RAID 10 for the Data file (or RAID 5 for few writes)
RAID 1 (or 10) for the transaction log
39. What are the steps you will take to improve the performance of a poor performing query?
Answer: This is a very open-ended question and 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.
Some of the tools/ways that help you troubleshooting performance problems are:
SET SHOWPLAN_ALL ON,
SET SHOWPLAN_TEXT ON,
SET STATISTICS IO ON,
SQL Server Profiler,
Windows NT /2000 Performance monitor,
Graphical execution plan in Query Analyzer.
40. What is the Filtered Index?
Answer: Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with the WHERE clause, then it is called Filtered Index.
41. What is the CDC?
Answer: CDC is abbreviated as Change Data Capture which is used to capture the data that.
42. What are the methods used to protect against SQL injection attack?
Following are the methods used to protect against SQL injection attack:
Use Parameters for Stored Procedures
Filtering input parameters
Use Parameter collection with Dynamic SQL
In like clause, the user escapes characters
43. Where are SQL Server user names and passwords stored in SQL Server?
Answer: User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.
44. What is the difference between COMMIT and ROLLBACK?
Answer: Every statement between BEGIN and COMMIT becomes persistent to the database when the COMMIT is executed. Every statement between BEGIN and ROLLBACK is 7.
45. What is the difference between varchar and varchar types?
Answer: Varchar and nvarchar are the same but the only difference is that nvarchar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.
46. What is the use of FOR Clause?
Answer: FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or the browser.
47. What will be the maximum number of indexes per table?
For SQL Server 2008 100 Index can be used as the maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be used as the maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
48. What are Magic Tables in SQL Server?
Answer: Insert and Delete tables are created when the trigger is fired for any DML command. Those tables are called Magic Tables in SQL Server. These magic tables are used inside the triggers for data transactions.
49. Which SQL server table is used to hold the stored procedure script?
Answer: Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. The name of the stored procedure is saved in the table called Sys. Procedure.
50. What are the differences between Stored Procedure and the dynamic SQL?
Answer: Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply executes during run time.