1. CodersCay »
  2. SQL Server »
  3. Experience SQL Server/Developer Interview Questions and Answers

Published On: 11/23/2018

CodersCay Logo

Experience SQL Server/Developer Interview Questions and Answers

Collection of recent, popular scenario based SQL Server/SQL developer Interview questions and answers for freshers, beginners and experienced candidate/professional.

SQL Server Interview Questions

SQL Server/Developer Interview Questions and Answers

  1. What is the difference between ROW_NUMBER, RANK, DENSE_RANK and NTILE?


    ROW_NUMBER, RANK, DENSE_RANK, NTILE are functions in SQL server and returns numeric output by different sequence order. These functions are having some similarities and significant difference....Read More

  2. Difference between physical and logical joins in SQL Server?


    Logical Joins: Logical joins are actual joins that we are applying in our SQL queries like INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, CROSS APPLY and OUTER APPLY.

    Physical Joins: Physical joins are implemented by SQL Engine as internally to bring the matching records from the tables. These are classified as three different types such as Nested Loop, Merge Join and Hash Join. In Execution Plan, we can see these three joins.

  3. What are the physical joins available in SQL Server and their usage?


    Nested Loop: The Nested loops join compares each row from one table to each row from the other table looking for rows that satisfy the join predicate. 
    The total number of rows compared and, thus, the cost of this algorithm is proportional to the size of the outer table multiplied by the size of the inner table. Since this cost grows quickly as the size of the input tables grow, in practice we try to minimize the cost by reducing the number of inner rows that we must consider for each outer row.

    Merge Join: The Merge Join algorithm is the fastest of them all. The algorithm is actually a two-step process. The first step is to sort both inputs in the same order. The second step is the Merge Join step. Here the rows from both inputs get matched together. The Merge part is the amazingly fast part. So if both inputs are sorted already because of an index or because of another sort requirement in the same query, the Sort Merge Join algorithm is the first choice. But if the inputs are not sorted, it rarely makes sense for SQL Server to first sort them.

    Hash Join: The Hash Join algorithm is a good choice, if the tables are large and there is no usable index. Like the Sort Merge Join algorithm, it is a two-step process. The first step is to create an in-memory hash index on the left side input. This step is called the build phase. The second step is to go through the right side input one row at a time and find the matches using the index created in step one. This step is called the probe phase.

  4. What is the difference between JOIN and CROSS APPLY?


    CROSS APPLY is treating as INNER JOIN and it can returns only matching records available in both sides.
    The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using ON clause comparison operator.
    CROSS APPLY will be used with table-valued functions(TVF) and do not have ON clause comparison operator.

  5. What are the temporary tables available in SQL Server and when will you choose each type?


    Table Variable: Table variables (DECLARE @temp_table TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.

    Local Temporary Table: Local temporary tables (CREATE TABLE #temp_table) are visible only to the connection that creates it, and are deleted when the connection is closed.

    Global Temporary Table: Global temporary tables (CREATE TABLE ##temp_table) are visible to everyone, and are deleted when all connections that have referenced them have closed.

    Another method that we can achieve by Tempdb permanent tables (USE tempdb CREATE TABLE table) are visible to everyone, and are deleted when the server is restarted. 

  6. What is Locking, Blocking, Dead Locking and when it will happen?


    Locking: Lock is acquired when we are reading or modifying the data on table, the process will lock the particular object to perform the operation.

    Blocking: Once the Lock acquired, the other process should wait until the current process completed. The blocking chain is like a queue, once the blocking process is complete, the next processes can continue.

    Dead Locking: Deadlock occurs when one process is blocked and waiting for a second process to complete its work and release locks, while the second process at the same time is blocked and waiting for the first process to release the lock.

  7. Can we insert NULL record on clustered index column?


    Yes. The Clustered index allows to insert NULL and duplicate record on clustered indexed column. The Clustered index only block the NULL and duplicate records if the column created clustered index with PRIMARY KEY Constraint.

  8. What are the Recovery Models available in SQL Server and usage?


    SQL Server backup and restore operations occur within the context for the recovery model of the database. Recovery models are designed to control transaction log maintenance.

    FULL:
    1. Full recovery model will trace all the database operations to the log file.
    2. The full recovery model is always best one for production servers.
    3. In the full recovery model we can take all kinds of backups like full, differential, transaction log and point in time recovery(PTR) too.

    BULK LOGGED: Bulk-logged recovery model is similar to FULL recovery model with the exception that bulk data modification operations(Index Creation, SELECT INTO, INSERT SELECT, BCP, BULK INSERT) are minimally logged in this case and hence it reduces the performance impact but at the same time, you might not be able to do point-in-time restore.

    SIMPLE: Simple recovery mode will not keep the log fils permanently. When a SQL statement executes, changes are written to the data and log files, but they are not kept in the log file for long before being truncated.

  9. Can we use HAVING clause without GROUP BY clause?


    Yes. We can use HAVING clause without GROUP BY clause, but aggregate function is needed.

  10. What is MAXDOP? When and How will you change it?


    MAXDOP - Maximum Degree of Parallelism controls the number of processors that can be used to run a single SQL statement using a parallel execution plan. The default value is zero which is indicating all available processors can be used.
    We can change this value by using "SP_CONFIGURE 'max degree of parallelism', 'value'".

  11. What are the ISOLATION level available in SQL Server?


    There are five different ISOLATION LEVEL available in SQL Server.

    READ COMMITTED: Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.

    READ UNCOMMITTED: Specifies that statements can read rows that have been modified by other transactions but not yet committed.

    REPEATABLE READ: Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

    SNAPSHOT: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

    SERIALIZABLE: Statements cannot read data that has been modified but not yet committed by other transactions.
    No other transactions can modify data that has been read by the current transaction until the current transaction completes.
    Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

  12. What is the default ISOLATION level in SQL Server?


    READ COMMITTED is the default isolation level in SQL Server.

  13. How will you perform the data comparison between two tables?


    There are multiple ways to compare the data comparison between two tables by using LEFT JOIN, TABLEDIFF Utility and etc.

    SQL Query for LEFT JOIN

    SELECT
    -----
    ST.Column1,
    ST.Column2,
    ---
    FROM SourceTable ST
    LEFT JOIN DestinationTable DT ON DT.Column1 = ST.Column1
    WHERE DT.Column1 IS NULL

    Using TABLEDIFF Utility:The TABLEDIFF utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the comparison task.

    tablediff -sourceserver localhost\sqlsrv2012
    -sourcedatabase tablediffdb
    -sourcetable difftest1
    -destinationserver localhost\sqlsrv2008r2
    -destinationdatabase tablediffdb
    -destinationtable DiffTest1



  14. What is the difference between physical and logical reads and how will you evaluate it?


    There are two different components mainly used in buffer management to access data from disk such as Physical reads and Logical reads.
    Physical reads that reads the data from disk and keep the data in buffer pool if the data is not available in buffer pool.
    Logical reads that reads the data from buffer pool and returns to query result which performed by users.

  15. What is Statistics in SQL Server?


    When we are executing the SQL Query on SQL Server, there are multiple ways the SQL engine to bring the data from the server. The statistics is maintaining the best approach of execution plan for the query result for future purpose.

  16. What is FILL FACTOR and what is best value of FILL FACTOR?


    A simple definition about fill factor, fill factor is a value that it keeps empty space on each page to accommodate new rows or modify rows which are inserted or modified on the table. Fill factor is another way to improve the query performance in SQL server which is configurable as a value in server and index level.. 

  17. What is NOLOCK in SQL Server and When will you use this?


    NOLOCK is also called as "Dirty Read" and it is equivalent of using READ UNCOMMITTED as a transaction isolation level. We can read the data that have been modified by another transaction but not committed. We can use this object when the table is accessed by continuous in SELECT Query.

  18. How will you identify the duplicate records in the table and how will you remove it?


    We can identify the duplicate records by using ROW_NUMBER() with PARTITION BY clause. We can remove duplicate giving a sequence for duplicating data and removed the data by sequence id from the original data.
    For Instance, if we take employee table and we need to remove duplicate employee based on employee id field.

    WITH CTE
    AS
    (SELECT ROW_NUMBER() OVER(PARTITION BY Employee_ID ORDER BY Employee_ID) AS RowNumber, Employee_ID, Employee_Name
    FROM Employee
    )
    DELETE FROM CTE WHERE RowNumber > 1

  19. What is RAISERROR method and what are the parameters can have?


    RAISERROR returns system/user defined error or warning message to application if problem occurred while executing the SQL Statement. RAISERROR method can have specific error number, error severity, error state and error Message.

    ERROR_SEVERITY(): Error Severity is a number which is associated with error message that can be identified what type of error. The error severity value between 0 and 25.

    Upto 10 are informational messages.
    11-16 are considered errors that can be fixed by the user.
    17-19 are considered Non-Fatal errors in Sql Server Resources, Engine and other stuff.
    20-25 are considered Fatal Error which causes sql server to shut down the process immediately.

    ERROR_STATE(): Error State will pin point the location where error occurred in your code. For instance if you have a 1000 lines long stored procedure and you are raising errors in different places, Error state will help you to tell which error was actually raised.

    ERROR_MESSAGE(): As the name implies, Error Message returns textual information about the error details.

  20. What is TRY-CATCH? Why do we need to use this?


    Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

    BEGIN TRY
    { sql_statement | statement_block }
    END TRY
    BEGIN CATCH
    [ { sql_statement | statement_block } ]
    END CATCH

Scenario Based SQL Server/Developer Interview Questions

  1. We have a web application, unfortunately the application got shut down. The application team verified thoroughly and they are suspecting the problem was happened in database side. How will you troubleshoot the issue?
  2. We have two tables and both are having same structure, we want to find out matching records from both tables. How will you do?
  3. We have a table which has more the 20 million records and which is affecting the application performance whenever call this table for DML operations(SELECT and UPDATE). How will you resolve this issue?
  4. We have a database which has some performance issue in certain time period. What are steps will you follow to identify the issue?
  5. We have a employee table with working time shift data. The employee IN and OUT time data are captured by login_time and logout_time columns. The employee can swipe his ID card multiple times per day and each IN and OUT time traced separate rows. The question is how will you write the query to calculate the employee worked hours per day?
  6. We have two tables like TableA and TableB. We want to copy the data from TableA to TableB those are not available in TableB. What are the queries you follow and which one is best?


No comments:

Post a Comment