50 Best SQL Server Interview Questions and Answers
Structured Query Language or SQL is the programming language for manipulating relational databases. Several famous gear combine this technology with effective, proprietary applications. One such instance is the Microsoft SQL Server.
Owing to the mammoth recognition enjoyed by means of SQL Server, many SQL activity interviews call for an awesome understanding of SQL Server. Learning SQL Server needs a robust understanding of:
Databases
Servers
Database management systems, which include MySQL, PostgreSQL, and MongoDB, and
Programming
Top SQL Server Interview Questions and Answers
Here, we have compiled a list of fifty first-class SQL server Interview questions that SQL/database management aspirants will discover immensely beneficial. These questions cover the entirety from the fundamentals of SQL Server to a number of its advanced concepts. Here they are:
Question: Please provide an explanation for the two authentication modes inside the SQL Server.
Answer: Authentication modes are used for authenticating the person in Microsoft SQL Server. Although customers are prompted for it at some stage in the database engine setup, it is able to be changed later. We can use one of the following two authentication modes in SQL Server:
Windows Mode - Enables Windows Authentication and disables SQL Server Authentication. For authenticating, the server takes the pc’s username and password. SQL Server authentication is disabled in the Windows Mode.
Mixed Mode - Enables both Windows Authentication and SQL Server Authentication. Every database calls for a unique username and password in this authentication mode.
To switch between the authentication modes, go to SQL Server Configuration Properties -> Tools -> Security page.
Question: Explain how you may create a table in SQL Server?
Answer: We can create a table in SQL Server using the following code:
create table TableName (column1 datatype, column2 datatype,..., columnN datatype)
For instance, the subsequent code:
create table Dummy
(
Name varchar(20), Address varchar(forty), PhoneNo. Nvarchar(12)
)
Will create a table Dummy with three columns; Name, Address, and PhoneNo.
Question: Can you explain SQL Server Profiler?
Answer: System administrators use the SQL Server Profiler tool to screen activities within the SQL Server. It is used for:
Creating a hint.
Finding out bugs in queries and fixing the same.
Starting, stopping, pausing, and editing the trace outcomes.
Storing the hint effects in a desk.
Watch the trace consequences once the identical is going for walks.
The SQL Profiler is a GUI this is usually used for taking pictures and saving records of one or each event of a record or desk for actual-time or future analysis.
Question: Do you recognize what is a recursive saved procedure?
Answer: SQL Server offers a sort of saved procedure that calls itself. This is called a recursive saved method. Here, the answer arrives repetitively. It is feasible to nest saved methods up to 32 ranges. A recursive stored manner may be known as directly or in a roundabout way.
We can achieve recursion in saved strategies in the following two ways:
Chain Recursion: For achieving a sequence recursion, the mutual recursion process is prolonged.
Mutual Recursion: Indirect recursion is performed the use of the at the same time recursive saved manner.
Question: Draw a evaluation between nearby and worldwide temporary tables.
Answer: Global temporary tables are seen to all customers. These are deleted while the relationship developing them is closed. Local transient tables, alternatively, are seen handiest when there may be a connection. These are deleted once the connection is closed.
Question: What are the limitations in SQL Server?
Answer: Constraints are the set of rules governing the kind of facts permissible for database tables. There are a total of 6 constraints in Microsoft SQL Server:
Check
Default
Foreign Key
Not Null
Primary Key
Unique
Question: Please provide an explanation for the CHECK constraint.
Answer: The CHECK constraint enforces integrity. It is implemented to a column in a desk for limiting the values that may be inserted inside the equal. A column upon which the CHECK constraint is applied can best have some precise values. Following is an example of applying the CHECK constraint in a SQL Server database:
CREATE TABLE Dummy
(
S.No. Int,
Name varchar(255),
Age int,
CONSTRAINT CHK_Dummy CHECK (Age>17)
);
Question: Is it viable to hyperlink SQL Server to different servers?
Answer: Yes, it's miles possible to connect SQL Server to any database server helping the OLE-DB company. Database servers supporting the OLE-DB issuer are:
IBM Informix
Microsoft Access
Oracle Database
Question: Please explain the subquery and its residences.
Answer: A question that may be nested inside the essential query, consisting of a Select or Update declaration, is called a sub-query. Subqueries may be used while an expression is permitted. A subquery is also called an INNER query. Properties of sub-queries are:
It can, optionally, add GROUP BY, HAVING, and WHERE clauses.
It may be introduced to the FROM, SELECT, and WHERE clauses.
It should be positioned on the right aspect of the comparison operator of the primary question.
It must be enclosed in parenthesis. This is due to the fact it's far carried out before the primary question.
It ought to no longer have an ORDER BY clause.
There may be multiple subquery.
Question: Can you give an explanation for the forms of subqueries?
Answer: Subqueries are of 3 types:
Single-row subquery - Returns best a single row.
Multiple row subquery - Returns multiple rows.
Multiple column subquery - Returns a couple of columns.
Question: What is the purpose of the SQL Server agent?
Answer: The purpose of the SQL Server agent is to put in force the duties in-line with the scheduler engine. This makes the roles run at a scheduled date and time. The SQL Server agent is a heritage tool, i.E., it continues going for walks in the historical past as a Windows provider.
Hence, the SQL Server agent performs an critical role in accomplishing not unusual tasks with the aid of a SQL server administrator. As such, it is an critical factor of the SQL Server.
Question: Please give an explanation for scheduled duties within the Microsoft SQL Server.
Answer: For automating strategies which could run on a scheduled time at special everyday periods, we use scheduled duties or jobs. This facilitates in reducing the desired human intervention. The user also can decide the execution order of such scheduled responsibilities.
Question: Explain how exceptions are treated inside the SQL Server?
Answer: We use TRY----CATCH constructs to address exceptions within the SQL Server. Conditions are written inside the TRY block, and the exceptions are caught in the CATCH block.
Question: What are the features in SQL Server?
Answer: Functions in SQL Server are predefined methods of doing some thing. The following points ought to be cited about SQL Server features:
They are compiled every time.
They may be used everywhere.
They don’t permit the usage of try to catch statements.
They must go back a few fee or end result.
They best work with input parameters.
They paintings most effective with SELECT statements.
There are styles of features in SQL Server; integrated and user-described. An SQL Server characteristic should return a result. When we use a characteristic in SQL Server, it ought to specify a go back value type. Microsoft SQL Server has various inbuilt capabilities. They are labeled as follows:
Table-valued Functions
Scalar-valued Functions
Aggregate Functions
System Functions
Aggregate functions
Configuration features
Cursor functions
Data and time functions
Mathematical features
Metadata capabilities
Other features
Hierarchy Id capabilities
Rowset features
Security functions
String capabilities
System Statistical functions
Text and Image features
Question: What is using the FLOOR feature?
Answer: When there may be a need to round up a non-integer value to the preceding integer, the FLOOR characteristic is used. For instance, FLOOR (12.Eight) will return 12.
Question: How will you take a look at locks in a SQL Server database?
Answer: Microsoft SQL Server comes with an built in stored system known as sp_lock to test locks in databases.
Question: Why do we have the SIGN characteristic? What does it return?
Answer: The SIGN function exams whether or not a given variety is wonderful, bad, or 0. It returns:
+1 when the wide variety is high quality,
-1 whilst the wide variety is poor, and
zero while the quantity is zero.
Question: Please differentiate among UNION and UNION ALL instructions.
Answer: The UNION command is used for deciding on similar statistics from tables. The UNION ALL command does the equal however selects all to be had values and doesn’t do away with replica rows.
Question: What is the distinction among CHARINDEX and SUBSTR functions?
Answer: While the SUBSTR characteristic returns a few particular portion of a given string, the CHARINDEX feature returns a positive character's individual position from the given string. For example, SUBSTRING ('String', 1, 3) will return Si, and CHARINDEX ('i', 'String', 1) will return four.
Question: How is the dynamic SQL one-of-a-kind from stored processes?
Answer: Both stored tactics and dynamic SQL are a set of statements. While the stored tactics are stored in a compiled form inside the database, dynamic SQL is dynamically built on the run time. They aren’t saved and finished at the run time publish their dynamic creation.
Question: Can you give an explanation for Collation?
Answer: Collation specifies the sort order in a database desk. It is of 3 sorts:
Binary
Case sensitive
Case insensitive
Question: How can we get the version information of the Microsoft SQL Server?
Answer: We use the subsequent command in SQL Server to fetch the model facts:
Select SERVERPROPERTY('productversion')
Question: Why can we use the SET NOCOUNT ON/OFF assertion?
Answer: The NOCOUNT is set to OFF by default. It returns the total variety of data which might be affected when a command is executed. It can be explicitly became on while there may be no need to show the range of data affected.
Question: Can you give an explanation for Magic Tables inside the SQL Server?
Answer: SQL Server mechanically creates brief tables at the same time as undergoing DML operations, along with Delete, Insert, and Update. These are known as Magic Tables. Triggers use them for information transactions.
Question: What do you imply by triggers in SQL Server?
Answer: In SQL Server, triggers are database items similar to stored approaches. These special types of saved processes fire when a few precise occasion occurs in their respective databases. Triggers are of 3 sorts:
DDL Trigger - Fires in response to DDL command occasions starting with Alter, Create, and Drop.
DML Trigger - Fires in response to DML command events starting with Delete, Insert and Update.
Triggers are certain to tables and execute routinely. There isn't any provision for explicitly calling them. They provide information integrity and are used to get entry to and check data earlier than and publish the change.
Question: How do you update a database in SQL Server?
Answer: We can do so by using the Update command. It may be used to feature or delete new columns, update statistics in a column, and many others. The preferred syntax for the UPDATE command is:
UPDATE TableName SET ColumnName = NewData wherein Condition
Question: Can you explain relationships in SQL Server?
Answer: Relationships are utilized in SQL Server to hyperlink columns of various tables. These are of three sorts:
One-to-One - A single column in a desk has one dependent column in some different desk.
One-to-Many/Many-to-one - A unmarried column in a desk has more than one established column inside the other desk (One-to-many). More than one column in a table has a single dependent column inside the different desk (Many-to-one).
Many-to-Many - Multiple columns in a desk have multiple established columns in a few other table.
Question: Please give an explanation for the idea of database normalization.
Answer: Database normalization refers to the system of organizing tables in a relational database for minimizing redundancy and dependency. It also improves the integrity and overall performance of tables.
Normalization involves dividing bigger tables into smaller tables and defining relationships some of the identical. The database normalization technique follows the following hierarchy:
1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)
4NF (4th Normal Form)
5NF (fifth Normal Form)
6NF (6th Normal Form)
Question: Please provide an explanation for the idea of an index within the Microsoft SQL Server?
Answer: When statistics is considerable, database tables aren’t enough for effectively operating with the identical. So, we index the columns (or views) in them. Technically, an index is a database object created and maintained by way of the DBMS.
Columns that are indexed are called indexed columns. These are ordered and, hence, supports rapid looking. A database desk could have a couple of indexes. SQL Server has two styles of indexes:
Clustered Index - This form of index sorts and stores the statistics based on keys. As rows can be taken care of in best a unmarried order, defining a clustered index in a desk only once is possible. Doesn’t permit Image, Text, and nText records as a clustered index.
Non-clustered Index - This is an index that is created out of doors a database desk. Each table will have as many as 999 non-clustered indexes in SQL Server, where each index can have 1023 columns at max. Like clustered indexes, those don’t assist Image, Text, and nText types of statistics.
Question: Can you provide an explanation for joins in SQL Server?
Answer: We use joins in database operations when there is a need to retrieve information from more than one tables. The records this is retrieved relies upon on things:
The type of be part of used, and
The relationships some of the columns of different tables.
SQL Server has the subsequent kinds of joins:
Full Join
Inner Join/Self Join/Simple Join
Outer Join
Left Outer Join
Right Outer Join
Full Outer Join
Question: What do you recognize about the database engine in Microsoft SQL Server?
Answer: The SQL Server Database Engine, just like SQL Server Agent and other SQL Server additives, runs as a database service. Database services start as quickly because the operating system starts offevolved.
What database services will start with the aid of default is defined in the course of the SQL Server setup. Services offer core working gadget features like occasion logging, file serving, and net serving.
Question: Please give an explanation for Microsoft SQL Server Analysis Services.
Answer: Microsoft SQL Server Analysis Services or SSAS is an internet-based totally analytical processing and information mining tool. It intends to allow database directors to:
Make sense out of information unfold across severa databases, tables, and files.
Provide selection-makers with easy and short access to the information available.
SSAS offers OLAP by permitting DBAs to create, layout, and manipulate multi-dimensional structures containing statistics aggregated from various statistics assets.
The integrated SQL Server device also capabilities a variety of records mining algorithms to dig out information from exclusive assets. SSAS gives short outcomes from distinct statistics assets as quickly as a question is carried out. Analysis Services relies on a client-server structure.
Client Architecture - SASS capabilities a skinny consumer issue structure. As all calculations and queries are resolved simplest by means of the server, a server to purchaser connection is required for every request. Several carriers offer SSAS for supporting numerous programming languages. The communication between these vendors is made viable using SOAP packets.
Server Architecture - SSAS has an application named Msmdsrv.Exe. It is a server component that runs as a Windows provider. It consists of many components, which include a question processor and an XMLA listener. Msmdsrv.Exe performs the subsequent things:
Caching gadgets,
Creating aggregations,
Handling transactions,
Managing metadata,
Managing server assets,
Parsing statements obtained from customers,
Processing calculations,
Scheduling queries, and
Storing dimension and cell data.
Question: What is denormalization in databases?
Answer: Denormalization is the technique of deliberately introducing redundant information in database tables for optimizing performance. This method trades-off enhancement with the analyzing overall performance for a few degradation inside the write overall performance.
P.S. - It must be cited that un-normalized and denormalized databases are completely one-of-a-kind standards. An unnormalized database is one that hasn’t been normalized. On the other hand, a denormalized database is a database that is first normalized after which denormalized.
Question: Please provide an explanation for the idea of a standby server. What are its kinds?
Answer: When the primary database server goes offline and a reliant software desires it continuously, a standby database server fills its footwear. Standby servers are of three types:
Hot standby - Both primary and standby servers run in-line. Both servers include same information in this case.
Warm standby - Here, the standby server runs inside the background of the primary server. Data contained by the primary server is mirrored to the standby server at regular durations.
Cold standby - In this state of affairs, the standby server is called best while the number one server studies a failure. This sort of standby server is utilized in instances wherein there may be:
No frequent change in information, or
The reliant application is non-essential.
Question: What TCP/IP port does the Microsoft SQL Server run in? Is it feasible to trade the identical?
Answer: The SQL Server runs on port 1433. Yes, it is feasible to exchange it. We can alternate it thru the network utility TCP/IP properties.
Question: What is DBCC in SQL Server? Why can we use it?
Answer: DBCC way Database Consistency Checker. It is a SQL Server command supposed for checking the consistency of a database. It allows to:
Monitor and evaluation the preservation of databases and tables, or
For validating operations accomplished on a database or desk.
When a DBCC command is done, the database engine creates a database image and maintains in a regular transactional kingdom. Afterward, the database engine runs exams towards this stored database photo and drops the equal after finishing the command. Following are some examples of the usage of the DBCC command:
DBCC CHECKALLOC - Checks and guarantees that all pages in a database are allocated efficaciously.
DBCC CHECKDB - Ensures that each one tables in a database and the indexes are linked correctly.
DBCC CHECKFILEGROUP - Checks all desk report corporations for harm.
Question: Can you explain Log Shipping?
Answer: Log delivery refers back to the method of automating the backup and transaction log document of a database on a number one database server after which restoring the identical on a standby server. This technique is supported with the aid of numerous popular database servers, such as:
4D Server
Microsoft SQL Server
MySQL
PostgreSQL
The main reason of log shipping is to increase database availability. The log backups are implemented, specifically on each secondary database. Log delivery involves the subsequent three steps:
Step 1 - Take a backup of the transaction log report on the number one server instance.
Step 2 - Copy the log file at the secondary server instance.
Step three - Restore the log backup record onto the secondary server example.
Question: Please provide an explanation for the distinctive sorts of replication inside the Microsoft SQL Server.
Answer: Replication is a way in database servers for boosting database availability. It is of three sorts in the Microsoft SQL Server:
Merge Replication - Groups the information from extraordinary resources into a unmarried, centralized database. Generally used inside the server to the purchaser surroundings, merge replication is suitable in scenarios where numerous subscribers update the same information at specific instances.
Snapshot Replication - It distributes information in the identical manner as it appears sooner or later in time. It is the maximum appropriate replication approach in scenarios wherein records doesn’t change regularly.
Transactional Replication - It is the procedure of distributing facts from the publisher to the subscriber. Usually used inside the server to the server environment. Ideal for instances wherein there may be a demand for incremental exchange propagation to the subscriber.
Question: What are a few famous 1/3-birthday celebration equipment for SQL Server?
Answer: Microsoft SQL Server is one of the main database management structures. Hence, there's no scarcity of third-birthday party equipment to be had for the identical. Here are some of the maximum popular ones:
LiteSpeed by using Quest Soft - Used for making ready backup and repair.
SQL Backup 10 by using RedGate - Used for automating the SQL Server backup process.
SQL Check by way of Idera - Used for monitoring server sports and memory degrees.
SQL Doc 2 with the aid of RedGate - Used for documenting the databases.
SQL Prompt via RedGate - Offers IntelliSense for SQL Server.
There are numerous blessings of the usage of 0.33-party equipment in Microsoft SQL Server, along with:
Better insights and data.
Enhanced visibility into the transaction log and transaction log backups.
Enterprise view of the backup and restoration surroundings.
Faster backup and repair.
Flexible backup and restoration alternatives.
Recovery of man or woman database items.
Saves time.
Secure backups with encryption.
Question: What do you apprehend by means of Patches and Hotfixes within the SQL Server?
Answer: A patch is a application used for fixing a sure problem(s) within the device and ensuring the identical security. Hotfixes are proprietary patches released by using Microsoft. These are designed to deal with distinctive issues, mostly recently-discovered security flaws.
Hotfixes respond proactively towards bugs. This is because they're designed to be applied to stay systems. A Hotfix can be a unmarried document or more than one files.
Question: Please enumerate the maximum common hint flags used in SQL Server.
Answer: Trace flags are for SQL Server what the if statement is for programming languages. A hint flag units specific traits for the SQL Server. Some of the most commonplace hint flags used within the SQL Server are:
1118 - Do Force uniform extent allocations as opposed to blended page allocations
1204, 1205, 1222 - Deadlock Information
1807 - Network Database files
4013 - Log Record for Connections
4022 - Skip Startup Stored Procedures
8755 - Disable Locking Hints
Question: What are the numerous encryption mechanisms within the SQL Server?
Answer: SQL Server gives guide for various encryption mechanisms to protect facts. These are:
Asymmetric keys
Certificates
Symmetric keys
Transact-SQL capabilities
Transparent Data Encryption
Question: Please give an explanation for the Filtered Index.
Answer: An index created with a WHERE clause is referred to as a Filtered Index. It is used to clear out certain rows in a desk. This facilitates in enhancing index maintenance and question performance and lowering index storage fees.
Question: Can you provide an explanation for a few services in SQL Server?
Answer: Microsoft SQL Server capabilities a number of offerings for running efficaciously with large databases. Some of these are:
Data Quality Services - DQS facilitates performing a range of statistics fine obligations, together with correction, enrichment, and standardization. It gives the following capabilities for resolving data fine problems:
Data cleaning
Knowledge base
Matching
Profiling and tracking
Reference information services
Integration Services - A platform for growing excessive-overall performance information integration and workflow answers, such as ETL operations for records warehousing. Includes graphical tools and wizards for constructing and debugging packages.
Master Data Services - MDS addresses demanding situations approximately analytical and operational master records management. It affords a grasp data hub for centrally preserving, dealing with, and organizing the grasp records. MDS is constructed on top of the SQL Server and the WCF (Windows Communication Foundation) APIs.
Reporting Services - A platform for reporting that consists of processing additives primarily based at the multilayered structure of the SQL Server Reporting Services. Processing components interact amongst themselves to retrieve facts and supply a record. Reporting services has two fundamental additives:
Processors, and
Extensions
Question: Please explain COALESCE in Microsoft SQL Server.
Answer: The COALESCE feature returns the primary non-null expression inside the arguments having more than one columns. The function accepts all varieties of values but returns simplest the non-null gift inside the expression. General syntax:
COALESCE (expression1, expression2, expression3,.......,expressionN)
Question: Do which SQL Server table holds the stored manner scripts?
Answer: The saved process scripts are saved inside the Sys.SQL_Modules desk. The name of the stored tactics, but, are stored inside the Sys. Procedures desk.
Question: Please provide an explanation for COMMIT and ROLLBACK instructions.
Answer: Statements accompanying the COMMIT command grow to be persistent inside the database. Statements, however, with the ROLLBACK command are reverted to a preceding nation.
Question: How is nvarchar extraordinary from varchar?
Answer: Both varchar and nvarchar data types are nearly the same. The difference between the 2 is that nvarchar can save Unicode characters for unique languages, even as varchar can’t. Also, nvarchar calls for greater space in assessment to the varchar records type.
Question: Can you tell the difference between GETDATE and SYSDATETIME functions?
Answer: Both the instructions perform the identical operation, i.E., fetching the present date and time. The distinction between the 2 is that even as GETDATE offers out time in milliseconds, SYSDATETIME gives out nanoseconds. Therefore, SYSDATETIME is extra correct.
Question: Do you know in which the SQL Server shops usernames and passwords?
Answer: Usernames and passwords in the SQL Server are saved in sys.Server_principals and sys.Sql_logins. While usernames are saved in a regular textual content form, passwords are stored in a cryptic shape.
Conclusion
That completes our list of the 50 best SQL Server interview questions. These questions will help you prepare for an upcoming database/SQL-based interview or self-check yourself in opposition to your DBMS/SQL Server information. You may also need to check out SQL books and SQL courses for your reference.
