SQL Essentials Training & Certification
- 5k Enrolled Learners
- Self Paced
In the era where humongous amount of data gets generated on a day to day basis, data plays a crucial role in decision making for business operations. So, to handle data we need databases and this gives us the need to understand database management systems. With various database management systems, MS SQL Server is one of the most popular relational database management systems. This type of DBMS uses a structure that allows users to identify and access data in relation to another piece of data in the database. So, knowing MS SQL Server opens the doors for you to become a Database Administrator. I believe that you are already aware of these facts and this has made you land on this MS SQL Server Interview Questions article.
In this article on Microsoft SQL Server Interview Questions, I will be discussing the top questions related to MS SQL Server asked in your interviews. These questions are collected after consulting with people having excellent skills in this field.
Let us get started!
Developed by Microsoft
Developed by Oracle
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
SQL Server Agent is a Windows service which is used to schedule and execute jobs. Here, each job contains one or more step, 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.
If an enterprise wishes to take a backup of the company servers at 9:00 pm on every Friday, then you can very well automate this task to let the schedule happen on its own. In a scenario, the backup encounters an error, the SQL Server Agent records the event and notifies the corresponding team.
Before I tell you the different authentication modes in SQL Server, let me tell you that the authentication mode is used for authenticating a user in the SQL Server. The authentication mode is selected while setting up the database engine. So, if you want to know how to set up Microsoft SQL Server, you can refer to my article.
The different authentication modes offered by SQL SERVER are as follows:
|Local Temporary Table||Global Temporary Table|
These tables only exist for the duration of connection or duration of that statement.
These tables exist permanently in the database and only the rows get deleted when the connection gets closed.
Syntax: CREATE TABLE #<tablename>
Syntax: CREATE TABLE ##<tablename>
To check the version of SQL Server, you can use the following command:
The @@VERSION gives output as one nvarchar string.
It may often happen that you would want to start an instance of SQL Server in the single-user mode. You could do this, either when you want to recover data from other database systems or may want to change server configurations.
When you start the SQL Server in the single-user mode then, any member of the computer’s local Administrators group gets connected to the instance of SQL Server as sysadmin.
The following events occur on starting the database in single-user mode:
Also, note that you have to stop the SQL Server Agent service before connecting to an instance of SQL Server in the single-user mode.
-m"Microsoft SQL Server Management Studio - Query".
The Microsoft SQL Server Profiler is an interface used to create and manage traces. It also analyzes and replays the trace results. Here, events are saved in a trace file which are later analyzed or used to replay a specific series of steps while debugging an issue.
You can use SQL Server Profiler for activities such as:
The TCP/IP port on which SQL Server runs is 1433.
A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called as the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,< or =.
The properties of subqueries are as follows:
In clustered installation, the SQL Server uses DLL available connection, and thus blocks any other connections to the server.
In this state, if you try to bring SQL Server Agent resources online, then it may fail over SQL resources to a different node as it could be configured to a group. So, to start a single user-mode in clustered installation, you can follow the below steps:
net start MSSQLSERVER /m.
SQLCMD -E -S<servername>.
Replication in Microsoft SQL Server is a process to synchronize the data across multiple servers. This is generally done by a replica set, and these sets provide multiple copies of data with redundancy and high availability on different servers.
Not only this, but replication provides a mechanism to recover from failures. It also removes dependencies from single server to protect the loss of data from a single server.
Following are three types of replications in SQL Server:
|MS SQL Server||Oracle|
Provides simple and easy syntax.
Consists of complex and comparatively more efficient syntax.
Uses transact SQL or T-SQL.
Does not support query optimization.
Uses star query optimization.
Rollbacks are not allowed in the transaction process.
Rollbacks are allowed during the transaction process.
Allows incremental , partial and full backups
Allows incremental , full, file level, and differential backups.
Does not support clustering.
Offers support for clustered configuration.
Statements such as INSERT, UPDATE, DELETE are executed serially.
Statements such as INSERT, UPDATE, DELETE, MERGE are executed parallely.
Job are scheduled via the SQL Server Agent
Job are scheduled via Oracle scheduler or OEM
Microsoft SQL Server uses a lock hierarchy whenever the data is read or something is changed in the data. Whenever a row is read, SQL Server acquires a shared lock. Similarly, as soon as we change a row, SQL Server acquires an Exclusive lock. These locks are incompatible with each other. So, the INTENT locks are used to indicate at a higher level which locks are applied within a lock hierarchy. There are mainly three kinds of INTENT locks:
The steps you must follow to hide SQL Server instances are as follows:
The data quality services in SQL Server is a knowledge-driven data quality product. SQL Server Data Quality Services (DQS) enable the user to build a knowledge base and thereafter use it to perform tasks such as correction, deduplication, enrichment, standardization of data.
Apart from this, DQS also provides profiling and enables you to perform data cleaning with the help of cloud-based data services.
DQS consists of two components:
Magic Tables are tables automatically created tables in SQL Server used to internally store the inserted, updated values for DML operations such as (SELECT, DELETE, INSERT, UPDATE, etc).
Change Data Capture or most commonly known as CDC is used to record INSERT, UPDATE, DELETE activities applied on the tables. So, as the name suggests, Change Data Capture is used to capture the data that has been changed recently. The column information and metadata required to apply the changes to the target environment are captured for the modified rows and are eventually stored in the change tables. These change tables are the mirror image of the original column structure.
Triggers are used to execute batches of SQL code whenever INSERT, DELETE, or UPDATE commands are executed against a table. So, basically triggers are automatically executed whenever the data is modified based on the data manipulation operations.
The different types of triggers are as follows:
A recursive stored procedure is a problem-solving method through which you can arrive at the solution again and again.
The process of automation of backup to restore databases from one standalone server to another standalone standby server is known as Log Shipping. You can also understand Log shipping as one of the disaster recovery solutions, as it makes sure that even if one server fails, the standby server will have the same data as that of the server itself.
The advantages of Log Shipping are as follows:
These flags are used to alter server behavior or set server characteristics. Few common trace flags used with SQL Server are as follows
Used to return a specific portion of the string in a given string
Used to return a character position in a given specified string
Analysis Services in Microsoft SQL Server is an analytical data engine used in business analytics and decision support. This service provides enterprise-grade semantic models for client applications and reports such as Power BI, Microsoft Excel, and other visualization tools.
The Analysis Services is available in platforms such as :
Mirroring in SQL Server is designed to maintain a hot standby server, that is consistent with the primary server in terms of a transaction. Also, the transaction log records are sent from the principal server to the secondary server.
Following are the advantages of Mirroring:
SQL Server-based cursors are used when you wish to work on a record at any instance of time, rather than taking all the data from a table as a bulk. However, cursors are not preferred to be used when large volumes of data is present as it affects performance. In a scenario, where it is not possible to avoid cursors, then try to reduce the number of records to process by using a temporary table and then eventually build the cursor from this.
The physical and logical design plays an important part in the performance of SQL Server-based applications. We need to ensure that correct data is captured in proper tables, the data items have proper relationships between them and data redundancy is reduced. I would also suggest that while you are designing a database, make sure that it is an iterative process to achieve all the required system goals and is under constant observation. Once the database design is set, it is very tough to change the design according to requirement. You can only add new relationships and data items.
Q27. What do you understand by User-Defined function in the SQL Server and explain the steps to create and execute a user-defined function in the SQL Server?
A user-defined function is a function written as per the needs of the user by implementing logic. In these kinds of functions the user is not limited to pre-defined functions and simplify the complex code of predefined function by writing simple code. This function returns a scalar value or a table.
To create a user-defined function, refer to the following example:
CREATE FUNCTION samplefunc(@num INT) RETURNS TABLE AS RETURN SELECT * FROM customers WHERE CustId=@num
To execute the above-created function, refer to the following command:
SELECT * FROM samplefunc(10)
A developer must check type of information stored, volume of data and the data that will be accessed.
In a scenario, where you are upgrading an existing system, you should analyze the present data, existing data volumes occur, and check the method through which data is accessed, to help you understand the problem areas for design.
In a scenario, where you are using a new system, you have to keep the information about what data will be captured, and what are the constituents of data, and the relationship between the data items.
A relationship in DBMS is the scenario where two entities are related to each other. In such a scenario, the table consisting of foreign key references to that of a primary key of the other table.
The different types of relationships in DBMS are as follows:
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:
The command DBCC CHECKDB is used to check the physical and logical integrity of all the objects in the mentioned database. To do that, it performs the following operations:
So, you just have to execute the DBCC CHECKDB command, and automatically, DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands get executed.
Also, note that DBCC is supported on databases that contain memory-optimized tables but does not provide repair options. This implies that you must regularly back up databases and test those backups.
The CHECK constraint in SQL Server is used to limit the values or type of data stored in a column. Once you apply the CHECK constraint on a single column, you can go forward and apply specific values for that particular column.
CREATE TABLE Customer ( Cust_ID int NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Customer CHECK (Age>20 AND City= 'Hyderabad') );
This function is used to return the first non-null expression within arguments. The COALESCE command is used to return a non-null value from more than a single column in arguments.
SELECT COALESCE(CustID, CustName, Amount) from Customers;
The FLOOR function is used to round up a non-integer value to the previous least integer value. This function returns a unique value after rounding the digits.
To check locks in the database, you can use the in-built stored procedure sp_lock.
sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ] [ ; ]
To list all the locks currently held in an instance of the Database Engine, use the following command:
USE SampleDB; GO EXEC sp_lock; GO
The following are three ways to count the number of records in the table:
SELECT * FROM TableName; SELECT COUNT(*) FROM TableName; SELECT rows FROM indexes WHERE id = OBJECT_ID(TableName) AND indexid< 2;
This function is used to determine whether the mentioned number is zero, positive, and negative. So, it will either return 0, +1, -1.
SIGN (0) returns 0 SIGN (21) returns 1 SIGN (-21) returns -1
To find the first week day of the month, you can write a query as follows:
SELECT DATENAME(dw, DATEADD(dd, – DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay;
To rename a database, you have to use the sp_renamedb command in the following manner:
sp_renamedb 'OldDatabaseName', 'NewDatabaseName';
To find the 5th highest amount paid from the customers’ table, you can write a query as below:
SELECT TOP 1 amount FROM (SELECT DISTINCT TOP 5 amount FROM customers ORDER BY amount DESC) ORDER BY amount;
To delete a table in SQL Server, use the Delete command.
With REPEATABLE_READ and SERIALIZABLE isolation levels, locks are held during the transaction. But, if you consider READ_COMMITTED, then locks are held for isolation level.
|Used only with SELECT statement||Used in a GROUP BY clause|
|Used with the GROUP BY function in a query||Applied to each row before they are a part of the GROUP BY function in a query|
Note: Whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Integration services is a platform offered by Microsoft to build enterprise-level data transformation solutions and integration. These services solve complex business problems by loading data warehouses, perform data wrangling, copy or download files, and manage SQL Server objects.
Also, integration services can extract and transform data from a wide variety of sources such as relational data sources, XML data files, load the data into more than a single database. So, basically, you can use the integration services to create solutions without coding, code complex tasks, program the extensive integration object model to create packages.
The integration services include good set of built-in tasks and transformations, graphical tools used for building packages and also contain the Catalog database to store, run and manage packages.
Hotfixes are single, cumulative software packages applied to live systems. This includes one or more files used to address a problem in a software product. Patches are a programs installed on the machines to rectify the problem occurred in the system and ensured the security of the system. So, basically hotfixes are a kind of patch offered by Microsoft SQL Server to address specific issues.
These are few encryption mechanisms in SQL Server to encrypt data in the database:
The READ_COMMITED_SNAPSHOT option and the ALLOW_SNAPSHOT_ISOLATION option must be set to allow the usage of optimistic models.
The common performance issues in SQL Server are as follows:
So this brings us to the end of the SQL Server Interview Questions article. I hope this set of SQL Server Interview Questions will help you ace your job interview. All the best for your interview!
Check out this MySQL DBA Certification Training by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database. It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions, etc. End of the training you will be able to create and administer your own MySQL Database and manage data.
Got a question for us? Please mention it in the comments section of this “SQL Server Interview Questions” article and we will get back to you as soon as possible.