New 31 Big Data Interview Questions For Freshers
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.
Top 60 Advanced SQL Interview Questions And Answers Pdf
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:
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. (interview questions and answers)
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.
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.