Our Special Offer - Get 3 Courses at 24,999/- Only. Read more
Hire Talent (HR):+91-9707 240 250

Interview Questions

SQL Server Interview Questions and Answers

SQL Server Interview Questions and Answers

SQL Server Interview Questions and answers

SQL Server Interview Questions and answers for beginners and experts. List of frequently asked SQL Server Interview Questions with answers by Besant Technologies. We hope these SQL Server Interview Questions and answers are useful and will help you to get the best job in the networking industry. This SQL Server Interview Questions and answers are prepared by SQL Server Professionals based on MNC Companies expectation. Stay tuned we will update New SQL Server Interview questions with Answers Frequently.

Best SQL Server Interview Questions and answers

Besant Technologies supports the students by providing SQL Server Interview Questions and answers for the job placements and job purposes. SQL Server is the leading important course in the present situation because more job openings and the high salary pay for this SQL Server and more related jobs. We provide the SQL Server online training also for all students around the world through the Gangboard medium. These are top SQL Server Interview Questions and answers, prepared by our institute experienced trainers. If you want to learn Practical SQL Server Training then please Join our SQL Server Training in Chennai .

SQL Server Interview Questions and answers for the job placements

Here is the list of most frequently asked SQL Server Interview Questions and answers in technical interviews. These questions and answers are suitable for both freshers and experienced professionals at any level. The questions are for intermediate to somewhat advanced SQL Server professionals, but even if you are just a beginner or fresher you should be able to understand the answers and explanations here we give.

Q1) What are the different types of backup?

Full Backup, Differential Backup, Log Backup, and Filegroup Backup.

Q2) What is the difference between a Full and Full – Copy the only backup

The only difference between full and full-copy is that full-copy does not break the differential chain. A copy-only backup doesn’t affect that differential base. Neither Full or Full-Copy only backup breaks the log chain as neither of them truncates the log file.

Q3) Which are the available recovery models?

SIMPLE, FULL & BULK LOGGED

Q4) What does ACID stand for?

Atomicity, Consistency, Isolation, and Durability

Q5) Which are the different isolation levels

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and  SERIALIZABLE

Q6) What happens when you Update STATISTICS ?

It updates the distribution of key values for an index, Table or an indexed view. Statistics can be for columns or indexes.

Q7) Which trace flags should be turned On to write more information about the deadlock to the log

Trace flags :1204 & 1222

Q8) How to run on Trace flag, provide an example

DBCC TRACEON(1204)

Q9) Which events should you choose in profiler to capture Deadlock ?

“Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events

Q10) What is termed as a deadlock victim

In case of deadlock, SQL Server will pick one task/session, whichever will be least expensive to rollback and kill it. This session/task is termed as a deadlock victim.

Q11) Which are the system databases?

Master, Model, TempDB & MSDB.

Q12) Where is the backup history and SQL agent job history saved?

MSDB

Q13) What is the use of the Resource database?

It s a read-only database and has all system tables and views. These objects appear in the sys schema of each database.

Q14) What is the purpose of Model database?

It’s a template database, changes made to Model database are applied to all databases created afterward.

Q15) Provide some examples of catalog views

Sys.objects , sys.allocation_units  , sys.columns etc

Q16) List at least three server level roles

Sysadmin, server admin , setupadmin

Q17) List at least three fixed database roles

db_owner, db_securityadmin and db_accessadmin

Q18) Which statement can be used to grant permission to a user or role GRANT statement

Example:  GRANT Execute on TestTable to TestUser1

Q19) Which statement is used to remove a previously granted permission

REVOKE

Q20) Which are some of the new features of SQL Server 2016

Always Encrypted, Dynamic Data Masking, Stretch database, Polybase etc

Q21) Does Log shipping provide for Automatic failover?

No, if the primary database is not online, Secondary database should be brought online manually.

Q22) Give one difference between Log shipping and replication

Log shipping is at database level while replication replicates data at the individual table level.

Q23) Which are the different modes of configuring Mirroring

high-safety mode and high-performance mode

Q24) Is it compulsory to have Witness instance in database Mirroring

No, it is an optional instance. It is needed only if automatic failover is required.

Q25) Does the database need to be in Full recovery mode for Mirroring to be configured?

Yes, the database needs to be in FULL recovery mode.

Q26) Which are the different types of SQL server Cluster setup

Active – Active and Active – Passive configuration type

Q27) List SQL Server services that are cluster aware
  • SQL Server Database Emgine
  • SQL Server Agent
  • SQL Server analysis services
Q28) What is a table without a clustered index called?

Heap

Q29) How many clustred indexes can we have on a table

Only one

Q30) Which dynamic management function can we use to get the fragmentation level of an index

sys.dm_db_index_physical_stats

Q31) When do we generally Rebuild and Re-organise index?

Generally, when the fragmentation level is more than 30% , it is advisable to rebuild indexes and when the fragmentation is less than 30% we can re-organize indexes.

Q32) List some of commonly used DBCC statements
  • DBCC CHECKDB
  • DBCC Show_statistics
  • DBCC Opentran
  • DBCC Input buffer
Q33) Whats I ste default port number for SQL Server instance if it has not been changed during installation

1433

Q34) What is Ghost Records
  • Records which have been marked for deletion (logically deleted) but have not been physically deleted are termed as Ghost records. This feature was introduced to enhance performance.
  • SQL Server has a background thread that checks for Ghost records on a periodic basis and deletes it .
Q35) When a database is created , what is the minimum number of files that it will have?
  • It should have atleast two files – Data file and Log file.
Q36) Can we store all indexes on separate data file

Yes, Indexes can be moved to a separate data file. This will help from performance point of  view as well.

Q37) What is the difference between a Login and a User in SQL Server
  • Logins are at instance level while Users are at the database level. Instance level privileges and permissions are granted to Logins and they are also known as ‘Server principals’.
  • Users are at the database level and are granted database level permissions. They are also known as
  • ‘database principals’. Logins are mapped to users at the database level.
Q38) What is an Orphan user
  • A database user (based on a login) which is not mapped to a valid login is termed as an Orphan user.
  • This can occur when the login is deleted or the database is moved from one server to the other.
Q39) What is the minimum level of permission required for a login to be able to change the password of another login?

A login can change its own password, but it needs at least “Alter Any Login” permission to be able to change the password for other logins, except for logins with “sysadmin” role.

Q40) List the SQL server startup parameters

Whenever SQL Server starts, it needs a minimum of below listed three startup

parameters:

  • Master database data file location (-d parameter)
  • Errorlog file location (-e parameter)
  • Master database transaction log file location (-l parameter)
Q41) List at least three major wait events:

PAGEIOLATCH_SH, CXPACKET, SOS_SCHEDULER_YIELD, ASYNC_NETWORK_IO etc

Q42) How can you send email notification on completion of SQL server agent jobs

This can be done using the Database Mail feature

Q43) How to check if SQL Instance is configured to use Database Mail XPs ?

Run the following query: SELECT * FROM sys.configurations WHERE name = ‘Database Mail XPs’GO

Q44) List the available editions for SQL Server 2016/17

Enterprise, Standard, Express and Developer

Q45) Transparent data encryption (TDE) is a new feature in SQL Server that is available with which all editions?

Enterprise

Q46) What is the limitation of Developer Edition?

We cannot use production data in Developer edition.

Q47) List some of the new features of SQL Server 2017

Always Encrypted technology, row-level security, dynamic data masking, transparent data encryption (TDE) etc

Q48) Starting with which version, can we install SQL Server on Linux

SQL Server 2017

Q49) Which are the jobs that are created while setting up log shipping

Backup, Copy , Restore jobs

Q50) What is the difference between MySQL Server and MS SQL Server?

Both MySQL Server and MS SQL Server are used to maintain big comoanies database system but there are differences between them.MySQL is an open source Real time Database Management System and MS SQL Server is the non-free licensed computer software. MySQL is used for database management and MS SQL server is only used to manage data.

Q51) What is the model database server intended for?

Model Database maintains information related to system-level for SQL server like login details, linked servers, endpoints, and also other details of the configuration setting. The model database is also a store point for SQL Server for storing other database information and their file location.

Q52) How do you find a SQL Server looking for traffic hitting?

SQL profiler is a utility which you can use for looking the traffic in SQL Server and control the traffic on a particular instance.

Q53) What types of replication are supported in SQL Server?

3 types of replications.

  • Snap: It captures snap short of instance
  • Merge: Merge uses the snap replication and perform central repository serve.
  • Dealing: Dealing works both with snap and merge .
Q54) Utility of SQL Agent.

SQL Agent give a scope to the database administrator to execute the process automatically, managing other system based works and a backup process.

Q55) Utility of checkpoints.

One of the important data recovery tool in SQL server is Checkpoint and it maintains the change and modification in cache of database page.

Q56) Explain about DBCC.

Database Console Commands in short DBCC is available in 4 types.

  • Maintenance
  • Miscellaneous
  • Validation
  • Informational
Q57) How do you control the amount of free space on your index pages?

You can set the fill factor on your indexes, at the time of re-indexing SQL server will automatically get the update about how much free space to leave in the index pages.

Q58) Why would you call Update Statistics?

It is used to give power to the computation of question optimization statistics table or indexed read. Your data changes and sometimes auto-update statics are not enough; in that case, a query is a beneficiary for updating those kinds of statistics to more frequently.

Q59) What is a correlated subquery in SQL?

Correlated sub-query is a subquery that uses values from outer side query and as it is related with the column of outer side query you must re-execute it for each row of the result.

Q60) Authentication modes supported by SQL Server.

Windows Authentication and mixed-mode. You can use both Windows Authentication and SQL Server Authentication in the mixed-mode to log into your SQL server.

Q61) Share your Experience working with SQL Server DBA.

It is very common Questions in most of the interview. By asking this question the selection board wants to know about your working experience with multiple versions of SQL Server and make an idea about your capability to choose the right person for the job.

Q62) How many SQL Server published Versions you worked on till date?

2000,2005, 2008 and SQL server7 are the published version till date. The work experience depends on every individual candidate.

Q63) How many Indexes available in SQL Server?

Two types of Indexes, Clustered Indexes and Non-Clustered Indexes.

Q64) Dissimilarities between Clustered and Non-Clustered Index.

Clustered index is used in table formation, to arranged data pages according to its key. In Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages..

Q65) What are the extra added features in SQL Server 2005 than SQL Server 2000?

Extra added features in SQL Server 2005 are

  • Database Snapshots
  • Dynamic Management Views
  • Resource Database
  • SQL Server Integration Services
  • Database Partitioning
  • System Catalogue Views
  • Support for Analysis Services on a Failover Cluster.
Q66) What are the high-availability solutions of SQL Server and what distinguishes them briefly.

The solutions are Log Shipping, Database Mirroring, Failover cluster and Replication high-available in SQL Server.

Q67) How do you troubleshoot a SQL Server Agent Job?

Select the unsuccessful job, right click and select view history from the drop-down menu. The implementation history of the job is displayed and you will select the implementation time. There you will get all the details about the error and how much time to fix it .

Q68) What is the default Port no for SQL Server listening?

1433

Q69) What number files will an information contains in the SQL Server? How many types of data forms exist on SQL Server? How many of these files can exist for a single database?
  • The contained information will be a most of 32 to 767 files.
  • 2 types of data files, Primary data file and Secondary data file(s)
  • There is only one Primary file and multiple numbers of  secondary information files which is smaller amount than 32 to 767 files.
Q70) Fullform of DCL.

Data Control Language.

Q71) Commands used in DCL.

RANT, DENY and REVOKE.

Q72) Describe Fill Factor.

Fill issue is a setting used to apply Index in SQL Server. The fill issue determines price of the proportion of written information in a created/rebuilt index page.

Q73) Value of default fill factor.

It is zero by default.

Q74) Where do you find the default index fill element and how to change it?

You can get it from Management Studio, right-click the SQL Server and choose properties, change the value as you wanted there and click OK to save the changes.

Q75) What is a system database and what is a user database?

In the Installation procedure of SQL Server, the SQL server installed a database called System Database. There are 4 types of system database master, MSDB, model, and TempDB. Most of the SQL server systems are maintained by system databases like logins, databases, linked servers, jobs, schedules, reports, report data sources etc. If the system database corrupts the whole SQL server will stop. The user database is porously created to store information for our necessity. If the user database corrupts it will not affect the SQL server.

Q76) Available recovery models for a database?

3 types of recovery model are available in the market , FULL, Simple and Bulk-Logged.

Q77) Importance of a recovery model in SQL server.

Recovery model maintain the total transaction log in SQL server. So if you have to protect data loss issue the suggestion is to use Full Recovery model, because it has the complete protection capability.

Q78) Describe about Replication and its advantages.

Replication is a technology of coping and distribution of data from one server to another.The benefits of Replication is improvement in data availability, user can easily browse or share the same data without any interruption.

Q79) Types of Replication and their uses.

3 types of Replication are available Snapshot Replication, Transactional Replication and Merge Replication.

  • Snapshot Replication is used to take data snapshots in one server to relocate it to another server. It is the simplest replication process and used for small table works.
  • Transactional Replication is used to copy data from main source to scatter between the users. Transactional Replication is used for duplicating sales order like tables.
  • Merge Replication is the collector of data from multiple sources. It works with both Snapshot and Transaction Replication. Merge Replication is useful for the remote areas where network is unavailable for most of the time.
Q80) Types of Replication components and their use.

3 types of elements are available in Replication Publisher, Distributor, and Subscriber. The publisher is responsible for data publication. The distributor is responsible for catering the info objects to various sources. The subscriber is the element where the published subscription is copied.

Q81) What are the different topologies where replication can be configured?

Replication can be any of the following:

  • Publisher, Distributor, and Subscriber are on the same SQL Instance.
  • Publisher and Distributor are on same SQL Instance and Subscriber on a separate Instance.
  • Publisher, Distributor, and Subscriber all are on individual SQL Instances.
Q82) If you were given access to an SQL server, how would you discover if the SQL instance is a named instance or a default instance?

First I would go to SQL Server Configuration Manager. Then click the left panel of the tool, I would select SQL Server Services, the right side panel displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

Q83) What are the various authentication methods on SQL Server and how can you change authentication mode?

2 Authentication modes; Windows Authentication and SQL Server. Windows Authentication mode also referred to as Mixed Mode.

Q84) What are the differences between the clustering of SQL Server 2005 and 2008 or 2008 R2?

SQL Server 2005, installing failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. In SQL Server 2005, the Installation process itself installs all of the nodes (be it 2 nodes or 3 nodes) but in 2008 or 2008 R2 this process is modified, you have to install separately on every nodes, 2 times for two nodes and 3 times for three nodes.

Q85) Describe Active-Passive and Active-Active clustering setup.

The design of Active –Passive cluster is a failover cluster in which only one node is active and other node, called as the Passive node is always online but disabled. But if the   Active Node fails the Passive Node takes its place on the SQL Server Services and becomes the Active Node, the failed Active Node become Passive Node.

Design of an Active-Active cluster is a failover cluster in which both the cluster nodes are active at any given point in time. One Instance of SQL Server is running on every nodes of the cluster and if one amongst the nodes failed, both the Instances run on the only one node till the failed node is repaired. The instance will then back to its selected node.

Q86) List the number of SQL Server failover cluster you want to set up.

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.

Q87) What is Transparent Data Encryption?

Transparent Data Encryption is a mechanism newly added in SQL Server 2008 for coding and encryption to defend unauthorized access of information files of SQL Server. Also, TDE will protect the backup information of the instance.

Q88) At the time of transmission data across network does Transparent Data Encryption provide encryption?

No, Transparent Data Encryption doesn’t provide encryption at the time of data transmission.

Q89) What is the difference between the High-Safety and Superior operating modes of Database Mirroring
  • High-Safety Mode ensures that the Principal and reflected info area unit synchronization, transactions are committed at the same time on both servers to secure stable condition, but there is/might be a time lag.
  • High-Performance Mode is to ensure that the Principal database run faster, without depending on the Mirrored database to commit the transactions. There is very little chance of loss of knowledge and the material behind the collectively reflective information can be insulated
Q90) what’s the distinction between dropping info and taking an info offline?

Drop info deletes the info with its source file, without a backup you can’t recover it.  Offline database is not a deleted database only unavailable for user unless you back to online.

Q91) Which auto growth database setting is better?

The better option is to change the Autogrowth database settings into Megabyte than Percentage.

Q92) SQL server 2008 introduced different types of data compression what are they?

Row compression and Page Compressionn.

Q93) How many types of Upgradation can be performed in SQL Server?

2 types, In-place upgrade and Side-by-Side Upgrade.

Q94) On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?

Use Cluster Administrator to connect to the cluster and select the SQL Server cluster and select the SQL Server group, at the right side of the console, the “Owner” column indicates the proper node on which the SQL Server group is currently active.

Q95) Procedure of opening a Cluster Administrator.

From begin -> Run and sort CluAdmin (case insensitive) and therefore the Cluster Administrator console is displayed or you can even head to begin -> All Programs -> body Tools -> Cluster Administrator.

Q96) The SQL Server on a failover cluster needs to be brought down. What is the procedure?

Go to Cluster Administrator then right click on the SQL Server Group and from the popup menu item choose to Take Offline.

Q97) What are the various ways in which you can create databases on SQL Server?
  • T-SQL; Create Database command.
  • Using Management Studio
  • Restoring a database backup
  • Copy Database wizard
Q98) What are operating modes maintained by Database Mirroring?

Two operational modes High-Safety Mode and Superior Mode.

Q99) Once the transcript is set, is it possible to own a publisher as a distributor or subscriber, as a twenty-four bit SQL server and thirty-bit bit SQL server?

Yes, it’s possible to own numerous configurations in a very Replication atmosphere.

Besant Technologies WhatsApp