Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence, and analytics applications in corporate IT environments. Microsoft SQL Server is one of the three market-leading database technologies, along with Oracle Database and IBM’s DB2.
Here are some of the most frequently asked SQL Server Interview Questions to make you familiar with the type of questions that can be asked during a Job Interview related to the SQL SERVER:
1. What are DBMS and RDBMS?
A database management system or DBMS is system software that can create, retrieve, update, and manage a database. It ensures the consistency of data and sees to it that it is organized and easily accessible by acting as an interface between the database and its end-users or application software. DBMS can be classified into four types:
- Hierarchical Database: It has a tree-like structure with the data being stored in a hierarchical format. The parent in a database can have multiple children, but a child can have only a single parent.
- Network Database: This type of database is presented as a graph that can have many-to-many relationships allowing children to have multiple children.
- Relational Database: It is the most widely-used and easy-to-use database. It is represented as a table and the values in the columns and rows are related to each other.
- Object-oriented Database: The data values and operations are stored as objects in this type of database, and these objects have multiple relationships among them.
RDBMS stores data in the form of a collection of tables. The relations are defined between the common fields of these tables. Microsoft SQL Server, MySQL, IBM DB2, Oracle, and Amazon Redshift are all based on RDBMS.
2. What do you understand by SQL Server Agent?
SQL Server Agent is a Windows service that is used to schedule and execute jobs. Here, each job contains one or more steps, and each step contains a task. So, the Server Agent uses the SQL Server to store job information and run a job on a schedule.
The main components of the SQL Server Agent are Jobs, Schedules, Operators, and Alerts.
3. What is MS SQL Server?
- It is a software, developed by Microsoft, which is implemented from the specification of RDBMS.
- It is also an ORDBMS.
- It is platform-dependent.
- It is both GUI and command-based software.
- It supports SQL (SEQUEL) language which is an IBM product, non-procedural, common database and case insensitive language.
4. What are the features of MS SQL?
- Break down data silos
- Run SQL Server anywhere
- Intelligent database capabilities
- Fewest vulnerabilities
- Built-in security and compliance
- Always encrypted data enclaves
- Maximum availability
- Accelerated database recovery
- Visual data exploration
- Highly scalable cloud services
There are so many other features as well you can visit their official website.
5. What is some Usage of SQL Server?
- To create databases.
- To maintain databases.
- To analyze the data through SQL Server Analysis Services (SSAS).
- To generate reports through SQL Server Reporting Services (SSRS).
- To carry out ETL operations through SQL Server Integration Services (SSIS).
6. What are some advantages of MS SQL?
- To install different versions on one machine.
- To reduce cost.
- To maintain production, development, and test environments separately.
- To reduce temporary database problems.
- To separate security privileges.
- To maintain standby server.
7. What are the two authentication modes in SQL Server?
There are two authentication modes –
- Windows Mode
- Mixed Mode
Modes can be changed by selecting the tools menu of SQL Server configuration properties and choosing the security page.
8. Can SQL Servers be linked to other servers?
SQL Server can be connected to any database which has an OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.
9. Which TCP/IP port does SQL Server run on?
By default SQL Server runs on port 1433.
10. What is the recovery model? List the types of recovery models available in SQL Server?
The recovery model tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model. It also tells the SQL server which backup is possible in a particular selected recovery model.
There are three types of recovery models:
- Full
- Simple
- Bulk-Logged
11. What are the subsets of SQL?
SQL queries are categorized into four main categories:
- Data Definition Language (DDL)
DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.- CREATE: Creates databases, tables, schema, etc.
- DROP: Drops tables and other database objects.
- DROP COLUMN: Drops a column from any table structure
- ALTER: Alters the definition of database objects
- TRUNCATE: Removes tables, views, procedures, and other database objects.
- ADD COLUMN: Adds any column to the table schema.
- Data Manipulation Language (DML)
These SQL queries are used to manipulate data in a database.- SELECT INTO: Selects data from one table and inserts it into another
- INSERT: Inserts data or records into a table
- UPDATE: Updates the value of any record in the database
- DELETE: Deletes records from a table
- Data Control Language (DCL)
These SQL queries manage the access rights and permission control of the database.- GRANT: Grants access rights to database objects
- REVOKE: Withdraws permission from database objects
- Transaction Control Language (TCL)
TCL is a set of commands that essentially manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions.- COMMIT: Commits an irreversible transaction, i.e., the previous image of the database prior to the transaction cannot be retrieved
- ROLLBACK: Reverts the steps in a transaction in case of an error
- SAVEPOINT: Sets a savepoint in the transaction to which rollback can be executed
- SET TRANSACTION: Sets the characteristics of the transaction
12. What is Mirroring?
Mirroring is a high-availability solution. It is designed to maintain a hot standby server that is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.
13. What is Log Shipping?
Log shipping is nothing but the automation of backup and restores the database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.
14. What is a performance monitor?
Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, networks, etc.
15. What is the difference between a Local and a Global temporary table?
If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.
16. What is the SQL Profiler?
SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to capture the specific data we want.
17. What is the PRIMARY KEY?
The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.
18. What is a UNIQUE KEY constraint?
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.
19. What is the FOREIGN KEY
When one table’s primary key field is added to related tables to create the common field which relates the two tables, it is called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.
20. What is a CHECK Constraint?
A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.
21. What are the differences between SQL Server and MySQL.
| SQL Server | MySQL |
| Developed by Microsoft | Developed by Oracle |
| Licensed software | Open-source software |
| Supports C#, Java C++, PHP, Visual Basic, Perl, Python, Ruby, etc | Supports PHP, Perl, Python, Ruby, etc |
| Doesn’t allow any kind of database file manipulation while running | Allows database file manipulation while running. |
| Allows query cancellation mid-way in the process | Doesn’t allow query cancellation mid-way in the process. |
| While backing up the data, It doesn’t block the database | While backing up the data, it blocks the database |
| Takes a large amount of operational storage space. | Takes less amount of operational storage space. |
| Available in Express and Custom mode. | Available in MySQL Community Edition, and MySQL Enterprise Edition |
22. What are joins in SQL?
Join is a clause that is used to combine records or rows from multiple, two or more, tables in an SQL database based on related columns between the tables.
Practical Questions
23. How can you check the version of SQL Server?
To check the version of SQL Server, you can use the following command:
SELECT@@versionThe @@VERSION gives output as one nvarchar string.
24. What are 3 ways to get a count of the number of records in a table?
SELECT* FROMtable_Name;SELECTCOUNT(*) FROMtable_Name;SELECTrowsFROMindexes WHEREid = OBJECT_ID(tableName) ANDindid< 2;25. Can we rename a column in the output of the SQL query?
Yes, by using the following syntax we can do this.
SELECTcolumn_name ASnew_name FROMtable_name;26. Explain the types of SQL joins
There are four different types of SQL joins:
- (Inner) Join: It is used to retrieve the records that have matching values in both the tables that are involved in the join. Inner join is mostly used to join queries.SELECT * FROM Table_A JOIN Table_B; SELECT * FROM Table_A INNER JOIN Table_B;
- Left (Outer) Join: This type of join is used to retrieve all the records or rows from the left and the matched ones from the right.SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.col = B.col;
- Right (Outer) Join: It is used to retrieve all the records or rows from the right and the matched ones from the left.SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.col = B.col;
- Full (Outer) Join: Full join is used to retrieve the records that have a match either in the left table or the right table.SELECT * FROM Table_A A FULL JOIN Table_B B ON A.col = B.col;