Sql Server Interview Questions And Answers Pdf
1. Explain about your SQL Server DBA Experience?
Answer: This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.
2. What is the difference between Clustered and Non-Clustered Index?
Answer: In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
3. What are the High-Availability solutions in SQL Server and differentiate them briefly?
Answer: Failover Clustering, Database Mirroring, Log Shipping, and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.
information such as the time it took to execute that Job and details about the error occurred.
4. What are the commands used in DCL?
Answer: GRANT, DENY and REVOKE.
5. Where do you find the default Index fill factor and how to change it?
Answer: The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes. The other option of viewing and changing this value is using sp_configure.
6. What is Replication?
Answer: Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages of Replication is that it can be configured on databases which are in simple recovery model.
7. What the different components in Replication and what is their use?
Answer: The 3 main components in Replication are Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is responsible for distributing the database objects to one or more destinations. Subscriber is the destination where the publisher’s data is copied/replicated.
8. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?
Answer: On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3node cluster and so on…
9. List out some of the requirements to set up a SQL Server failover cluster?
Answer: Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hear beat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.
10. Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?
Answer: In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose to Take Offline.
- T-SQL; Create Database command.
- Using Management Studio
- Restoring a database backup
- Copy Database wizard
11. What are the different SQL Server Versions you have worked on?
Answer: The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
12. What is a UNIQUE KEY constraint?
Answer: A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
13. What is a FOREIGN KEY?
Answer: A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
14. Can a stored procedure call itself or recursive stored procedure? How many levels SP nesting is possible?
Answer: 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.
15. What is OLTP (Online Transaction Processing)?
Answer: In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
16. When setting Replication, is it possible to have a Publisher as 64 Bit SQL Server and Distributor or Subscribers as a 32 Bit SQL Server?
Answer: Yes, it is possible to have various configurations in a Replication environment.
17. What is Transparent Data Encryption?
Answer: Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.
18. On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?
Answer: Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected the SQL Server group, in the right-hand side of the console, the column “Owner” gives us the information of the node on which the SQL Server group is currently active.
19. What are the different types of Indexes available in SQL Server?
Answer: The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
20. What are the operating modes in which Database Mirroring runs?
Answer: Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
21. What is the difference between clustered and a non-clustered index?
Answer: A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore the table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
22. What are the different index configurations a table can have?
Answer:
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
23. What is the difference between dropping a database and taking a database offline?
Answer: Drop database deletes the database along with the physical files, it is not possible to bring back the database unless you have a backup of the database. When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.
24. What are the operating modes in which Database Mirroring runs?
Answer: Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
25. What is the PRIMARY KEY?
Answer: A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
26. What are the properties and different Types of Sub-Queries?
Answer:
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 sub-query cannot contain an ORDER-BY clause.
- A query can contain more than one sub-query.
Types of Sub-Query
- Single-row sub-query, where the subquery returns only one row.
- Multiple-row sub-query, where the subquery returns multiple rows, and
- Multiple column sub-query, where the sub-query returns multiple columns
27. What is Log Shipping?
Answer: Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping, the transaction log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same DB and can be used as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at a defined interval.
28. Which autogrowth database setting is good?
Answer: Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).
29. What is CHECK Constraint?
Answer: A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
30. What are a Scheduled Jobs or What is a Scheduled Tasks?
Answer: 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 the database, Update Stats of Tables. Job steps give the user control over the flow of execution. If one job fails, the user can configure SQL Server Agent to continue to run the remaining tasks or to stop the execution.
31. What are the advantages of using Stored Procedures?
Answer:
- The stored procedure can reduce 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 the stored procedure code without affecting clients.
32. What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
Answer: Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers to it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from the table and then do the bulk of inserts and to restore those indexes after that.
33. Can SQL Servers link to other servers like Oracle?
Answer: SQL Server can be linked to any server provided it has 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.
34. What is SQL or Structured Query Language?
Answer: SQL is a language which is used to communicate with the database and this language supports operations like insertion, updation, retrieval, and deletion.
35. Explain the Relational Database Management System (RDBMS)?
Answer: RDBMS is a database management system which is used to maintain the data records in the tables and also indices in tables. Relationships can be created to maintain the data in the table.
36. What are the differences between Left join and Inner join in Sql Server?
Answer: The left join will return all the rows from the left table and matching rows from the right table. “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.
Inner join matches the common records in two tables joined. In Inner join, each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
37. List out the differences between the Clustered Index and Non-Clustered Index in Sql Server?
Answer: Clustered Index – Clustered Index will reorder the number of records in a table and by default, the reordering will be done on the basis of the primary key as it default acts like a clustered index.
Non-Clustered Index – Non-Clustered Index depends on the clustered index internally. Leaf nodes will not be data pages as in clustered index instead it will have index rows, which acts as a pointer to point to the root node of the clustered index.
38. List the different types of collation sensitivities in Sql Server?
Below is the list of collation sensitivities in sql server –
- Case sensitivity
- Accent sensitivity
- Kana Sensitivity
- Width sensitivity
39. Explain Mixed authentication mode of sql server?
Answer: A mixed-mode of authentication can either use SQL Server authentication or Windows authentication. If the user opts using windows authentication then the validation will happen in the operating system level and if the user opts for Sql server authentication then the password has to be set up while installing.
40. List out different types of normalizations in Sql Server and explain each of them?
Answer:
Below are the types of normalizations –
- 1 NF – Removing the duplicate records from the table by assigning the primary key to a table.
- 2 NF – Meet all the requirements of 1 NF and create the relationship between the tables and segregate the data storing between multiple tables.
- 3 NF – Meet all the requirements of 2 NF. Remove the list of columns from the table which does not meet the primary key constraint.
- 4 NF – Tables should not more than two relationships
- 5 NF – Practical constraints on info for justifying the many-to-many relationships.
41. Why use “IN” clause in Sql Server?
Answer: “IN” clause is used to specify multiple values in the WHERE clause. For example
SELECT * FROM Employees WHERE City IN (‘Bangalore’,’Kochin’)
42. What is Normalization and DeNormalization in Sql Server?
Answer:
- Normalization – It’s the process of minimizing dependency and redundancy by properly organizing the fields or columns of the table.
- DeNormalization – It is the process of accessing the data from higher to lower normalization forms.
43. Explain the properties of a relational table?
Answer:
Below is the list of properties relational table should have –
- Column value of any kind.
- The insignificant sequence of columns.
- The insignificant sequence of rows.
- Unique name for each column.
- Atomic values.
44. What is ACID mean in Sql Server?
Answer: ACID is used for evaluating application and database architecture. Below are the ACID properties –
- Atomicity
- Consistency
- Isolation
- Durability
45. Explain unique key in Sql Server?
Answer: Unique Key constraint will enforce the uniqueness of values in the list of columns of the table. No duplicate values are allowed. The unique key will allow NULL in one row, unlike Primary Key.
46. What is the use of “JOIN” in Sql Server?
Answer: “JOIN” is used to get the data from multiple tables by joining those. Keys created in the tables will play a major role in the “JOIN”.
47. List out the differences between Global and Local temp tables in Sql Server?
Answer: Global temp tables can be created with – “##” and it will be visible to all active sessions and this temp table will be deleted when all active sessions are abandoned or disconnected. Local temp table will be visible to only to the user who created and users of another session will not be able to see this. And this will be deleted once the table creator session is abandoned or disconnected.
48. What are the differences between Left join and Inner join in Sql Server?
Answer: The left join will return all the rows from the left table and matching rows from the right table. “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.
Inner join matches the common records in two tables joined. In Inner join, each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
49. Why use UPDATE_STATISTICS command in Sql Server?
Answer: This command is used to update the index of the table whenever there is a bulk insertion or updation or deletion in the table.
50. Which operator do you use to return all of the rows from one query except rows are returned in a second query?
Answer: You use the EXCEPT operator to return all rows from one query except where duplicate rows are found in a second query. The UNION operator returns all rows from both queries minus duplicates. The UNION ALL operator returns all rows from both queries including duplicates. The INTERSECT operator returns only those rows that exist in both queries.
Note: Browse latest SQL Interview Questions and SQL Tutorial. Here you can check SQL Server Training details and SQL Videos for self learning. Contact +91 988 502 2027 for more information.