Top 50 SQL Server Interview Questions and Answers

Top 50 SQL Server Interview Questions and Answers

25 Mar 2024
40.8K Views
34 min read

Top SQL Server Interview Questions and Answers: An Overview

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It is designed to run on a central server so that multiple users can access the same data simultaneously. Generally, users access the database through an application. This SQL Server Tutorial, part of our comprehensive SQL Server Certification Course, contains the top 20 SQL Server interview questions and answers, to prepare you for the interview.

Q.1. What is an SQL Server?

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It is designed to run on a central server so that multiple users can access the same data simultaneously. Generally, users access the database through an application.

Q.2. What are the different versions of SQL Server?

There are the following versions of SQL Server have been released at the time of writing this book:

  1. Version
    Year
    Release Name
    8.0
    2000
    SQL Server 2000
    8.0
    2003
    SQL Server 2000 (64-bit)
    9.0
    2005
    SQL Server 2005
    10.0
    2008
    SQL Server 2008
    10.5
    2010
    SQL Server 2008 R2
    11.0
    2012
    SQL Server 2012
    13.0
    2016
    SQL Server 2016

Q.3. What new features are coming to SQL Server 2019?

At the time of writing this book, SQL Server 2019 RTM was not released. The following features are coming in the SQL Server 2019 release.

    • The single virtual data layer

    • Data virtualization and Integrating Data

    • No data replication and Managing all data

    • Spark Built-In

    • Unified platform for big data analytics

    • Spark jobs

    • Train machine learning models

Q.4. Can you access or query a remote SQL Server database from a Mac, Linux, or Ubuntu machine?

Yes, you can connect or query your remote SQL Server database from your Mac, Linus, or Ubuntu machines using the Azure Data Studio tool.

    Q.5. What is Azure Data Studio?

    Azure Data Studio is an alternative way to SQL Server Management Studio (SSMS) which you can run only on Windows machines to query, edit, and data development tasks. Azure Data Studio offers a modern editor experience to connect with a remote SQL Server database. It helps us to query and manage data across multiple sources with IntelliSense.

    Q.6. What is the difference between DBMS and RDMS?

    DBMS and RDBMS, both are used to store, manage, and query the data. But both have some important differences as listed below-

    1. DBMS (Database Management System)
      RDBMS (Relational Database Management System)
      DBMS stands for Database Management System and treats data as files internally.
      RDBMS stands for Relational Database Management System and treats data as relations means tables.
      It defines the relationship between the files programmatically.
      It defines the relationship between the relations called tables at the time of table creation.
      It does not impose any constraints or security concerning data manipulation.
      It imposes constraints or security on data manipulation.
      It does not support distributed architecture.
      It supports distributed architecture
      It does not support Client-Server architecture
      It supports Client-Server Architecture
      Only one user can access data at a time
      Multiple users can access the data at the same time
      It satisfies a maximum of 6 to 7 rules of E.F. Codd (Edgar Frank "Ted" Codd) out of 12 rules.
      It satisfies more than 6 to 7 rules of E.F. Codd out of 12 rules.
      Example- File System, XML, FoxPro, IMS
      Example – SQL Server, Oracle, DB2, MySQL

    Q.7. What is normalization?

    Normalization or data normalization is a process to organize the data into a tabular format (database tables) keeping two goals in mind.

      • Reducing data redundancy.

      • Ensuring data dependency.

        A good database design includes normalization, without normalization, a database system may be slow, inefficient, and might not produce the expected result. Normalization reduces data redundancy and inconsistent data dependency.

    Q.8. What are the different normal forms?

    We organize the data into database tables by using normal form rules or conditions. Normal forms help us to make a good database design. There are the following normal forms:

      1. First Normal Form (1NF)

      2. Second Normal Form (2NF)

      3. Third Normal Form (3NF)

      4. BCNF

      5. Fourth Normal Form (4NF)

      6. Fifth Normal Form (5NF)

    Generally, we organize the data up to the third normal form. We rarely use the fourth and fifth normal forms.

    Q.9. What are the differences between char and nchar?

    These data type is used to store characters but these are different in many cases as given below:

    char

    This is a fixed-length characters data type. It takes one byte per character and is used to store non-Unicode characters. Suppose, you declare a field with char(20) then it will allocate memory for 20 characters whether you are using only 10 characters. Hence memory for 10 characters that is empty will be wasted.

    nchar

    This is like a char data type but it takes two bytes per character and is used to store Unicode characters, which means multiple languages (like Hindi, Chinese, etc.) characters in the database.

    Q.10. What are the differences between varchar and nvarchar?

    There are the following differences between varchar and nvarchar:

    varchar

    This is a variable length characters data type. It takes one byte per character and can store non-Unicode characters (like English). This data type allocates the memory based on the number of characters inserted. Hence, no waste of memory.

    nvarchar

    This is like a char data type but it takes 2 bytes per character and is used to store Unicode characters, which means multiple languages (like Hindi, Chinese, etc.) characters in the database.

    Q.11. What is SQL key?

    A key is a single or combination of multiple fields in a table. It is used to retrieve records or data rows from the data table based on the condition. Keys are also used to create a relationship among different database tables or views.

    Q.12. What are the different types of SQL Keys?

    There are the following types of SQL Keys

      1. Super Key - The superkey is a set of one or more than one key that can be used to identify a record uniquely in a table.

        For Example, the Primary key, Unique key, and Alternate key are a subset of Super Keys.

      2. Candidate Key - A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as a Primary Key.

        For Example: In the below diagram ID, RollNo, and EnrollNo are Candidate Keys since all these three fields can work as Primary Key.

      3. Primary Key -The primary key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It cannot accept null, or duplicate values. Only one Candidate Key can be the Primary Key.

      4. Alternate key - An alternate key is a key that can work as a primary key. It is a candidate key that currently is not the primary key.

        For Example:In the below diagram, RollNo and EnrollNo become Alternate Keys when we define ID as Primary Key.

      5. Alternate key

      6. Composite/Compound Key -A Composite Key is a combination of more than one field/column of a table. It can be a Candidate key or a primary key.

      7. Unique Key - The unique key is a set of one or more fields/columns of a table that uniquely identifies a record in a database table. It is like a Primary key but it can accept only one null value and it cannot have duplicate values.

      8. Foreign Key -A Foreign Key is a field in a database table that is the Primary key in another table. It can accept multiple null, and duplicate values.

        For Example, We can have a DeptID column in the Employee table which is pointing to the DeptID column in a department table where it is a primary key.

        Note- Practically in the database, we have only three types of keys Primary Key, Unique Key, and Foreign Key. Other types of keys are only concepts of RDBMS that we need to know.

      Q.13. What are SQL Commands?

      SQL commands are a set of instructions that are used to interact with the database like SQL Server, MySQL Oracle, etc. SQL commands are responsible for creating and doing all the manipulation on the database. These are also responsible to give or take out access rights on a particular database. There are the following types of commands:

        • Data Definition Language (DDL)

        • Data Manipulation Language (DML)

        • Data Query Language (DQL)

        • Transaction Control Language (TCL)

        • Data Control Language (DCL)

      Q.14. What is a Database Table?

      An RDBMS stores the data using one than a database table. A database table manages the data in row and column format. Each row in a table has its primary key which uniquely identifies that row or record. The data associated with tables are physically stored in the database memory.

        Database Table

          Q.15. What is a transaction?

          A transaction is a set of T-SQL statements that are executed together as a unit like a single T-SQL statement. If all of these T-SQL statements are executed successfully, then a transaction is committed and the changes made by T-SQL statements are permanently saved to the database. If any of these T-SQL statements within a transaction fail, then the complete transaction is canceled/ rolled back.

          Q.16. Why use transactions in SQL Server?

          We use transaction in that case when we try to modify more than one table or view that is related to e each other. Transactions affect SQL Server performance greatly. Since, when a transaction is initiated then it locks all the tables’ data that are used in the transaction. Hence during the transaction life cycle, no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

          Q.17. What are the different types of transactions in SQL Server?

          There are the following types of transactions in SQL Server as given below:

            • Implicit Transaction

            • Explicit Transaction

          Q.18. Write SQL queries to get the nth highest and lowest salary of an employee.

          The queries are given below-

          1. Query to get nth (3rd) highest Salary:

            Select TOP 1 Salary as '3rd Highest Salary' 
            from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) 
            a ORDER BY Salary ASC
            

            Query to get nth (3rd) lowest Salary:

            Select TOP 1 Salary as '3rd Lowest Salary' 
            from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC) 
            a ORDER BY Salary DESC 
            

          Q.19. Write an SQL query to get the field name, data type, and size of a database table.

          The query is given below-

          1. SELECT column_name as 'Column Name', data_type as 'Data Type',
            character_maximum_length as 'Max Length'
            FROM information_schema.columns
            WHERE table_name = 'tblUsers' 
            
            SQL query database table with example

          Q.20. What are SQL Joins?

          SQL joins are used to retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in SQL join. In SQL Server, there are three types of joins as given below:

            • Inner Join

            • Outer Join

            • Cross Join

          Q.21. Explain different data types in SQL Server.

          The various data type classifications are as follows:

          • Exact numerics: Whole numbers (integers) and decimal numbers are stored in these data types. Decimal, numeric, money, smallmoney, bit, tinyint, smallint, int, bigint, and so on are among them.
          • Approximate numerics: These data types are used to record numbers with fractional parts, however, they are less precise than exact numerics. Real and float are among them.
          • Date and time: Timestamps, dates, and times are stored in these data types. Time, date, datetime2, datetimeoffset, smalldatetime, and date are among them.
          • Character strings: Text data is stored in these data types. Text, char, varchar, nchar, nvarchar, & ntext are among them.
          • Binary strings: Binary data, such as sounds or pictures, are stored in these data formats. They consist of picture, varbinary, and binary.
          • Others: Data types that don't fit into any of the other categories are included in this category. They consist of spatial geometry types, XML, cursor, hierarchyid, sql_variant, table, rowversion, and uniqueidentifier.

          different data types in SQL Server.

          Q.22. Define DDL Statements in SQL Server.

          A type of SQL command called DDL (Data Definition Language) is used to define data structures and alter data. Tables, views, indexes, and users are just a few of the database objects that it may create, modify, and remove. DDL statements include concepts like CREATE, ALTER, DROP, and TRUNCATE.

          Q.23. Explain DML Statements in SQL Server.

          Data Manipulation Language is shortened to DML. consists of a group of computer languages that are specifically used to alter databases using CRUD activities (create, read, update, and delete data). use the commands INSERT, SELECT, UPDATE, and DELETE.

          Q.24. What are DCL Statements in SQL Server?

          A subset of SQL commands called Data Control Language (DCL) is used to manage who has access to what data in a database. In multi-user database setups, in particular, DCL plays an essential part in guaranteeing security and appropriate data management. It includes GRANT & REVOKE

          Q.25. What are the functions in SQL Server?

          Database objects known as SQL Server functions comprise a collection of SQL statements that work together to carry out a certain task. A function receives input, processes it, and outputs the result. It is important to remember that functions always return a table or a single result.

          functions in SQL Server

          Q.26. Define Views in SQL Server.

          In SQL Server, a VIEW is comparable to a virtual table that holds information from one or more tables. It does not physically exist in the database and does not contain any data. A view name in a database should be unique, much like a SQL table. It includes a list of pre-written SQL queries to retrieve information from the database.

           Views in SQL Server

          Q.27. What are indexes in SQL Server?

          The table or view's columns can be used as building blocks for keys in an index. Because these keys are kept in a structure called a B-tree, SQL Server can locate the row or rows that are connected to the key values with speed and efficiency.

          Q.28. Explain the Stored Procedure.

          An SQL code that has been prepared and saved for later use is called a stored procedure. To avoid writing the same SQL query twice, consider saving it as a stored procedure that can be called whenever needed. A stored procedure can also receive parameters, which allows it to take action based on the parameter value or values that are supplied.

          Q.29. What do SQL Server triggers do?

          You can designate SQL operations that should be carried out automatically when a particular event takes place in the database by using an SQL trigger. A trigger, for instance, can be used to update a record in one table automatically each time a record is added to another table.

          SQL Server triggers

          Q.30. What do SQL Server cursors mean?

          In SQL Server, a cursor is a database object that lets us access and modify individual rows one at a time. All that a cursor is is a pointer to a row. It is always utilized with a SELECT statement attached.

          SQL Server cursors

          Q.31. What is security in SQL Server?

          Data can be shielded from unauthorized access using SQL security. Users can only access the features or capabilities they need. A whole database instance, a table, and a database are the three security levels you can select when setting up a SQL server.

          Q.32. What does SQL Server authentication mean?

          Authentication with SQL Server works similarly. Users connect to the database, which verifies details like port number, domain and instance names, and user account credentials.

          Q.33. What is SQL Server auditing?

          Monitoring and recording system events is a necessary part of auditing a SQL Server server or database. A single instance of server-level or database-level activities, as well as sets of actions, are gathered by the SQL Server Audit object for monitoring. The SQL Server instance level is being audited.

          Q.34. What does SQL Server backup and recovery mean?

          In general, backup and recovery refer to the several approaches and procedures used to safeguard your database from data loss and restore the data if it does occur.

          Q.35. Explain replication in SQL Server.

          A group of technologies known as replication is used to copy and distribute database objects and data from one database to another, synchronizing afterward to preserve consistency across databases. Replication can be used to send data over dial-up, wireless, local and wide area networks, the Internet, and other networks to users who are remote or mobile.

          replication in SQL Server

          Q.36. Describe SQL Server clustered indexes.

          Based on their key values, clustered indexes sort & store the data rows in the table or view. The columns that make up the index definition are these key values. Because the data rows themselves can only be stored in a single order, each table can only have one clustered index.

          SQL Server clustered indexes.

          Q.37. What are non-clustered indexes in SQL Server?

          Search processes are also speeding up by using a non-clustered index. A non-clustered index does not physically specify the sequence in which records are added to a table, unlike a clustered index. In actuality, the data table and the non-clustered index are kept apart.

           non-clustered indexes in SQL Server

          Q.38. What are unique indexes in SQL Server?

          An index that guarantees that the values in the indexed columns are distinct throughout the table is known as a unique index in SQL Server. This indicates that the values in the columns that comprise the unique index cannot be the same in two rows.

          Q.39. What is query optimization in SQL Server?

          Query optimization is the practice of improving SQL queries to minimize resource usage, speed up system execution, and enhance overall system performance. The main goals of this optimization are to reduce pointless data retrieval, streamline join processes, and make better use of available resources.

          Q.40. What is an execution plan in SQL Server?

          The set of instructions known as the SQL Server execution plan, or query plan, specifies the steps that must be taken for the database engine to carry out a query. The primary objective of the query optimizer, which creates the query plans, is to produce the most inexpensive and efficient query plan.

          Q.41. Can you define all the keys in a database table?

          Practically in a database table, you can have only three types of keys: Primary Key, Unique Key, and Foreign Key. Other types of keys are only concepts of RDBMS that you need to know.

          Q. 42. What are the differences between the Primary Key and the Unique Key?

          In SQL Server, we have two keys that distinctively or uniquely identify a record in the database. Both the keys seem identical, but actually, both are different in features and behaviors.
          Primary KeyUnique Key
          Primary Key can't accept null values. The unique key can accept only one null value.
          By default, the Primary key is a clustered index, and data in the database table is physically organized in the sequence of a clustered index.  By default, the Unique key is a unique non-clustered index.
          You can have only one Primary key (it may be a composite primary key, which means a key on multiple fields) in a table.  You can have more than one unique key in a table.
          The primary key can be made a foreign key in another table.  In SQL Server, a Unique key can be made a foreign key in another table.

          Q. 43. What is the difference between Primary Key and Foreign Key?

          The difference between Primary Key and Foreign Key is given below-
          Primary KeyForeign Key
          A primary key uniquely identifies a record in the table. A foreign key is a field in the table that is the primary key in another table.
          Primary Key can't accept null values.A foreign key can accept multiple null values.
          By default, the Primary key is a clustered index, and data in the database table is physically organized in the sequence of the clustered index. A foreign key does not automatically create an index, clustered or non-clustered. You can manually create an index on the foreign key.
          You can have only one Primary key in a table. You can have more than one foreign key in a table.

          Q. 44. What are TCL Commands?

          TCL commands are used to handle changes that affect the data in the database. These commands are used with the transaction or used to make a stable point during changes in the database at which You can roll back the database state if needed. The TCL commands are SAVEPOINT, ROLLBACK, and COMMIT.

          Q. 45. What are DQL Commands?

          DQL commands are used to fetch/retrieve data from database tables. There is only one DQL command which is a SELECT command.

          Q. 46. What are Grant and Revoke Commands?

          The Grant and Revoke commands are explained as:
          • Grant Command: This command is used to permit specific users on specific database objects like table, view, etc.
          • Revoke Command: This command is used to take out permission from specific users on specific database objects like table, view, etc.

          Q.47. What are SQL Constraints or SQL Integrity Constraints?

          Constraints are some rules that enforce the data to be entered into the database table. Constraints are used to restrict the type of data that can be inserted into a database table. Constraints can be defined at two levels as given below-
          1. Column Level: These constraints are defined with the column definition inside a CREATE TABLE statement.
          2. Table Level: These constraints are defined after the table is created using the ALTER TABLE statement.

          Q. 48. What is the need for a SQL view?

          In SQL Server we make views for security purposes since it restricts the user to view some columns/fields of the table(s). Views show only those columns that are present in the query which is used to make a view. One more advantage of Views is, data abstraction since the end user is not aware of all the data present in the table.

          Q. 49. What are Simple View and Complex View?

          • Simple View - When we create a view on a single table, it is called a simple view. In a simple view, we can insert, update, and delete data. We can only insert data in a simple view if we have a primary key and all not null fields in the view.
          • Complex View - When we create a view on more than one table, it is called a complex view. We can only update data in the complex view. We can't insert, or delete data in the complex view.

          Q. 50. What are the different types of SQL Server Views?

          There are two types of Views as given below –
          1. System-Defined Views: System-defined Views are predefined Views that already exist in the Master database of SQL Server. These are also used as template Views for all newly created databases. These system Views will be automatically attached to any user-defined database.
          2. User-Defined Views: These types of views are defined by users. We have two types of user-defined views.
          1. Simple View
          2. Complex View
          Summary

          I hope these questions and answers will help you to crack your SQL Server Interview. These interview Questions have been taken from our newly released eBook SQL Server Interview Questions & Answers. This book contains more than 100+ SQL interview questions.

          This eBook has been written to make you confident in SQL Server with a solid foundation. Also, this will help you to turn your programming into your profession. It would be equally helpful in your real projects or to crack your SQL Server Interview.

          Buy this eBook at a Discounted Price!

          SQL Server Interview Questions & Answers eBook

          Take our free sqlserver skill challenge to evaluate your skill

          In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

          GET CHALLENGE

          Share Article
          Batches Schedule
          About Author
          Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

          Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
          Self-paced Membership
          • 22+ Courses
          • 750+ Hands-On Labs
          • 300+ Quick Notes
          • 55+ Skill Tests
          • 45+ Interview Q&A
          • 10+ Real-world Projects
          • Career Coaching
          • Email Support
          Upto 66% OFF
          KNOW MORE..

          To get full access to all courses

          Accept cookies & close this