InterviewBit Academy is now Scaler!
Learn Tech Skills from Scratch @ Scaler EDGE

SQL Server Interview Questions




This guide will help you to crack any sql server interview round. If you are looking for sql interview questions you can visit this page.

1. What is SQL Server?

SQL Server is a relational database management system. It provides the functionality of storing and retrieving data to applications. Microsoft developed SQL Server on April 24, 1989. There are several editions of SQL Server serving different audiences.

2. Which TCP/IP port does SQL Server run on?

By default SQL Server runs on port 1433. We can configure the server to listen to a specific port. This can be done by changing the TCP/IP properties from configuration-box in SQL Server.

3. Describe the different index configurations possible for a table?

Different index configurations possible for a table are:

  • A clustered index:

    When only a single clustered index is present.

  • A non-clustered index:

    When only a single non-clustered index is present.

  • Many non-clustered indexes:

    When more than one non-clustered indexes is present.

  • A clustered index and a non-clustered index:

    When a single clustered index and a single non-clustered index is present.

  • A clustered index and many non-clustered indexes:

    When a single clustered index and more than one non-clustered indexes are present.

  • No index:

    When there are no indexes present.

4. What is the difference between clustered and non-clustered index?

Indexing is a way of optimizing database query time. It helps in quickly locating and accessing the data in the database. It avoids full table scans.

Difference Between Clustered and Non-Clustered Index

Clustered Index Non-Clustered Index
It physically sorts the rows of a table based on the primary key or on a column that is unique and not null (generally we use primary key). This is an index structure that is separate from the actual table which sorts one or more selected columns. Think of it as another table with few columns sorted together. For example, if you have a phone_book table where it is sorted by the name of a person and you want to know the count of people living in a specific country. What you’ll do? You’ll make another table with columns ‘country’ and ‘count_of_people’ which is sorted by the country name. Now finding the number of people in a given country will be much faster otherwise you will have to do the full table scan to get the answer. This is a non-clustered index.
Querying data is fast. A typical use case can be where there are range-based queries on the primary key. Querying data is faster when there is no index in a table. A scenario where it can be used is when you want to find no. of people from a particular country in the phonebook. Even if the phonebook is sorted by name, you would like to have some kind of mapping of country with no. of people living there for a faster answer to such queries.
There can only be one clustered index per table. There can be many non-clustered indexes per table.
It doesn’t need extra disk space. It requires extra space to store those indexes.
It is faster than the non-clustered index. It is slower than the clustered index in terms of SELECT queries.
Updation and Insertion are slow as the sorted order has to be maintained (can be faster when insertion always happens at the last, e.g.: Index on ID col). Updation and Insertion are slow as the sorted order has to be maintained.

5. What are the two authentication modes in SQL Server?

Authentication means identifying a user based on its username and password.
Two authentication modes on SQL Server are

  • Windows Authentication:

    It is the default authentication mode in SQL Server. Trusted user and group accounts are authenticated when they login to the system. They do not have to present any additional credentials.

  • Mixed Authentication:

    It supports Windows authentication as well as SQL Server authentication. Windows authentication is the same as above. SQL Server maintains a username and password for authentication of valid users.

You can choose an authentication mode by changing Server Authentication on Security page in Properties of SQL Server Management Studio.

6. What part does database design have to play in the performance of the SQL Server-based application?

Database design plays a vital role in the performance of SQL Server-based applications. Generally, when data is less, people do not care about database design. As when data is less there aren’t any noticeable performance issues because of bad database design. When data grows, there will be noticeable performance issues because of data redundancy. This is where normalization of the database comes into play. Right design principles ensure better performance at the later stage of software development. There will not be much redundant data. This will lead to reduced storage space. Also, it will reduce overhead to maintain consistency in the database.

7. What is a function in SQL Server?

Functions are part of SQL. A function in SQL Server is a group of statements that might take input, perform some task and return a result.
There are two types of function in SQL Server:

  • System Defined Function:

    These functions are built-in ready-to-use and provided by SQL Server. Pass in input parameters if it takes one and get the result.
    Example: Below code show min, max, and sum of ‘salary’ column values from ‘employee’ table

    SELECT MIN(salary) AS MinSalary, MAX(salary) AS MaxSalary, SUM(salary) AS TotalSalary
    FROM employee
    
  • User Defined Function:

    These are the functions that are written by users.

    CREATE FUNCTION getAverageSalary(@salary int)
    RETURNS int
    AS
    BEGIN RETURN(SELECT @salary)
    END
    

=> Check result here

8. What is CHECK constraint?

CHECK constraint is applied to any column to limit the values that can be placed in it. It helps to enforce integrity in the database.
Suppose, your website caters to users between age 18 and 60 years. You can use CHECK to ensure that users who are creating an account have age in that range.

CREATE TABLE Users (
    id int NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    age int CHECK (age >= 18 AND age <= 60)
);

=> Check result here

9. What is a trigger in SQL Server?

The trigger is a special type of stored procedure. It gets invoked automatically when an event like INSERT, UPDATE, DELETE, etc. happens on the database server. You can use it, for example, to enforce referential integrity in the database. Suppose you want to delete an author from the ‘authors’ table. You can use triggers to delete all rows in the ‘books’ table which has ‘author_id’ as of the deleted author.

Types of triggers:

  • DML trigger:

    DML trigger gets fired whenever a user tries to manipulate data using DML(Data Manipulation Language) event on the database server. DML events are INSERT, DELETE, or UPDATE.

  • DDL trigger:

    DDL trigger gets fired whenever a user tries to manipulate data using DDL(Data Definition Language) event on the database server. DDL events are CREATE, ALTER, DROP, etc.

  • Logon trigger:

    Logon trigger gets fired when a LOGON event is raised whenever a user’s session is created.

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT | UPDATE | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
   Declaration-statements
BEGIN
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

10. What are the differences between local and global temporary tables?

Temporary tables are the tables that ephemeral in nature. Temporary tables are useful when we want to work with a small subset of records from a large table. Instead of filtering records, again and again, we can store that small subset of records in a temporary table. We can perform our queries on it.

Difference Between Local and Global Temporary Tables

Local Temporary Tables Global Temporary Tables
Local temporary tables are only visible to that session of SQL Server that has created it. Global temporary tables are visible to all SQL Server sessions.
Local temporary tables are automatically dropped when the session of the SQL Server that has created it is closed. Global temporary tables are dropped when the last session of SQL Server referencing to the global temporary tables is closed.
Local temporary tables are prefixed with a single pound ‘#’ symbol. Global temporary tables are prefixed with double pounds ‘##’ symbol.
SQL Server appends some random numbers at the end of the local temporary table name. SQL Server doesn’t append any random numbers at the end of the global temporary table name.

11. What is the SQL Server Agent?

SQL Server Agent is a background tool for Microsoft SQL Server. It helps the database administrator(DBA) to schedule a job to run after a specific interval of time. These tasks can be scheduling backups, handling reporting services subscription or log-shipping tasks.

12. What are the different types of backups available in SQL Server?

Suppose your server is running fine. Your application is booming all over the internet. Then, due to short-circuiting your servers went on fire. Now, all the data is gone and there’s nothing to show. Scary? It should be. This is the reason we always want to back up our data. So that in case of any disaster like hardware or software failure there will not be any data loss.

There are several types of backup options.

  • Full backup:

    This backup includes all database objects, system tables, and data. Transactions that occur during the backup are also recorded.

  • Transaction log backup:

    This backup records the transactions since the previous backup. Previous backup can be transaction log backup or full backup (whichever happened last). It then truncates the transaction log. Transaction log backup represents the state of the transaction log at the time the backup is initiated rather than at the time the backup completes. Transaction log backup functions incrementally rather than differentially. When restoring transaction log backup, you must restore in the sequence of transaction log backups.

  • Differential backup:

    This backup backs the data that has been altered since the last full backup. Differential backup requires less time than a full database backup. Differential backups record transactions that occur during the differential backup process.

13. What are the scheduled tasks in SQL Server?

Scheduled tasks in SQL Server are predefined steps or tasks. SQL Server Agent automates these tasks. It executes them sequentially and at a scheduled time.

14. What is COALESCE in SQL Server?

In SQL Server, COALESCE function returns the first non-null value from a list. If all the values evaluate to null then it will also return null.

SELECT COALESCE(NULL, 2, 1, 3) 	 /* OUTPUT: 2 */

=> Check result here

15. How exceptions are handled in SQL Server programming?

Exceptions in SQL Server are handled using the try and catch block.

BEGIN TRY
	--code which might raise exception
END TRY
BEGIN CATCH
	--code to run if error occurs in try block
END CATCH

Try block contains the code which might raise exception. Catch block contains the code which runs in case an exception occurs in the try block.

BEGIN TRY
	--divide-by-zero error
    SELECT 5 / 0 AS Error;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_STATE() AS ErrorState,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

The above code raises divide-by-zero error and handles it in the catch block. There are some special functions which are only usable inside the catch block. Outside the catch block they return NULL.

These functions are:

  • ERROR_NUMBER(): As the name says, it returns the error number.
  • ERROR_STATE(): It returns the state number of the error.
  • ERROR_SEVERITY(): This function returns the severity value of the error.
  • ERROR_PROCEDURE(): It returns the name of the stored procedure or function in which the error has occurred.
  • ERROR_LINE(): Returns the line number at which the error has occurred.
  • ERROR_MESSAGE(): Returns the message about the error.

16. What is the reporting services in SQL Server?

SQL Server Reporting Services(SSRS) is a set of tools that helps in generating reports. Businesses can use it for getting visual insights into data.
A common use case can be a shopping store. The owner of the store might want to see how the sales of different products are performing. The owner might also want to see the performances in a particular quarter of a year. What could be better than a beautiful and detailed visualization for this? SSRS does that exact thing. It will help us visualize data, meeting our requirements. We can see charts, graphs, and whatnot.

17. What is log shipping?

Log shipping is a process in which we automate the back-up process of transaction log files. We back-up the files from a primary database server to a secondary(stand by) database server.
To set up the log shipping process you must have sysadmin rights on the server.
Log shipping helps in mitigating the risk of disasters. In case of a disaster like a production server failure, we can use the secondary server.

18. What is the master data services in SQL Server?

SQL Server Master Data Services is a product made by Microsoft to develop the Master Data Management(MDM) solutions. It is built on top of SQL Server for backend processing.
The possibility of inconsistency in the database gives rise to the need for Master Data Management. It becomes especially important when there is a huge amount of data.
Consider a scenario, there is a Credit Card company which maintains a database. There is a table “Customer” which has the “Address” attribute. There is another table “Bills” which also has the “Address” attribute for a customer. Now suppose a customer moves to a different location and changes his address immediately. It gets updated in the “Customer” table but not in the “Bills” table. Now, this is a disaster as all the bills of this particular customer will be sent to the wrong address.
The disaster can be avoided by setting up a master customer data. All the tables would reference there for use. This way there will not be any inconsistency and extra memory consumption.

19. What are Hotfixes and Patches in SQL Server?

Hotfixes in SQL Server are the updates to fix issues which are not released publicly.
Patches in SQL Server are the updates to fix known issues or bugs. Microsoft releases patches publicly.

20. What are the Magic Tables in SQL Server?

There are two magic tables in SQL Server: "inserted" and "deleted". These tables are called magic tables because they do not exist physically.

"inserted" table stores the data of last inserted row or “insert operation”. "deleted" table stores the data of last deleted row or “delete operation”.

Note: An update operation is actually a delete operation + an insert operation i.e., old data is deleted and new data is inserted.

For the sake of example, we assume there is a table with name Employee and columns ‘ID’ and ‘Name’.

SQL Server provides two ways to view data in magic tables.

  • Use Triggers:

    One way is to use triggers on delete, insert or update operations in a table to view the data in “inserted” and “deleted” magic tables.

    CREATE TRIGGER Inserted_Trigger
    ON Employee
    FOR INSERT
    AS
    BEGIN
        SELECT * FROM Inserted
    END
    

    Whenever an insert operation will be done on Employee table, this trigger will be called and this will show the “inserted” table.

  • Use OUTPUT clause:

    Use OUTPUT clause: Another way is to use the OUTPUT clause to view data in “inserted” or “deleted” magic tables. OUTPUT returns the copy of data inserted into or deleted from a table.

    INSERT INTO Employee OUTPUT Inserted.* VALUES('Robert')
    





Practice SQL Server Questions asked in Interviews



Q - Select the correct statement:


Q - Select the wrong statement:


Q - What set of SQL statements is a stored procedure?


Q - Select the wrong statement


Q - The procedures created by a user are:


Q - Select the wrong statement


Q - In SQL Server, stored procedure that is already defined:


Q - Main advantage of using a clustered index over a non-clustered index?


Q - For which kind of statement a function cannot be used?


Q - In SQL Server, what benefit can we get by using user-defined functions?


Q - Select the correct statement:

Q - In SQL Server, where can we apply AFTER trigger:



Click here to start solving coding interview questions