The top SQL Server interview questions and answers are right here! These expert-curated SQL Server interview questions and answers for experienced will boost your knowledge and confidence in a short period of time. We have compiled a list of both Intermediate & Expert Level SQL Server interview questions and answers. These SQL Server interview questions and answers for developers have been answered by experts. Be prepared and get recruited by your dream employer. Convert your dream job interview into a new job offer.
A page is a fundamental unit of storage that stores data. The page is the size of 8KB and we have 8 types of pages in SQL Server.
An identity column in the table has auto-generate & auto increase value with each new row insert. The user cannot insert value in the identity column.
The sequence is a new feature introduced with SQL Server 2012 similar to Oracle’s sequence objects. A sequence object generates a sequence of unique numeric values as per the specifications mentioned. Next VALUE for a SEQUENCE object can be generated using the NEXT VALUE FOR clause.
IDENTITY is column level property & tied to a particular table. This cannot be shared among multiple tables.
SEQUENCE is an object defined by the user with specific details and can be shared by multiple tables. This is not tied to any particular table.
IDENTITY property cannot be reset to its initial value but the SEQUENCE object can be reset to initial value any time.
Maximum value cannot be defined for IDENTITY whereas this can be done for SEQUENCE object.
The DMV (Dynamic Management Views) is a set of system views introduced with SQL Server 2005. DMV’s are a new tool of DBA to get internal information of the system and it’s working.
SQL Server supports different types of isolation level.
SQL Server supports the following types of database backups:
SQL Server database backups are supported by all 3 recovery models:
With Compressed backups, SQL Server works on the pre-allocation algorithm to determine how much space the compressed backup will require and will allocate this space at the start of the backup process. At the time of backup completion, the backup process might expand or shrink the backup file on the basis of actual requirement.
Pre-Allocation helps SQL Server save the overhead of constantly growing the backup file.
Sometimes when we have a very large database and the difference in uncompressed and compressed backups is very high, preallocated and actual backup space has a significant difference. Trace flag 3042 promote better space management with little over-head to SQL server to grow backup file on a regular basis. This is useful with limited disk space.
Enabling trace flag 3014 may result in a little spike in CPU & memory but not significant to cause performance issues.
Pseudo-simple Recovery Model is situational based recovery model. This is something not given by project but ones with circumstances. It’s a situation where the database may be configured in the FULL recovery model but behaves like a SIMPLE recovery model.
The database will behave like a SIMPLE recovery model until a full database backup is taken after switching recovery model to Full.
For example, You have a database in Simple Recovery Model and now you have switched it to the FULL recovery model. You need to perform a full backup after switching recovery model otherwise database will keep behaving like simple and keeps truncating Transaction log on commit.
Consider a scenario where the server is performing a full backup at Sunday 2 AM, Differential backup Monday to Saturday 2 AM and Transaction Log backup in each 30 Min. You have been reported system crash around Wednesday 7 AM, Please share the recovery path with required backups to recover the system.
You need to restore Last Sunday full backup happen at 2 AM then Wednesday Differential backup of 2 AM followed by all Transaction log backups after. Consider all backups are valid, in case of any bad backup file recovery path, will be changed.
SQL Server Agent provides multiple functionalities like:-
SQL Server agent has lots of features and sometimes you need to give rights on an agent to manage and view jobs. Allowing every one with sysadmin permission is not appropriate.
SQL Server has 3 fixed DB roles for permission on SQL Agent & Jobs.
The Guest user account is created by default with SQL server installation. Guest user is not mapped to any login but can be used by any login when explicit permissions are not granted to access an object. You can drop the guest user from all databases except Master and TempDB.
When any user login to the SQL Server, it has 2 parts Server & database. First, at the Server level, user authentication verifies at the server level and User is able to login to SQL Servers. Second, Login and mapping to the database are verified. In case, Login is not mapped to any user but able to log in on SQL server. SQL automatically map that login to Guest and grant him database access.
One of the security recommendations of Microsoft is to Drop or disable a Guest user in every database except Master & TempDB database. By having Guest user, you are at risk of unauthorized access to data.
An SQL injection is a web hacking techniques done by unauthorized personnel or processes that might destroy your database.
An SQL injection is a web security vulnerability that allowed hackers to access application code and queries used to interact with the database. Hacker uses those methods to retrieve data belonging to other users and data not authorized to them. The major challenge is SQL injection can cause a system crash, data stolen, data corruption, etc.
An SQL injection is not a task of one man or team. Complete support and architecture team work together to get it prevented to happen.
Developers \ DBAs are responsible for DB security and proper SQL code.
Applications developers are responsible for application code and Db access methods.
Infra team is responsible for network, firewall & OS security.
Proper SQL instance, OS & Farwell security with a well-written application can help to reduce the risk of SQL injection.
Each table can have only one Cluster & multiple nonclustered indexes. All nonclustered indexes use index key of cluster index or directly depends on the clustered index.
Because of this dependency, we usually got this question if rebuilding the clustered index will rebuild the nonclustered index on a table or not.
The answer is NO. Cluster index rebuild will not rebuild or reorganize the nonclustered index. You need to plan a nonclustered index rebuild or reorganization separately.
Push & Pull is a type of Replication configuration. It decides how data will be replicated to the subscriber.
Push – In Push subscription, the publisher is responsible for sending data. Publisher pushes data from the publisher to the subscriber. Distributor agent will run on the Distributor to push changes. The distributor can be configured on the Publisher or separate server. Changes can be pushed to subscribers on demand, continuously, or on a scheduled basis.
Pull is best suited configured when the subscriber is connected all the time and need the latest data all the time accessed.
Pull - In Pull subscription, Subscribers is responsible for fetching data. Subscribers requests changes from the Publisher. Distributor agent will run on the subscriber to Pull changes. The subscriber can pull data as & when needed.
Pull is best suited when subscribers are not online every time. The subscriber can allow data lag and can wait for delay in data refresh
Log shipping is a Microsoft SQL Server DR solution from the very beginning. In Log Shipping, Backup and restores of a database from one Server to another standby server happens automatically.
Log Shipping works with 3 mandatory & 1 optional Job.
These 3 jobs are created separately for each database configured in log shipping.
Also, You can use the Transaction Log Shipping Report at Server Level to view the current status.
Principal Server:- The main server holding a primary copy of the database and serving client application & requests.
Mirror Server:- The secondary server which holds a mirrored copy of Principal database and acts as a hot or warm standby server on basis Synchronous & Asynchronous configuration.
Witness Server: - The witness server is an optional server and it controls automatic failover to the mirror if the principal becomes unavailable.
The FQDN (fully qualified domain name) is computer name of each server with the domain name. This can be found running the following from the command prompt:
Concatenate the “Host Name” and “Primary DNS Suffix”
Host Name . . . . . . . . . . . . : XYZ
Primary DNS Suffix . . . . . . . : corp.mycompany.com
The FQDN of your computer name will be XYZ.corp.mycompany.com.
The physical database architecture is a description of the way the database and files are organized in a SQL server.
Pages and extents- A page is the size of 8KB and set of 8 Pages are called extent. This is the fundamental unit where data is stored.
Physical Database Files and Filegroups- Database files visible at file system to store data and logs.
Table and Index Architecture- Database objects inside the database to store & access data.
Database- Database is a set of data & Log file which resides over the filesystem and managed by the operating system.
SQL Instance- SQL instance is a logical entity controlled databases. One SQL instance can have multiple databases. Security and accessibility is also part of the SQL instance.
Write-ahead transaction log controls & defined recording of data modifications to disk. To maintain ACID (Atomicity, Consistency, Isolation, and Durability) property of DBMS, SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk.
Write-ahead log work as below:-
To understand Row versioning, you should first understand RCSI (Read Committed Snapshot Isolation). RCSI does not hold a lock on the table during the transaction so that the table can be modified in other sessions, eliminate the use of shared locks on read operations.
RCSI isolation maintains versioning in Tempdb for old data called Row Versioning. Row versioning increases overall system performance by reducing the resources used to manage locks.
When any transaction updates the row in the table, New row version was generated. With each upcoming update, If DB is already having the previous version of this row, the previous version of the row is stored in the version store and the new row version contains a pointer to the old version of the row in the version store.
SQL Server keeps running clean up task to remove old versions which are not in use. Until the transaction is open, all versions of rows that have been modified by that transaction must be kept in the version store. Long-running open transactions and multiple old row versions can cause a huge tempDB issue.
Sometimes we have a situation when we cannot perform such activities using SSIS due to multiple types of databases, Different domains, Older version, etc.
We have several commands available to import using T-SQL.
We need to run the SQL Server patch installation minimally 4 times to patch both SQL instance on all 3 cluster nodes.
SQL 2005 - 1 installation, SQL 2005 support remote installation. SQL 2005 patch installation will install the patch on all cluster nodes in one go but only for cluster objects like DB engine or agent.
SQL 2008 R2 – 3 installation, SQL 2008 R2 does not support remote installation. We need to patch all 3 nodes separately.
Additionally, We may need to run SQL 2005 setup on other 2 nodes to patch non-cluster objects like SSMS but that’s the additional part.
Full backups are set of the complete database with all data. AT the time of any crash or data recovery it’s the starting point to select which full to sue to plan recovery path.
To make things clear and doubt free, SQL Server includes all data or transactions data into full backup till the END TIME of backup.
If your backup took 2 hours that backup will also contain data changes and transaction happen in this 2 hours.
A full backup includes all data and transaction at the completion of backup time.
The full backup cover complete transaction with all the changes that were made after the backup start checkpoint to apply those changes during the database recovery process.
DCM (Differential Changed map): DCM is used by SQL server for differential backup. DCM pages keep track of all extents which has changed since the last full database backup.
During SQL server differential backup, database engine reads the DCM page and takes a backup of only those pages which has been changed since the last full backup. If the value is 1 means extent has been modified and 0 means not modified. After each full backup, all these extents are marked to 0.
BCM (Bulk Changed map): BCM page is used in bulk-logged recovery model to track extends changed due to bulk-logged or minimally logged operations.
During log backup, the database engine reads BCM page and includes all the extents which have been modified by the bulk-logged process. If the Value 1 means modified extent and 0 means not modified. After each log backup, all these extents are marked to 0
While trying to take a differential backup of MASTER database, I am getting below error. Differential backup is supported by all recovery model then why it’s failing for MASTER database. Can you explain what can be the reason?
“You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.”
To restore the differential backup of any database, DB needs to be in restoring mode which means DB will not accessible.
The MASTER database is a startup database for any SQL server instance. SQL instance will be in an offline state if the MASTER database is not accessible.
If we combined both statements, We can see that differential backup of the MASTER database is unnecessary as we can not restore. That’s why SQL server will not allow you to do take a differential backup of MASTER DB.
SQL Server provides the feature of managing jobs from Master \ central server on target servers called multi-server administration. Jobs and steps information is stored on Master Server. When the jobs complete on the target servers notification is sent to the master server so this server has the updated information. This is an enterprise level solution where a consistent set of jobs need to run on numerous SQL Servers.
As the name implies, SQL Server Agent Proxy is an account that grant privilege to the user to execute a particular process or an action when a user does not have rights. The SQL Server Agent Proxies include multiple sub-systems:
All these sub-systems are available under Proxy in SSMS and you can create as per your requirement.
To secure your SQL Server instance, it’s advisable to hide your SQL instance. SQL Server instance can be marked as hidden from the SQL Server Configuration Manager.
SQL Server Configuration Manager > Select the instance of SQL Server, > Right click and select Properties > After selecting properties you will just set Hide Instance to "Yes" and click OK or Apply.
You need to restart the instance of SQL Server.
An SQL injection is a web hacking techniques done by unauthorized personnel or processes that might destroy your database.
The major challenge is SQL injection can cause a system crash, data stolen, data corruption, etc.
Proper SQL instance, OS & Farwell security with the well-written application can help to reduce the risk of SQL injection.
Powershell is windows scripting and powerful enough to manage things from the core in a very efficient manner.
Powershell help in deep automation and quick action on DBA activities. The new face of DBA automation.
We can perform multiple actions from a DBA perspective using Powershell, like:-
Update Statistics has performed a recalculation of query optimization statistics for a table or indexed view. Although, with Auto Stats Update option, Query optimization statistics are automatically recomputed, in some cases, a query may benefit from updating those statistics more frequently. UPDATE STATISTICS uses tempdb for its processing.
Please note that update statistics causes queries to be recompiled. This may lead to performance issues for initial execution.
You can perform Update Statistics by 2 methods:
UPDATE STATISTICS - This need Alter right on the table and has more controlled options of performing an update only for one table or specific stats. This can’t be used for the complete database in one go.
SP_UPDATESTATS - This need sysadmin rights on SQL instance. This can help in performing update stats for all the stats of all table in the database.
If a column in the table is having >50% NULL values then index selection if very selective.
Index schema is based on B-Tree structure and if the column is having more than 50% NULL values then all data will reside on one side of the tree result ZERO benefits of the index on query execution.
The SQL server is having a special index called filtered index which can be used here. You can create an index on column only on NON NULL values. NULL data will not be included in the index.
By Default, SQL Server works in Simple Parameterization where SQL Server internally will add parameters to an SQL statement executed without parameters so that it can try to reuse a cached execution plan. But Simple parametrization has lots of limitation but did not work in the following cases:-
To force SQL Server to have Auto Parameterization for every SQL execution, You need to enable Force Parameterization at the database level.
Force Parameterization settings need proper testing as it may also create an adverse impact on performance.
In the existing replication setup, Sometimes we need to add new articles. When you perform such operations in existing setup, it will mark your snapshot invalid which results in snapshot re-initialize for all articles.
This sounds normal but considers the situation where a number of articles are very high or the size of the remaining article is huge, reinitialization can bring down your system.
SQL server provides you option of generating a snapshot of only newly added article. This can be achieved by T-SQL like:-
Log shipping may fail due to multiple issues can cause data long, Here are some possible reasons:
TUF means Transaction Undo file used when log shipping is configured in standby mode on secondary.
TUF contains details of transaction not applied on secondary in last Log restore. Basically, these transactions were not committed to the Primary database when the Transaction log backup was in progress. On Next T-Log restore on secondary, Log shipping refer TUF file or state of active transactions.
If TUF is missing, You can’t recover your log shipping.
We have 3 Operating Modes in DB Mirroring.
We need to perform force failover to bring mirror online when Principle is down. We can perform this using below command:
ALTER DATABASE <DB Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
If you try to do failover like the normal situation when the principle is online [ALTER DATABASE <DB Name> SET PARTNER FAILOVER] then you will receive below error.
ALTER DATABASE <DB Name> SET PARTNER FAILOVER
Msg 1404, Level 16, State 10, Line 1
Above command failed because the database mirror is busy. Reissue the command later.d later.
AlwaysOn provided you with the feature of Readable replica. This is a long-awaited feature where you can utilize your passive node.
Unlike cluster where your passive node consume all your resources but you cannot utilize them before the primary node goes.
The Remote Procedure Call (RPC) is a system service for interprocess communication (IPC) between different processes. This can be in over on the same computer, on the LAN, or in a remote location, and it can be accessed over a WAN connection or over a VPN connection.
RPC working on a dynamic range of ports and connect using any available port from the available range.
There is a long list of services that depends on RPC like Telnet, DHCP, DNS, COM+, DTC, WINS, etc.
You may face multiple errors when RPC is not working like