MS SQL Developer Job Interview Questions Answers

Here I will share some of very frequently asked SQL Server Interview Question Answers for experienced SQL developers

1. What are different types of query supported by SQL?
   • DDL (Data Definition Language): Query that are used to define a database structure such as tables, view etc. Create, Alter, and Drop
   • DML (Data Manipulation Language): Query that are used to manipulate the data in records, Insert, Update, and Delete
   • DCL (Data Control Language): query used to set privileges such as Grant and Revoke database access permission to the specific user
2. Which constraints we use while creating sql database?
   • NOT NULL: That indicates that the column must have some value and cannot be left null
   • UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column
   • PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one columns to identify the particular record with a unique identity.
   • FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
   • CHECK: It is used to ensure whether the value in columns fulfills the specified condition
3. What's the difference between primary key and unique key?
   All primary key values are unique, but all unique values are not like primary values, here are some differences.

   • All Primary key values are unique in a table, value cannot be NULL, and they create a clustered index on the column
   • All Unique key values are unique in a table, but one value can be NULL, and they create non-clustered index on the column
4. What are the different type of JOINS in SQL?
   There are 4 major types of join

   • INNER JOIN
   • LEFT JOIN (LEFT OUTER JOIN)
   • RIGHT JOIN (RIGHT OUTER JOIN)
   • FULL JOIN (FULL OUTER JOIN):
     This joins returns all when there is a match either in the RIGHT table or in the LEFT table.

   Learn more about SQL Joins
5. What are the transaction controls?
   There are 3 transaction controls

   • SET TRANSACTION: Set the name of transaction
   • COMMIT: save all changes made through the transaction
   • ROLLBACK: roll back the transaction, all changes made by the transaction are reverted back

   Learn more about SQL Transaction

6. How to create temp table?
   Temp table is created with single hash sign like Create TABLE #tempStudents

   Create TABLE #tempStudents (
       [StuId] [bigint] NULL,
   );
   
   

   Temp tables are created under tempdb database=> temporary tables

   When we use double hash (##) that become global.

7. How many Aggregate Functions are available in SQL ?
   There are 7 aggregate functions in SQL

   • AVG(): AVG function returns the average value from specified columns
   • COUNT():COUNT returns number of table rows
   • MAX():MAX returns largest value among the records
   • MIN():MIN returns smallest value among the records
   • SUM():SUM returns the sum of specified column values
   • FIRST():FIRST returns the first value
   • LAST():LAST returns Last value
8. Differences between truncate and delete in sql server?
   • The TRUNCATE command is like a DELETE command without the WHERE clause. TRUNCATE statement executes very fast;
   • TRUNCATE command reset the identity column value, DELETE does not do that.
   • TRUNCATE is considered a DDL command; whereas, DELETE is DML
   • We use TRUNCATE command to remove all data from any table (consider testing scenario), rarely used.
   • TRUNCATE is executed using a table lock and whole table is locked for remove all records.
   • Note: DELETE and TRUNCATE both can be rolled back if Specified within TRANSACTION and if the current session is not closed
9. What is Trigger? How many types of triggers are there in SQL?

   A trigger (is a database object) is a special kind of a stored procedure that executes in response to certain action on the table like insertion, deletion or updation of data

   There are three types of triggers

   • DDL Triggers

     We can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.

     CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server

   • DML Triggers
   • Logon Triggers
     Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established.

     CREATE TRIGGER trigger_name
     ON ALL SERVER
     [WITH ENCRYPTION] 
     {FOR|AFTER} LOGON 
     AS
     sql_statement [1...n ] 

   Learn more about SQL Triggers

10. What are the difference between Trigger and Stored procedure in SQL?
    • Stored procedures can return values. But Triggers can’t return a value.
    • A Stored procedure is called directly by a user code. Whereas, a Trigger is fired when some event occurs.
    • We can pass parameters to a Stored procedure. But we can’t pass parameters to a Trigger.
    • We can call stored procedure within a trigger but we can not call trigger within a stored procedure
11. What is isolation level? what are the different isolation levels in sql server ?
    The isolation level of any transaction determines how sensitive your application is to changes other users' transactions make, and consequently, how transaction must hold locks to protect data against these changes.

    • Committed Read
    • Uncommitted Read
    • Repeatable Read
    • Serializable

    default isolation level is "Read Committed"
12. What is the difference between WHERE CLAUSE and HAVING CLAUSE?
    Both clauses are used with some search condition, but HAVING clause is typically used with GROUP BY clause, If GROUP BY clause is not there then HAVING behaved like WHERE clause only.
13. What is the difference between non-clustered and clustered index?

    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; one table can have many non-clustered indexes.

    A clustered index is an index that rearranges the table in the order of the index itself. One table can have only one clustered index.

14. Which TCP/IP port does SQL Server run on?
    By default SQL Server runs on port 1433.
15. What are the different backups available in SQL Server?
    There are five different backups are possible:

    • Full backup

      Full backup is the most common type of backup in SQL Server. this provide complete backup of the sql database.

    • Transactional Log Backup
    • Differential Backup
    • Copy Only Backup
    • File and Filegroup backup
16. What are the advantages of the Mirroring?
    Here are some advantages

    • It has an automatic failover mechanism.
    • The secondary server is synced with the primary in near real-time.
    • It is more robust and efficient than Log shipping.
17. What are the common performance issues in SQL Server?
    Apart from bad query issue, here are some commonly known issue we must consider while checking performance

    • Blocking
    • Deadlocks
    • I/O bottlenecks
    • Missing and unused indexes.
    • Poor Query plans
    • Fragmentation

    You should also read how to improve sql query performance.

18. How we can achieve pagination or paging in sql server?
    we can write pagination in sql query using OFFSET then rows fetch next with select statment OFFSET 5 rows fetch next 6 rows only. here are some examples

    select * from GConsolePages
    ORDER BY Impressions
    OFFSET 5 rows fetch next 6 rows only 
    

    Another similar one with dynamic page number and row number

    declare @pgNo int, @rownumber int 
    set @pgNo=5
    set @rownumber=50
    select * from GConsolePages
    ORDER BY Impressions
    OFFSET ((@pgNo-1)*@rownumber) rows
    fetch next @rownumber rows only
    

19. What is identity in SQL table?
    An identity column in the SQL table automatically generates numeric values. We can be define identity as a start and increment value of the identity column, like identity(1,1) . Identity columns do not need to be indexed.
20. How can we check the SQL Server version?
    We can check the version by running the sql command : SELECT @Version in In query analyzer.
21. What is the PRIMARY KEY in SQL Server?
    SQL primary key is an constant, the primary key is a column values uniquely identify every new row in a table, primary key values can never be reused, primary key can be null, learn more about sql primary key .
22. What is the Foreign KEY in sql table?
    Foreign Key constraints enforce referential integrity.

    When one sql table's primary key field is added to another related sql table to indicate the common field, which relates the second table, that's called a foreign key in sql table.

    Learn more about sql table foreign key example