1. MS SQL Server Vs MySQL?

MS SQL Server Vs MySQL

Name MS SQL Server MySQL
Microsoft Relational DataBase Wide usage of Relational DataBase
Define
Management System Management system
Primary DB
Relational DB Management System Relational DB Management System
Model
Secondary Graph DBMS, Document & Value
Document & Key-Value store
DB Model store
Developer It is developed by Microsoft It is developed by Oracle
It supports Linux, Solarwinds, Windows, OS
Server OS It supports Windows, Linux
X
Supporting Python, TCL, Scheme, Haskell, Javascript,
Java, PHP, Ruby, C#, C++, R, etc
Languages Ruby, C, C++, C# few more
FK(Foreign
Yes they support Yes they support
Key)
API's JDBC, ODBC, OLE DB, TDS ODBC, ADO.NET, JDBC
License
Only Commercial OpenSource (Free)
Permit

2. What purpose does the model database serve?

The model database, as its name implies, serves as the model (or template) for all databases created on the
same instance. If the model database is modified, all subsequent databases created on that instance will pick
up those changes, but earlier created databases will not. Note that TEMPDB is also created from the model
every time SQL Server starts up.

3. How do you trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can
be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace.
The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

4. What types of replication are supported in SQL Server?

SQL Server has three types of replication: Snapshot, Merge, and Transaction.

Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the
subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to
replicate, or large changes occur over a small period of time.

Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked
so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication
is in a client and server scenario. A server would act as a central repository and multiple clients would
independently update their copies of the data until connected. At which time, they would all send up their
modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the
name implies). Changes are replicated from publisher to subscriber the same as they occurred on the
publisher, in the same order as they occurred, and in near real-time. This type of replication is useful when the
subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume
is high, and when the subscriber needs near real-time access to the changes.

5. Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or
when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to
schedule administrative jobs such as backups.

6. What happens at the checkpoint?

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the
beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer
cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL
Server. However, you do not want the transaction log to get too big because it might consume too many
resources and, should your database fail, take too long to process to recover the database.

One important thing to note here is that SQL Server can only truncate up to the oldest open transaction.
Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone
forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as
possible.

7. What is DBCC?

DBCC statements are Database Console Commands and come in four flavors:

1. Maintenance

2. Informational

3. Validation

4. Miscellaneous

Maintenance commands are those commands that allow the DBA to perform maintenance activities on the
database such as shrinking a file.

Informational commands provide feedback regarding the database such as providing information about the
procedure cache. Validation commands include commands that validate the database such as the ever-popular
CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories.
This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

8. How can you control the amount of free space in your index pages?

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index
pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy
rows from one index page to another to make room for an inserted row) because there is room for growth built
into the index.

9. Why would you call Update Statistics?

Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view.
Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from
updating those statistics more frequently.

Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not
negate all performance gains you might have achieved by calling update statistics. In fact, it could have a
negative impact on performance depending on the characteristics of the system.

10. What is a correlated sub-query?

A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all
the employees who have not entered their time for the week. I could query the Employee table to get their first
and last name, but I need to look at the time entry table to see if they’ve entered their time or not. I can’t do a
straight join here because I’m looking for the absence of time data, so I’ll do a correlated sub-query similar to
this:

SELECT FirstName, LastName

FROM EMPLOYEE e
WHERE NOT EXISTS (SELECT 1 FROM TimeEntry te
WHERE te.EmpID = e.EmpID
AND te.WeekID = 35)

Notice that the inner query relates to the outer query on the employee ID, thus making it a correlated sub-
query. The inner query will be evaluated once per the outer query row.

11. What authentication modes does SQL Server support?

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both
Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note
that if you use Windows Authentication, you will not be able to log in.

12. Explain your SQL Server DBA Experience?

This is a generic question often asked by many interviewers. Explain what are the different SQL Server
Versions you have worked on, what kind of administration of those instances has been done by you.

Your role and responsibilities carried out in your earlier projects would be of significance to the potential
employer. This is the answer that lets the interviewer know how suitable are you for the position to which you
are being interviewed.

13. What are the different SQL Server Versions you have worked on?
The answer would be depending on the versions you have worked on, I would say I have experience working
in SQL Server 7, SQL Server 2000, 2005, and 2008. If you have worked only on some versions be honest in
saying that, remember, no one would be working on all versions, it varies from individual to individual.

14. What are the different types of Indexes available in SQL Server?

The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes that
can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

15. What is the difference between Clustered and Non-Clustered Index?

In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is
created on a table, the data pages are arranged accordingly based on the clustered index key. There can only
be one Clustered index on a table.

In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data
pages. There can multiple non-clustered indexes on a single table.

16. What are the new features in SQL Server 2005 when compared to SQL Server 2000?

There are quite a lot of changes and enhancements in SQL Server 2005. A few of them are listed here:

 Database Partitioning

 Dynamic Management Views

 System Catalog Views

 Resource Database

 Database Snapshots

 SQL Server Integration Services

Support for Analysis Services on a Failover Cluster.

1. Profiler being able to trace the MDX queries of the Analysis Server.

2. Peer-to-peer Replication

3. Database Mirroring

17. What are the High-Availability solutions in SQL Server and differentiate them briefly?

Failover Clustering, Database Mirroring, Log Shipping, and Replication are the High-Availability features
available in SQL Server. I would recommend reading this blog of mine which explains the differences
between these 4 features.

18. How do you troubleshoot errors in a SQL Server Agent Job?

Inside SSMS, in Object Explorer under SQL Server Agent look for Job Activity Monitor. The job activity
monitor displays the current status of all the jobs on the instance. Choose a particular job that failed, right-
click, and choose view history from the drop-down menu.

The execution history of the job is displayed and you may choose the execution time (if the job failed multiple
times during the same day). There would information such as the time it took to execute that Job and details
about the error that occurred.

19. What is the default Port No on which SQL Server listens?

1433

20. How many files can a Database contain in SQL Server? How many types of data files exist in SQL
Server? How many of those files can exist for a single database?

1. A Database can contain a maximum of 32,767 files.

2. There are Primarily 2 types of data files Primary data file and Secondary data file(s)

3. There can be only one Primary data file and multiple secondary data files as long as the total # of files is

less than 32,767 files

21. What is DCL?

DCL stands for Data Control Language.

22. What are the commands used in DCL?

GRANT, DENY, and REVOKE.

23. What is Fill Factor?

Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much
data is written to an index page when it is created/rebuilt.

24. What is the default fill factor value?

By default, the fill factor value is set to 0.

25. Where do you find the default Index fill factor and how to change it?

The easiest way to find and change the default fill factor value is from Management Studio, right-click the
SQL Server, and choose properties. In the Server Properties, choose Database Settings, you should see the
default fill factor value in the top section.

You can change to the desired value there and click OK to save the changes. The other option for viewing and
changing this value is using.

26. What is a system database and what is a user database?

System databases are the default databases that are installed when the SQL Server is installed. Basically, there
are 4 system databases: Master, MSDB, TempDB, and Model. It is highly recommended that these databases
are not modified or altered for the smooth functioning of the SQL System. A user database is a database that
we create to store data and start working with the data.

27. What are the recovery models for a database?

There are 3 recovery models available for a database. Full, Bulk-Logged, and Simple are the three recovery
models available.

28. What is the importance of a recovery model?

Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford. If one
expects to have minimal or no data loss, choosing the Full recovery model is a good choice.

Depending on the recovery model of a database, the behavior of the database log file changes. I would
recommend you read more material on log backups and log file behavior and so on to understand in depth.

29. What is Replication?

Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to
one or more destinations. It is often considered as one of the High-Availability options. One of the advantages
of Replication is that it can be configured on databases that are in a simple recovery model.

30. What the different types of Replication and why are they used?

There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of
Replication you choose depends on the requirements and/or the goals one is trying to achieve. For example,
Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount
of data is not too large, such as a monthly summary table or a product list table, etc.

Transactional Replication would useful when maintaining a copy of a transactional table such as sales order
tables etc. Merge Replication is more useful in the case of remote / distributed systems where the data flow
can be from multiple sites, for example, sales done at a promotional event that might not be connected to the
central servers always.

31. What the different components of Replication and what is their use?

The 3 main components in Replication are Publisher, Distributor, and Subscriber. The publisher is the data
source of a publication. The distributor is responsible for distributing the database objects to one or more
destinations. The subscriber is the destination where the publisher's data is copied/replicated.

32. What are the different Topologies in which Replication can be configured?

Replication can be configured in any topology depending on keeping in view the complexity and the
workload of the entire Replication. It can be any of the following:

 Publisher, Distributor, and Subscriber on the same SQL Instance.

 Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.
  Publisher, Distributor, and Subscriber on individual SQL Instances.

33. If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or
a default instance?

I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server
Services, the right side pane 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.

34. What are the different authentication modes in SQL Server and how can you change authentication
mode?

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

35. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?

On SQL Server 2005, installing the SQL Server failover cluster is a single-step process whereas on SQL
Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself
installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to
install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster, and so on.

36. What is meant by Active-Passive and Active-Active clustering setup?

An Active-Passive cluster is a failover cluster configured in a way that only one cluster node is active at any
given time. The other node, called the Passive node is always online but in an idle condition, waiting for a
failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this
becomes the Active Node, the previous Active Node now being a Passive Node.

An Active-Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any
given point in time. That is, one Instance of SQL Server is running on each of the nodes always; when one of
the nodes has a failure, both the Instances run on only one node until the failed node is brought up (after
fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

37. List out some of the requirements to set up a SQL Server failover cluster?

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

38. What is Transparent Data Encryption?

Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can
protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect
the database backups of the instance on which TDE was set up.

39. Does Transparent Data Encryption provide encryption when transmitting data across the network?
No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication
channel.

40. What are the operating modes in which Database Mirroring runs?

Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.

41. What is the difference between the 2 operating modes of Database Mirroring (mentioned in the
above answer)?

1. High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is

the transactions are committed at the same time on both servers to ensure consistency, but there

is/might be a time lag.

2. High-Performance Mode is to ensure that the Principal database runs faster, by not waiting for the

Mirrored database to commit the transactions. There is a slight chance of data loss and also the

Mirrored database can be lagging behind (in terms of being up to date with the Principal database) if

there is a heavy load on the Mirrored Server.

42. When setting Replication, is it possible to have a Publisher as a 64 Bit SQL Server and Distributor
or Subscribers as a 32 Bit SQL Server?

Yes, it is possible to have various configurations in a Replication environment.

43. What is the difference between dropping a database and taking a database offline?

Drop database deletes the database along with the physical files, it is not possible to bring back the database
unless you have a backup of the database. When you take a database offline, the database is not available for
users, it is not deleted physically, it can be brought back online.

44. Which auto-growth database setting is good?

Setting an auto-growth in multiples of MB is a better option than setting auto growth in percentage (%).

45. What are the different types of database compression introduced in SQL Server 2008?

Row compression and Page Compression.

46. What are the different types of Upgrades that can be performed in SQL Server?

In-place upgrade and Side-by-Side Upgrade.

47. On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is
active?
 Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected
the SQL Server group, on the right-hand side of the console, the column “Owner” gives us the information of
the node on which the SQL Server group is currently active.

48. How do you open a Cluster Administrator?

From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed
OR you can also go to Start -> All Programs -> Administrative Tools -> Cluster Administrator.

49. Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought
down. How do you bring the SQL Server down?

In the Cluster Administrator, rick click on the SQL Server Group, and from the popup menu item choose to
Take Offline.

50. What are the different ways you can create Databases in SQL Server?

 1. T-SQL; Create Database command.

 2. Using Management Studio

 3. Restoring a database backup

 4. Copy Database wizard

51. When setting Replication, can you have a Distributor on SQL Server 2005, Publisher of SQL Server
2008?

No, you cannot have a Distributor on a previous version than the Publisher.

List of Related Microsoft Certification Courses:

SSIS SQL Server

SSRS Power BI
SSAS BizTalk Server
Top-project-management-tools AWS
SCCM Team Foundation Server
SharePoint BizTalk Server Administrator

1. Question 1. What Is The Difference Between Lock, Block And Deadlock?

Answer :
Lock: DB engine locks the rows/page/table to access the data which is worked
upon according to the query.
 Block: When one process blocks the resources of another process then blocking
happens. Blocking can be identified by using
SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0
Deadlock: When something happens as follows: Error 1205 is reported by SQL
Server for deadlock.
2. Question 2. What Is The Meaning Of Lock Escalation And Why/how To
Stop This?
Answer :
understand that whole table would be locked for the processing thenn this is
better to use TABLOCK hint and get complete table blocked. This is a nice way to
avoid the wastage of sql server DB engine processing for lock escalation.
Somewhere you may also need to use TABLOCKX when you want an exclusive
lock on the table in the query.

3. Question 3. How To Truncate The Log In Sql Server 2008?

Answer :BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server
doesn’t allow you to truncate the log now otherwise whole purpose of a DB is
defeated.

4. Question 4. What Changes In The Front End Code Is Needed If Mirroring

Is Implemented For The High Availability?
Answer :
You need to add only FAILOVER PARTNER information in your front end code.
“Data Source=ServerA;Failover Partner=ServerB;Initial
Catalog=AdventureWorks;Integrated Security=True;”.

5. Question 5. Where Does The Copy Job Runs In The Log Shipping Primary
Or Secondary?
Answer :
Secondary server. This question is basically asked to find out whether you have a
hands on work on log shipping or not.

6. Question 6. What Are The Ways To Find What Code Is Running For Any
Spid?
Answer :
Well there are many ways to do this.
o find the spid which you want to analyze. An spid is assigned as soon as a
client connection is established with the SQL server. To find the spid you
can run any of the following command:
o SP_WHO2 ‘ACTIVE’ — This will give you only active spids.
o SELECT * FROM sys.dm_exec_requests
o Get the spid from above two queries and use any of the following query
to get what is happening behind that spid.
o dbcc inputbuffer()
o sql2005 and sql2008 – SELECT * FROM sys.dm_exec_sql_text()
 o sql2005 and sql2008 – SELECT * FROM fn_get_sql()

7. Question 7. When You Get Following Error? Error 3154: The Backup Set
Holds A Backup Of A Database Other Than The Existing Database?
Answer :
The error comes when you are trying to restore the DB which already exists. Use
WITH REPLACE option to restore the DB with a different name

8. Question 8. Does Dbcc Checkdb Requires Db To Be In Single_user Mode?

Answer :
Yes and No. This is tricky question. If you are using repair option with CHECKDB
then you have to have the DB in single user mode. Following is the method to
have your DB in a single user mode.
Use master
go sp_dboption dbname, single, true
Following is the error which you get when you run the DBCC CHECKDB with
repair option wo having the DB in single user mode. The same is true for DBCC
CHECKDB also

9. Question 9. How To View The Error Log For Any Specific Instance?
Answer :
There are many ways but I prefer following method. Take a scenario when you
want to find the error log when the DB was put in a single user mode.
CREATE TABLE #Errorlog (Logdate Datetime, Processinfo
VARCHAR(20),Text VARCHAR(2000))
INSERT INTO #Errorlog
EXEC xp_readerrorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLE%USER%’

10. Question 10. According To You What Goes Into Making The Best
Database Administrator?
Answer :
The primary job of DBAs is to secure the data. They should be able to keep it safe
as well as reproduce it efficiently, whenever required. So as per my view, a
Database Administrator who can fulfill the requirements of Securing Data and
Retrieving Data is the best DBA.
When I hire a DBA I always ask them questions about backup strategies and
efficient restoring methodologies.

11. Question 11. I Have All The Primary Data Files, Secondary Data Files
As Well As Logs. Now, Tell Me Can I Still Restore The Database Without
Having A Full Backup?
Answer :
You cannot restore the database without having a full database backup.
However, if you have the copy of all the data files (.mdf and .ndf) and logs (.ldf)
 when database was in working condition (or your desired state) it is possible to
attach the database using sp_attach_db.

12. Question 12. As Per Your Opinion What Are The Five Top
Responsibilities Of A Dba?
Answer :
I rate the following five tasks as the key responsibilities of a DBA.
o Securing database from physical and logical integrity damage.
o Restoring database from backup as a part of disaster management plan.
o Optimizing queries performance by appropriate indexing and optimizing
joins, where conditions, select clause etc.
o Designing new schema, support legacy schema, and legacy database
systems.
o Helping developers improve their SQL-related code writing skill.

13. Question 13. One Of The Developers In My Company Moved One Of

The Columns From One Table To Some Other Table In The Same
Database. How Can I Find The Name Of The New Table Where The
Column Has Been Moved?
Answer :
This question can be answered by querying system views.
For SQL Server 2005 run the following code:
SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.columns
WHERE name = 'YourColumnName'
The previous query will return all the tables that use the column name specified
in the WHERE condition. This is a very small but a very handy script.

14. Question 14. What Is The Difference Between Sql Server 2000
Object Owner And Sql Server 2005 Schema?
Answer :
Let us first see the fully qualified query name to access a table for SQL Server
2000 and SQL Server 2005.
SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]
In SQL Server 2000, prior to dropping the user who owns database objects, all
the objects belonging to that user either need to be dropped or their owner has
to be changed. Every time a user is dropped or modified, system admin has to
undergo this inconvenient process.
In SQL Server 2005 and the later versions, instead of accessing a database
through database owner, it can be accessed through a schema. Users are
assigned to schemas, and by using this schema a user can access database
objects. Multiple users can be assigned to a single schema, and they all can
automatically receive the same permissions and credentials as the schema to
which they are assigned. Because of the same reason in SQL Server 2005 and
 the later versions – when a user is dropped from database – there is no negative
effect on the database itself.
15. Question 15. What Is Bi? I Have Heard This Term Before But I Have
No Idea About It?
Answer :
BI stands for Business Intelligence. Microsoft started to promote the acronym BI
since the launch of SQL Server 2005. However, it has been in use for a long time.
The basic idea of BI is quite similar to Data Warehousing. Business intelligence is
a method for storing and presenting accurate and timely key enterprise data to
CXO, IT Managers, Business Consultants, and distributed teams of a company, to
provide them with up-to-date information to drive intelligent decisions for
business success, which ultimately leads to enhanced revenue, reduced risk,
decreased cost, and better operational control for business agility and
competitiveness. An effective BI empowers end users to use data to understand
the cause that led to a particular business result, to decide on the course of
action based on past data, and to accurately forecast future results.

16. Question 16. What Is Your Recommendation For A Query Running

Very Slow?
Answer :
Well, your question is very difficult to answer without looking at the code,
application and physical server. In such situations, there are a few things that
must be paid attention to right away.
Restart Server
Upgrade Hardware
Check Indexes on Tables and Create Indexes if necessary Make sure SQL Server
has priority over other operating system processes in SQL Server settings.
Update statistics on the database tables.

17. Question 17. What Should Be The Fill Factor For Indexes Created On
Tables?
Answer :
Fill factor specifies a percentage that indicates how full the Database Engine
should make the leaf level of each index page during index creation or alteration.
Fill factor must be an integer value from 1 to 100. The default is 0. I prefer to
keep my servers default fill factor as 90.

18. Question 18. Which Feature In Sql Server 2008 Has Surprised You?
You Can Name Just One.
Answer :
Plan Freezing is a new feature I never thought of. I find it very interesting! It is
included in SQL Server 2008 CTP5. SQL Server 2008 enables greater query
performance stability and predictability by providing new functionality to lock
down query plans. This empowers organizations to promote stable query plans
across hardware server replacements, server upgrades, and production
deployments.
19. Question 19. How Do You Test Your Database?
Answer :
This is a very generic question. I would like to describe my generic database
testing method as well as stored procedure testing methods.
Testing Databases:
Table Column data type and data value validation.
Index implementation and performance improvement.
Constraints and Rules should be validated for data integrity.
Application field length and type should match the corresponding database field.
Database objects like stored procedures, triggers, functions should be tested
using different kinds of input values and checking the expected output variables.

Testing Stored Procedures:

Understand the requirements in terms of Business Logic.
Check if the code follows all the coding standards.
Compare the fields’ requirements of application to the fields retrieved by a
stored procedure. They must match.
Repeatedly run the stored procedures several times with different input
parameters and then compare the output with the expected results.
Pass invalid input parameters and see if a stored procedure has good error
handling.

20. Question 20. What Are System Databases Into Sql Server
(2005/2008)
Answer :
TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

21. Question 21. What Stored By The Tempdb ?

Answer :Row versions, cursor, temp objects.

22. Question 22. What Stored By The Model?

Answer :
Templates of new database objects, like tables and column.

23. Question 23. What Stored By The Master?

Answer :
Server’s configurations and logins.

24. Question 24. What Stored By The Msdb?

Answer :
Scheduled jobs, Backup/Restore and DTA information.

25. Question 25. Can We Perform Backup Restore Operation On

Tempdb?
Answer :
 NO

26. Question 26. What Is Stored In The Mssqlsystemresource Database?

Answer :
Definition of sys objects, which logically shows into all database and DMVs.

27. Question 27. Where The Sql Logs Gets Stored?

Answer :
It’s stored into root folder SQL server, LOG folder.

28. Question 28. What Are The Joins In Sql Server?

Answer :
Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.

29. Question 29. Describe The Left Outer Join & Right Outer Join.
Answer :
Left Outer join Retrieves the all records from LEFT table and matching from the
RIGHT table, and null values where is no match. Right Outer Join just opposite.

30. Question 30. How To Find The Version Of Sql Server?

Answer :
Select @@version

31. Question 31. How To Find The Service Pack Installed?

Answer :
Select @@version Or select serverproperty (‘productlevel’)

32. Question 32. What Are The Difference Between Primary Key And
Unique Key?
Answer :
An unique key cant not be referenced as foreign key. And it may allow on null.

33. Question 33. What Is Mean By Clustered Index And Non Clustered
Index, Give Syntax Of Creation?
Answer :
create clustered index index_name on empmst(card)

34. Question 34. What Is Scan Table/view And Seek Table/view When Its
Occurs?
Answer :
A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all
data, row by row, to find the match.
35. Question 35. What Is Sql Profiler. What Are The Default Templates
With It?
Answer :
SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an
instance of the Database Engine or Analysis Services. You can capture and save
data about each event to a file or table to analyze later.

36. Question 36. What Are The Dmvs?

Answer :
Dynamic Management Views (DMV) return server state information that can be
used to monitor the health of a server instance, diagnose problems, and tune
performance.

37. Question 37. What Is The Syntax To Execute The

Sys.dm_db_missing_index_details?
Answer :
Select * from sys.dm_db_missing_index_details

38. Question 38. What Is Lock Escalation?

Answer :
The Query Optimizer initially locks the required Rows for DML operations /
Retrieval operations. This also get relevant Pages and Completely table to be
'INTENT' Locked. If more than 50% of rows are specifically locked then this
automatically gets the complete PAGE or TABLE to be locked. Lock Escalation
mechanism can be controlled by using Locking Hints.

39. Question 39. How To Truncate The Log In Sql Server 2012?
Answer :
BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server does not allow
you to truncate the log now otherwise whole purpose of a DB is defeated. You
have to make sure whether you need log or not. If you do not need log then have
the recovery model simple instead of full. If you do not want the log to be
accumulated in some particular bulk logging then change the recovery model
BULK LOGGED for that duration and take one TLog Backup just before and after
this change. I shall discuss this later in my later blog. BACKUP LOG command
backs up the t-log and frees the space in the log file.

40. Question 40. What Is The Purpose Of Sql Profiler In Sql Server?
Answer :
SQL profiler is a tool to monitor performance of various stored procedures. It is
used to debug the queries and procedures. Based on performance, it identifies
the slow executing queries. Capture any problems by capturing the events on
production environment so that they can be solved.

41. Question 41. What Is Copyonly Data Backup? How This Is Useful?
 Answer :
There are 21 types of Backups in SQL Server. A non-base FULL BACKUP is called
COPYONLY Data Backup. This is used to service 'on demand' Backup requests
from end users. Using this type of Backups, we can service the 'on demand'
backup requests from end users without disturbing the Backup sequence of Jobs /
Maintenance Plans or other differential Backups. This gives us easy restore paths
as well in case of DR process.

42. Question 42. What Are Truncate Options Available In Sql Server?
Answer :
Use TRUNCATE_ONLY option for SQL Server 2005 systems while performing
Backup. This option is not available in SQL Sever 2008 and R2. Instead, we use
ON_TRUNCATE option available in the Backup statement.

43. Question 43. What Are Advantages Of Peer-peer Replication?

Answer :
Peer-Peer Replication decreases / nullifies the dependency on Distributor. In this
Replication topology each node is Publisher, Distributor and Subscriber. This
increases availability of the database system and Failure of any node does not
impact the health of Replication process. This topology also offers automatic
conflict detection and correction. Hence, recommended in Realtime.

44. Question 44. What Options We Use To Secure Replication Data?

Answer :
Ensure that SQL Browser is running and TCP/IP is enabled. Enforce TDE
(Transparent Data Encryption) so that every data bit is encrypted.

45. Question 45. What Are The Uses Of Standby Mode In Log Shipping?
Answer :
If the Restore Jobs is scheduled / delayed for longer intervals this option can be
used. As uses needs to be disconnected every time Restore Job runs(to ensure
timely shipping of Transaction Logs) its recommended not to use this option for
regular, less interval Log Shipping configurations. Using this option gives us
advantage of Load Balancing for READONLY connections and Reporting purposes
at the cost of timely disconnections to help success of Restore Jobs.

46. Question 46. When Does Error 3154 Occur?

Answer :
The error comes when you are trying to restore the DB which already exists. Use
WITH REPLACE option to restore the DB with a different database name.

47. Question 47. What Are Recommended Options To Be Used While

Using Db Mirroring?
Answer :
 Database Mirroring is to be configured with TCP Protocol and ensure that data
over each endpoint is encrypted. Better to make use of TDE for more security.

48. Question 48. Where Can You Find The Error Log Information?
Answer :
We can make use of SQL Profiler , SQL Server Log or use xp_readerrorlog
extended Stored Procedure to retrieve the error log information.

49. Question 49. What Is The Status Of Services On Passive Node For
Failover Cluster In Sql Server?
Answer :
SQL services will be in manual and stopped. Cluster service will be in automatic
and started mode on both the nodes.

50. Question 50. Can You Move The Resources After Pausing The Node?
Answer :
Yes resources can be moved after pausing the node. But we can't move them
back till the node is paused.

51. Question 51. What Happens If We Start The Full Text Service On
Passive Node.
Answer :
This can be started on both the nodes as this doesn't have any dependency on
SQL service or any resource which is possessed by active node.

52. Question 52. What Is Data Compression?

Answer :
In SQL SERVER 2008 R2, Data Compression comes in two types viz., Row
Compression where It minimizes the metadata (column information, length,
offsets, etc.) associated with each record. Numeric data types and fixed length
strings are stored in variable-length storage format, just like Varchar. Page
compression uses the Row compression technique internally and also applies
prefix level compression.For every column in a page, duplicate prefixes are
identified. These prefixes are saved in compression information headers (CI)
which reside after page headers. A reference number is assigned to these
prefixes and that reference number is replaced wherever those prefixes are
being used.

53. Question 53. What Are The Basic Functions For Master, Msdb,
Model, Tempdb And Resource System Databases?
Answer :
o The master database holds information for all databases located on the
SQL Server instance. As SQL Server cannot start without a functioning
master database, we must administer this database with care and
monitor Startup Parameters in Configuration Manager.
 o The msdb database stores information regarding database backups, SQL
Agent information, DTS packages, SQL Server jobs, and some replication
information such as for log shipping.
o The tempdb holds temporary objects such as global and local temporary
tables and stored procedures.
o The model is essentially a template database used in the creation of any
new user database created in the instance.
o The resource Database is a read-only database that contains all the
system objects that are included with SQL Server. SQL Server system
objects, such as sys.objects, are physically persisted in the Resource
database, but they logically appear in the sys schema of every database.
The Resource database does not contain user data or user metadata.
54. Question 54. What Is Service Broker?
Answer :
Service Broker is a message-queuing technology in SQL Server that allows
developers to integrate SQL Server fully into distributed applications. Service
Broker is feature which provides facility to SQL Server to send an asynchronous,
transactional message. it allows a database to send a message to another
database without waiting for the response, so the application will continue to
function if the remote database is temporarily unavailable.

55. Question 55. Where Sql Server User Names And Passwords Are
Stored In Sql Server?
Answer :
They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

56. Question 56. What Is Policy Based Management (pbm)?

Answer :
Policy Based Management in SQL SERVER 2012 Administration allows you to
define and enforce policies for configuring and managing SQL Server across the
enterprise. Policy-Based Management is configured in SQL Server Management
Studio (SSMS). Navigate to the Object Explorer and expand the Management
node and the Policy Management node; you will see the Policies, Conditions, and
Facets nodes.

57. Question 57. What Is Replication With Database Mirroring?

Answer :
Database mirroring can be used with replication to provide availability for the
publication database. Database mirroring involves two copies of a single
database that typically reside on different computers. At any given time, only
one copy of the database is currently available to clients which are known as the
principal database. Updates made by clients to the principal database are
applied on the other copy of the database, known as the mirror database.
Mirroring involves applying the transaction log from every insertion, update, or
deletion made on the principal database onto the mirror database.
58. Question 58. What Are Sparse Columns In Sql Server?
Answer :
A sparse column is another tool used to reduce the amount of physical storage
used in a database. They are the ordinary columns that have an optimized
storage for null values. Sparse columns reduce the space requirements for null
values at the cost of more overhead to retrieve non null values.

59. Question 59. What Are The Steps To Take To Improve Performance
Of A Poor Performing Query?
Answer :
Steps to take to improve performance of queries:
o Use indexes efficiently
o Create all primary and foreign keys and relationships among tables.
o Avoid using cursors
o Avoid using Select*, rather mention the needed columns and narrow the
resultset as needed.
o Denormalize
o Use partitioned views
o Use temporary tables and table variables
o Reduce joins and heavy clauses like GROUP BY if not needed
o Implement queries as stored procedures.
o Have a WHERE Clause in all SELECT queries.
o Use data types wisely
o Instead of NULLS use string values such as N/A

60. Question 60. What Is A Deadlock And What Is A Live Lock? How Will
You Go About Resolving Deadlocks?
Answer :
o Deadlock occurs when two user processes/transactions have locks on 2
separate objects and each process is trying to acquire a lock on the
object that has been acquired by the other process. In such a scenario
each process is waiting for the other process to release the lock to
acquire a lock on the object itself. When a request for exclusive lock is
denied again and again because a series of overlapping shared locks are
interfering with each other and to adapt from each other they keep on
changing the status, it is known as live lock.
o One can resolve deadlocks by using TRY CATCH blocks. If the code inside
a TRY statement fails, the CATCH automatically catches the control of
the flow letting the transaction rollback and resume execution.

61. Question 61. What Is Blocking And How Would You Troubleshoot It?
Answer :
Blocking occurs when a process has acquired lock on a set of rows, and another
process is trying to acquire a lock on the same set of rows. In such a case, the
other process has to wait until the first process finishes its job and releases the
lock on the above said rows.
 o Use sp_lock procedure to see type of locks acquired by various sessions
on the server to find the cause of blocking.
o Problem is hinted by the WAIT status is a lot of rows that are returned as
an output of sp_lock stored procedure execution.
o Use sp_who and sp_who2 to return more columns to get more
information around the blocking.
o Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of
the last T-SQL statement executed from connection referred through
spid. This way one can identify the stored procedure or application
module that caused blocking.
o To resolve blocking, you can disconnect the connection causing the
blocking using KILL command. If this does not solve the problem
permanently, then rewrite the stored procedure/module causing the
block more efficiently.

62. Question 62. Explain The Different Types Of Backups Available In

Sql Server?
Answer :
Types of backups available in SQL Server:
o Complete: This creates a complete stand alone image of the database.
This backup is self dependent and can be restored to either the same or
a new database on same or other server.
o Differential: This backs up only the modified contents since the last
backup. They do not provide much flexibility.
o Transaction log: This backs up all transaction logs since the previous
transaction log backup or the complete transaction log backup if there
has not been one in past.
o Files and Filegroups backup: This option is suitable when time constraints
are high and one cannot afford to perform a complete database backup.
It also needs transaction logs backup to take place to make it worth
choosing this option. After restoring file backup, apply transaction logs to
roll the file contents forward to make it consistent with the database..

63. Question 63. What Is Database Isolation In Sql Server?

Answer :
Database isolation comes into play when we need to isolate the database and
protect it from other things on the network. This protection is achieved using
locks. The type of lock and the level of isolation level needed is referred as
isolation level in SQL Server.
Types of isolation levels:
READ COMMITTED: Shared locks are held while any data is being read.
READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no
shared locks or exclusive locks. Lease restrictive of all the isolation levels.
REPEATABLE READ: Locks are applied on all data being used by a query.
However, new phantom rows can be inserted into the data set by another user
and are included in later reads within the current transaction.
 SERIALIZABLE: Issues a range lock on data set, preventing other users to
update or insert data into dataset until the transaction is complete.

SQL Server Interview Questions

What is SQLOS?

SQLOS stands for SQL Server Operating System. SQLOS is the lowest layer of SQL Server Database Engine
which is responsible to perform critical internal tasks like scheduling threads on CPU to execute SQL Serve
transactions, memory management, deadlock detection, and IO completion activities. It works just like
another mini operating system for SQL Server operations.

What is the ghost cleanup process in SQL Server?

Any record deleted from SQL Server is not actually deleted from its physical data pages but marked as “To
BE DELETED” or “GHOSTED” by changing a bit in the row header. These records will be cleaned up by an
internal process that is called the ghost cleanup process. This is a single-threaded background process that
automatically runs on some intervals to check if any page is marked for ghost records or not and if it finds
any, then it physically removes those ghosted records from pages.

What would be the potential reason behind SQL Server connection issues?

You might be failed to connect to SQL Server instance because of various reasons such as:

 Ensure you are entering the right connection details like server name and port no
 Ensure SQL Server port is open from the machine you are connecting to the instance
 Make sure SQL Server instance is running
  TCP or named pipe protocols are enabled through SQL Server configuration manager
 SQL browser service is not running

Is it possible that database physical file names also changed after renaming a database?

No, renaming a database will not change its physical file names. You need to manually perform additional
steps to change the database physical file names.

Explain how will you change the physical file name of a data file?

Changing the physical database file name is multistep activity. We must not perform such activities on regular
basis and only do them on a need basis. Below are the high-level steps to change a physical file of a database.

 Set database in OFFLINE state

 Go to the location where your database files are saved and rename each file as per your desired
naming convention

 Update system catalog with new files names by running ALTER DATABASE statement for each file
separately

USE master
1
GO
2
ALTER DATABASE SQLSHACK MODIFY FILE (Name='SQLSHACK_Data1', FILENAME='F:\
3
MSSQL12.MSSQLSERVER\MSSQL\DATA\SQLSHACK_Renamed.mdf')
4
GO

 Bring database ONLINE

How do you get I/O statistics of a specific database file like the number of reads, writes, IO stalls, etc.?

We can use either the system dynamic management function sys.dm_io_virtual_file_stats or a system function
fn_virtualfilestats to get this information. We need to pass database and file id details about that specific
database file for which we are looking for IO statistics. Below is its example. I am pulling IO statistics for the
master database log file.

1 SELECT * FROM sys.dm_io_virtual_file_stats(db_id, file_id);

2 GO
3 SELECT * FROM fn_virtualfilestats(dbid,fileid)

What is Automatic Seeding in Always on availability group?

Automatic seeding is a feature launched in SQL Server 2016 to initialize the secondary replica. Earlier we
have only one option to initialize secondary replica that is using backup, copy and restore database operation.
We use this feature while creating the always on availability group. Although Microsoft has given this feature,
but it is not suitable for very big databases or if you have multiple databases on your SQL Server instance
because this feature is a single-threaded process that affects performance and can take long hours to initialize
your secondary replica.

Can you explain the limitation of Basic availability groups?

 Basic availability groups replace database mirroring features. It provides us to maintain a single secondary
replica. Below is its main limitation when we compare it with standard availability groups.

 Only 2 replicas can be created. One primary replica and another one is a secondary replica
 Only 1 database will work as an availability database just like in database mirroring
 Secondary replica cannot be used for reading transactions, database backups, or other database
maintenance activities
 Basic availability groups cannot be part of distributed availability groups
 We cannot upgrade basic availability groups to standard availability groups. We need to drop them
and reconfigure standard availability groups from scratch

What is Enhanced Database Failover in Always on availability group?

Microsoft has added a new feature in SQL Server 2016 availability groups where you can configure your
databases participating in availability groups to failover if they are unable to do write operations. Earlier
versions of SQL Server were not triggering failover if any database from the availability group was unable to
write operation. It is also called database level health detection in the availability group. This is an optional
feature that can be configured during creating an availability group or you can even configure it by running
the below ALTER statement for existing availability groups.

1 --Enable Enhanced database failover on an existing availability group

2 ALTER AVAILABILITY GROUP [AGNAME] SET (DB_FAILOVER = ON)
3
4 --Disable Enhanced database failover on an existing availability group
5 ALTER AVAILABILITY GROUP [AGNAME] SET (DB_FAILOVER = OFF)

How do you know whether Enhanced Database Failover is configured or enabled for your availability
group or not?

We can get this information by accessing a dynamic management view sys.availability_groups. This DMV
has a column named DB_Failover, if the value of this column is 0 it means enhanced database failover is not
enabled and if the value of this column is 1 then enhanced database failover is enabled for that availability
group.

Can we run DBCC CHECKDB on the secondary replica of the availability group?

Yes, we can execute CHEKDB on a secondary replica for database integrity check. This will ensure the
integrity of the database hosted on secondary replica only. This execution will not guarantee the integrity of
the database hosted on a primary replica as a primary replica uses a different set of storage systems to host
their databases.

Can we create database snapshots of a secondary database in the availability group?

Yes, database snapshots can be created of primary or secondary databases if their role state is either
PRIMARY or SECONDARY. Database snapshots cannot be created if the role state is RESOLVING.

You have created a database snapshot on the primary replica. What would happen to the database snapshot if
the primary replica failed over to the secondary replica?

Database snapshots will not have any impact. They will be there on previous primary replica and you can use
them for your requirements.
Can we get how much space is needed in the tempdb database to run DBCC CHECKDB without
running its actual execution?

DBCC CHECKDB heavily uses a tempdb database to temporarily store data and perform activities that are
needed for integrity check like reading each data page and ensuring there is no corruption. We can use DBCC
CHECKDB WITH ESTIMATEONLY statement to predict the estimated amount of tempdb space before
its actual execution.

Can we change the restore mode of the secondary database from NORECOVERY to STANDBY in
SQL Server log shipping configuration?

Yes, we can change the secondary database restore mode from restoring to standby to use it for read-only
operations. We will use a system stored procedure sp_change_log_shipping_secondary_database to make
this change. This stored procedure will take an input value 1 for argument @restore_mode while running it.
There are two values for this argument. Use 1 to restore the log with STANDBY and use 0 to restore the log
with NORECOVERY.

What is the functionality of Queue Reader Agent in Merge Replication?

Merge Replication does not use a queue reader agent so there is no functionality of queue reader agent in
Merge replication.

Can we replicate SQL Server data to other RDBMS systems like Oracle?

Yes, SQL Server replication can be used to publish data from SQL Server to Oracle or Oracle to SQL Server.

Can we prevent replicating DELETE operations for a specific article in Replication?

DELETE statements can be prevented to replicate any articles in SQL Server replication. We can get this
done by accessing the properties window of the identified article and choose not to replicate delete statements
there.

What would be the impact on Log shipping if the SQL Server Agent job is stopped and not running?

Log shipping relies on agent jobs to do all its activities. If SQL Server Agent is stopped, log shipping will not
perform these activities of backup, copy, and restore and can become out of sync if the agent will be stopped
for a longer duration.

What types of replication are supported in SQL server?

Replication is the process by which data is stored in more than one place in a database. This is a general
question that tests your knowledge of data processing in an SQL system.

Example: “There are three types of replication in SQL server, namely snapshot, merge and transaction.

*Snapshot replication: This kind of replication involves a snapshot of the publisher’s database that is then
distributed to subscribers. Snapshot replication is rarely used, as it’s time and resource intensive, but would,
for instance, be applicable when a subscriber’s database needs to be completely overwritten.*

*Merge replication: This replication is well-suited when both the publisher and subscriber need to make
changes to their databases and these changes have to be merged.*
*Transaction replication: This kind of replication takes place when changes happen frequently. The
replication process constantly monitors the publisher for changes and then updates subscribers accordingly.”*

How do you troubleshoot a performance issue on your SQL Server?

As a database administrator, troubleshooting database issues should be your specialty and is an essential
function of the job. Answering this question allows you to show, by example, that you understand how to
handle performance issues that are a part of the DBA role.

In the example below, you’ll see the STAR method of answering interview questions is used in part of the
answer. This is a method that asks you to speak from your experience by considering a challenging situation,
your role in the situation, what tasks were performed to solve it and what overall outcome was achieved.

Example: “The most common SQL server issues relate to CPU, memory, networking, and I/O bottlenecks. To
get to the bottom of the problem, there are a few troubleshooting tools you can use.

*Performance Monitor: This is a Windows system monitoring tool that displays metrics with regard to key
system components such as CPU, memory, disks, and networking.*

*Execution Plan: This is a useful SQL server tool, as query execution plans can be used to establish
processes that need to be fine-tuned for a query to run faster.*

*SQL Profiler: This tool helps you trace and troubleshoot problems in SQL server, by, for instance, tracing
and evaluating database queries.*

*DMV and DMF Queries: Dynamic Management Views and Functions are system views and functions that
allow administrators to monitor the performance of the SQL server instance, in this way diagnosing issues.*

*SP_WHO2: This is a defined stored procedure that will give you information on current users, sessions and
processes in a SQL server instance.*

What is blocking and what’s the best way to deal with it?

To deal with reads and writes from multiple inputs in a database, either a read lock or an exclusive lock has to
be applied to a piece of data. To prevent data changes while a person is reading it, a read lock is applied on
that data and, if data needs to be edited, an exclusive lock is used to prevent people from reading the data
while it's being changed.

These two important features of a database result in the intentional blocking of data. Blocking is important for
administrators to understand because it’s a function they may be called upon to use or troubleshoot.
Answering this question also gives you the chance to explain a database issue in simple, clear terms that even
people without the same level of technical expertise can understand.

Example: “Blocking occurs when one connection is holding a lock on a resource while a second connection
wants to read or write to it. An even more complex situation occurs when two connections each hold a lock
but want to access each other’s resource, which is referred to as deadlocking.

Two effective ways of locating and dealing with block issues are:

*The SP_WHO2: This stored procedure will provide you with a list of queries, and any process that is locked
will display the SPID of the process that is causing the block.*
*The DBCC set of commands, and more specifically DBCCINPUTBUFFER, can also be used to trace the
“bad query” that is blocking other processes.*

*SQL Server Management Activity Monitor: This tool can also be used to view processes to find out
which ones are in a blocked state.”*

What is DBCC?

DBCC is a hub of essential functions for any DBA. To appropriately answer this question, rather than recite
the meaning of the acronym, instead, offer both definition and functional value to your answer.

Example: “DBCC is an acronym for Database Console Commands, and they are grouped in four categories:

*Maintenance commands: These commands allow a DBA to do maintenance activities on a database, such
as clean caches, shrink databases, and maintain database space. Examples of maintenance DBCC commands
include DBCCCLEANTABL, DBCCINDEXDEFRAG and DBCCSHRINKFILE.*

*Information commands: Provides information regarding the database, such as transaction logs, space
usage, and system information. Information commands include DBCCDBINFO, DBCC LOG and
DBCCDBTABLE.*

*Validation commands: These commands are used to check the consistency of the database. Commands
include DBCCCHECKDB, DBCCCHECKCATALOG and DBCCCHECKCONSTRAINTS.*

*Miscellaneous commands: These commands do not fit into the other three categories. Examples are DBCC
HELP, DBCCOUTPUTBUFFER and DBCC SHOW_STATISTICS.”*

Related: Common SQL Joins Interview Questions

What kind of recovery models are there in SQL server?

To set up a functional backup plan, you need to decide on what recovery model you need to set for each
database. This recovery model tells SQL what data should be kept in the transaction log and for what amount
of time. The type of backups you will perform will also depend on the recovery model you have chosen, and
the type of restores that you can do.

For a DBA, recovery is an important function because sometimes data gets lost, accidental changes are made
to it or it gets deleted. To answer this question, be straightforward and detailed with your response.

Example: “There are three types of recovery models:

*Full: This is the most complete recovery model as all database operations are fully logged to the
transaction file, which allows you to recover data to any specific point in time. You can do all kinds of
backups with this recovery model, including full, differential, partial and point-in-time recovery. With this
kind of model, however, you need to ensure that you back up the transaction log periodically, otherwise, it
will just keep growing.*

*Bulked logged: This recovery model is the same as the full recovery model, except that certain bulk
data operations, such as BULK INSERT, SELECT INTO and CREATE INDEX, are not fully logged, with
the effect that they don’t take up so much space. With this recovery model, you can still do a point-in-time
restore, as long as your last transaction log backup didn’t include a bulk operation. You can do all of the
backups with this recovery model too, and should also ensure that you prevent constant expansion of the log
file by scheduling regular backups.*

*Simple: This simple recovery model doesn’t keep log files permanently, but overwrites transactions
once they have been completed. Since the data is overwritten, you will not be able to do a point-in-time
recovery. Your latest restore point will be the latest full or differential backup that was completed. With this
recovery model, however, your transaction log will not keep growing as is the case with the previous two
models.”*

Q1: Working as a SQL Server database administrator, A SQL Server developer asked
you to allow him to view the code definition of the stored procedures in one
database. Which permission should you grant him?

View definition.

Q2: During your absence, you ask one of your colleagues to terminate a specific
session if it takes more than one hour. To which Fixed Server-Level role should you
add his account to perform that task?

Processadmin

Q3: A SQL development engineer claims that each time he tries to run a BULK
INSERT statement, the query failed with permission related error. To which Fixed
Server-Level role should you add that engineer to allow him to execute the BULK
INSERT statement?

Bulkadmin

Q4: While trying to assign tasks to the new database administration team engineers,
you ask one of the engineers to run a DBCC CHECKDB command to check the
consistency of a user database. What server scoped permissions should you grants
to that engineer in order to perform that task?

Alter Server State

Q5: A request received from the development team to grant permission for a specific
service account that will create a new database in the SQL Server instance at the
first application launch. Which permission should be granted to the service account
to achieve that?

DBCreator

Q6: As a SQL Server database administrator, you are planning to perform the
multiple tasks with the minimum effort performed by your team. These tasks involve
scheduling a backup plan for all user databases, checking the databases consistency
once a week, and maintaining the indexes during the weekend. How could you
achieve that?

Creating Maintenance plans.

Q7: After creating a list of linked servers, you need to leave and ask one of your
colleagues to test and reconfigure these linked servers in case there is anything
incorrect. Which permission should you grant the engineer to perform that task?

setupadmin

Q8: You are managing the permission of the new database administration team. To
which fixed server role should you add the SQL Server database administrators in
order to be able to perform any action on the server?

sysadmin

Q9: You asked one of your colleagues from the database administration team to
execute a script that checks the blocking chain by querying a number of dynamic
management objects. Which server scoped permissions should you grant that
engineer to be able to execute this script?

View Server State

Q10: Working as a SQL Server database administrator, you are planning to track and
audit all logon operations at your SQL Server. Which type of triggers can you take
advantage of to achieve that?

LOGON trigger

 Note: To learn more about trends that impact Database Administration, please read this
article: 5 DBMS Trends Impacting Database Administration

Q11: Which tool can you use to check the performance-related information about
CPU times, logins and Disk I/O on your SQL Server instance?

Activity Monitor from the SQL Server Management Studio.

Q12: As a SQL Server database administrator you are responsible for securing your
SQL Server environment. Part of this task, you need to choose the security protocol
that will be used to manage the security policies such as strong passwords, account
locking, and password expiration. Which authentication protocol will you use?
Kerberos Authentication protocol.

Q13: The managed codes are .NET codes that are used to write any complex
procedure or function that cannot be performed using the T-SQL language. After
building that code, which permission should be granted to that assembly in order to
allow the code to access local and network resources, registry and environment
variables?

External_Access

Q14: During the backup job configuration, it is recommended to ensure that the
backup file is valid and will be successfully restored without any errors. Which
backup option should be used to achieve that?

WITH CHECKSUM

Q15: Working as SQL Server database administrator, you are planning to recover a
user database to the Tuesday 7:15 AM point in time, before the database becomes
corrupted.

The full backup is taken every Friday.

The Differential backup is taken every day at 12 AM.

The Transaction Log backup is taken every one hour. And the last log backup file has taken at 7
AM on Tuesday.

How could you restore that database?

1. Take a tail-log backup to cover the changes on Tuesday between 7 AM and 7:15 AM
2. Restore the Full backup using the NORECOVERY option
3. Restore the differential backup taken on Tuesday using the NORECOVERY option
4. Restore the Transaction Log backups starting from the file taken Tuesday 12 AM till 7 AM
and the tail log backup file, where all files will be restored with NORECOVERY except for
the last file that will be restored with RECOVERY

Q16: You are working as a SQL Server database administrator on a company. After
restarting one of the SQL Servers, the SQL service cannot be brought online as the
driver where the tempdb database is stored fails. How could you start the SQL
Server service?

Start SQL Server in minimal configuration mode

Q17: You are a SQL Server database administrator in a company. One of your tasks is
adding another disk drive to your FCI for the SQL Server cluster site.

Which windows tool should you use to perform that?

Failover Cluster Manager

Q18: Assume that you have a data warehouse database that contains 15 large
database data files. Which Backup operation should you use to take a backup from
that database, taking into consideration that they are writing to 3 database data
files only?

File Backup operation

Q19: While trying to take backup for a database, you need to ensure that all backup
sets will be overwritten. Which backup option should you use to achieve that?

WITH INIT

Q20: You are using the Always Encrypted technique for encryption of your critical
data and prevent the SQL Server database administrators from viewing it. Always
Encrypted supports both Deterministic and Randomized encryption types. Which
encryption option is more secure?

Randomized Encryption

SQL Server Backup and Recovery

 Question 1 - What are 2 options to validate whether or not a backup will restore successfully?
o Restore the backup as a portion of a testing process or log shipping.
o Restore the backup with the Verify Only option.
 Additional information - Verifying Backups with the RESTORE
VERIFYONLY Statement

 Question 2 - How can you issue a full backup and not interrupt the LSN's?
o Issue a copy only backup.
o Additional information - COPY_ONLY Backups with SQL Server 2005

SQL Server Performance Tuning

 Question 1 - Name as many native SQL Server performance monitoring and tuning tools that
you know of and their associated value.
o System objects - System objects such as sp_who2, sp_lock, fn_get_sql, etc.
provide a simple means to capture basic metrics related to locking, blocking,
executing code, etc.
  Additional information - SQL Server Command Line Tools To Manage
Your Server
o Profiler - In a nutshell, Profiler provides the lowest common denominator of
activity on a SQL Server instance. Profiler captures per session code with the
ability to filter the data collection based on database, login, host name,
application name, etc. in order to assess the IO, CPU usage, time needed, etc.
 Additional information - SQL Server Performance Statistics Using a
Server Side Trace
o Perfmon\System Monitor - Perfmon\System Monitor is responsible for macro
level metrics related to processes and sub systems.
 Additional information - Free Microsoft Tools to Help Setup and Maintain
PerfMon
o Dynamic Management Views and Functions - New to SQL Server 2005 and
beyond, the Dynamic Management Views and Functions offer a real time view
into the SQL Server sub systems.
 Additional information - Dynamic Management Views and Functions in
SQL Server 2005
o TYPEPERF.EXE - TYPEPERF.EXE is a command line tool included with the
Windows operating system that writes performance data to the command
window or to a file. It is necessary to capture performance data whenever you
are trying to diagnose performance issues on a server. Performance data
provides information on the server's utilization of the processor, memory, and
disk, as well as SQL Server-specific performance data.
 Additional information - How To Collect Performance Data With
TYPEPERF.EXE
o SQL Server Management Studio Built-in Performance Reports - As part of the
installation of SQL Server 2005 and beyond a number of performance-related
reports are installed. To get to these reports open the SQL Server
Management Studio (SSMS) and connect to a SQL Server instance. If you don't
have an instance of Reporting Services installed then the icon will be disabled.
 Additional information
 Built-In Performance Reports in SQL Server 2005
o Additional resources for this question:
 Tutorial - SQL Server Performance Monitoring and Tuning
 Tip Category - SQL Server Performance Tuning

 Question 2 - How do you go about tuning a SQL Server query?

o Identify the query causing the issue.
o Review the query plan by issuing SHOWPLAN_TEXT, SHOWPLAN_ALL,
Graphical Query Plan or sys.dm_exec_query_stats.
o Review the individual query components to determine which components of
the query have the highest cost.
o Outline options to improve the query such as moving from cursor based logic
to set based logic or vice versa, changing the JOIN order, WHERE clause or
ORDER BY clause, adding indexes, removing indexes, creating covering
indexes, etc.
o Test the options to determine the associated performance improvement.
o Implement the solution.
 Additional information - Query Plans in SQL Server 2000 vs SQL Server
2005
 Tip Category - SQL Server Query Plans
  Tutorial - SQL Server Graphical Query Plan

SQL Server Maintenance

 Question 1 - What are the three options in SQL Server 2005 and beyond to rebuild indexes?
o CREATE INDEX with DROP_EXISTING
o DROP INDEX and CREATE INDEX
o ALTER INDEX
 Additional information - Index Rebuilds in SQL Server 2000 vs SQL
Server 2005
 Tip Category - SQL Server Indexing

 Question 2 - Name 3 or more DBCC commands and their associated purpose.

o DBCC CACHESTATS - Displays information about the objects currently in the
buffer cache.
o DBCC CHECKDB - This will check the allocation of all pages in the database as
well as check for any integrity issues.
o DBCC CHECKTABLE - This will check the allocation of all pages for a specific
table or index as well as check for any integrity issues.
o DBCC DBREINDEX - This command will reindex your table. If the indexname is
left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use
the original fillfactor when the table was created.
o DBCC PROCCACHE - This command will show you information about the
procedure cache and how much is being used.
o DBCC MEMORYSTATUS - Displays how the SQL Server buffer cache is divided
up, including buffer activity.
o DBCC SHOWCONTIG - This command gives you information about how much
space is used for a table and indexes. Information provided includes number
of pages used as well as how fragmented the data is in the database.
o DBCC SHOW_STATISTICS - This will show how statistics are laid out for an
index. You can see how distributed the data is and whether the index is really
a good candidate or not.
o DBCC SHRINKFILE - This will allow you to shrink one of the database files. This
is equivalent to doing a database shrink, but you can specify what file and the
size to shrink it to. Use the sp_helpdb command along with the database
name to see the actual file names used.
o DBCC SQLPERF - This command will show you much of the transaction logs are
being used.
o DBCC TRACEON - This command will turn on a trace flag to capture events in
the error log. Trace Flag 1204 captures Deadlock information.
o DBCC TRACEOFF - This command turns off a trace flag.
 Additional information - SQL Server Command Line Tools To Manage
Your Server
 Tip Category - Database Consistency Checks DBCCs

SQL Server Database Design

  Question 1 - What happens when you add a column in the middle of a table (dbo.Test1) in SQL
Server Management Studio?
o Management Studio creates a temporary table called dbo.Tmp_Test1 with the
new structure.
o If there is data in the original table dbo.Test1 this data is inserted into the new
temp table dbo.Tmp_Test1 (now you have two sets of the same data).
o The original table dbo.Test1 is dropped.
o The new table dbo.Tmp_Test1 is renamed to dbo.Test1.
o If the table has indexes all of the indexes are recreated.
 Additional information - Modifying SQL Server database tables and
keeping like columns together

 Question 2 - What are included columns with respect to SQL Server indexing?
o A new type of index was developed in SQL Server 2005 and beyond that
assists in situations where a covering index is needed.
o Indexes with Included Columns are nonclustered indexes that have the
following benefits:
 Columns defined in the include statement, called non-key columns, are
not counted in the number of columns by the database engine.
 Columns that previously could not be used in queries, like
nvarchar(max), can be included as a non-key column.
 A maximum of 1023 additional columns can be used as non-key
columns.
o Additional information - Improve Performance with SQL Server 2005 Covering
Index Enhancements

SQL Server Business Intelligence

 Question 1 - Name some new features from Data Transformation Services to SQL Server
Integration Services.
o SSIS checkpoints.
o SSIS logging.
o SSIS package configurations.
o SSIS Breakpoint.
o Dynamic flat file connections.
o SSIS batch processing.
o MERGE JOIN.
o Additional information - SQL Server Integration Services

 Question 2 - How do you backup Analysis Services databases?

o Create the XML statement to backup the Analysis Services databases, then
create a SQL Server Agent Job to perform the task on a daily basis.
 Additional information - Automating Backups for SQL Server 2005
Analysis Services Databases
SQL Interview Questions

1. What is Database?

A database is an organized collection of data, stored and retrieved digitally from a

remote or local computer system. Databases can be vast and complex, and such
databases are developed using fixed design and modeling approaches.

2. What is DBMS?

DBMS stands for Database Management System. DBMS is a system software

responsible for the creation, retrieval, updation, and management of the
database. It ensures that our data is consistent, organized, and is easily accessible
by serving as an interface between the database and its end-users or application
software.

3. What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference
here, compared to DBMS, is that RDBMS stores data in the form of a collection of
tables, and relations can be defined between the common fields of these tables.
Most modern database management systems like MySQL, Microsoft SQL Server,
Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

4. What is SQL?

SQL stands for Structured Query Language. It is the standard language for
relational database management systems. It is especially useful in handling
organized data comprised of entities (variables) and relations between different
entities of the data.

5. What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases.

On the contrary, MySQL is a relational database management system, like SQL
Server, Oracle or IBM DB2, that is used to manage SQL databases.

6. What are Tables and Fields?

A table is an organized collection of data stored in the form of rows and columns.
Columns can be categorized as vertical and rows as horizontal. The columns in a
table are called fields while the rows can be referred to as records.

7. What are Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be
applied for single or multiple fields in an SQL table during the creation of the table
or after creating using the ALTER TABLE command. The constraints are:
 NOT NULL - Restricts NULL value from being inserted into a column.
 CHECK - Verifies that all values in a field satisfy a condition.
 DEFAULT - Automatically assigns a default value if no value has been specified for the
field.
 UNIQUE - Ensures unique values to be inserted into the field.
 INDEX - Indexes a field providing faster retrieval of records.
 PRIMARY KEY - Uniquely identifies each record in a table.
 FOREIGN KEY - Ensures referential integrity for a record in another table.

8. What is a Primary Key?

The PRIMARY KEY constraint uniquely identifies each row in a table. It must
contain UNIQUE values and has an implicit NOT NULL constraint.
A table in SQL is strictly restricted to have one and only one primary key, which is
comprised of single or multiple fields (columns).

CREATE TABLE Students ( /* Create table with a single field as primary key */
ID INT NOT NULL
Name VARCHAR(255)
PRIMARY KEY (ID)
);

CREATE TABLE Students ( /* Create table with multiple fields as primary key */
ID INT NOT NULL
LastName VARCHAR(255)
FirstName VARCHAR(255) NOT NULL,
CONSTRAINT PK_Student
PRIMARY KEY (ID, FirstName)
);

ALTER TABLE Students /* Set a column as primary key */

ADD PRIMARY KEY (ID);
ALTER TABLE Students /* Set multiple columns as primary key */
ADD CONSTRAINT PK_Student /*Naming a Primary Key*/
PRIMARY KEY (ID, FirstName);

write a sql statement to add primary key 't_id' to the table 'teachers'.

Write a SQL statement to add primary key constraint 'pk_a' for table 'table_a' and fields
'col_b, col_c'.

9. What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. This
provides uniqueness for the column(s) and helps identify each row uniquely.
Unlike primary key, there can be multiple unique constraints defined per table.
The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be
used interchangeably.

CREATE TABLE Students ( /* Create table with a single field as unique */

ID INT NOT NULL UNIQUE
 Name VARCHAR(255)
);

CREATE TABLE Students ( /* Create table with multiple fields as unique */

ID INT NOT NULL
LastName VARCHAR(255)
FirstName VARCHAR(255) NOT NULL
CONSTRAINT PK_Student
UNIQUE (ID, FirstName)
);

ALTER TABLE Students /* Set a column as unique */

ADD UNIQUE (ID);
ALTER TABLE Students /* Set multiple columns as unique */
ADD CONSTRAINT PK_Student /* Naming a unique constraint */
UNIQUE (ID, FirstName);

10. What is a Foreign Key?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially

refers to the PRIMARY KEY in another table. Foreign key constraint ensures
referential integrity in the relation between two tables.
The table with the foreign key constraint is labeled as the child table, and the
table containing the candidate key is labeled as the referenced or parent table.

CREATE TABLE Students ( /* Create table with foreign key - Way 1 */

ID INT NOT NULL
Name VARCHAR(255)
LibraryID INT
PRIMARY KEY (ID)
FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);

CREATE TABLE Students ( /* Create table with foreign key - Way 2 */

ID INT NOT NULL PRIMARY KEY
Name VARCHAR(255)
LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);

ALTER TABLE Students /* Add a new foreign key */

ADD FOREIGN KEY (LibraryID)
REFERENCES Library (LibraryID);

What type of integrity constraint does the foreign key ensure?

Write a SQL statement to add a FOREIGN KEY 'col_fk' in 'table_y' that references 'col_pk'
in 'table_x'.

11. What is a Join? List its different types.

The SQL Join clause is used to combine records (rows) from two or more tables in
a SQL database based on a related column between the two.
 There are four different types of JOINs in SQL:

 (INNER) JOIN: Retrieves records that have matching values in both tables involved in the
join. This is the widely used join for queries.

SELECT *
FROM Table_A
JOIN Table_B;
SELECT *
FROM Table_A
INNER JOIN Table_B;

 LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched
records/rows from the right table.

SELECT *
FROM Table_A A
LEFT JOIN Table_B B
ON A.col = B.col;

 RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched
records/rows from the left table.

SELECT *
FROM Table_A A
RIGHT JOIN Table_B B
ON A.col = B.col;

 FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or
right table.

SELECT *
FROM Table_A A
FULL JOIN Table_B B
ON A.col = B.col;

12. What is a Self-Join?

A self JOIN is a case of regular join where a table is joined to itself based on some
relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN
clause and a table alias is used to assign different names to the table within the
query.

SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee",

B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor"
FROM employee A, employee B
WHERE A.emp_sup = B.emp_id;

13. What is a Cross-Join?

Cross join can be defined as a cartesian product of the two tables included in the
join. The table after join contains the same number of rows as in the cross-product
of the number of rows in the two tables. If a WHERE clause is used in cross join
then the query will work like an INNER JOIN.

SELECT stu.name, sub.subject

FROM students AS stu
CROSS JOIN subjects AS sub;

Write a SQL statement to CROSS JOIN 'table_1' with 'table_2' and fetch 'col_1' from
table_1 & 'col_2' from table_2 respectively. Do not use alias.

Write a SQL statement to perform SELF JOIN for 'Table_X' with alias 'Table_1' and
'Table_2', on columns 'Col_1' and 'Col_2' respectively.

14. What is an Index? Explain its different types.

 A database index is a data structure that provides a quick lookup of data in a
column or columns of a table. It enhances the speed of operations accessing data
from a database table at the cost of additional writes and memory to maintain the
index data structure.

CREATE INDEX index_name /* Create Index */

ON table_name (column_1, column_2);
DROP INDEX index_name; /* Drop Index */

There are different types of indexes that can be created for different purposes:

 Unique and Non-Unique Index:

Unique indexes are indexes that help maintain data integrity by ensuring that no
two rows of data in a table have identical key values. Once a unique index has
been defined for a table, uniqueness is enforced whenever keys are added or
changed within the index.

CREATE UNIQUE INDEX myIndex

ON students (enroll_no);

Non-unique indexes, on the other hand, are not used to enforce constraints on the
tables with which they are associated. Instead, non-unique indexes are used solely
to improve query performance by maintaining a sorted order of data values that
are used frequently.

 Clustered and Non-Clustered Index:

Clustered indexes are indexes whose order of the rows in the database
corresponds to the order of the rows in the index. This is why only one clustered
index can exist in a given table, whereas, multiple non-clustered indexes can exist
in the table.

The only difference between clustered and non-clustered indexes is that the
database manager attempts to keep the data in the database in the same order
as the corresponding keys appear in the clustered index.

Clustering indexes can improve the performance of most query operations

because they provide a linear-access path to data stored in the database.

Write a SQL statement to create a UNIQUE INDEX "my_index" on "my_table" for fields
"column_1" & "column_2".

15. What is the difference between Clustered and Non-clustered

index?

As explained above, the differences can be broken down into three small factors -
 Clustered index modifies the way records are stored in a database based on the indexed
column. A non-clustered index creates a separate entity within the table which references
the original table.
 Clustered index is used for easy and speedy retrieval of data from the database, whereas,
fetching records from the non-clustered index is relatively slower.
 In SQL, a table can have a single clustered index whereas it can have multiple non-
clustered indexes.

16. What is Data Integrity?

Data Integrity is the assurance of accuracy and consistency of data over its entire
life-cycle and is a critical aspect of the design, implementation, and usage of any
system which stores, processes, or retrieves data. It also defines integrity
constraints to enforce business rules on the data when it is entered into an
application or a database.

17. What is a Query?

A query is a request for data or information from a database table or combination

of tables. A database query can be either a select query or an action query.

SELECT fname, lname /* select query */

FROM myDb.students
WHERE student_id = 1;
UPDATE myDB.students /* action query */
SET fname = 'Captain', lname = 'America'
WHERE student_id = 1;

18. What is a Subquery? What are its types?

A subquery is a query within another query, also known as a nested

query or inner query. It is used to restrict or enhance the data to be queried by
the main query, thus restricting or enhancing the output of the main query
respectively. For example, here we fetch the contact information for students who
have enrolled for the maths subject:

SELECT name, email, mob, address

FROM myDb.contacts
WHERE roll_no IN (
SELECT roll_no
FROM myDb.students
WHERE subject = 'Maths');

There are two types of subquery - Correlated and Non-Correlated.

 A correlated subquery cannot be considered as an independent query, but it can refer to

the column in a table listed in the FROM of the main query.
 A non-correlated subquery can be considered as an independent query and the output
of the subquery is substituted in the main query.
 Write a SQL query to update the field "status" in table "applications" from 0 to 1.

Write a SQL query to select the field "app_id" in table "applications" where "app_id" less
than 1000.

Write a SQL query to fetch the field "app_name" from "apps" where "apps.id" is equal to
the above collection of "app_id".

19. What is the SELECT statement?

SELECT operator in SQL is used to select data from a database. The data returned
is stored in a result table, called the result-set.

SELECT * FROM myDB.students;

20. What are some common clauses used with SELECT query in
SQL?

Some common SQL clauses used in conjuction with a SELECT query are as follows:

 WHERE clause in SQL is used to filter records that are necessary, based on specific
conditions.
 ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending
(ASC) or descending order (DESC).

SELECT *
FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;

 GROUP BY clause in SQL is used to group records with identical data and can be used in
conjunction with some aggregation functions to produce summarized results from the
database.
 HAVING clause in SQL is used to filter records in combination with the GROUP BY clause.
It is different from WHERE, since the WHERE clause cannot filter aggregated records.

SELECT COUNT(studentId), country

FROM myDB.students
WHERE country != "INDIA"
GROUP BY country
HAVING COUNT(studentID) > 5;

21. What are UNION, MINUS and INTERSECT commands?

The UNION operator combines and returns the result-set retrieved by two or more
SELECT statements.
The MINUS operator in SQL is used to remove duplicates from the result-set
 obtained by the second SELECT query from the result-set obtained by the first
SELECT query and then return the filtered results from the first.
The INTERSECT clause in SQL combines the result-set fetched by the two SELECT
statements where records from one match the other and then returns this
intersection of result-sets.

Certain conditions need to be met before executing either of the above

statements in SQL -

 Each SELECT statement within the clause must have the same number of columns
 The columns must also have similar data types
 The columns in each SELECT statement should necessarily have the same order

SELECT name FROM Students /* Fetch the union of queries */

UNION
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */
MINUS /* that aren't present in contacts */
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */
INTERSECT /* that are present in contacts as well */
SELECT name FROM Contacts;

Write a SQL query to fetch "names" that are present in either table "accounts" or in table
"registry".

Write a SQL query to fetch "names" that are present in "accounts" but not in table
"registry".

Write a SQL query to fetch "names" from table "contacts" that are neither present in
"accounts.name" nor in "registry.name".

22. What is Cursor? How to use a Cursor?

A database cursor is a control structure that allows for the traversal of records in a
database. Cursors, in addition, facilitates processing after traversal, such as
retrieval, addition, and deletion of database records. They can be viewed as a
pointer to one row in a set of rows.

Working with SQL Cursor:

1. DECLARE a cursor after any variable declaration. The cursor declaration must
always be associated with a SELECT Statement.
2. Open cursor to initialize the result set. The OPEN statement must be called before
fetching rows from the result set.
 3. FETCH statement to retrieve and move to the next row in the result set.
4. Call the CLOSE statement to deactivate the cursor.
5. Finally use the DEALLOCATE statement to delete the cursor definition and release
the associated resources.

DECLARE @name VARCHAR(50) /* Declare All Required Variables */

DECLARE db_cursor CURSOR FOR /* Declare Cursor Name*/
SELECT name
FROM myDB.students
WHERE parent_name IN ('Sara', 'Ansh')
OPEN db_cursor /* Open cursor and Fetch data into @name */
FETCH next
FROM db_cursor
INTO @name
CLOSE db_cursor /* Close the cursor and deallocate the resources */
DEALLOCATE db_cursor

23. What are Entities and Relationships?

Entity: An entity can be a real-world object, either tangible or intangible, that can
be easily identifiable. For example, in a college database, students, professors,
workers, departments, and projects can be referred to as entities. Each entity has
some associated properties that provide it an identity.

Relationships: Relations or links between entities that have something to do with

each other. For example - The employee's table in a company's database can be
associated with the salary table in the same database.
 24. List the different types of relationships in SQL.

 One-to-One - This can be defined as the relationship between two tables where each
record in one table is associated with the maximum of one record in the other table.
 One-to-Many & Many-to-One - This is the most commonly used relationship where a
record in a table is associated with multiple records in the other table.
 Many-to-Many - This is used in cases when multiple instances on both sides are needed
for defining a relationship.
 Self-Referencing Relationships - This is used when a table needs to define a
relationship with itself.

25. What is an Alias in SQL?

An alias is a feature of SQL that is supported by most, if not all, RDBMSs. It is a

temporary name assigned to the table or table column for the purpose of a
particular SQL query. In addition, aliasing can be employed as an obfuscation
technique to secure the real names of database fields. A table alias is also called a
correlation name.

An alias is represented explicitly by the AS keyword but in some cases, the same
can be performed without it as well. Nevertheless, using the AS keyword is always
a good practice.

SELECT A.emp_name AS "Employee" /* Alias using AS keyword */

B.emp_name AS "Supervisor"
FROM employee A, employee B /* Alias without AS keyword */
WHERE A.emp_sup = B.emp_id;

Write an SQL statement to select all from table "Limited" with alias "Ltd".

26. What is a View?

A view in SQL is a virtual table based on the result-set of an SQL statement. A

view contains rows and columns, just like a real table. The fields in a view are
fields from one or more real tables in the database.

27. What is Normalization?

Normalization represents the way of organizing structured data in the database

efficiently. It includes the creation of tables, establishing relationships between
them, and defining rules for those relationships. Inconsistency and redundancy
can be kept in check based on these rules, hence, adding flexibility to the
database.

28. What is Denormalization?

 Denormalization is the inverse process of normalization, where the normalized
schema is converted into a schema that has redundant information. The
performance is improved by using redundancy and keeping the redundant data
consistent. The reason for performing denormalization is the overheads produced
in the query processor by an over-normalized structure.

29. What are the various forms of Normalization?

Normal Forms are used to eliminate or reduce redundancy in database tables. The
different forms are as follows:

 First Normal Form:

A relation is in first normal form if every attribute in that relation is a single-valued
attribute. If a relation contains a composite or multi-valued attribute, it violates the first
normal form. Let's consider the following students table. Each student in the table, has a
name, his/her address, and the books they issued from the public library -

Students Table

Studen Salutati
Address Books Issued
t on

Amanora Park Town Until the Day I Die (Emily Carpenter), Inception
Sara Ms.
94 (Christopher Nolan)

Ansh 62nd Sector A-10 The Alchemist (Paulo Coelho), Inferno (Dan Brown) Mr.

24th Street Park Beautiful Bad (Annie Ward), Woman 99 (Greer

Sara Mrs.
Avenue Macallister)

Ansh Windsor Street 777 Dracula (Bram Stoker) Mr.

As we can observe, the Books Issued field has more than one value per record,
and to convert it into 1NF, this has to be resolved into separate individual records
for each book issued. Check the following table in 1NF form -

Students Table (1st Normal Form)

Studen Salutati
Address Books Issued
t on

Amanora Park Town Until the Day I Die (Emily

Sara Ms.
94 Carpenter)

Amanora Park Town

Sara Inception (Christopher Nolan) Ms.
94

Ansh 62nd Sector A-10 The Alchemist (Paulo Coelho) Mr.

 Studen Salutati
Address Books Issued
t on

Ansh 62nd Sector A-10 Inferno (Dan Brown) Mr.

24th Street Park

Sara Beautiful Bad (Annie Ward) Mrs.
Avenue

24th Street Park

Sara Woman 99 (Greer Macallister) Mrs.
Avenue

Ansh Windsor Street 777 Dracula (Bram Stoker) Mr.

 Second Normal Form:

A relation is in second normal form if it satisfies the conditions for the first normal
form and does not contain any partial dependency. A relation in 2NF has no
partial dependency, i.e., it has no non-prime attribute that depends on any
proper subset of any candidate key of the table. Often, specifying a single column
Primary Key is the solution to the problem. Examples -

Example 1 - Consider the above example. As we can observe, the Students Table
in the 1NF form has a candidate key in the form of [Student, Address] that can
uniquely identify all records in the table. The field Books Issued (non-prime
attribute) depends partially on the Student field. Hence, the table is not in 2NF. To
convert it into the 2nd Normal Form, we will partition the tables into two while
specifying a new Primary Key attribute to identify the individual records in the
Students table. The Foreign Key constraint will be set on the other table to
ensure referential integrity.

Students Table (2nd Normal Form)

Student_I Stude Salutati

Address
D nt on

Amanora Park Town

1 Sara Ms.
94

2 Ansh 62nd Sector A-10 Mr.

24th Street Park

3 Sara Mrs.
Avenue

4 Ansh Windsor Street 777 Mr.

Books Table (2nd Normal Form)

 Student_I
Book Issued
D

Until the Day I Die (Emily

1
Carpenter)

1 Inception (Christopher Nolan)

2 The Alchemist (Paulo Coelho)

2 Inferno (Dan Brown)

3 Beautiful Bad (Annie Ward)

3 Woman 99 (Greer Macallister)

4 Dracula (Bram Stoker)

Example 2 - Consider the following dependencies in relation to R(W,X,Y,Z)

WX -> Y [W and X together determine Y]

XY -> Z [X and Y together determine Z]

Here, WX is the only candidate key and there is no partial dependency, i.e., any
proper subset of WX doesn’t determine any non-prime attribute in the relation.

 Third Normal Form

A relation is said to be in the third normal form, if it satisfies the conditions for the
second normal form and there is no transitive dependency between the non-
prime attributes, i.e., all non-prime attributes are determined only by the
candidate keys of the relation and not by any other non-prime attribute.

Example 1 - Consider the Students Table in the above example. As we can

observe, the Students Table in the 2NF form has a single candidate key
Student_ID (primary key) that can uniquely identify all records in the table. The
field Salutation (non-prime attribute), however, depends on the Student Field
rather than the candidate key. Hence, the table is not in 3NF. To convert it into
the 3rd Normal Form, we will once again partition the tables into two while
specifying a new Foreign Key constraint to identify the salutations for individual
records in the Students table. The Primary Key constraint for the same will be set
on the Salutations table to identify each record uniquely.

Students Table (3rd Normal Form)

Student_I Studen Salutation_

Address
D t ID

1 Sara Amanora Park Town 1

Student_I Studen Salutation_
Address
D t ID

94

2 Ansh 62nd Sector A-10 2

24th Street Park

3 Sara 3
Avenue

4 Ansh Windsor Street 777 1

Books Table (3rd Normal Form)

Student_I
Book Issued
D

Until the Day I Die (Emily

1
Carpenter)

1 Inception (Christopher Nolan)

2 The Alchemist (Paulo Coelho)

2 Inferno (Dan Brown)

3 Beautiful Bad (Annie Ward)

3 Woman 99 (Greer Macallister)

4 Dracula (Bram Stoker)

Salutations Table (3rd Normal Form)

Salutation_ Salutati
ID on

1 Ms.

2 Mr.

3 Mrs.

Example 2 - Consider the following dependencies in relation to R(P,Q,R,S,T)

P -> QR [P together determine C]

RS -> T [B and C together determine D]
Q -> S
 T -> P

For the above relation to exist in 3NF, all possible candidate keys in the above
relation should be {P, RS, QR, T}.

 Boyce-Codd Normal Form

A relation is in Boyce-Codd Normal Form if satisfies the conditions for third normal
form and for every functional dependency, Left-Hand-Side is super key. In other
words, a relation in BCNF has non-trivial functional dependencies in form X –> Y,
such that X is always a super key. For example - In the above example, Student_ID
serves as the sole unique identifier for the Students Table and Salutation_ID for
the Salutations Table, thus these tables exist in BCNF. The same cannot be said
for the Books Table and there can be several books with common Book Names
and the same Student_ID.

30. What are the TRUNCATE, DELETE and DROP statements?

DELETE statement is used to delete rows from a table.

DELETE FROM Candidates

WHERE CandidateId > 1000;

TRUNCATE command is used to delete all the rows from the table and free the
space containing the table.

TRUNCATE TABLE Candidates;

DROP command is used to remove an object from the database. If you drop a
table, all the rows in the table are deleted and the table structure is removed from
the database.

DROP TABLE Candidates;

Write a SQL statement to wipe a table 'Temporary' from memory.

Write a SQL query to remove first 1000 records from table 'Temporary' based on 'id'.

Write a SQL statement to delete the table 'Temporary' while keeping its relations intact.

31. What is the difference between DROP and TRUNCATE

statements?

If a table is dropped, all things associated with the tables are dropped as well. This
includes - the relationships defined on the table with other tables, the integrity
checks and constraints, access privileges and other grants that the table has. To
 create and use the table again in its original form, all these relations, checks,
constraints, privileges and relationships need to be redefined. However, if a table
is truncated, none of the above problems exist and the table retains its original
structure.

32. What is the difference between DELETE and TRUNCATE

statements?

The TRUNCATE command is used to delete all the rows from the table and free
the space containing the table.
The DELETE command deletes only the rows from the table based on the
condition given in the where clause or deletes all the rows from the table if no
condition is specified. But it does not free the space containing the table.

33. What are Aggregate and Scalar functions?

An aggregate function performs operations on a collection of values to return a

single scalar value. Aggregate functions are often used with the GROUP BY and
HAVING clauses of the SELECT statement. Following are the widely used SQL
aggregate functions:

 AVG() - Calculates the mean of a collection of values.

 COUNT() - Counts the total number of records in a specific table or view.
 MIN() - Calculates the minimum of a collection of values.
 MAX() - Calculates the maximum of a collection of values.
 SUM() - Calculates the sum of a collection of values.
 FIRST() - Fetches the first element in a collection of values.
 LAST() - Fetches the last element in a collection of values.

Note: All aggregate functions described above ignore NULL values except for the
COUNT function.

A scalar function returns a single value based on the input value. Following are the
widely used SQL scalar functions:

 LEN() - Calculates the total length of the given field (column).

 UCASE() - Converts a collection of string values to uppercase characters.
 LCASE() - Converts a collection of string values to lowercase characters.
 MID() - Extracts substrings from a collection of string values in a table.
 CONCAT() - Concatenates two or more strings.
 RAND() - Generates a random collection of numbers of a given length.
 ROUND() - Calculates the round-off integer value for a numeric field (or decimal point
values).
 NOW() - Returns the current date & time.
 FORMAT() - Sets the format to display a collection of values.

34. What is User-defined function? What are its various types?

The user-defined functions in SQL are like functions in any other programming
language that accept parameters, perform complex calculations, and return a
 value. They are written to use the logic repetitively whenever required. There are
two types of SQL user-defined functions:

 Scalar Function: As explained earlier, user-defined scalar functions return a single scalar
value.
 Table-Valued Functions: User-defined table-valued functions return a table as output.
o Inline: returns a table data type based on a single SELECT statement.
o Multi-statement: returns a tabular result-set but, unlike inline, multiple SELECT
statements can be used inside the function body.

35. What is OLTP?

OLTP stands for Online Transaction Processing, is a class of software applications

capable of supporting transaction-oriented programs. An essential attribute of an
OLTP system is its ability to maintain concurrency. To avoid single points of
failure, OLTP systems are often decentralized. These systems are usually designed
for a large number of users who conduct short transactions. Database queries are
usually simple, require sub-second response times, and return relatively few
records. Here is an insight into the working of an OLTP system [ Note - The figure
is not important for interviews ] -

36. What are the differences between OLTP and OLAP?

OLTP stands for Online Transaction Processing, is a class of software

applications capable of supporting transaction-oriented programs. An important
attribute of an OLTP system is its ability to maintain concurrency. OLTP systems
often follow a decentralized architecture to avoid single points of failure. These
systems are generally designed for a large audience of end-users who conduct
short transactions. Queries involved in such databases are generally simple, need
 fast response times, and return relatively few records. A number of transactions
per second acts as an effective measure for such systems.

OLAP stands for Online Analytical Processing, a class of software programs

that are characterized by the relatively low frequency of online transactions.
Queries are often too complex and involve a bunch of aggregations. For OLAP
systems, the effectiveness measure relies highly on response time. Such systems
are widely used for data mining or maintaining aggregated, historical data, usually
in multi-dimensional schemas.

37. What is Collation? What are the different types of Collation

Sensitivity?

Collation refers to a set of rules that determine how data is sorted and compared.
Rules defining the correct character sequence are used to sort the character data.
It incorporates options for specifying case sensitivity, accent marks, kana
character types, and character width. Below are the different types of collation
sensitivity:

 Case sensitivity: A and a are treated differently.

 Accent sensitivity: a and á are treated differently.
 Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated
differently.
 Width sensitivity: Same character represented in single-byte (half-width) and double-
byte (full-width) are treated differently.

38. What is a Stored Procedure?

A stored procedure is a subroutine available to applications that access a

relational database management system (RDBMS). Such procedures are stored in
the database data dictionary. The sole disadvantage of stored procedure is that it
can be executed nowhere except in the database and occupies more memory in
the database server. It also provides a sense of security and functionality as users
who can't access the data directly can be granted access via stored procedures.

DELIMITER $$
CREATE PROCEDURE FetchAllStudents()
BEGIN
SELECT * FROM myDB.students;
END $$
DELIMITER ;

39. What is a Recursive Stored Procedure?

A stored procedure that calls itself until a boundary condition is reached, is called
a recursive stored procedure. This recursive function helps the programmers to
deploy the same set of code several times as and when required. Some SQL
programming languages limit the recursion depth to prevent an infinite loop of
procedure calls from causing a stack overflow, which slows down the system and
may lead to system crashes.

DELIMITER $$ /* Set a new delimiter => $$ */

CREATE PROCEDURE calctotal( /* Create the procedure */
IN number INT, /* Set Input and Ouput variables */
OUT total INT
) BEGIN
DECLARE score INT DEFAULT NULL; /* Set the default value => "score" */
SELECT awards FROM achievements /* Update "score" via SELECT query */
WHERE id = number INTO score;
IF score IS NULL THEN SET total = 0; /* Termination condition */
ELSE
CALL calctotal(number+1); /* Recursive call */
SET total = total + score; /* Action after recursion */
END IF;
END $$ /* End of procedure */
DELIMITER ; /* Reset the delimiter */

40. How to create empty tables with the same structure as

another table?

Creating empty tables with the same structure can be done smartly by fetching
the records of one table into a new table using the INTO operator while fixing a
WHERE clause to be false for all records. Hence, SQL prepares the new table with
a duplicate structure to accept the fetched records but since no records get
fetched due to the WHERE clause in action, nothing is inserted into the new table.

SELECT * INTO Students_copy

FROM Students WHERE 1 = 2;

41. What is Pattern Matching in SQL?

SQL pattern matching provides for pattern search in data if you have no clue as to
what that word should be. This kind of SQL query uses wildcards to match a string
 pattern, rather than writing the exact word. The LIKE operator is used in
conjunction with SQL Wildcards to fetch the required information.

 Using the % wildcard to perform a simple search

The % wildcard matches zero or more characters of any type and can be used to
define wildcards both before and after the pattern. Search a student in your
database with first name beginning with the letter K:

SELECT *
FROM students
WHERE first_name LIKE 'K%'

 Omitting the patterns using the NOT keyword

Use the NOT keyword to select records that don't match the pattern. This query
returns all students whose first name does not begin with K.

SELECT *
FROM students
WHERE first_name NOT LIKE 'K%'

 Matching a pattern anywhere using the % wildcard twice

Search for a student in the database where he/she has a K in his/her first name.

SELECT *
FROM students
WHERE first_name LIKE '%Q%'

 Using the _ wildcard to match pattern at a specific position

The _ wildcard matches exactly one character of any type. It can be used in
conjunction with % wildcard. This query fetches all students with letter K at the
third position in their first name.

SELECT *
FROM students
WHERE first_name LIKE '__K%'

 Matching patterns for a specific length

The _ wildcard plays an important role as a limitation when it matches exactly one
character. It limits the length and position of the matched results. For example -

SELECT * /* Matches first names with three or more letters */

FROM students
WHERE first_name LIKE '___%'

SELECT * /* Matches first names with exactly four characters */

FROM students
WHERE first_name LIKE '____'
Conclusion:

SQL is a language for the database. It has a vast scope and robust capability of
creating and manipulating a variety of database objects using commands like
CREATE, ALTER, DROP, etc, and also in loading the database objects using
commands like INSERT. It also provides options for Data Manipulation using
commands like DELETE, TRUNCATE and also does effective retrieval of data using
cursor commands like FETCH, SELECT, etc. There are many such commands which
provide a large amount of control to the programmer to interact with the database
in an efficient way without wasting many resources. The popularity of SQL has
grown so much that almost every programmer relies on this to implement their
application's storage functionalities thereby making it an exciting language to
learn. Learning this provides the developer a benefit of understanding the data
structures used for storing the organization's data and giving an additional level of
control and in-depth understanding of the application.

PostgreSQL being an open-source database system having extremely robust and

sophisticated ACID, Indexing, and Transaction supports has found widespread
popularity among the developer community.

Best SQL Server Interview Questions

Let’s start.

Q #1) Which TCP/IP port does SQL Server run on?

Answer: By default SQL Server runs on port 1433.
Q #2) What is the difference between clustered and non-clustered index?
Answer: A clustered index is an index that rearranges the table in the order of the index itself. Its
leaf nodes contain data pages. A table can have only one clustered index.
A non-clustered index is an index that does not re-arrange the table in the order of the index itself.
Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered
indexes.
Q #3) List the different index configurations possible for a table?
Answer: A table can have one of the following index configurations:
 No indexes
 A clustered index
 A clustered index and many non-clustered indexes
 A non-clustered index
 Many non-clustered indexes
Q #4) What is the recovery model? List the types of recovery models available in SQL
Server?
Answer: 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 SQL server which
backup is possible in a particular selected recovery model.
There are three types of recovery models:
  Full
 Simple
 Bulk-Logged
Q #5) What are the different backups available in SQL Server?
Answer: Different possible backups are:
 Full backup
 Differential Backup
 Transactional Log Backup
 Copy Only Backup
 File and Filegroup backup
Q #6) What is a Full Backup?
Answer: A full backup is the most common type of backup in SQL Server. This is the complete
backup of the database. It also contains part of the transaction log so that it can be recovered.
Q #7) What is OLTP?
Answer: OLTP means Online Transaction Processing which follows rules of data normalization to
ensure data integrity. Using these rules, complex information is broken down into a most simple
structure.
Q #8) What is RDBMS?
Answer: RDBMS or Relational Database Management Systems are database management
systems that maintain data in the form of tables. We can create relationships between the tables.
An RDBMS can recombine the data items from different files, providing powerful tools for data
usage.
Q #9) What are the properties of the Relational tables?
Answer: Relational tables have six properties:
 Values are atomic.
 Column values are of the same kind.
 Each row is unique.
 The sequence of columns is insignificant.
 The sequence of rows is insignificant.
 Each column must have a unique name.
Q #10) What’s the difference between a primary key and a unique key?
Answer: The differences between the primary key and a unique key are:
 The primary key is a column whose values uniquely identify every row in a table. Primary key
values can never be reused. They create a clustered index on the column and cannot be
null.
 A Unique key is a column whose values also uniquely identify every row in a table but they
create a non-clustered index by default and it allows one NULL only.
Q #11) When is the UPDATE_STATISTICS command used?
Answer: As the name implies UPDATE_STATISTICS command updates the statistics used by the
index to make the search easier.
Q #12) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Answer: The differences between HAVING CLAUSE and WHERE CLAUSE is:
 Both specify a search condition but the HAVING clause is used only with the SELECT
statement and typically used with GROUP BY clause.
 If the GROUP BY clause is not used, then the HAVING clause behaves like a WHERE
clause only.
Q #13) What is Mirroring?
Answer: Mirroring is a high availability solution. It is designed to maintain a hot standby server
which 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.
Q #14) What are the advantages of the Mirroring?
Answer: Advantages of Mirroring are:
 It is more robust and efficient than Log shipping.
 It has an automatic failover mechanism.
 The secondary server is synced with the primary in near real-time.
Q #15) What is Log Shipping?
Answer: 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.
Q #16) What are the advantages of Log shipping?
Answer: Advantages of Log Shipping includes:
 Easy to set up.
 The secondary database can be used as a read-only purpose.
 Multiple secondary standby servers are possible
 Low maintenance.
Q #17) Can we take the full database backup in Log shipping?
Answer: Yes, we can take the full database backup. It won’t affect the log shipping.
Q #18) What is an execution plan?
Answer: An execution plan is a graphical or textual way of showing how the SQL server breaks
down a query to get the required result. It helps a user to determine why queries are taking more
time to execute and based on the investigation user can update their queries for the maximum
result.
Query Analyzer has an option, called “Show Execution Plan” (located on the Query drop-down
menu). If this option is turned on, it will display a query execution plan in a separate window when
the query is run again.

Q #19) What is the Stored Procedure?

Answer: A stored procedure is a set of SQL queries that can take input and send back output. And
when the procedure is modified, all clients automatically get the new version. Stored procedures
reduce network traffic and improve performance. Stored procedures can be used to help ensure the
integrity of the database.
Q #20) List the advantages of using Stored Procedures?
Answer: Advantages of using Stored procedures are:
 Stored procedure boosts application performance.
 Stored procedure execution plans can be reused as they cached in SQL Server’s memory
which reduces server overhead.
 They can be reused.
 It can encapsulate logic. You can change the stored procedure code without affecting clients.
 They provide better security for your data.
Q #21) What is identity in SQL?
Answer: An identity column in the SQL automatically generates numeric values. We can be defined
as a start and increment value of the identity column. Identity columns do not need to be indexed.
Q #22) What are the common performance issues in SQL Server?
Answer: Following are the common performance issues:
 Deadlocks
 Blocking
 Missing and unused indexes.
 I/O bottlenecks
 Poor Query plans
 Fragmentation
Q #23) List the various tools available for performance tuning?
Answer: Various tools available for performance tuning are:
 Dynamic Management Views
 SQL Server Profiler
 Server Side Traces
 Windows Performance monitor.
 Query Plans
 Tuning advisor
Q #24) What is a performance monitor?
Answer: 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, Network, etc.
Q #25) What are 3 ways to get a count of the number of records in a table?
Answer:
SELECT * FROM table_Name;

SELECT COUNT(*) FROM table_Name;

SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid&amp;amp;lt; 2;

Q #26) Can we rename a column in the output of the SQL query?

Answer: Yes, by using the following syntax we can do this.
SELECT column_name AS new_name FROM table_name;

Q #27) What is the difference between a Local and a Global temporary table?
Answer: 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.
Q #28) What is the SQL Profiler?
Answer: 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 captures the specific data we want.
Q #29) What do you mean by authentication modes in SQL Server?
Answer: There are two authentication modes in SQL Server.
 Windows mode
 Mixed Mode – SQL and Windows.
Q #30) How can we check the SQL Server version?
Answer: By running the following command:
SELECT @@Version
Q #31) Is it possible to call a stored procedure within a stored procedure?
Answer: Yes, we can call a stored procedure within a stored procedure. It is called the recursion
property of the SQL server and these types of stored procedures are called nested stored
procedures.
Q #32) What is the SQL Server Agent?
Answer: SQL Server agent allows us to schedule the jobs and scripts. It helps in implementing the
day to day DBA tasks by automatically executing them on a scheduled basis.
Q #33) What is the PRIMARY KEY?
Answer: The primary key is a column whose values uniquely identify every row in a table. Primary
key values can never be reused.
Q #34) What is a UNIQUE KEY constraint?
Answer: 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.
Q #35) What is FOREIGN KEY
Answer: When a one table’s primary key field is added to related tables to create the common field
which relates the two tables, it called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.

Q #36) What is a CHECK Constraint?

Answer: 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.
Q #37) What are a Scheduled Jobs?
Answer: The scheduled job allows a user to run the scripts or SQL commands automatically on a
scheduled basis. The user can determine the order in which command executes and the best time
to run the job to avoid the load on the system.
Q #38) What is a heap?
Answer: A heap is a table that does not contain any clustered index or non-clustered index.
Q #39) What is BCP?
Answer: BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and
views. BCP does not copy the structures the same as source to destination. BULK INSERT
command helps to import a data file into a database table or view in a user-specified format.
Q #40) What is Normalization?
Answer: The process of table design to minimize the data redundancy is called normalization. We
need to divide a database into two or more tables and define relationships between them.
Normalization usually involves dividing a database into two or more tables and defining
relationships between the tables.
Q #41) List the different normalization forms?
Answer: Different normalization forms are:
 1NF (Eliminate Repeating Groups): Make a separate table for each set of related
attributes, and give each table a primary key. Each field contains at most one value from its
attribute domain.
 2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key,
remove it to a separate table.
 3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to the
description of the key, remove them to a separate table. All attributes must be directly
dependent on the primary key.
 BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between
candidate key attributes, separate them into distinct tables.
 4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n
or n:m relationships that are not directly related.
 5NF (Isolate Semantically Related Multiple Relationships): There may be practical
constraints on information that justifies separating logically related many-to-many
relationships.
 ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as
expressed in Object Role Model notation.
 DKNF (Domain-Key Normal Form): A model free from all modification is said to be in
DKNF.
Q #42) What is De-normalization?
Answer: De-normalization is the process of adding redundant data to a database to enhance the
performance of it. It is a technique to move from higher to lower normal forms of database modeling
to speed up database access.
Q #43) What is a Trigger and types of a trigger?
Answer: The trigger allows us to execute a batch of SQL code when table event occurs (INSERT,
UPDATE or DELETE command executed against a specific table). Triggers are stored in and
managed by DBMS. It can also execute a stored procedure.
3 types of triggers that are available in the SQL Server are as follows:
 DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of
the DML commands like INSERT, DELETE or UPDATE happens on the table or the view.
 DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any
changes occur in the definition of any of the database objects instead of actual data. These
are very helpful to control the production and development of database environments.
 Logon Triggers: These are very special triggers that fire in case of the logon event of the
SQL Server. This is fired before the setup of a user session in the SQL Server.
Q #44) What is the Subquery?
Answer: A Subquery is a subset of SELECT statements, whose return values are used in filtering
conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause.
It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
Types of Sub-query:
 Single-row sub-query: The subquery returns only one row
 Multiple-row sub-query: The subquery returns multiple rows
 Multiple column sub-query: The subquery returns multiple columns
Q #45) What is a Linked Server?
Answer: Linked Server is a concept by which we can connect another SQL server to a Group and
query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to
add link server.
Q #46) What is Collation?
Answer: Collation refers to a set of rules that determine how data is sorted and compared.
Character data is sorted using rules that define the correct character sequence, with options for
specifying case-sensitivity, accent marks, kana character types, and character width.
Q #47) What is View?
Answer: A view is a virtual table that contains data from one or more tables. Views restrict data
access of the table by selecting only required values and make complex queries easy.
Rows updated or deleted in the view are updated or deleted in the table the view was created with.
It should also be noted that as data in the original table changes, so does data in the view, as views
are the way to look at part of the original table. The results of using a view are not permanently
stored in the database

Q #48) Where SQL server usernames and passwords are stored in a SQL server?
Answer: They get stored in System Catalog Views sys.server_principals and sys.sql_logins.
Q #49) What are the properties of a transaction?
Answer: Generally, these properties are referred to as ACID properties.
They are:
 Atomicity
 Consistency
 Isolation
 Durability
Q #50) Define UNION, UNION ALL, MINUS, INTERSECT?
Answer:
 UNION – returns all distinct rows selected by either query.
 UNION ALL – returns all rows selected by either query, including all duplicates.
 MINUS – returns all distinct rows selected by the first query but not by the second.
 INTERSECT – returns all distinct rows selected by both queries.
Q #51) What is SQL Server used for?
Answer: SQL Server is one of the very popular Relational Database Management Systems. This is
a product from Microsoft to store and manage the information in the database.
Q #52) Which language is supported by SQL Server?
Answer: SQL Server is based upon the implementation of the SQL also known as Structured
Query Language to work with the data inside the database.
Q #53) Which is the latest version of SQL Server and when it is released?
Answer: SQL Server 2019 is the latest version of SQL Server that is available in the market and
Microsoft launched this on November 4th, 2019 with the support of the Linux O/S.
Q #54) What are the various editions of SQL Server 2019 that are available in the market?
Answer: SQL Server 2019 is available in 5 editions. These are as follows:
 Enterprise: This delivers comprehensive high-end datacenter capabilities with blazing-fast
performance, unlimited virtualization, and end-to-end business intelligence for mission-
critical workloads and end-user access to data insights.
 Standard: This delivers basic data management and business intelligence database for
departments and small organizations to run their applications and supports common
development tools for on-premises and cloud-enabling effective database management.
 Web: This edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to
provide scalability, affordability, and manageability capabilities for small to large-scale Web
properties.
 Express: Express edition is the entry-level, free database and is ideal for learning and
building desktop and small server data-driven applications.
 Developer: This edition lets developers build any kind of application on top of SQL Server. It
includes all the functionality of Enterprise edition, but is licensed for use as a development
and test system, not as a production server.
Q #55) What are functions in the SQL Server?
Answer: Functions are the sequence of the statements which accept inputs, process the inputs to
perform some specific task and then provide the outputs. Functions should have some meaningful
name but these should not start with a special character such as %,#,@, etc.
Q #56) What is a User-Defined function in the SQL Server and what is its advantage?
Answer: User-Defined Function is a function that can be written as per the needs of the user by
implementing your logic. The biggest advantage of this function is that the user is not limited to pre-
defined functions and can simplify the complex code of pre-defined function by writing a simple
code as per the requirement.
This returns Scalar value or a table.

Q #57) Explain the creation and execution of a user-defined function in the SQL Server?
Answer: A User-Defined function can be created in the following way:
CREATE Function fun1(@num int)

returns table

as

return SELECT * from employee WHERE empid=@num;

This function can be executed as follows:

SELECT * from fun1(12);

So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an
employee having empid=12.

Q #58) What are the Pre-Defined functions in the SQL Server?

Answer: These are built-in functions of the SQL Server like String functions which are provided by
SQL Server like ASCII, CHAR, LEFT, etc. string functions.
Q #59) Why are Views required in the SQL Server or any other database?
Answer: Views are very beneficial because of the following reasons:
 Views are required to hide the complexity that is involved in the database schema and also
to customize the data for a particular set of users.
 Views provide a mechanism to control access to particular rows and columns.
 These help in aggregating the data to improve the performance of the database.
Q #60) What is TCL in SQL Server?
Answer: TCL is Transaction Control Language Commands which are used to manage the
transactions in the SQL Server.
Q #61) Which TCL Commands are available on the SQL Server?
Answer: There are 3 TCL Commands in the SQL Server. These are as follows:
 Commit: This command is used to save the transaction permanently in the database.
 Rollback: This is used to roll back the changes that are done i.e. to restore the database in
the last committed state.
 Save Tran: This is used for saving the transaction to provide the convenience that the
transaction can be rolled back to the point wherever required.
Q #62) What are the 2 types of classifications of constraints in the SQL Server?
Answer: Constraints are classified into the following 2 types in the SQL Server:
 Column Types Constraints: These constraints are applied to the columns of a table in the
SQL Server. The definition of these can be given at the time of the creation of a table in the
database.
 Table Types Constraints: These constraints are applied on a table and these are defined
after the creation of a table is complete. Alter command is used to apply the table type
constraint.
Q #63) How is table type constraint applied to a table?
Answer: Table Type Constraint is applied in the following way:
Alter Table Name of the Constraint
Alter Table Constraint_1

Q #64) What are the different types of Columns Types Constraints in the SQL Server?
Answer: SQL Server provides 6 types of Constraints. These are as follows:
1. Not Null Constraint: This puts a constraint that the value of a column cannot be null.
2. Check Constraint: This puts a constraint by checking some particular condition before
inserting data in the table.
3. Default Constraint: This constraint provides some default value that can be inserted in the
column if no value is specified for that column.
4. Unique Constraint: This puts a constraint that each row of a particular column must have a
unique value. More than one unique constraint can be applied to a single table.
5. Primary Key Constraint: This puts a constraint to have a primary key in the table to identify
each row of a table uniquely. This cannot be null or duplicate data.
6. Foreign Key Constraint: This puts a constraint that the foreign key should be there. A
Primary key in one table is the foreign key of another table. Foreign Key is used to create a
relation between 2 or more tables.
Q #65) What command is used to delete a table from the database in the SQL Server and
how?
Answer: DELETE Command is used to delete any table from the database in the SQL Server.
Syntax: DELETE Name of the table
Example: If the name of a table is “employee” then DELETE command to delete this table can be
written as
DELETE employee;

Q #66) Why is replication required on the SQL Server?

Answer: Replication is the mechanism that is used to synchronize the data among the multiple
servers with the help of a replica set.
This is mainly used to increase the capacity of reading and to provide an option to its users to select
among various servers to perform the read/write operations.

Q #67) What command is used to create a database in the SQL Server and how?
Answer: CREATEDATABASE Command is used to create any database in the SQL Server.
Syntax: CREATEDATABASE Name of the Database
Example: If the name of a database is “employee” then create command to create this database
that can be written as CREATEDATABASE employee.
Q #68) What function does a database engine serve in the SQL Server?
Answer: Database Engine is a type of service in the SQL Server which starts as soon as the
Operating System starts. This may run by default depending upon the settings in the O/S.
Q #69) What are the advantages of having an index on the SQL Server?
Answer: The index has the following advantages:
 Index supports the mechanism of having faster data retrieval from the database.
 This forms a data structure in a way that helps in minimizing data comparisons.
 This improves the performance of the retrieval of the data from the database.
Conclusion
This is all about SQL Server interview questions. I hope this article must have provided insight
regarding the questions that can be asked in an interview and you can now confidently handle your
interview process.

Practice all the important SQL Server topics for better understanding and appearing for the
interview confidently.

Happy Learning!!
Q1. What is the difference between SQL and MySQL?

SQL vs MySQL

SQL MySQL

SQL is a standard language which stands for

Structured Query Language based on the English MySQL is a database management system.
language

SQL is the core of the relational database which is MySQL is an RDMS (Relational Database
used for accessing and managing database Management System) such as SQL Server,
Informix etc.
Q2. What are the different subsets of SQL?

 Data Definition Language (DDL) – It allows you to perform various operations on the database
such as CREATE, ALTER, and DELETE objects.
 Data Manipulation Language(DML) – It allows you to access and manipulate data. It helps you to
insert, update, delete and retrieve data from the database.
 Data Control Language(DCL) – It allows you to control access to the database. Example – Grant,
Revoke access permissions.

Q3. What do you mean by DBMS? What are its different types?

A Database Management System (DBMS) is a software

application that interacts with the user, applications, and the database itself to capture and
analyze data. A database is a structured collection of data.

A DBMS allows a user to interact with the database. The data stored in the database can be
modified, retrieved and deleted and can be of any type like strings, numbers, images, etc.
There are two types of DBMS:

 Relational Database Management System: The data is stored in relations (tables). Example –
MySQL.
 Non-Relational Database Management System: There is no concept of relations, tuples and
attributes. Example – MongoDB

Let’s move to the next question in this SQL Interview Questions.

Q4. What is RDBMS? How is it different from DBMS?

A relational database management system (RDBMS) is a set of applications and features that
allow IT professionals and others to develop, edit, administer, and interact with relational
databases. Most commercial relational database management systems use Structured Query
Language (SQL) to access the database, which is stored in the form of tables.
The RDBMS is the most widely used database system in businesses all over the world. It offers
a stable means of storing and retrieving massive amounts of data.

Databases, in general, hold collections of data that may be accessed and used in other
applications. The development, administration, and use of database platforms are all supported
by a database management system.

A relational database management system (RDBMS) is a type of database management

system (DBMS) that stores data in a row-based table structure that links related data
components. An RDBMS contains functions that ensure the data’s security, accuracy, integrity,
and consistency. This is not the same as the file storage utilized by a database management
system.

The following are some further distinctions between database management systems and
relational database management systems:

The number of users who are permitted to utilise the system

A DBMS can only handle one user at a time, whereas an RDBMS can handle numerous users.
Hardware and software specifications
In comparison to an RDBMS, a DBMS requires fewer software and hardware.
Amount of information
RDBMSes can handle any quantity of data, from tiny to enormous, whereas DBMSes are
limited to small amounts.
The structure of the database
Data is stored in a hierarchical format in a DBMS, whereas an RDBMS uses a table with
headers that serve as column names and rows that hold the associated values.
Implementation of the ACID principle
The atomicity, consistency, isolation, and durability (ACID) concept is not used by DBMSs for
data storage. RDBMSes, on the other hand, use the ACID model to organize their data and
assure consistency.
Databases that are distributed
A DBMS will not provide complete support for distributed databases, whereas an RDBMS will.
Programs that are managed
A DBMS focuses on keeping databases that are present within the computer network and
system hard discs, whereas an RDBMS helps manage relationships between its incorporated
tables of data.
Normalization of databases is supported
A RDBMS can be normalized , but a DBMS cannot be normalized.

Q5. What is a Self-Join?

A self-join is a type of join that can be used to connect two tables. As a result, it is a unary
relationship. Each row of the table is attached to itself and all other rows of the same table in a
self-join. As a result, a self-join is mostly used to combine and compare rows from the same
database table.

Q6. What is the SELECT statement?

A SELECT command gets zero or more rows from one or more database tables or views. The
most frequent data manipulation language (DML) command is SELECT in most applications.
SELECT queries define a result set, but not how to calculate it, because SQL is a declarative
programming language.

Q7. What are some common clauses used with SELECT query in SQL?

The following are some frequent SQL clauses used in conjunction with a SELECT query:

WHERE clause: In SQL, the WHERE clause is used to filter records that are required
depending on certain criteria.
ORDER BY clause: The ORDER BY clause in SQL is used to sort data in ascending (ASC) or
descending (DESC) order depending on specified field(s) (DESC).
GROUP BY clause: GROUP BY clause in SQL is used to group entries with identical data and
may be used with aggregation methods to obtain summarised database results.
HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is
different from WHERE, since the WHERE clause cannot filter aggregated records.

Q8. What are UNION, MINUS and INTERSECT commands?

The UNION operator is used to combine the results of two tables while also removing duplicate
entries.
The MINUS operator is used to return rows from the first query but not from the second query.
The INTERSECT operator is used to combine the results of both queries into a single row.
Before running either of the above SQL statements, certain requirements must be satisfied –
Within the clause, each SELECT query must have the same amount of columns.
The data types in the columns must also be comparable.
In each SELECT statement, the columns must be in the same order.

Q9. What is Cursor? How to use a Cursor?

After any variable declaration, DECLARE a cursor. A SELECT Statement must always be
coupled with the cursor definition.
To start the result set, move the cursor over it. Before obtaining rows from the result set, the
OPEN statement must be executed.

To retrieve and go to the next row in the result set, use the FETCH command.

To disable the cursor, use the CLOSE command.

Finally, use the DEALLOCATE command to remove the cursor definition and free up the
resources connected with it.

Q10. List the different types of relationships in SQL.

There are different types of relations in the database:

One-to-One – This is a connection between two tables in which each record in one table
corresponds to the maximum of one record in the other.

One-to-Many and Many-to-One – This is the most frequent connection, in which a record in
one table is linked to several records in another.

Many-to-Many – This is used when defining a relationship that requires several instances on
each sides.

Self-Referencing Relationships – When a table has to declare a connection with itself, this is
the method to employ.

Q12. What is OLTP?

OLTP, or online transactional processing, allows huge groups of people to execute massive
amounts of database transactions in real time, usually via the internet. A database transaction
occurs when data in a database is changed, inserted, deleted, or queried.

Q13. What are the differences between OLTP and OLAP?

OLTP stands for online transaction processing, whereas OLAP stands for online analytical
processing. OLTP is an online database modification system, whereas OLAP is an online
database query response system.

Q14. How to create empty tables with the same structure as another table?

To create empty tables:

Using the INTO operator to fetch the records of one table into a new table while setting a
WHERE clause to false for all entries, it is possible to create empty tables with the same
structure. As a result, SQL creates a new table with a duplicate structure to accept the fetched
entries, but nothing is stored into the new table since the WHERE clause is active.

Q15. What is PostgreSQL?

In 1986, a team lead by Computer Science Professor Michael Stonebraker created PostgreSQL
under the name Postgres. It was created to aid developers in the development of enterprise-
level applications by ensuring data integrity and fault tolerance in systems. PostgreSQL is an
enterprise-level, versatile, resilient, open-source, object-relational database management
system that supports variable workloads and concurrent users. The international developer
community has constantly backed it. PostgreSQL has achieved significant appeal among
developers because to its fault-tolerant characteristics.
It’s a very reliable database management system, with more than two decades of community
work to thank for its high levels of resiliency, integrity, and accuracy. Many online, mobile,
geospatial, and analytics applications utilise PostgreSQL as their primary data storage or data
warehouse.

Q16. What are SQL comments?

SQL Comments are used to clarify portions of SQL statements and to prevent SQL statements
from being executed. Comments are quite important in many programming languages. The
comments are not supported by a Microsoft Access database. As a result, the Microsoft Access
database is used in the examples in Mozilla Firefox and Microsoft Edge.
Single Line Comments: It starts with two consecutive hyphens (–).
Multi-line Comments: It starts with /* and ends with */.

Q17. What is the usage of the NVL() function?

You may use the NVL function to replace null values with a default value. The function returns
the value of the second parameter if the first parameter is null. If the first parameter is anything
other than null, it is left alone.

This function is used in Oracle, not in SQL and MySQL. Instead of NVL() function, MySQL have
IFNULL() and SQL Server have ISNULL() function.

Let’s move to the next question in this SQL Interview Questions.

Q18. Explain character-manipulation functions? Explains its different types in

SQL.

Change, extract, and edit the character string using character manipulation routines. The
function will do its action on the input strings and return the result when one or more characters
and words are supplied into it.

The character manipulation functions in SQL are as follows:

A) CONCAT (joining two or more values): This function is used to join two or more values
together. The second string is always appended to the end of the first string.

B) SUBSTR: This function returns a segment of a string from a given start point to a given
endpoint.

C) LENGTH: This function returns the length of the string in numerical form, including blank
spaces.
D) INSTR: This function calculates the precise numeric location of a character or word in a
string.

E) LPAD: For right-justified values, it returns the padding of the left-side character value.

F) RPAD: For a left-justified value, it returns the padding of the right-side character value.

G) TRIM: This function removes all defined characters from the beginning, end, or both ends of
a string. It also reduced the amount of wasted space.

H) REPLACE: This function replaces all instances of a word or a section of a string (substring)
with the other string value specified.

Q19. Write the SQL query to get the third maximum salary of an employee from
a table named employees.

Employee table

employee_name salary

A 24000

C 34000

D 55000

E 75000

F 21000

G 40000

H 50000

SELECT * FROM(

SELECT employee_name, salary, DENSE_RANK()

OVER(ORDER BY salary DESC)r FROM Employee)

WHERE r=&n;

To find 3rd highest salary set n = 3

Q20. What is the difference between the RANK() and DENSE_RANK()

functions?
The RANK() function in the result set defines the rank of each row within your ordered partition.
If both rows have the same rank, the next number in the ranking will be the previous rank plus a
number of duplicates. If we have three records at rank 4, for example, the next level indicated is
7.

The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the
provided column value, with no gaps. It always indicates a ranking in order of precedence. This
function will assign the same rank to the two rows if they have the same rank, with the next rank
being the next consecutive number. If we have three records at rank 4, for example, the next
level indicated is 5.

Q21. What are Tables and Fields?

A table is a collection of data components organized in rows and columns in a relational

database. A table can also be thought of as a useful representation of relationships. The most
basic form of data storage is the table. An example of an Employee table is shown below.

ID Name Department Salary

1 Rahul Sales 24000

2 Rohini Marketing 34000

3 Shylesh Sales 24000

4 Tarun Analytics 30000

A Record or Row is a single entry in a table. In a table, a record represents a collection of

connected data. The Employee table, for example, has four records.

A table is made up of numerous records (rows), each of which can be split down into smaller
units called Fields(columns). ID, Name, Department, and Salary are the four fields in the
Employee table above.

Q22. What is a UNIQUE constraint?

The UNIQUE Constraint prevents identical values in a column from appearing in two records.
The UNIQUE constraint guarantees that every value in a column is unique.

Q23. What is a Self-Join?

A self-join is a type of join that can be used to connect two tables. As a result, it is a unary
relationship. Each row of the table is attached to itself and all other rows of the same table in a
self-join. As a result, a self-join is mostly used to combine and compare rows from the same
database table.

Q24. What is the SELECT statement?

A SELECT command gets zero or more rows from one or more database tables or views. The
most frequent data manipulation language (DML) command is SELECT in most applications.
SELECT queries define a result set, but not how to calculate it, because SQL is a declarative
programming language.

Q25. What are some common clauses used with SELECT query in SQL?

The following are some frequent SQL clauses used in conjunction with a SELECT query:

WHERE clause: In SQL, the WHERE clause is used to filter records that are required
depending on certain criteria.
ORDER BY clause: The ORDER BY clause in SQL is used to sort data in ascending (ASC) or
descending (DESC) order depending on specified field(s) (DESC).
GROUP BY clause: GROUP BY clause in SQL is used to group entries with identical data and
may be used with aggregation methods to obtain summarised database results.
HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is
different from WHERE, since the WHERE clause cannot filter aggregated records.

Q26. What are UNION, MINUS and INTERSECT commands?

The UNION operator is used to combine the results of two tables while also removing
duplicate entries.

The MINUS operator is used to return rows from the first query but not from the second query.

The INTERSECT operator is used to combine the results of both queries into a single row.
Before running either of the above SQL statements, certain requirements must be satisfied –

Within the clause, each SELECT query must have the same amount of columns.

The data types in the columns must also be comparable.

In each SELECT statement, the columns must be in the same order.

Let’s move to the next question in this SQL Interview Questions.

Q27. What is Cursor? How to use a Cursor?

After any variable declaration, DECLARE a cursor. A SELECT Statement must always be
coupled with the cursor definition.

To start the result set, move the cursor over it. Before obtaining rows from the result set, the
OPEN statement must be executed.

To retrieve and go to the next row in the result set, use the FETCH command.

To disable the cursor, use the CLOSE command.

Finally, use the DEALLOCATE command to remove the cursor definition and free up the
resources connected with it.

Q28. List the different types of relationships in SQL.

There are different types of relations in the database:

One-to-One – This is a connection between two tables in which each record in one table
corresponds to the maximum of one record in the other.
One-to-Many and Many-to-One – This is the most frequent connection, in which a record in one
table is linked to several records in another.
Many-to-Many – This is used when defining a relationship that requires several instances on
each sides.
Self-Referencing Relationships – When a table has to declare a connection with itself, this is
the method to employ.

Q29. What is SQL example?

SQL is a database query language that allows you to edit, remove, and request data from
databases. The following statements are a few examples of SQL statements:

 SELECT
 INSERT
 UPDATE
 DELETE
 CREATE DATABASE
 ALTER DATABASE

Q30. What are basic SQL skills?

SQL skills aid data analysts in the creation, maintenance, and retrieval of data from relational
databases, which divide data into columns and rows. It also enables users to efficiently retrieve,
update, manipulate, insert, and alter data.

The most fundamental abilities that a SQL expert should possess are:

1. Database Management
2. Structuring a Database
3. Creating SQL clauses and statements
4. SQL System SKills like MYSQL, PostgreSQL
5. PHP expertise is useful.
6. Analyze SQL data
7. Using WAMP with SQL to create a database
8. OLAP Skills

Q31. What is schema in SQL Server?

A schema is a visual representation of the database that is logical. It builds and

specifies the relationships among the database’s numerous entities. It refers to the
several kinds of constraints that may be applied to a database. It also describes the
various data kinds. It may also be used on Tables and Views.

Schemas come in a variety of shapes and sizes. Star schema and Snowflake
schema are two of the most popular. The entities in a star schema are represented in
a star form, whereas those in a snowflake schema are shown in a snowflake shape.
Any database architecture is built on the foundation of schemas.

Q32. How to create a temp table in SQL Server?

Temporary tables are created in TempDB and are erased automatically after the last
connection is closed. We may use Temporary Tables to store and process interim results.
When we need to store temporary data, temporary tables come in handy.

The following is the syntax for creating a Temporary Table:

CREATE TABLE #Employee (id INT, name VARCHAR(25))

INSERT INTO #Employee VALUES (01, ‘Ashish’), (02, ‘Atul’)

Let’s move to the next question in this SQL Interview Questions.

Q33. How to install SQL Server in Windows 11?

Install SQL Server Management Studio In Windows 11

Step 1: Click on SSMS, which will take you to the SQL Server Management Studio page.

Step 2: Moreover, click on the SQL Server Management Studio link and tap on Save File.

Step 3: Save this file to your local drive and go to the folder.

Step 4: The setup window will appear, and here you can choose the location where you want to
save the file.
Step 5: Click on Install.
Step 6: Close the window after the installation is complete.
Step 7: Furthermore, go back to your Start Menu and search for SQL server management
studio.

Step 8: Furthermore, double-click on it, and the login page will appear once it shows up.

Step 9: You should be able to see your server name. However, if that’s not visible, click on the
drop-down arrow on the server and tap on Browse.

Step 10: Choose your SQL server and click on Connect.

After that, the SQL server will connect, and Windows 11 will run good.
Q34. What is the case when in SQL Server?

The CASE statement is used to construct logic in which one column’s value is determined by
the values of other columns.

At least one set of WHEN and THEN commands makes up the SQL Server CASE Statement.
The condition to be tested is specified by the WHEN statement. If the WHEN condition returns
TRUE, the THEN sentence explains what to do.

When none of the WHEN conditions return true, the ELSE statement is executed. The END
keyword brings the CASE statement to a close.

1 CASE

2 WHEN condition1 THEN result1

3 WHEN condition2 THEN result2

4 WHEN conditionN THEN resultN

5 ELSE result

END;
6

Q35. NoSQL vs SQL

In summary, the following are the five major distinctions between SQL and NoSQL:

Relational databases are SQL, while non-relational databases are NoSQL.

SQL databases have a specified schema and employ structured query language. For
unstructured data, NoSQL databases use dynamic schemas.

SQL databases scale vertically, but NoSQL databases scale horizontally.

NoSQL databases are document, key-value, graph, or wide-column stores, whereas SQL
databases are table-based.

SQL databases excel in multi-row transactions, while NoSQL excels at unstructured data such
as documents and JSON.

Q36. What is the difference between NOW() and CURRENT_DATE()?

NOW() returns a constant time that indicates the time at which the statement began to execute.
(Within a stored function or trigger, NOW() returns the time at which the function or triggering
statement began to execute.
The simple difference between NOW() and CURRENT_DATE() is that NOW() will fetch the
current date and time both in format ‘YYYY-MM_DD HH:MM:SS’ while CURRENT_DATE() will
fetch the date of the current day ‘YYYY-MM_DD’.

Let’s move to the next question in this SQL Interview Questions.

Q37. What is BLOB and TEXT in MySQL?

BLOB stands for Binary Huge Objects and can be used to store binary data, whereas TEXT
may be used to store a large number of strings. BLOB may be used to store binary data, which
includes images, movies, audio, and applications.
BLOB values function similarly to byte strings, and they lack a character set. As a result, bytes’
numeric values are completely dependent on comparison and sorting.
TEXT values behave similarly to a character string or a non-binary string. The
comparison/sorting of TEXT is completely dependent on the character set collection.

Q38. How to remove duplicate rows in SQL?

If the SQL table has duplicate rows, the duplicate rows must be removed.

Let’s assume the following table as our dataset:

ID Name Age

1 A 21

2 B 23

2 B 23

4 D 22

5 E 25

6 G 26

5 E 25

The following SQL query removes the duplicate ids from the table:

DELETE FROM table WHERE ID IN (

SELECT
ID, COUNT(ID)
FROM table
GROUP BY ID
HAVING
COUNT (ID) > 1);
Q39. How to create a stored procedure using SQL Server?

A stored procedure is a piece of prepared SQL code that you can save and reuse again and
over.
So, if you have a SQL query that you create frequently, save it as a stored procedure and then
call it to run it.
You may also supply parameters to a stored procedure so that it can act based on the value(s)
of the parameter(s) given.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name

AS

sql_statement

GO;

Execute a Stored Procedure

EXEC procedure_name;

Q40. What is Database Black Box Testing?

Black Box Testing is a software testing approach that involves testing the functions of software
applications without knowing the internal code structure, implementation details, or internal
routes. Black Box Testing is a type of software testing that focuses on the input and output of
software applications and is totally driven by software requirements and specifications.
Behavioral testing is another name for it.

Q41. What are the different types of SQL sandbox?

Databases Training

SQL ESSENTIALS TRAINING & CERTIFICATION

SQL Essentials Training & Certification
Reviews

5(8617)
MYSQL DBA CERTIFICATION TRAINING
MySQL DBA Certification Training
Reviews

5(5223)

MONGODB CERTIFICATION TRAINING COURSE

MongoDB Certification Training Course
Reviews

4(15716)

APACHE CASSANDRA CERTIFICATION TRAINING

Apache Cassandra Certification Training
Reviews

5(12545)

TERADATA CERTIFICATION TRAINING

Teradata Certification Training
Reviews

5(2669)

MASTERING NEO4J GRAPH DATABASE CERTIFICATION TRAINING

Mastering Neo4j Graph Database Certification Training
Reviews

5(902)

Next
SQL Sandbox is a secure environment within SQL Server where untrusted programmes can be
run. There are three different types of SQL sandboxes:

Safe Access Sandbox: In this environment, a user may execute SQL activities like as building
stored procedures, triggers, and so on, but they can’t access the memory or create files.

Sandbox for External Access: Users can access files without having the ability to alter memory
allocation.

Unsafe Access Sandbox: This contains untrustworthy code that allows a user to access
memory.

Let’s move to the next question in this SQL Interview Questions.

Q42. Where MyISAM table is stored?

Prior to the introduction of MySQL 5.5 in December 2009, MyISAM was the default storage
engine for MySQL relational database management system versions. It’s based on the older
ISAM code, but it comes with a lot of extra features. Each MyISAM table is split into three files
on disc (if it is not partitioned). The file names start with the table name and end with an
extension that indicates the file type. The table definition is stored in a.frm file, however this file
is not part of the MyISAM engine; instead, it is part of the server. The data file’s suffix is.MYD
(MYData). The index file’s extension is.MYI (MYIndex). If you lose your index file, you may
always restore it by recreating indexes.

Q43. How to find the nth highest salary in SQL?

The most typical interview question is to find the Nth highest pay in a table. This work can be
accomplished using the dense rank() function.
Employee table

employee_name salary

A 24000

C 34000

D 55000

E 75000

F 21000

G 40000

H 50000

SELECT * FROM(
SELECT employee_name, salary, DENSE_RANK()

OVER(ORDER BY salary DESC)r FROM Employee)

WHERE r=&n;

To find to the 2nd highest salary set n = 2

To find 3rd highest salary set n = 3 and so on.

Q44. What do you mean by table and field in SQL?

A table refers to a collection of data in an organised manner in form of rows and columns. A
field refers to the number of columns in a table. For example:

Table: StudentInformation
Field: Stu Id, Stu Name, Stu Marks

Q45. What are joins in SQL?

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 types of
joins, as you can refer to below:

 Inner join: Inner Join in SQL is the most common type of join. It is used to return all the rows
from multiple tables where the join condition is satisfied.

 Left Join: Left Join in SQL is used to return all the rows from the left table but only the
matching rows from the right table where the join condition is fulfilled.
  Right Join: Right Join in SQL is used to return all the rows from the right table but only
the matching rows from the left table where the join condition is fulfilled.

 Full Join: Full join returns all the records when there is a match in any of the tables.
Therefore, it returns all the rows from the left-hand side table and all the rows from the
right-hand side table.

Let’s move to the next question in this SQL Interview Questions.

Q46. What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both Char and Varchar2 are used for characters datatype but varchar2 is used for character
strings of variable length whereas Char is used for strings of fixed length. For example, char(10)
can only store 10 characters and will not be able to store a string of any other length whereas
varchar2(10) can store any length i.e 6,8,2 in this variable.

Q47. What is a Primary key?

 A Primary key in SQL is a column (or collection of columns) or a

set of columns that uniquely identifies each row in the table.
 Uniquely identifies a single row in the table
 Null values not allowed

Example- In the Student table, Stu_ID is the primary key.

Q48. What are Constraints?

Constraints in SQL are used to specify the limit on the data type of the table. It can be specified
while creating or altering the table statement. The sample of constraints are:

 NOT NULL
 CHECK
 DEFAULT
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY

Q49. What is the difference between DELETE and TRUNCATE statements?

DELETE vs TRUNCATE
 DELETE TRUNCATE

Truncate is used to delete all the rows from a

Delete command is used to delete a row in a table.
table.

You can rollback data after using delete statement. You cannot rollback data.

It is a DML command. It is a DDL command.

It is slower than truncate statement. It is faster.

Q50. What is a Unique key?

 Uniquely identifies a single row in the table.

 Multiple values allowed per table.
 Null values allowed.

Apart from this SQL Interview Questions blog, if you want to get trained from professionals on
this technology, you can opt for structured training from edureka!

Q51. What is a Foreign key in SQL?

 Foreign key maintains referential integrity by enforcing a link between the data in two tables.
 The foreign key in the child table references the primary key in the parent table.
 The foreign key constraint prevents actions that would destroy links between the child and parent
tables.

Q52. What do you mean by data integrity?

Data Integrity defines the accuracy as well as the consistency of the data stored in a database.
It also defines integrity constraints to enforce business rules on the data when it is entered into
an application or a database.

Q53. What is the difference between clustered and non-clustered index in

SQL?

The differences between the clustered and non clustered index in SQL are :

1. Clustered index is used for easy retrieval of data from the database and its faster whereas
reading from non clustered index is relatively slower.
2. Clustered index alters the way records are stored in a database as it sorts out rows by the
column which is set to be clustered index whereas in a non clustered index, it does not alter the
way it was stored but it creates a separate object within a table which points back to the original
table rows after searching.
3. One table can only have one clustered index whereas it can have many non clustered
index.
Q54. Write a SQL query to display the current date?

In SQL, there is a built-in function called GetDate() which helps to return the current
timestamp/date.

Q55.What do you understand by query optimization?

The phase that identifies a plan for evaluation query which has the least estimated cost is
known as query optimization.

The advantages of query optimization are as follows:

 The output is provided faster

 A larger number of queries can be executed in less time
 Reduces time and space complexity

Q56. What do you mean by Denormalization?

Denormalization refers to a technique which is used to access data from higher to lower forms
of a database. It helps the database managers to increase the performance of the entire
infrastructure as it introduces redundancy into a table. It adds the redundant data into a table by
incorporating database queries that combine data from various tables into a single table.

Q57. What are Entities and Relationships?

Entities: A person, place, or thing in the real world about which data can be stored in a
database. Tables store data that represents one type of entity. For example – A bank database
has a customer table to store customer information. The customer table stores this information
as a set of attributes (columns within the table) for each customer.

Relationships: Relation or links between entities that have something to do with each other.
For example – The customer name is related to the customer account number and contact
information, which might be in the same table. There can also be relationships between
separate tables (for example, customer to accounts).

Let’s move to the next question in this SQL Interview Questions.

Q58. What is an Index?

An index refers to a performance tuning method of allowing faster retrieval of records from the
table. An index creates an entry for each value and hence it will be faster to retrieve data.

Q59. Explain different types of index in SQL.

There are three types of index in SQL namely:

Unique Index:
This index does not allow the field to have duplicate values if the column is unique indexed. If a
primary key is defined, a unique index can be applied automatically.

Clustered Index:

This index reorders the physical order of the table and searches based on the basis of key
values. Each table can only have one clustered index.

Non-Clustered Index:
Non-Clustered Index does not alter the physical order of the table and maintains a logical order
of the data. Each table can have many nonclustered indexes.

Q60. What is Normalization and what are the advantages of it?

Normalization in SQL is the process of organizing data to avoid duplication and redundancy.
Some of the advantages are:

 Better Database organization

 More Tables with smaller rows
 Efficient data access
 Greater Flexibility for Queries
 Quickly find the information
 Easier to implement Security
 Allows easy modification
 Reduction of redundant and duplicate data
 More Compact Database
 Ensure Consistent data after modification

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on
this technology, you can opt for structured training from edureka!

Q61. What is the difference between DROP and TRUNCATE commands?

DROP command removes a table and it cannot be rolled back from the database whereas
TRUNCATE command removes all the rows from the table.

Q62. Explain different types of Normalization.

There are many successive levels of normalization. These are called normal forms. Each
consecutive normal form depends on the previous one.The first three normal forms are usually
adequate.

Normal Forms are used in database tables to remove or decrease duplication. The following
are the many forms:

First Normal Form:

When every attribute in a relation is a single-valued attribute, it is said to be in first normal form.
The first normal form is broken when a relation has a composite or multi-valued property.
Second Normal Form:

A relation is in second normal form if it meets the first normal form’s requirements and does not
contain any partial dependencies. In 2NF, a relation has no partial dependence, which means it
has no non-prime attribute that is dependent on any suitable subset of any table candidate key.
Often, the problem may be solved by setting a single column Primary Key.

Third Normal Form:

If a relation meets the requirements for the second normal form and there is no transitive
dependency, it is said to be in the third normal form.

Q63. What is OLTP?

OLTP, or online transactional processing, allows huge groups of people to execute massive
amounts of database transactions in real time, usually via the internet. A database transaction
occurs when data in a database is changed, inserted, deleted, or queried.

What are the differences between OLTP and OLAP?

OLTP stands for online transaction processing, whereas OLAP stands for online analytical
processing. OLTP is an online database modification system, whereas OLAP is an online
database query response system.

Q64. How to create empty tables with the same structure as another table?

To create empty tables:

Using the INTO operator to fetch the records of one table into a new table while setting a
WHERE clause to false for all entries, it is possible to create empty tables with the same
structure. As a result, SQL creates a new table with a duplicate structure to accept the fetched
entries, but nothing is stored into the new table since the WHERE clause is active.

Q65. What is PostgreSQL?

In 1986, a team lead by Computer Science Professor Michael Stonebraker created PostgreSQL
under the name Postgres. It was created to aid developers in the development of enterprise-
level applications by ensuring data integrity and fault tolerance in systems. PostgreSQL is an
enterprise-level, versatile, resilient, open-source, object-relational database management
system that supports variable workloads and concurrent users. The international developer
community has constantly backed it. PostgreSQL has achieved significant appeal among
developers because to its fault-tolerant characteristics.
It’s a very reliable database management system, with more than two decades of community
work to thank for its high levels of resiliency, integrity, and accuracy. Many online, mobile,
geospatial, and analytics applications utilise PostgreSQL as their primary data storage or data
warehouse.

Q66. What are SQL comments?

SQL Comments are used to clarify portions of SQL statements and to prevent SQL statements
from being executed. Comments are quite important in many programming languages. The
comments are not supported by a Microsoft Access database. As a result, the Microsoft Access
database is used in the examples in Mozilla Firefox and Microsoft Edge.
Single Line Comments: It starts with two consecutive hyphens (–).
Multi-line Comments: It starts with /* and ends with */.

Let’s move to the next question in this SQL Interview Questions.

Q67. What is the difference between the RANK() and DENSE_RANK() functions?

The RANK() function in the result set defines the rank of each row within your ordered partition.
If both rows have the same rank, the next number in the ranking will be the previous rank plus a
number of duplicates. If we have three records at rank 4, for example, the next level indicated is
7.

SQL Essentials Training & Certification

Weekday / Weekend BatchesSee Batch Details

The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the
provided column value, with no gaps. It always indicates a ranking in order of precedence. This
function will assign the same rank to the two rows if they have the same rank, with the next rank
being the next consecutive number. If we have three records at rank 4, for example, the next
level indicated is 5.

Q68. What is SQL Injection?

SQL injection is a sort of flaw in website and web app code that allows attackers to take control
of back-end processes and access, retrieve, and delete sensitive data stored in databases. In
this approach, malicious SQL statements are entered into a database entry field, and the
database becomes exposed to an attacker once they are executed. By utilising data-driven
apps, this strategy is widely utilised to get access to sensitive data and execute administrative
tasks on databases. SQLi attack is another name for it.

The following are some examples of SQL injection:

 Getting access to secret data in order to change a SQL query to acquire the desired results.
  UNION attacks are designed to steal data from several database tables.
 Examine the database to get information about the database’s version and structure

Q69. How many Aggregate functions are available in SQL?

SQL aggregate functions provide information about a database’s data. AVG, for example,
returns the average of a database column’s values.

SQL provides seven (7) aggregate functions, which are given below:

AVG(): returns the average value from specified columns.

COUNT(): returns the number of table rows, including rows with null values.
MAX(): returns the largest value among the group.
MIN(): returns the smallest value among the group.
SUM(): returns the total summed values(non-null) of the specified column.
FIRST(): returns the first value of an expression.
LAST(): returns the last value of an expression.

Q70. What is the default ordering of data using the ORDER BY clause? How could it be
changed?

The ORDER BY clause in MySQL can be used without the ASC or DESC modifiers. The sort
order is preset to ASC or ascending order when this attribute is absent from the ORDER BY
clause.

Q71. How do we use the DISTINCT statement? What is its use?

The SQL DISTINCT keyword is combined with the SELECT query to remove all duplicate
records and return only unique records. There may be times when a table has several duplicate
records.
The DISTINCT clause in SQL is used to eliminate duplicates from a SELECT statement’s result
set.

Q72. What are the syntax and use of the COALESCE function?

From a succession of expressions, the COALESCE function returns the first non-NULL value.
The expressions are evaluated in the order that they are supplied, and the function’s result is
the first non-null value. Only if all of the inputs are null does the COALESCE method return
NULL.

The syntax of COALESCE function is COALESCE (exp1, exp2, …. expn)

Q73. What is the ACID property in a database?

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data
transactions are processed reliably in a database system.
  Atomicity: Atomicity refers to the transactions that are completely done or failed where
transaction refers to a single logical operation of a data. It means if one part of any transaction
fails, the entire transaction fails and the database state is left unchanged.
 Consistency: Consistency ensures that the data must meet all the validation rules. In simple
words, you can say that your transaction never leaves the database without completing its state.
 Isolation: The main goal of isolation is concurrency control.
 Durability: Durability means that if a transaction has been committed, it will occur whatever may
come in between such as power loss, crash or any sort of error.

Want to upskill yourself to get ahead in your career? Check out this video in this SQL Interview
Questions

Top 10 Technologies to Learn in 2022 | Edureka

Q74. What do you mean by “Trigger” in SQL?

Trigger in SQL is are a special type of stored procedures that are defined to execute
automatically in place or after data modifications. It allows you to execute a batch of code when
an insert, update or any other query is executed against a specific table.

Q75. What are the different operators available in SQL?

There are three operators available in SQL, namely:

1. Arithmetic Operators
2. Logical Operators
3. Comparison Operators

Apart from this SQL Interview Questions blog, if you want to get trained from professionals on
this technology, you can opt for structured training from edureka!

Q76. Are NULL values same as that of zero or a blank space?

A NULL value is not at all same as that of zero or a blank space. NULL value represents a
value which is unavailable, unknown, assigned or not applicable whereas a zero is a number
and blank space is a character.

Q77. What is the difference between cross join and natural join?

The cross join produces the cross product or Cartesian product of two tables whereas the
natural join is based on all the columns having the same name and data types in both the
tables.

Q78. What is subquery in SQL?

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 =.

Q79. What are the different types of a subquery?

There are two types of subquery namely, Correlated and Non-Correlated.

Correlated subquery: These are queries which select the data from a table referenced in the
outer query. It is not considered as an independent query as it refers to another table and refers
the column in a table.

Non-Correlated subquery: This query is an independent query where the output of subquery
is substituted in the main query.

Let’s move to the next question in this SQL Interview Questions.

Q80. List the ways to get the count of records in a table?

To count the number of records in a table in SQL, you can use the below commands:

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on
this technology, you can opt for structured training from edureka!

Q81. Write a SQL query to find the names of employees that begin
with ‘A’?

To display name of the employees that begin with ‘A’, type in the below command:

1 SELECT * FROM Table_name WHERE EmpName like 'A%'

Q82. Write a SQL query to get the third-highest salary of an

employee from employee_table?

1 SELECT TOP 1 salary

2 FROM(

SELECT TOP 3 salary

3
FROM employee_table
 4
ORDER BY salary DESC) AS emp
5
ORDER BY salary ASC;
6

Q83. What is the need for group functions in SQL?

Group functions work on the set of rows and return one result per group. Some of the
commonly used group functions are: AVG, COUNT, MAX, MIN, SUM, VARIANCE.

Q84. What is a Relationship and what are they?

Relation or links are between entities that have something to do with each other. Relationships
are defined as the connection between the tables in a database. There are various
relationships, namely:

 One to One Relationship.

 One to Many Relationship.
 Many to One Relationship.
 Self-Referencing Relationship.

Q85. How can you insert NULL values in a column while inserting
the data?

NULL values in SQL can be inserted in the following ways:

 Implicitly by omitting column from column list.

 Explicitly by specifying NULL keyword in the VALUES clause

Q86. What is the main difference between ‘BETWEEN’ and ‘IN’

condition operators?

BETWEEN operator is used to display rows based on a range of values in a row whereas the
IN condition operator is used to check for values contained in a specific set of values.

Example of BETWEEN:

SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;

Example of IN:

SELECT * FROM students where ROLL_NO IN (8,15,25);

Q87. Why are SQL functions used?

SQL functions are used for the following purposes:

 To perform some calculations on the data

 To modify individual data items
 To manipulate the output
  To format dates and numbers
 To convert the data types

Q88. What is the need for MERGE statement?

This statement allows conditional update or insertion of data into a table. It performs an
UPDATE if a row exists, or an INSERT if the row does not exist.

Q89. What do you mean by recursive stored procedure?

Recursive stored procedure refers to a stored procedure which calls by itself until it reaches
some boundary condition. This recursive function or procedure helps the programmers to use
the same set of code n number of times.

Q90. What is CLAUSE in SQL?

SQL clause helps to limit the result set by providing a condition to the query. A clause helps to
filter the rows from the entire set of records.

For example – WHERE, HAVING clause.

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on
this technology, you can opt for a structured training from edureka! Click below to know more.

Q91. What is the difference between ‘HAVING’ CLAUSE and a

‘WHERE’ CLAUSE?

HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY
clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is
applied to each row before they are a part of the GROUP BY function in a query.

Q92. List the ways in which Dynamic SQL can be executed?

Following are the ways in which dynamic SQL can be executed:

 Write a query with parameters.

 Using EXEC.
 Using sp_executesql.

Q93. What are the various levels of constraints?

Constraints are the representation of a column to enforce data entity and consistency. There
are two levels of a constraint, namely:

 column level constraint

 table level constraint
Q94. How can you fetch common records from two tables?

You can fetch common records from two tables using INTERSECT. For example:

Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam

Q95. List some case manipulation functions in SQL?

There are three case manipulation functions in SQL, namely:

 LOWER: This function returns the string in lowercase. It takes a string as an argument and
returns it by converting it into lower case. Syntax:

LOWER(‘string’)

 UPPER: This function returns the string in uppercase. It takes a string as an argument and
returns it by converting it into uppercase. Syntax:

UPPER(‘string’)

 INITCAP: This function returns the string with the first letter in uppercase and rest of the letters in
lowercase. Syntax:

INITCAP(‘string’)

Apart from this SQL Interview Questions blog, if you want to get trained from professionals on
this technology, you can opt for a structured training from edureka! Click below to know more.

Q96. What are the different set operators available in SQL?

Some of the available set operators are – Union, Intersect or Minus operators.

Q97. What is an ALIAS command?

ALIAS command in SQL is the name that can be given to any table or a column. This alias
name can be referred in WHERE clause to identify a particular table or a column.

For example-

Select emp.empID, dept.Result from employee emp, department as dept

where emp.empID=dept.empID
In the above example, emp refers to alias name for employee table and dept refers to alias
name for department table.

Let’s move to the next question in this SQL Interview Questions.

Q98. What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and returns a single value.
These calculations are done from the columns in a table. For example- max(),count() are
calculated with respect to numeric.

Scalar functions return a single value based on the input value. For example – UCASE(),
NOW() are calculated with respect to string.

Let’s move to the next question in this SQL Interview Questions.

Q99. How can you fetch alternate records from a table?

You can fetch alternate records i.e both odd and even row numbers. For example- To display
even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where

mod(rowno,2)=1
Q100. Name the operator which is used in the query for pattern
matching?

LIKE operator is used for pattern matching, and it can be used as -.

1. % – It matches zero or more characters.

For example- select * from students where studentname like ‘a%’

_ (Underscore) – it matches exactly one character.

For example- select * from student where studentname like ‘abc_’

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on
this technology, you can opt for structured training from edureka!

Q101. How can you select unique records from a table?

You can select unique records from a table by using the DISTINCT keyword.

Select DISTINCT studentID from Student

Using this command, it will print unique student id from the table Student.

Q102. How can you fetch first 5 characters of the string?

There are a lot of ways to fetch characters from a string. For example:

Select SUBSTRING(StudentName,1,5) as studentname from student

Q103. What is the main difference between SQL and PL/SQL?

SQL is a query language that allows you to issue a single query or execute a single
insert/update/delete whereas PL/SQL is Oracle’s “Procedural Language” SQL, which allows
you to write a full program (loops, variables, etc.) to accomplish multiple operations such as
selects/inserts/updates/deletes.

Q104. What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Since views are
not present, it takes less space to store. View can have data of one or more tables combined
and it depends on the relationship.

Let’s move to the next question in this SQL Interview Questions.

Q105. What are Views used for?

A view refers to a logical snapshot based on a table or another view. It is used for the following
reasons:

 Restricting access to data.

 Making complex queries simple.
 Ensuring data independence.
 Providing different views of same data.

Q106. What is a Stored Procedure?

A Stored Procedure is a function which consists of many SQL statements to access the
database system. Several SQL statements are consolidated into a stored procedure and
execute them whenever and wherever required which saves time and avoid writing code again
and again.

Q107. List some advantages and disadvantages of Stored

Procedure?

Advantages:

A Stored Procedure can be used as a modular programming which means create once, store
and call for several times whenever it is required. This supports faster execution. It also reduces
network traffic and provides better security to the data.

Disadvantage:

The only disadvantage of Stored Procedure is that it can be executed only in the database and
utilizes more memory in the database server.

Q108. List all the types of user-defined functions?

There are three types of user-defined functions, namely:

 Scalar Functions
 Inline Table-valued functions
 Multi-statement valued functions

Scalar returns the unit, variant defined the return clause. Other two types of defined functions
return table.

Let’s move to the next question in this SQL Interview Questions.

Q109. What do you mean by Collation?

Collation is defined as a set of rules that determine how data can be sorted as well as
compared. Character data is sorted using the rules that define the correct character sequence
along with options for specifying case-sensitivity, character width etc.

Let’s move to the next question in this SQL Interview Questions.

Q110. What are the different types of Collation Sensitivity?

Following are the different types of collation sensitivity:

 Case Sensitivity: A and a and B and b.

 Kana Sensitivity: Japanese Kana characters.
 Width Sensitivity: Single byte character and double-byte character.
 Accent Sensitivity.

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on
this technology, you can opt for structured training from edureka!

Q111. What are Local and Global variables?

Local variables:

These variables can be used or exist only inside the function. These variables are not used or
referred by any other function.

Global variables:

These variables are the variables which can be accessed throughout the program. Global
variables cannot be created whenever that function is called.

Q112. What is Auto Increment in SQL?

Autoincrement keyword allows the user to create a unique number to get generated whenever a
new record is inserted into the table.
This keyword is usually required whenever PRIMARY KEY in SQL is used.
AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in
SQL SERVER.

Q113. What is a Datawarehouse?

Datawarehouse refers to a central repository of data where the data is assembled from multiple
sources of information. Those data are consolidated, transformed and made available for the
mining as well as online processing. Warehouse data also have a subset of data called Data
Marts.

Q114. What are the different authentication modes in SQL Server?

How can it be changed?

Windows mode and Mixed Mode – SQL and Windows. You can go to the below steps to
change authentication mode in SQL Server:

 Click Start> Programs> Microsoft SQL Server and click SQL Enterprise Manager to run SQL
Enterprise Manager from the Microsoft SQL Server program group.
 Then select the server from the Tools menu.
 Select SQL Server Configuration Properties, and choose the Security page.

Q115. What are STUFF and REPLACE function?

STUFF Function: This function is used to overwrite existing character or inserts a string into another
string. Syntax:

STUFF(string_expression,start, length, replacement_characters)

where,
string_expression: it is the string that will have characters substituted

start: This refers to the starting position

length: It refers to the number of characters in the string which are substituted.

replacement_string: They are the new characters which are injected in the string.

REPLACE function: This function is used to replace the existing characters of all the occurrences.
Syntax:

REPLACE (string_expression, search_string, replacement_string)

Here every search_string in the string_expression will be replaced with the replacement_string.

So this brings us to the end of the SQL interview questions blog. I hope this set of SQL
Interview Questions will help you ace your job interview. All the best for your interview!
Q1. Mention 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,
Supports PHP, Perl, Python, Ruby, etc
Perl, Python, Ruby, etc
Doesn’t allow any kind of database file Allows database file manipulation while
manipulation while running running.
Doesn’t allow query cancellation mid-way
Allows query cancellation mid-way in the process
in the process.
While backing up the data, It doesn’t block the While backing up the data, it blocks the
database database
Takes a large amount of operational storage Takes less amount of operational storage
space. space.
Available in MySQL Community Edition,
Available in Express and Custom mode.
and MySQL Enterprise Edition
Q2. What do you understand by SQL Server Agent?
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.

Example:

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.

Q3. Mention the different authentication modes in SQL Server.

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:

  Windows Authentication Mode: This mode is used to connect the server through a Windows
account. Here, the server takes computers’ username and password for authentication purposes.
Also, the SQL server authentication mode is disabled in this mode.
 Mixed mode: The Mixed mode is used to connect with an instance of SQL Server using either
the SQL Server authentication or Windows authentication. In this mode, a username and
password is set by the user for the database.

Q4. Mention the differences between local and global temporary tables.
Local Temporary Table Global Temporary Table

These tables exist permanently in the database and

These tables only exist for the duration of
only the rows get deleted when the connection gets
connection or duration of that statement.
closed.
Syntax: CREATE TABLE #<tablename> Syntax: CREATE TABLE ##<tablename>
Q5. How can you check the version of SQL Server?
To check the version of SQL Server, you can use the following command:

1 SELECT @@version

The @@VERSION gives output as one nvarchar string.

Q6. What is single-user mode and what are the steps you should follow to start
SQL Server in single-user mode?
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:

 A single user connects to the server.

 The CHECKPOINT process is not executed since it is executed by default at the startup.

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.

 To start SQL Server in single-user mode use the command: sqlcmd –m

 To connect through the Query Editor in Management Studio use: -m"Microsoft SQL Server
Management Studio - Query".

Q7. What is SQL Server Profiler?

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:
 1. Finding the root cause of the problem
2. Monitoring the performance of SQL Server to handle the workloads.
3. Diagnosing the slow queries
4. Capturing a series of SQL statements causing problem, to further replicate the problem on the
test server, while debugging the issue.
5. It also helps in correlating performance counters to debug issues easily.

Q8. What is the TCP/IP port on which SQL Server runs?

The TCP/IP port on which SQL Server runs is 1433.

Q9. What are subqueries in SQL server? Explain its properties.

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:

 Must be enclosed in parentheses as it must be executed first before the main query
 More than one query can be included.
 A sub query should not have ORDER BY clause but can include WHERE, GROUP BY, and
HAVING CLAUSE
 The subquery must be on the right hand side of the comparison operator of the main query
 A subquery must include the SELECT clause and a FROM clause.

Q10. How do you start single user mode in clustered installations?

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:

1. Go to advanced properties and remove -m startup parameter.

2. Now, put the SQL Server resource offline.
3. Issue the following command from the command prompt, and make sure you are at the current
owner node of the group:
net start MSSQLSERVER /m.
4. Next, you have to verify from the cluster administrator or fail over cluster management console
that SQL Server resource is still offline or not.
5. Then, connect to the SQL Server by using the following command and perform the required
operation: SQLCMD -E -S<servername>.
6. Once an operation is complete, you have to close the command prompt and then bring back the
SQL and other resources online through cluster administrator.
Q11. What do you understand by replication in SQL Server? Mention the different
types of replication in SQL Server.
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:

1. Merge Replication: This replication groups the data from various sources to a single centralized
database and is used in the server to the client environment.
2. Transactional Replication: This replication is a process of distributing data from publisher to
subscriber and is used in server to server environment.
3. Snapshot Replication: This replication distributes data exactly as it appears at a specific
moment and is used for replicating data, which changes infrequently.

Q12. What are the differences between MS SQL Server & Oracle?
MS SQL Server Oracle

Consists of complex and comparatively more

Provides simple and easy syntax.
efficient syntax.
Uses transact SQL or T-SQL. Uses PL/SQL
Does not support query optimization. Uses star query optimization.
Rollbacks are not allowed in the transaction Rollbacks are allowed during the transaction
process. process.
Allows incremental , full, file level, and differential
Allows incremental , partial and full backups
backups.
Does not support clustering. Offers support for clustered configuration.
Statements such as INSERT, UPDATE, Statements such as INSERT, UPDATE, DELETE,
DELETE are executed serially. MERGE are executed parallely.
Job are scheduled via the SQL Server
Job are scheduled via Oracle scheduler or OEM
Agent
Q13. What do you understand by INTENT locks?
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:

1. Intent Shared Lock(IS): This lock is used when you have a shared lock at the row level.
2. Intent Update Lock(IU): The Intent update lock is used when you have an update lock at the
row level.
3. Intext Exclusive Lock(IX): This lock is used when you have an exclusive lock at the row level.

Q14. What are the steps you must follow to hide SQL Server instances?
The steps you must follow to hide SQL Server instances are as follows:
MySQL DBA Certification Training

Explore Curriculum

 Open the SQL Server Configuration Manager and expand the SQL Server Network
Configuration.
 Then go to Protocols and choose the instance of SQL Server.
 Later on, right-click on the instance and select the Properties
  Next, in the Hide Instance box, go to the on the Flags tab, and select Yes.
 Finally, click on OK, and close the dialog box.

Q15. What do you understand by the data quality services in SQL Server?
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:

 Data Quality Server: It is an SQL Server instance feature consisting of three SQL Server
catalogs with data-quality functionality and storage
 Data Quality Client: It is an SQL Server feature which users can use to perform computer-
assisted data quality analysis and manage their data quality interactively.
Q16. Explain Magic Tables in SQL server
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).

Q17. What do you understand by Change Data Capture?

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.

Q18. What do you understand by triggers and mention the different types of it?
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:

1. INSERT
2. UPDATE
3. DELETE
4. INSTEAD OF

Q19. What do you understand by recursive stored procedure?

A recursive stored procedure is a problem-solving method through which you can arrive at the
solution again and again.

Q20. Explain Log Shipping and mention its advantages.

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:

 Needs low maintenance and is easy to set up

 The secondary database created is used for read-only purpose.
 You can create multiple secondary standby servers
 Allows a delay specified by the user for the time between the primary server backs up the log of
the primary database when the secondary servers restore (apply) the log backup.

Q21. What are trace flags and mention a few common trace flags used with SQL
Server?
These flags are used to alter server behavior or set server characteristics. Few common trace
flags used with SQL Server are as follows
  1204, 1205, 1222 – These flags are used for deadlock Information.
 174 – This trace flag increases the SQL Server Database Engine plan cache bucket count from
40,009 to 160,001 on 64-bit systems.
 1118 – Forces uniform extent allocations instead of mixed page allocations – (SQL 2005 and
2008) To reduces TempDB contention.
 652 – This trace flag disables page prefetching scans.
 2566 – Used to run the DBCC CHECKDB command without data purity check, unless
the DATA_PURITY option is specified.

Q22. Mention the differences between SUBSTR and CHARINDEX in SQL Server.
SUBSTR CHARINDEX

Used to return a specific portion of the string Used to return a character position in a given
in a given string specified string
Example: Example:

SUBSTRING(‘Edureka’,1,4) CHARINDEX(‘r’,’Edureka’,1)

Output: Output:

Edur 4
Q23. What do you understand by the Analysis Services in SQL Server?
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 :

1. Azure Analysis Services

2. Power BI Premium
3. SQL Server Analysis Services

Q24. What do you understand by Mirroring and mention the advantages of the
Mirroring?
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:

1. Consists of an automatic fail over mechanism.

2. More efficient than Log Shipping and is more robust.
3. The primary server is synced with the secondary server in real-time

Q25. When do you think a developer should use SQL Server-based cursors?
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.

Q26. What part does database design plays a role in performance of an SQL
Server-based application?
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:

1 CREATE FUNCTION samplefunc(@num INT)

2 RETURNS TABLE

3 AS

4 RETURN SELECT * FROM customers WHERE CustId=@num

To execute the above-created function, refer to the following command:

1 SELECT * FROM samplefunc(10)

Q28. How can you ensure that the database and SQL Server based application
perform well?
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.

Databases Training

SQL ESSENTIALS TRAINING & CERTIFICATION

SQL Essentials Training & Certification
Reviews

5(8617)

MYSQL DBA CERTIFICATION TRAINING

MySQL DBA Certification Training
Reviews

5(5223)

MONGODB CERTIFICATION TRAINING COURSE

MongoDB Certification Training Course
Reviews

4(15716)

APACHE CASSANDRA CERTIFICATION TRAINING

Apache Cassandra Certification Training
Reviews

5(12545)

TERADATA CERTIFICATION TRAINING

Teradata Certification Training
Reviews

5(2669)

MASTERING NEO4J GRAPH DATABASE CERTIFICATION TRAINING

Mastering Neo4j Graph Database Certification Training
Reviews

5(902)

Next

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.

Q29. What are relationships and mention different types of relationships in the
DBMS
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:

 One-to-One Relationship – Used when a single row in Table A is related to a single row in
Table B.
 One-to-Many Relationship – Used when a single row in Table A is related to many rows in
table B.
 Many-to-Many Relationship – Used when many rows in table A can be related to many rows in
table B.
 Self -Referencing Relationship – Used when a record in table A is related to record in same
table.

Q30. What are joins in SQL and what are the different types of joins?
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:

 Inner Join
 Right Join
 Left Join
 Full Join

Q31. What is the command DBCC CHECKDB used for?

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:

 Runs DBCC CHECKALLOC on the mentioned database.

 On every table and view in the database, the DBCC CHECKTABLE command is executed.
 Runs DBCC CHECKCATALOG on the database.
 Then it validated the contents of every indexed view in the mentioned database.
 It also validates link-level consistency between file system directories and table metadata while
storing varbinary(max) data in the file system using FILESTREAM.
 Finally, it validates the Service Broker data in the database.
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.

Q32. What do you understand by CHECK constraint in SQL Server?

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.

Example:

1 CREATE TABLE Customer (&nbsp;&nbsp;

2 &nbsp;&nbsp;&nbsp;&nbsp;Cust_ID int NOT NULL,&nbsp;&nbsp;

3 &nbsp;&nbsp;&nbsp;&nbsp;FirstName varchar(255),&nbsp;&nbsp;

4 &nbsp;&nbsp;&nbsp;&nbsp;Age int,&nbsp;&nbsp;

5 &nbsp;&nbsp;&nbsp;&nbsp;City varchar(255),&nbsp;&nbsp;

&nbsp;&nbsp;&nbsp;&nbsp;CONSTRAINT CHK_Customer CHECK (Age>20 AND City= 'Hyderabad

6
);&nbsp;&nbsp;
7

Q33. What do you understand by COALESCE in SQL Server?

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.

Example:

1 SELECT COALESCE(CustID, CustName, Amount) from Customers;

Q34. Explain the usage of FLOOR function in SQL Server.

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.

Syntax:

1 FLOOR(expression)

Example:

1 FLOOR(7.3)

Q35. What is the command used to check locks in Microsoft SQL Server?
To check locks in the database, you can use the in-built stored procedure sp_lock.
Syntax

1 sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ]

2 [ ; ]

Example:

To list all the locks currently held in an instance of the Database Engine, use the following
command:

1 USE SampleDB;

2 GO

3 EXEC sp_lock;

4 GO

Q36. Mention the 3 ways to get a count of the number of records in a table.
The following are three ways to count the number of records in the table:

1 SELECT * FROM TableName;

2 SELECT COUNT(*) FROM TableName;

3 SELECT rows FROM indexes WHERE id = OBJECT_ID(TableName) AND indexid< 2;

Q37. What is the usage of the SIGN function?

This function is used to determine whether the mentioned number is zero, positive, and
negative. So, it will either return 0, +1, -1.

Syntax:

1 SIGN(number)

Example:

1 SIGN (0) returns 0

2 SIGN (21) returns 1

3 SIGN (-21) returns -1

Q38. Write an SQL Query to find first weekday of the month?

To find the first week day of the month, you can write a query as follows:

1 SELECT DATENAME(dw, DATEADD(dd, – DATEPART(dd, GETDATE()) + 1, GETDATE())) AS First

Q39. Mention the command used to rename the database.
To rename a database, you have to use the sp_renamedb command in the following manner:

1 sp_renamedb 'OldDatabaseName', 'NewDatabaseName';

Q40. Write a query to find 5th highest amount paid from the Customer table.
To find the 5th highest amount paid from the customers’ table, you can write a query as below:

1 SELECT TOP 1 amount FROM (SELECT DISTINCT TOP 5 amount FROM customers ORDER BY amou

Q41. How can we delete a table in SQL Server?

To delete a table in SQL Server, use the Delete command.

Syntax:

1 DELETE TableName

Example:

1 DELETE Customers;

Q42. What is the purpose of UPDATE STATISTICS and SCOPE_IDENTITY()

function?

 UPDATE _STATISTICS is used to update the information used by indexes such as the
distribution of key values for one or more statistic groups in the mentioned indexed view or table.
 SCOPE_IDENTITY is used to create identity value for tables in the current execution scope.

Q43. What do you understand by PHYSICAL_ONLY option in DBCC CHECKDB?

 The PHYSICAL_ONLY option is used to limit check to the integrity of the physical structure of the
record headers, page, and also the allocation consistency of the database.
 The PHYSICAL_ONLY check is used to provide a small overhead check of the physical
consistency of the database.
 Also, the PHYSICAL_ONLY option causes a shorter run-time for DBCC CHECKDB on large
databases. So, it is generally recommended for frequent use on production systems.

Q44. Can you explain how long are locks retained within the REPEATABLE_READ
and SERIALIZABLE isolation levels, during a read operation with row-level
locking?
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.

Q45. Mention the differences between HAVING and WHERE clause.

HAVING WHERE

Used only with SELECT statement Used in a GROUP BY clause

Used with the GROUP BY function in Applied to each row before they are a part of the GROUP BY
a query function in a query

Note: Whenever GROUP BY is not used, HAVING behaves like a WHERE clause.

Q46. What do you understand by integration services in SQL Server?

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.

MySQL DBA Certification Training

Weekday / Weekend BatchesSee Batch Details

Q47. What do you understand by Hotfixes and Patches in SQL Server?

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.

Q48. Can you name a few encryption mechanisms in SQL server?

These are few encryption mechanisms in SQL Server to encrypt data in the database:

1. Transparent Data Encryption

2. Symmetric Keys
3. Asymmetric Keys
 4. Transact SQL functions
5. Certificates

Q49. What are the options which must be set to allow the usage of optimistic
models?
The READ_COMMITED_SNAPSHOT option and the ALLOW_SNAPSHOT_ISOLATION option
must be set to allow the usage of optimistic models.

 The READ_COMMITTED_SNAPSHOT option is used for the read committed optimistic model.
 ALLOW_SNAPSHOT_ISOLATION option is used for the snapshot isolation level.

Q50. What are the common performance issues in SQL Server?

The common performance issues in SQL Server are as follows:

 Fragmentation
 Input/Output bottlenecks
 Blocking Queues
 Deadlocks
 Missing and unused indexes

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!
SQL Server Interview Questions
A list of top frequently asked SQL Server interview questions and answers are
given below.

1) What is SQL Server?

SQL Server is the RDBMS system provided by Microsoft which functions mainly as
retrieving and storing the data as per user request. Sometimes it is mistakenly
referred as SQL, but both are different, as SQL is a language whereas SQL Server is
a Microsoft product that supports SQL.

2) What is Normalization?
In RDBMS, the process of organizing data to minimize redundancy and surety of
logical data integrity is called normalization. In normalization, the database is
divided into two or more tables, and a relationship is defined among the tables.
Normalization technique increases performance for the database.

Types of Normalization

25.4M

585

Features of Java - Javatpoint

Next

Stay

There are types of normalization used, which are given below.

o 1NF
o 2NF
o 3NF
o BCNF
o 4NF
o 5NF

However, the first three types are only frequently used, where "NF" stands for
normal form. The originator of the RD model "E.F Codd" has proposed the process
"normalization" with the first "normal form" and continued till third normal form.
3) What is De-Normalization?
It is a process of attempting to optimize the performance of a database by adding
redundant data. Redundancy is introduced intentionally in a table to improve
performance, and it is called de-normalization. The de-Normalization process
enhances the read performance while some degradation occurs in write
performance. It can be achieved by making a group of data in the redundant form.
The un-normalized and de-Normalized database are completely different from each
other. Before the process of de-normalization of any database, that should be
normalized firstly.

4) What is the difference between function and stored procedure?

o A Function must return a value while stored procedure can return zero or n value.
o Functions can have only input parameter while procedures can have input/ output
parameters.
o Functions take one mandatory input parameter while stored procedures may take 0
to n input parameter.
o Try-catch block can handle exceptions. In the stored procedure, while you can't use
try-catch in functions.

5) What is collation sensitivity? Explain different types.

Collation sensitivity is used to define the rules for sorting and comparing the strings
of character data. The basic rule for sorting a character data are correct character
sequence, Case-sensitivity, character width, and accent marks, etc.

Different types of collation sensitivity:

o Case sensitivity
o Accent sensitivity
o Kana sensitivity
o Width sensitivity

Case Sensitivity: Case sensitivity defines every character with a unique value, as
alphabet characters A and a are treated individually, as they have different ASCII
values for a computer language
Accent sensitivity: Accent sensitivity is related that whether the accent is off or
not, as a and á both should be treated differently

Kana sensitivity: Kana sensitivity defines the difference between two Japanese
words: Hiragana and Katakana

Width sensitivity: It differentiates between a single-byte character (half- width)

and representation of the double-byte character of the same character

6) What is the standby server?

The Standby server is the type of server which is brought online when the primary
server goes offline, and the application needs continuous availability of the server.
The requirement for a mechanism which can shift a primary server to secondary or
standby server is always there.

There are three types of standby servers:

Hot standby: Hot standby method is a method of redundancy in which the primary
and secondary backup systems run simultaneously so the data also present in the
secondary server in a real-time and this way both systems contain identical
information.

Warm standby: Warm standby is a method of redundancy in which the secondary

system runs in the background of the primary system. Data is mirrored in the
secondary server at a regular interval, so in this method sometimes both servers
don't contain the same data.

Cold standby: Cold standby is the method of redundancy in which the secondary
server is only called when the primary server fails. Cold standby systems are used
in cases where data is changed infrequently or for nor critical applications. The
physical replacement of Primary server with standby server occurs in cold standby.

7) What is the difference between clustered and non-clustered index?

Clustered Index: A clustered index is a particular type of index that reorders the
way records in the table are physically stored. It gives a sequence of data which is
physically stored in the database. Therefore a table can have only one clustered
index. The leaf nodes of a clustered index contain the data pages. Index id of the
clustered index is 0. So a primary key constraint automatically creates a clustered
index.

Non-clustered Index: A non-clustered index is a particular type of index in which

the logical order of the index does not match the physically stored order of the rows
 on disk. In non-clustered index data and indexes are stored in different places. The
leaf node of a non-clustered index does not consist of the data pages. Instead, the
leaf nodes contain index rows. Index id of non-clustered indexes is greater than 0.

8) What is the difference between HAVING CLAUSE and WHERE

CLAUSE in SQL Server?
HAVING Clause: HAVING CLAUSE is used only with the SELECT statement. It is
generally used in a GROUP BY clause in a query.

If GROUP BY is not used, HAVING works like a WHERE clause. HAVING clause can be
used with the aggregate function.

Syntax:

1. SELECT expr1, expr2, expr3..,

2. FROM tables
3. WHERE condition
4. GROUP BY expr1, expr2, expr3..,
5. HAVING condition;

WHERE Clause: The WHERE clause is applied to each row before they become a
part of the GROUP BY function in a query. 'WHERE' clause cannot use with the
aggregate function.

Syntax:

1. WHERE condition;

9) What is the recursive stored procedure in SQL Server?

The Recursive stored procedure is defined as a method of problem-solving wherein
the solution arrives repetitively. SQL Server supports recursive stored procedure
which calls by itself. It can nest up to 32 levels. It can be called by itself directly or
indirectly

There are two ways to achieve recursion in the stored procedure:

o Mutual Recursion: By Using mutually recursive stored procedure, indirect recursion

can be achieved
 o Chain Recursion: If we extend mutual recursion process then we can achieve chain
recursion.

10) What are the advantages of using stored procedures in SQL

Server?
A list of advantages of Stored Procedures:

o Stored procedures help in reducing the network traffic and latency. It boosts up the
application performance.
o Stored procedures facilitate the reusability of the code.
o Stored procedures provide better security for data.
o You can encapsulate the logic using stored procedures and change stored procedure
code without affecting clients.
o It is possible to reuse stored procedure execution plans, which are cached in SQL
Server's memory. This reduces server overhead.
o It provides modularity of application.

11) Define the one-to-one relationship while designing tables.

One-to-One relationship: It can be implemented as a single table and rarely as
two tables with primary and foreign key relationships.

One to one relationship exists if an entity in one table has a link with only one entity
on another table. Let?s take an example of the employee and their employee id so
that a unique employee id will be there for a particular employee at another table.

12) How can you hide the SQL Server instances?

You have to make a change in the SQL Server Configuration Manager to hide the
SQL Server instances.

Follow the below instructions to launch SQL Server Configuration Manager and do
the following:

o Select the instance of SQL Server.

 o Right-click and select Properties.
o After selecting properties, you will just set Hide Instance to "Yes" and click OK or
Apply.
o After the change is made, you need to restart the instance of SQL Server, not to
expose the name of the instance.

13) What is CHECK constraint in SQL Server?

A CHECK constraint is applied to a column in a table to limit the values that can be
placed in a column. It enforces integrity. After using the check constraint on the
single column, we can only give some specific values for that particular column.
Check constraint apply a condition for each column in the table.

EXAMPLE:

1. CREATE TABLE Employee (

2. EMP_ID int NOT NULL,
3. LastName varchar(255) NOT NULL,
4. FirstName varchar(255),
5. Age int,
6. City varchar(255),
7. CONSTRAINT CHK_Employee CHECK (Age>20AND City= 'Greenville')
8. );

14) What do you mean by SQL Server agent?

The SQL Server agent plays a vital role in day to day tasks of SQL server
administrator (DBA). It is one of the essential parts of the Microsoft's SQL server.
Server agent's purpose is to implement the tasks easily with the scheduler engine
which allows our jobs to run at scheduled date and time. SQL server agent store
scheduled administrative tasks information using SQL server.

15) What is COALESCE in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This
function is used to return a non-null from more than one column in the arguments.
 COALESCE accepts all the values but it only returns non-null value present in the
expression.

Syntax:

1. COALESCE(expr1, expr2, expr3,......,expr n)

16) In which TCP/IP port does SQL Server run? Can it be changed?
SQL Server runs on port 1433. Yes, it can be changed from the network utility
TCP/IP properties.

17) What are the authentication modes in SQL Server? How can it be
changed?
Authentication mode is used for authentication of the user in SQL server, and it can
be selected at the time of setup of the database engine.

SQL Server supports two authentication modes: Window authentication mode and
mixed mode.

Window authentication mode: This authentication mode is used to connect

through a Microsoft NT 4.0 or window 2000 user account. In Windows
authentication server take computer's username and password for authentication
purpose. SQL server authentication mode is disabled in this mode.

Mixed mode: It is used to connect with the instance of SQL Server using window
authentication or SQL Server authentication. In SQL server authentication mode a
unique username and password are required for a particular database, as it will not
depend on windows account.

18) What is SQL Server Profiler?

Microsoft SQL Server Profiler is a graphical user interface that allows system
administrators to monitor events of the database engine. SQL server profiler trace
monitor every event to a file. SQL profiler can be used for real-time monitoring of
data or also for future analysis of data.

You can do the following things with a SQL Server Profiler -

You can create a trace.

You can watch the trace results when the trace runs.

You can store the trace results in a table.

You can find out the bugs in queries and diagnose it.

If it is necessary, you can start, stop, pause and modify the trace results.

19) What is the SQL Server agent?

SQL Server agent is a component of Microsoft SQL Server. It is a background tool of
Microsoft SQL Server, so it runs continuously in the background as a window
service. SQL Server agent allows the database administrator to handles automated
tasks and schedules jobs. It runs a window service so can start automatically when
the system boots or you can start it manually.

20) What is "scheduled jobs" or "scheduled tasks"?

Scheduled tasks let you manage the tasks in an automated manner that runs on
regular or predictable cycles. You can schedule administrative tasks and also
determine the order in which tasks will run.

21) What is the DBCC command and why is it used?

DBCC stands for database consistency checker. This command is used to check the
consistency of the database. DBCC command help to review and monitoring the
maintenance of tables, database, and for validation of operations done on the
database, etc. For example:

DBCC CHECKDB: It makes sure that table in the database and the indexes are
correctly linked.

DBCC CHECKALLOC: It checks all pages in the database and makes sure that all
are correctly allocated.
DBCC CHECKFILEGROUP: It checks all table file group for any damage.

IF the user executes the above commands, a database snapshot is created through
the database engine, and it continues in the consistent transactional state. After
that, It runs the checks against stored database snapshot, and after the completion
of the command, it dropped the snapshot.

22) What command is used to rename the database?

sp_renamedb 'oldname', 'newname';

23) Can SQL Server be linked with other Servers like Oracle?
Yes, it can be linked to any Server. It has OLE-DB provider from Microsoft which
allow linking.

24) What is the difference between abstract and interface?

Abstract class:

o It provides a set of rules to implement next class. Rules are provided through
abstract methods.
o An Abstract method does not contain any definition.
o When a class contains all functions without the body, it is called as Fully Abstract
Class.
o Another class can inherit only one abstract class.

Interface:

o If a class contains all abstract methods, then that class is called Interface.
o Interface support like multiple inheritances.
o An Interface does not contain any implementation
o We can only use public or abstract modifiers.
25) What is the difference between the application object and session
object?
The session object is used to maintain the session of each user. If a user enters into
an application, he gets a session id, and when he leaves application, then the
session id is deleted. If he enters again into the application, he gets a different
session id, but for application object, once ad id is generated, it maintains the
whole application.

26) Is there any difference between primary key and unique with the
NOT NULL condition?
There is no difference between primary key and unique key but, a unique key will
allow single NULL, but in the primary key, no NULL is accepted.

27) What is the difference between value type and reference type?
Value type and reference type may be similar regarding declaration syntax and
usage, but their semantics are distinct. Value type and reference type differ with
the memory area, where it stored.

The Value type is stored on the stack while reference type is stored on the heap.

The Value type stores real data while reference type stores reference to the data.

Accessing is faster in the value type on comparison to reference type.

The value type can contain null value while reference type can't hold a null value.

The value types are derived from System. Value Type while Reference type is
derived from System Object. Means value type stores a particular value but a
reference type stores a reference or address to the object

String, Object, array are the reference type, as they are stored in heap and
dynamic in nature

28) What is the Boxing and Unboxing concept in .net?

Boxing: Implicit conversion of a value type (integer, character, etc.) to a reference
type (object) is called boxing. In boxing process, a value type (which generally
 stores on the stack) is being allocated on the heap rather than the stack. Boxing
wraps a value inside the object which can be stored in the heap section.

Example

1. //Boxing the variable i:

2. int i= 56;
3. Object obj=i;

Unboxing: explicit conversion of that same reference type (which is created by

boxing process) back to a value type is known as unboxing. In the unboxing
process, a boxed value type is unboxed from the heap and allocated on the stack.

Example

1. //UNBoxing the variable i:

2. obj=56;
3. i= (int) obj;

29) What is the difference between GET and POST methods?

GET and POST methods are 'form submission' method. Both are used to
send the data from client side to server side. These are some differences
between GET and POST method -

In GET method caching is possible while it is not possible in POST method.

Only ASCII character data types are allowed in GET method while in POST method
there is no restriction, it allows binary data also.
In GET method length of the string is restricted while in POST method length of the
string is not limited.

Get method is not secured as it can be bookmarked but, post method is secured as
it cannot be bookmarked

Get method is stored in browser history but post method is not stored in browser
history

30) What is the Log Shipping?

Log shipping is the process of automating the backup of a database and transaction
log file on a primary database server and then restoring them on a standby server.
Many servers support this technique for maintaining a backup server, such as
Microsoft SQL Server, 4D server, MySQL, and PostgreSQL.

The primary purpose of log shipping is to increase database availability just like
replication. On each of secondary database, log backups are applied particularly.

Steps for log shipping process:

o Firstly take a backup of transaction log file on Primary SQL server instance
o Copy the log file on secondary SQL server instance
o Restore the Log backup file onto secondary SQL Server instance

31) What are the different type of replication in SQL Server?

There are three types of replication in SQL Server.

1) Snapshot replication: Snapshot replication distributes data exactly as it

appears at a specific moment. Snapshot replication is the best method for
replicating data that changes infrequently. Snapshot replication is the easiest way
to maintain.

2) Transactional replication: Transactional replication is a process of distributing

data from publisher to subscriber. Transactional replication is generally used in the
"server to server" environment. It is appropriate when you want incremental
change propagated to the subscriber.

3) Merge replication: Merge replication grouped the data from various sources to
a single centralized database. It is generally used in the server to the client
environment. Merge replication is appropriate when multiple subscribers might
update the same data at the various time.
32) Which is the main third-party tool used in SQL Server?
A list of third-party tools used in SQL Server:

o SQL CHECK - Idera: It is used to monitor server activities and memory levels.
o SQL DOC 2 - RedGate: It is used to document the databases.
o SQL Backup 5 - RedGate: It is used to automate the Backup Process.
o SQL Prompt - RedGate: It provides IntelliSense for SQL SERVER 2005/2000.
o Lite Speed 5.0 - Quest Soft: It is used for Backup and Restore.

33) What are the advantages of using third-party tools?

A list of advantages of using third-party tools:

o Third party tools provide faster backups and restore.

o They provide flexible backup and recovery options.
o They provide secure backups with encryption.
o They provide the enterprise view of your backup and recovery environment.
o Easily identify optimal backup settings.
o Visibility into the transaction log and transaction log backups.
o Timeline view of backup history and schedules.
o Recover individual database objects.
o Encapsulate a complete database restore into a single file to speed up restore time.
o When we need to improve upon the functionality that SQL Server offers natively.
o Save time, better information or notification.
o Third party tools can put the backups in a single compressed file to reduce the space
and time.

34) What are the different types of collation sensitivity in SQL Server?
There are four types of collation sensitivity in SQL Server:
 o Case sensitivity
o Accent sensitivity
o Kana Sensitivity
o Width sensitivity

Explained in detail in Question number 5

35) What are the Hotfixes and Patches in SQL Server?

Hotfixes are small software patches that are applied to live systems. A hotfix is a
single, cumulative package that includes one or more files used to address a
problem in a software product.

For example - a software bug

A patch is a program installed in the machine to rectify the problem occurred in the
system and ensured the security of that system. The hotfix is a Kind of Patches
provided by the Microsoft.

In Microsoft SQL Server, hotfixes are small patches designed to address specific
issues, most commonly to freshly-discovered security holes. Hotfix response
proactively against any bug

36) What is the most common trace flags used with SQL Server?
Trace flag in SQL server sets the specific characteristic of the server. It works as an
"IF" condition for the SQL Server. The most common trace flags used with SQL
Server are:

o Deadlock Information: 1204, 1205, 1222

o Network Database files: 1807
o Log Record for Connections: 4013
o Skip Startup Stored Procedures: 4022
o Disable Locking Hints: 8755
o Do Force uniform extent allocations instead of mixed page allocations 1118 (SQL
2005 and 2008).
 37) How will you decide the active and passive nodes?
Open Cluster Administrator checks the SQL Server group where you can see the
current owner. So the current owner is the active node, and other nodes are
passive. Because at one time only one node can be active and must be in the
passive mode in a two node.

38) What is the use of FLOOR function in SQL Server?

FLOOR function is used to round up a non-integer value to the previous least
integer. Floor expression returns a unique value after rounding down the
expression.

SYNTAX:

1. FLOOR (expression)

For example:

FLOOR (7.3)

39) What is the usage of SIGN function?

SIGN function is used to define whether the number specified is Positive, Negative
and Zero. This will return +1,-1 or 0. SIGN function returns the value with its sign.

SYNTAX:

1. SIGN (number)
2.
3. If the number>0, then it will return +1
4. If the number=0, then it will return 0
5. If the number<0, then it will return -1
40) What is sub-query in SQL server? Explain its properties.
In SQL Server, a query within the main query like Select, Update, Insert or Delete, is
termed as sub-query. It is also called as INNER Query.

A subquery can be Added to WHERE clause, the FROM clause, or the SELECT clause.

Some properties of the subqueries are given below:

o A subquery must be enclosed in parenthesis

o A sub-query can add WHERE, GROUP BY, and HAVING CLAUSE but it's optional.
o SELECT clause and a FROM clause must be included a subquery.
o A User can include more than one query

41) How can we delete a table in SQL Server?

A user can delete a TABLE from the database by using SQL Server Management
Studio or by Transact-SQL in SQL Server

Following are the steps for deleting a table using SQL Server Management

o Select a Table(wanted to remove) in object explorer

o Choose DELETE from the shortcut menu by right- click on the table
o Click on the 'yes' to confirm the deletion of the table

42) What are the encryption mechanisms in SQL server?

We can use encryption for security of data in the database in SQL Server. Following
are the encryption mechanism used in SQL server:

o Transact-SQL functions
o Asymmetric keys
o Symmetric keys
o Certificates
o Transparent Data Encryption
43) Define Magic Tables in SQL server?
A Table which is automatically created and managed by SQL server internally to
store the inserted, updated values for any DML (SELECT, DELETE, UPDATE, etc.)
operation, is called as Magic tables in SQL server. The triggers preferably use it.

44) What is CDC in SQL Server?

CDC is termed as "Change Data Capture." It captures the recent activity of INSERT,
DELETE, and UPDATE, which are applied to the SQL Server table. It records the
changes made in the SQL server table in a compatible format.

45) How many types of database relationship in SQL server?

There are three types of relationship exist in SQL server:

o One to One Relationship

o Many to Many relationship
o One to One relationship

1) What is SQL?
SQL stands for the Structured Query Language. It is the standard language used to
maintain the relational database and perform many different data manipulation
operations on the data. SQL was initially invented in 1970. It is a database language
used for database creation, deletion, fetching and modifying rows, etc. sometimes,
it is pronounced as 'sequel.' We can also use it to handle organized data comprised
of entities (variables) and relations between different entities of the data.

2) When SQL appeared?

SQL first appeared in 1974. It is one of the most used languages for maintaining the
relational database. In 1986, SQL became the standard of the American National
Standards Institute (ANSI) and ISO (International Organization for Standardization)
in 1987.
3) What are the usages of SQL?
SQL is responsible for maintaining the relational data and the data structures
present in the database. Some of the common usages are given below:

o To execute queries against a database

o To retrieve data from a database
o To inserts records in a database
o To updates records in a database
o To delete records from a database
o To create new databases
o To create new tables in a database
o To create views in a database
o To perform complex operations on the database.

4) Does SQL support programming language features?

SQL refers to the Standard Query Language. Therefore, it is true that SQL is a
language but does not actually support the programming language. It is a common
language that doesn't have a loop, conditional statements, and logical operations. It
cannot be used for anything other than data manipulation. It is a command
language to perform database operations. The primary purpose of SQL is to
retrieve, manipulate, update, delete, and perform complex operations like joins on
the data present in the database.

5) What are the subsets of SQL?

The following are the four significant subsets of the SQL:

o Data definition language (DDL): It defines the data structure that consists of
commands like CREATE, ALTER, DROP, etc.
o Data manipulation language (DML): It is used to manipulate existing data in the
database. The commands in this category are SELECT, UPDATE, INSERT, etc.
 o Data control language (DCL): It controls access to the data stored in the
database. The commands in this category include GRANT and REVOKE.
o Transaction Control Language (TCL): It is used to deal with the transaction
operations in the database. The commands in this category are COMMIT, ROLLBACK,
SET TRANSACTION, SAVEPOINT, etc.

6) What is the purpose of DDL Language?

DDL stands for Data definition language. It is the subset of a database that defines
the data structure of the database when the database is created. For example, we
can use the DDL commands to add, remove, or modify tables. It consists of the
following commands: CREATE, ALTER and DELETE database objects such as
schema, tables, indexes, view, sequence, etc.

Example

1. CREATE TABLE Students

2. (
3. Roll_no INT,
4. Name VARCHAR(45),
5. Branch VARCHAR(30),
6. );

7) What is the purpose of DML Language?

Data manipulation language makes the user able to retrieve and manipulate data in
a relational database. The DML commands can only perform read-only operations
on data. We can perform the following operations using DDL language:

o Insert data into the database through the INSERT command.

o Retrieve data from the database through the SELECT command.
o Update data in the database through the UPDATE command.
 o Delete data from the database through the DELETE command.

Example

1. INSERT INTO Student VALUES (111, 'George', 'Computer Science')

8) What is the purpose of DCL Language?

Data control language allows users to control access and permission management
to the database. It is the subset of a database, which decides that what part of the
database should be accessed by which user at what point of time. It includes two
commands, GRANT and REVOKE.

GRANT: It enables system administrators to assign privileges and roles to the

specific user accounts to perform specific tasks on the database.

REVOKE: It enables system administrators to revoke privileges and roles from the
user accounts so that they cannot use the previously assigned permission on the
database.

Example

1. GRANT * ON mydb.Student TO javatpoint@localhsot;

9) What are tables and fields in the database?

A table is a set of organized data in the form of rows and columns. It enables users
to store and display records in the structure format. It is similar to worksheets in
the spreadsheet application. Here rows refer to the tuples, representing the simple
data item, and columns are the attribute of the data items present in a particular
row. Columns can categorize as vertical, and Rows are horizontal.
 Fields are the components to provide the structure for the table. It stores the same
category of data in the same data type. A table contains a fixed number of columns
but can have any number of rows known as the record. It is also called a column in
the table of the database. It represents the attribute or characteristics of the entity
in the record.

Example

Table: Student

Field: Stud_rollno, Stud_name, Date of Birth, Branch, etc.

10) What is a primary key?

A primary key is a field or the combination of fields that uniquely identify each
record in the table. It is one of a special kind of unique key. If the column contains a
primary key, it cannot be null or empty. A table can have duplicate columns, but it
cannot have more than one primary key. It always stores unique values into a
column. For example, the ROLL Number can be treated as the primary key for a
student in the university or college.

We can define a primary key into a student table as follows:

1. CREATE TABLE Student (

2. roll_number INT PRIMARY KEY,
3. name VARCHAR(45),
4. );

To read more information, click here

 .

11) What is a foreign key?

The foreign key is used to link one or more tables together. It is also known as the
referencing key. A foreign key is specified as a key that is related to the primary
key of another table. It means a foreign key field in one table refers to the primary
key field of the other table. It identifies each row of another table uniquely that
maintains the referential integrity. The primary key-foreign key relationship is a
very crucial relationship as it maintains the ACID properties of the database
sometimes. It also prevents actions that would destroy links between the child and
parent tables.

We can define a foreign key into a table as follows:

1. CONSTRAINT constraint_name]
2. FOREIGN KEY [foreign_key_name] (col_name, ...)
3. REFERENCES parent_tbl_name (col_name,...)

To read more information, click here

12) What is a unique key?

A unique key is a single or combination of fields that ensure all values stores in the
column will be unique. It means a column cannot stores duplicate values. This key
provides uniqueness for the column or set of columns. For example, the email
addresses and roll numbers of student's tables should be unique. It can accept a
null value but only one null value per column. It ensures the integrity of the column
or group of columns to store different values into a table.

We can define a foreign key into a table as follows:

1. CREATE TABLE table_name(
2. col1 datatype,
3. col2 datatype UNIQUE,
4. ...
5. );

To read more information, click here

13) What is the difference between a primary key and a unique key?
The primary key and unique key both are essential constraints of the SQL. The main
difference among them is that the primary key identifies each record in the table. In
contrast, the unique key prevents duplicate entries in a column except for a NULL
value. The following comparison chart explains it more clearly:

Primary Key Unique Key

The primary key act as a unique The unique key is also a unique identifier for records
identifier for each record in the table. when the primary key is not present in the table.

We cannot store NULL values in the We can store NULL value in the unique key column, but
primary key column. only one NULL is allowed.

We cannot change or delete the We can modify the unique key column values.
primary key column values.

To read more information, click here

14) What is a Database?

A database is an organized collection of data that is structured into tables, rows,
columns, and indexes. It helps the user to find the relevant information frequently.
It is an electronic system that makes data access, data manipulation, data retrieval,
data storing, and data management very easy. Almost every organization uses the
database for storing the data due to its easily accessible and high operational ease.
The database provides perfect access to data and lets us perform required tasks.

The following are the common features of a database:

o Manages large amounts of data

o Accurate
o Easy to update
o Security
o Data integrity
o Easy to research data

15) What is meant by DBMS?

DBMS stands for Database Management System. It is a software program that
primarily functions as an interface between the database and the end-user. It
provides us the power such as managing the data, the database engine, and the
database schema to facilitate the organization and manipulation of data using a
simple query in almost no time. It is like a File Manager that manages data in a
database rather than saving it in file systems. Without the database management
system, it would be far more difficult for the user to access the database's data.

The following are the components of a DBMS:

o Software
o Data
o Procedures
o Database Languages
o Query Processor
o Database Manager
o Database Engine
o Reporting
16) What are the different types of database management systems?
The database management systems can be categorized into several types. Some of
the important lists are given below:

o Hierarchical databases (DBMS)

o Network databases (IDMS)
o Relational databases (RDBMS
o Object-oriented databases
o Document databases (Document DB)
o Graph databases
o ER model databases
o NoSQL databases

17) What is RDBMS?

RDBMS stands for Relational Database Management System. It is a database
management system based on a relational model. It facilitates you to manipulate
the data stored in the tables by using relational operators. RDBMS stores the data
into the collection of tables and links those tables using the relational operators
easily whenever required. Examples of relational database management systems
are Microsoft Access, MySQL, SQL Server, Oracle database, etc.

18) What is Normalization in a Database?

Normalization is used to minimize redundancy and dependency by organizing fields
and table of a database.

There are some rules of database normalization, which is commonly known as

Normal From, and they are:

o First normal form(1NF)

o Second normal form(2NF)
o Third normal form(3NF)
o Boyce-Codd normal form(BCNF)
Using these steps, the redundancy, anomalies, inconsistency of the data in the
database can be removed.

19) What is the primary use of Normalization?

Normalization is mainly used to add, delete or modify a field that can be made in a
single table. The primary use of Normalization is to remove redundancy and
remove the insert, delete and update distractions. Normalization breaks the table
into small partitions and then links them using different relationships to avoid the
chances of redundancy.

20) What are the disadvantages of not performing database

Normalization?
The major disadvantages are:

The occurrence of redundant terms in the database causes the waste of space in
the disk.

Due to redundant terms, inconsistency may also occur. If any change is made in the
data of one table but not made in the same data of another table, then
inconsistency will occur. This inconsistency will lead to the maintenance problem
and effects the ACID properties as well.

21) What is an inconsistent dependency?

An Inconsistent dependency refers to the difficulty of getting relevant data due to a
missing or broken path to the data. It leads users to search the data in the wrong
table, resulting in an error as an output.

22) What is Denormalization in a Database?

Denormalization is a technique used by database administrators to optimize the
efficiency of their database infrastructure. The denormalization concept is based on
Normalization, which is defined as arranging a database into tables correctly for a
particular purpose. This method allows us to add redundant data into a normalized
database to alleviate issues with database queries that merge data from several
tables into a single table. It adds redundant terms into the tables to avoid complex
joins and many other complex operations.
Denormalization doesn't mean that normalization will not be done. It is an
optimization strategy that takes place after the normalization process.

23) What are the different types of SQL operators?

Operators are the special keywords or special characters reserved for performing
particular operations. They are also used in SQL queries. We can primarily use
these operators within the WHERE clause of SQL commands. It's a part of the
command to filters data based on the specified condition. The SQL operators can be
categorized into the following types:

o Arithmetic operators: These operators are used to perform mathematical

operations on numerical data. The categories of this operators are addition (+),
subtraction (-), multiplication (*), division (/), remainder/modulus (%), etc.
o Logical operators: These operators evaluate the expressions and return their
results in True or False. This operator includes ALL, AND, ANY, ISNULL, EXISTS,
BETWEEN, IN, LIKE, NOT, OR, UNIQUE.
o Comparison operators: These operators are used to perform comparisons of two
values and check whether they are the same or not. It includes equal to (=), not
equal to (!= or <>), less than (<), greater than (>), less than or equal to (<=),
greater than or equal to (>=), not less than (!<), not greater than (!>), etc.
o Bitwise operators: It is used to do bit manipulations between two expressions of
integer type. It first performs conversion of integers into binary bits and then applied
operators such as AND (& symbol), OR (|, ^), NOT (~), etc.
o Compound operators: These operators perform operations on a variable before
setting the variable's result to the operation's result. It includes Add equals (+=),
subtract equals (-=), multiply equals (*=), divide equals (/=), modulo equals (%=),
etc.
o String operators: These operators are primarily used to perform concatenation and
pattern matching of strings. It includes + (String concatenation), += (String
concatenation assignment), % (Wildcard), [] (Character(s) matches), [^]
(Character(s) not to match), _ (Wildcard match one character), etc.

24) What is a view in SQL?

 A view is a database object that has no values. It is a virtual table that contains a
subset of data within a table. It looks like an actual table containing rows and
columns, but it takes less space because it is not present physically. It is operated
similarly to the base table but does not contain any data of its own. Its name is
always unique. A view can have data from one or more tables. If any changes occur
in the underlying table, the same changes reflected in the views also.

The primary use of a view is to implement the security mechanism. It is the

searchable object where we can use a query to search the view as we use for the
table. It only shows the data returned by the query that was declared when the
view was created.

We can create a view by using the following syntax:

1. CREATE VIEW view_name AS

2. SELECT column_lists FROM table_name
3. WHERE condition;

25) What is an Index in SQL?

An index is a disc structure associated with a table or view that speeds up row
retrieval. It reduces the cost of the query because the query's high cost will lead to
a fall in its performance. It is used to increase the performance and allow faster
retrieval of records from the table. Indexing reduces the number of data pages we
need to visit to find a particular data page. It also has a unique value meaning that
the index cannot be duplicated. An index creates an entry for each value which
makes it faster to retrieve data.
 For example: Suppose we have a book which carries the details of the countries. If
you want to find out information about India, why will you go through every page of
that book? You could directly go to the index. Then from the index, you can go to
that particular page where all the information about India is given.

26) What are the different types of indexes in SQL?

SQL indexes are nothing more than a technique of minimizing the query's cost. The
higher the query's cost, the worse the query's performance. The following are the
different types of Indexes supported in SQL:

o Unique Index
o Clustered Index
o Non-Clustered Index
o Bit-Map Index
o Normal Index
o Composite Index
o B-Tree Index
o Function-Based Index

27) What is the unique index?

UNIQUE INDEX is used to enforce the uniqueness of values in single or multiple
columns. We can create more than one unique index in a single table. For creating
a unique index, the user has to check the data in the column because the unique
indexes are used when any column of the table has unique values. This indexing
does not allow the field to have duplicate values if the column is unique indexed. A
unique index can be applied automatically when a primary key is defined.

We can create it by using the following syntax:

1. CREATE UNIQUE INDEX index_name

2. ON table_name (index_column1, index_column2,...);

Example

1. CREATE TABLE Employee(

2. ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(45),
4. Phone varchar(15),
5. City varchar(25),
6. );

Suppose we want to make a Phone column as a unique index. We can do this like
below:

1. CREATE UNIQUE INDEX index_name_phone ON Employee (Phone);

To read more information, click here

28) What is clustered index in SQL?

A clustered index is actually a table where the data for the rows are stored. It
determines the order of the table data based on the key values that can sort in only
one direction. Each table can have only one clustered index. It is the only index,
which has been automatically created when the primary key is generated. If many
data modifications needed to be done in the table, then clustered indexes are
preferred.
To read more information, click here

29) What is the non-clustered index in SQL?

The indexes other than PRIMARY indexes (clustered indexes) are called non-
clustered indexes. We know that clustered indexes are created automatically when
primary keys are generated, and non-clustered indexes are created when multiple
joins conditions and various filters are used in the query. The non-clustered index
and table data are both stored in different places. It cannot be able to alter the
physical order of the table and maintains the logical order of data.

The purpose of creating a non-clustered index is for searching the data. Its best
example is a book where the content is written in one place, and the index is at a
different place. We can create 0 to 249 non-clustered indexes in each table. The
non-clustered indexing improves the performance of the queries which use keys
without assigning the primary key.

30) What are the differences between SQL, MySQL, and SQL Server?
The following comparison chart explains their main differences:

SQL MySQL SQL Server

SQL or Structured Query MySQL is the popular database SQL Server is an RDBMS
Language is useful for management system used for database system mainly
managing our relational managing the relational developed for the Windows
databases. It is used to query database. It is a fast, scalable, system to store, retrieve, and
and operate the database. and easy-to-use database. access data requested by the
developer.

SQL first appeared in 1974. MySQL first appeared on May SQL Server first appeared on
23, 1995. April 24, 1989.

SQL was developed by IBM MySQL was developed by SQL Server was developed by
Corporation. Oracle Corporation. Microsoft Company.

SQL is a query language for MySQL is database software SQL Server is also a software
managing databases. that uses SQL language to that uses SQL language to
conduct with the database. conduct with the database.

SQL has no variables. MySQL can use variables SQL Server can use variables
constraints and data types. constraints and data types.

SQL is a programming MySQL is software, so it gets SQL Server is also software, so

language, so that it does not frequent updation. it gets frequent updation.
get any updates. Its
commands are always fixed
and remain the same.

31) What is the difference between SQL and PL/SQL?

The following comparison chart explains their main differences:

SQL PL/SQL

SQL is a database structured query PL/SQL or Procedural Language/Structured Query

language used to communicate with Language is a dialect of SQL used to enhance the
relational databases. It was developed capabilities of SQL. Oracle Corporation developed it in
by IBM Corporations and first appeared the early 90's. It uses SQL as its database language.
in 1974.

SQL is a declarative and data-oriented PL/SQL is a procedural and application-oriented

language. language.

SQL has no variables. PL/SQL can use variables constraints and data types.

SQL can execute only a single query at PL/SQL can execute a whole block of code at once.
a time.

SQL query can be embedded in PL/SQL. PL/SQL cannot be embedded in SQL as SQL does not
support any programming language and keywords.

SQL can directly interact with the PL/SQL cannot directly interact with the database
database server. server.
SQL is like the source of data that we PL/SQL provides a platform where SQL data will be
need to display. shown.

32) Is it possible to sort a column using a column alias?

Yes. We can use the alias method in the ORDER BY instead of the WHERE clause for
sorting a column.

33) What is the difference between clustered and non-clustered

indexes in SQL?
Indexing is a method to get the requested data very fast. There are mainly two
types of indexes in SQL, clustered index and non-clustered index. The differences
between these two indexes are very important from an SQL performance
perspective. The following comparison chart explains their main differences:

Clustered Index Non-Clustered Index

A clustered index is a table or view where the The indexes other than PRIMARY indexes
data for the rows are stored. In a relational (clustered indexes) are called non-clustered
database, if the table column contains a primary indexes. It has a structure separate from the
key, MySQL automatically creates a clustered data row. The non-clustered indexes are also
index named PRIMARY. known as secondary indexes.

Clustered indexes store the data information Non-clustered indexes stores only the
and the data itself. information, and then it will refer you to the
data stored in clustered data.

There can only be one clustered index per table. There can be one or more non-clustered
indexes in a table.

A clustered index determines how data is stored It creates a logical ordering of data rows and
physically in the table. Therefore, reading from uses pointers for accessing the physical data
a clustered index is faster. files. Therefore, reading from a clustered index
is slower.

A clustered index always contains an index id of A non-clustered index always contains an

 0. index id>0.

To read more information, click here

34) What is the SQL query to display the current date?

There is a built-in function in SQL called GetDate(), which is used to return the
current timestamp.

35) Which are joins in SQL? Name the most commonly used SQL
joins?
SQL joins are used to retrieve data from multiple tables into a meaningful result set.
It is performed whenever you need to fetch records from two or more tables. They
are used with SELECT statement and join conditions.

The following are the most commonly used joins in SQL:

o INNER JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN

36) What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends on the
relationship between tables. According to the ANSI standard, the following are the
different types of joins used in SQL:

o INNER JOIN
o SELF JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN
o FULL OUTER JOIN
 o CROSS JOIN

To read more information, click here

37) What is INNER JOIN in SQL?

Inner join returns only those records from the tables that match the specified
condition and hides other rows and columns. In simple words, it fetches rows when
there is at least one match of rows between the tables is found. INNER JOIN
keyword joins the matching records from two tables. It is assumed as a default join,
so it is optional to use the INNER keyword with the query.

The below visual representation explain this join more clearly:

 The following syntax illustrates the INNER JOIN:

1. SELECT column_lists
2. FROM table1
3. INNER JOIN table2 ON join_condition1
4. INNER JOIN table3 ON join_condition2
5. ...;

To read more information, click here

38) What is the Right JOIN in SQL?

The Right join is used to retrieve all rows from the right-hand table and only those
rows from the other table that fulfilled the join condition. It returns all the rows from
the right-hand side table even though there are no matches in the left-hand side
table. If it finds unmatched records from the left side table, it returns a Null value.
This join is also known as Right Outer Join.
 The below visual representation explain this join more clearly:

The following syntax illustrates the RIGHT JOIN:

1. SELECT colum_lists
2. FROM table1
3. RIGHT JOIN table2
4. ON join_condition;

To read more information, click here

39) What is Left Join in SQL?

The Left Join is used to fetch all rows from the left-hand table and common records
between the specified tables. It returns all the rows from the left-hand side table
even though there are no matches on the right-hand side table. If it will not find any
matching record from the right side table, then it returns null. This join can also be
called a Left Outer Join.

The following visual representation explains it more clearly:

 The following syntax illustrates the RIGHT JOIN:

1. SELECT colum_lists
2. FROM table1
3. LEFT JOIN table2
4. ON join_condition;

To read more information, click here

40) What is Full Join in SQL?

The Full Join results from a combination of both left and right join that contains all
the records from both tables. It fetches rows when there are matching rows in any
one of the tables. This means it returns all the rows from the left-hand side table
and all the rows from the right-hand side tables. If a match is not found, it puts
NULL value. It is also known as FULL OUTER JOIN.

The following visual representation explains it more clearly:

 The following syntax illustrates the FULL JOIN:

1. SELECT * FROM table1

2. FULL OUTER JOIN table2
3. ON join_condition;

To read more information, click here

41) What is a "TRIGGER" in SQL?

A trigger is a set of SQL statements that reside in a system catalog. It is a special
type of stored procedure that is invoked automatically in response to an event. It
allows us to execute a batch of code when an insert, update or delete command is
run against a specific table because the trigger is the set of activated actions
whenever DML commands are given to the system.

SQL triggers have two main components one is action, and another is an event.
When certain actions are taken, an event occurs as a result of those actions.
 We use the CREATE TRIGGER statement for creating a trigger in SQL. Here is the
syntax:

1. CREATE TRIGGER trigger_name

2. (AFTER | BEFORE) (INSERT | UPDATE | DELETE)
3. ON table_name FOR EACH ROW
4. BEGIN
5. --variable declarations
6. --trigger code
7. END;

To read more information, click here

42) What is self-join and what is the requirement of self-join?

A SELF JOIN is used to join a table with itself. This join can be performed using table
aliases, which allow us to avoid repeating the same table name in a single
sentence. It will throw an error if we use the same table name more than once in a
single query without using table aliases.

A SELF JOIN is required when we want to combine data with other data in the same
table itself. It is often very useful to convert a hierarchical structure to a flat
structure.

The following syntax illustrates the SELF JOIN:

1. SELECT column_lists
2. FROM table1 AS T1, table1 AS T2
3. WHERE join_conditions;

Example

Suppose we have a table 'Student' having the following data:

If we want to get retrieve the student_id and name from the table where student_id
is equal, and course_id is not equal, it can be done by using the self-join:

1. SELECT s1.student_id, s1.name

2. FROM student AS s1, student s2
3. WHERE s1.student_id=s2.student_id
4. AND s1.course_id<>s2.course_id;

Here is the result:

To read more information, click here

43) What are the set operators in SQL?

 We use the set operators to merge data from one or more tables of the same kind.
Although the set operators are like SQL joins, there is a significant distinction. SQL
joins combine columns from separate tables, whereas SQL set operators combine
rows from different queries. SQL queries that contain set operations are called
compound queries. The set operators in SQL are categories into four different
types:

A. UNION: It combines two or more results from multiple SELECT queries into a
single result set. It has a default feature to remove the duplicate rows from the
tables. The following syntax illustrates the Union operator:

1. SELECT columns FROM table1

2. UNION
3. SELECT columns FROM table2;

B. UNION ALL: This operator is similar to the Union operator, but it does not
remove the duplicate rows from the output of the SELECT statements. The following
syntax illustrates the UNION ALL operator:
1. SELECT columns FROM table1
2. UNION ALL
3. SELECT columns FROM table2;

C. INTERSECT: This operator returns the common records from two or more
SELECT statements. It always retrieves unique records and arranges them in
ascending order by default. Here, the number of columns and data types should be
the same. The following syntax illustrates the INTERSECT operator:

1. SELECT columns FROM table1

2. INTERSECT
3. SELECT columns FROM table2;

D. MINUS: This operator returns the records from the first query, which is not
found in the second query. It does not return duplicate values. The following syntax
illustrates the MINUS operator:

1. SELECT columns FROM table1

2. MINUS
3. SELECT columns FROM table2;

To read more information, click here

44) What is the difference between IN and BETWEEN operators?

 The following comparison chart explains their main differences:

BETWEEN Operator IN Operator

This operator is used to selects the range It is a logical operator to determine whether or not a
of data between two values. The values specific value exists within a set of values. This
can be numbers, text, and dates as well. operator reduces the use of multiple OR conditions
with the query.

It returns records whose column value It compares the specified column's value and returns
lies in between the defined range. the records when the match exists in the set of
values.

The following syntax illustrates this The following syntax illustrates this operator:
operator: SELECT * FROM table_name
SELECT * FROM table_name WHERE column_name IN ('value1','value 2');
WHERE column_name BETWEEN 'value1'
AND 'value2';

45) What is a constraint? Tell me about its various levels.

The constraint is used to specify the rule and regulations that allows or restricts
what values/data will be stored in the table. It ensures data accuracy and integrity
inside the table. It enforces us to store valid data and prevents us from storing
irrelevant data. If any interruption occurs between the constraint and data action,
the action is failed. Some of the most commonly used constraints are NOT NULL,
PRIMARY KEY, FOREIGN KEY, AUTO_INCREMENT, UNIQUE KEY, etc.

The following syntax illustrates us to create a constraint for a table:

1. CREATE TABLE table_name (

2. column1 datatype constraint,
3. column2 datatype constraint,
4. .........
5. );

SQL categories the constraints into two levels:

Column Level Constraints: These constraints are only applied to a single column
and limit the type of data that can be stored in that column.

Table Level Constraints: These constraints are applied to the entire table and
limit the type of data that can be entered.

To read more information, click here

46) How to write an SQL query to find students' names start with 'A'?
We can write the following query to get the student details whose name starts with
A:

1. SELECT * FROM student WHERE stud_name like 'A%';

Here is the demo example where we have a table named student that contains two
names starting with the 'A' character.
 47) Write the SQL query to get the third maximum salary of an
employee from a table named employees.
The following query is the simplest way to get the third maximum salary of an
employee:

1. SELECT * FROM `employees` ORDER BY `salary` DESC LIMIT 1 OFFSET 2

Here is the demo example that shows how to get the third maximum salary of an
employee.
 The following are the alternative way to get the third-highest salary of an
employee:

A. Using LIMIT Keyword

1. SELECT salary FROM employees

2. ORDER BY salary DESC
3. LIMIT 2, 1;

B. Using Subquery
1. SELECT salary
2. FROM
3. (SELECT salary
4. FROM employees
5. ORDER BY salary DESC
6. LIMIT 3) AS Temp
7. ORDER BY salary LIMIT 1;

C. Using TOP Keyword

1. SELECT TOP 1 salary

2. FROM
3. (SELECT DISTINCT TOP 3 salary
4. FROM employees
5. ORDER BY salary DESC) AS Temp
6. ORDER BY salary ASC;

48) What is the difference between DELETE and TRUNCATE

statements in SQL?
The main difference between them is that the delete statement deletes data
without resetting a table's identity, whereas the truncate command resets a
particular table's identity. The following comparison chart explains it more clearly:

No. DELETE TRUNCATE

1) The delete statement removes single or The truncate command deletes the whole
multiple rows from an existing table contents of an existing table without the table
depending on the specified condition. itself. It preserves the table structure or
schema.

2) DELETE is a DML command. TRUNCATE is a DML command.

 3) We can use the WHERE clause in the We cannot use the WHERE clause with
DELETE command. TRUNCATE.

4) DELETE statement is used to delete a TRUNCATE statement is used to remove all

row from a table. the rows from a table.

5) DELETE is slower because it maintained TRUNCATE statement is faster than DELETE

the log. statement as it deletes entire data at a time
without maintaining transaction logs.

6) You can roll back data after using the It is not possible to roll back after using the
DELETE statement. TRUNCATE statement.

7) DELETE query takes more space. TRUNCATE query occupies less space.

To read more information, click here

49) What is the ACID property in a database?

The ACID properties are meant for the transaction that goes through a different
group of tasks. A transaction is a single logical order of data. It provides properties
to maintain consistency before and after the transaction in a database. It also
ensures that the data transactions are processed reliably in a database system.

The ACID property is an acronym for Atomicity, Consistency, Isolation, and

Durability.

Atomicity: It ensures that all statements or operations within the transaction unit
must be executed successfully. If one part of the transaction fails, the entire
transaction fails, and the database state is left unchanged. Its main features are
COMMIT, ROLLBACK, and AUTO-COMMIT.

Consistency: This property ensures that the data must meet all validation rules. In
simple words, we can say that the database changes state only when a transaction
will be committed successfully. It also protects data from crashes.

Isolation: This property guarantees that the concurrent property of execution in

the transaction unit must be operated independently. It also ensures that
statements are transparent to each other. The main goal of providing isolation is to
control concurrency in a database.
Durability: This property guarantees that once a transaction has been committed,
it persists permanently even if the system crashes, power loss, or failed.

To read more information, click here

50) Is a blank space or zero the same as a NULL value?

No. The NULL value is not the same as zero or a blank space. The following points
explain their main differences:

o A NULL value is a value, which is 'unavailable, unassigned, unknown or not

applicable.' It would be used in the absence of any value. We can perform arithmetic
operations on it. On the other hand, zero is a number, and a blank space is treated
as a character.
o The NULL value can be treated as an unknown and missing value, but zero and blank
spaces differ from the NULL value.
o We can compare a blank space or a zero to another blank space or a zero. On the
other hand, one NULL may not be the same as another NULL. NULL indicates that no
data has been provided or that no data exists.

51) What are functions and their usage in SQL?

SQL functions are simple code snippets that are frequently used and re-used in
database systems for data processing and manipulation. Functions are the
measured values. It always performs a specific task. The following rules should be
remembered while creating functions:

o A function should have a name, and the name cannot begin with a special character
such as @, $, #, or other similar characters.
o Functions can only work with the SELECT statements.
o Every time a function is called, it compiles.
o Functions must return value or result.
o Functions are always used with input parameters.

SQL categories the functions into two types:

 o User-Defined Function: Functions created by a user based on their needs are
termed user-defined functions.
o System Defined Function: Functions whose definition is defined by the system are
termed system-defined functions. They are built-in database functions.

SQL functions are used for the following purposes:

o To perform calculations on data

o To modify individual data items
o To manipulate the output
o To format dates and numbers
o To convert data types

52) What is meant by case manipulation functions? Explains its

different types in SQL.
Case manipulation functions are part of the character functions. It converts the data
from the state in which it is already stored in the table to upper, lower, or mixed
case. The conversion performed by this function can be used to format the output.
We can use it in almost every part of the SQL statement. Case manipulation
functions are mostly used when you need to search for data, and you don't have
any idea that the data you are looking for is in lower case or upper case.

There are three case manipulation functions in SQL:

LOWER: This function is used to converts a given character into lowercase. The
following example will return the 'STEPHEN' as 'stephen':

1. SELECT LOWER ('STEPHEN') AS Case_Reault FROM dual;

NOTE: Here, 'dual' is a dummy table.

UPPER: This function is used to converts a given character into uppercase. The
following example will return the 'stephen' as 'STEPHEN':
1. SELECT UPPER ('stephen') AS Case_Reault FROM dual;

INITCAP: This function is used to converts given character values to uppercase for
the initials of each word. It means every first letter of the word is converted into
uppercase, and the rest is in lower case. The following example will return the 'hello
stephen' as 'Hello Stephen':

1. SELECT INITCAP ('hello stephen') AS Case_Reault FROM dual;

53) Explain character-manipulation functions? Explains its different

types in SQL.
Character-manipulation functions are used to change, extract, and alter the
character string. When one or more characters and words are passed into the
function, the function will perform its operation on those input strings and return
the result.

The following are the character manipulation functions in SQL:

A) CONCAT: This function is used to join two or more values together. It always
appends the second string into the end of the first string. For example:

Input: SELECT CONCAT ('Information-', 'technology') FROM DUAL;

Output: Information-technology

B) SUBSTR: It is used to return the portion of the string from a specified start point
to an endpoint. For example:

Input: SELECT SUBSTR ('Database Management System', 9, 11) FROM DUAL;

Output: Management
C) LENGTH: This function returns the string's length in numerical value, including
the blank spaces. For example:

Input: SELECT LENGTH ('Hello Javatpoint') FROM DUAL;

Output: 16

D) INSTR: This function finds the exact numeric position of a specified character or
word in a given string. For example:

Input: SELECT INSTR ('Hello Javatpoint', 'Javatpoint');

Output: 7

E) LPAD: It returns the padding of the left-side character value for right-justified
value. For example:

Input: SELECT LPAD ('200', 6,'*');

Output: ***200

F) RPAD: It returns the padding of the right-side character value for left-justified
value. For example:

Input: SELECT RPAD ('200', 6,'*');

Output: 200***

G) TRIM: This function is used to remove all the defined characters from the
beginning, end, or both. It also trimmed extra spaces. For example:

Input: SELECT TRIM ('A' FROM 'ABCDCBA');

Output: BCDCB

H) REPLACE: This function is used to replace all occurrences of a word or portion

of the string (substring) with the other specified string value. For example:

Input: SELECT REPLACE ( 'It is the best coffee at the famous coffee shop.', 'coffee',
'tea');

Output: It is the best tea at the famous tea shop.

54) What is the usage of the NVL() function?

 The NVL() function is used to convert the NULL value to the other value. The
function returns the value of the second parameter if the first parameter is NULL. If
the first parameter is anything other than NULL, it is left unchanged. This function is
used in Oracle, not in SQL and MySQL. Instead of NVL() function, MySQL have
IFNULL() and SQL Server have ISNULL() function.

55) Which function is used to return remainder in a division operator in

SQL?
The MOD function returns the remainder in a division operation.

56) What are the syntax and use of the COALESCE function?
The COALESCE() function evaluates the arguments in sequence and returns the first
NON-NULL value in a specified number of expressions. If it evaluates arguments as
NULL or not found any NON-NULL value, it returns the NULL result.

The syntax of COALESCE function is given below:

1. COALESCE (exp1, exp2, .... expn)

Example:

1. SELECT COALESCE(NULL, 'Hello', 'Javatpoint', NULL) AS Result;

This statement will return the following output:

 57) How do we use the DISTINCT statement? What is its use?
The DISTINCT keyword is used to ensure that the fetched value always has unique
values. It does not allow to have duplicate values. The DISTINCT keyword is used
with the SELECT statement and retrieves different values from the table's column.
We can use it with the help of the following syntax:

1. SELECT DISTINCT column_lists FROM table_name WHERE [condition];

Suppose we have a table 'customer' containing eight records in which the name
column has some duplicate values.

If we want to get the name column without any duplicate values, the DISTINCT
keyword is required. Executing the below command will return a name column with
unique values.
 58) What is the default ordering of data using the ORDER BY clause?
How could it be changed?
The ORDER BY clause is used to sort the table data either in ascending or
descending order. By default, it will sort the table in ascending order. If we want to
change its default behavior, we need to use the DESC keyword after the column
name in the ORDER BY clause.

The syntax to do this is given below:

1. SELECT expressions FROM tables

2. WHERE conditions
3. ORDER BY expression [ASC | DESC];

We have taken a customer table in the previous example. Now, we will demonstrate
the ORDER BY clause on them as well.

In the below output, we can see that the first query will sort the table data in
ascending order based on the name column. However, if we run the second query
by specifying the DESC keyword, the table's order is changed in descending order.
 59) Is the following query returns the output?

1. SELECT subject_code, AVG (marks)

2. FROM Students
3. WHERE AVG(marks) > 70
4. GROUP BY subject_code;

Answer: No. The above query does not return the output because we cannot use
the WHERE clause to restrict the groups. We need to use the HAVING clause instead
of the WHERE clause to get the correct output.
 60) What is the difference between the WHERE and HAVING clauses?
The main difference is that the WHERE clause is used to filter records before any
groupings are established, whereas the HAVING clause is used to filter values from
a group. The below comparison chart explains the most common differences:

WHERE HAVING

This clause is implemented in row operations. This clause is implemented in column

operations.

It does not allow to work with aggregate functions. It can work with aggregate functions.

This clause can be used with the SELECT, UPDATE, This clause can only be used with the
and DELETE statements. SELECT statement.

To know more differences, click here

61) How many Aggregate functions are available in SQL?

The aggregate function is used to determine and calculate several values in a table
and return the result as a single number. For example, the average of all values,
the sum of all values, and the maximum and minimum value among particular
groupings of values.

The following syntax illustrates how to use aggregate functions:

1. function_name (DISTINCT | ALL expression)

SQL provides seven (7) aggregate functions, which are given below:
 o AVG(): This function is used to returns the average value from specified columns.
o COUNT(): This function is used to returns the number of table rows, including rows
with null values.
o MAX(): This function is used to returns the largest value among the group.
o MIN(): This function is used to returns the smallest value among the group.
o SUM(): This function is used to returns the total summed values(non-null) of the
specified column.
o FIRST(): This function is used to returns the first value of an expression.
o LAST(): This function is used to returns the last value of an expression.

62) What is SQL Injection?

SQL injection is a type of vulnerability in website and web app code that allows
attackers to control back-end operations and access, retrieve, and destroy sensitive
data from databases. In this technique, malicious SQL statements are inserted into
a database entry field, and once they are performed, the database becomes
vulnerable to an attacker. This technique is commonly used to access sensitive
data and perform administrative activities on databases by exploiting data-driven
applications. It is also known as SQLi attack.

Some common examples of SQL injection are:

o Accessing confidential data to modify an SQL query to get desired results.

o UNION attacks to steal data from different database tables.
o Examine the database to extract information regarding the version and structure of
the database.

63) What is the difference between the RANK() and DENSE_RANK()

functions?
The RANK function determines the rank for each row within your ordered partition
in the result set. If the two rows are assigned the same rank, then the next number
in the ranking will be its previous rank plus a number of duplicate numbers. For
example, if we have three records at rank 4, the next rank listed would be ranked 7.

The DENSE_RANK function assigns a unique rank for each row within a partition as
per the specified column value without any gaps. It always specifies ranking in
consecutive order. If the two rows are assigned the same rank, this function will
 assign it with the same rank, and the next rank being the next sequential number.
For example, if we have 3 records at rank 4, the next rank listed would be ranked 5.

64) Is it possible to implicitly insert a row for the identity column?

Yes. We can implicitly insert a row for the identity column. Here is an example of
doing this:

1. SET IDENTITY_INSERT TABLE1 ON

2. INSERT INTO demo_table1 (id, name, branch)
3. SELECT id, name, branch FROM demo_table2
4. SET IDENTITY_INSERT OFF

65) What are SQL comments?

Comments are explanations or annotations in SQL queries that are readable by
programmers. It's used to make SQL statements easier to understand for humans.
During the parsing of SQL code, it will be ignored. Comments can be written on a
single line or across several lines.

o Single Line Comments: It starts with two consecutive hyphens (--).

o Multi-line Comments: It starts with /* and ends with */.

SQL Server Backup and Recovery

 Question 1 - What are 2 options to validate whether or not a backup will restore successfully?
o Restore the backup as a portion of a testing process or log shipping.
o Restore the backup with the Verify Only option.
 Additional information - Verifying Backups with the RESTORE
VERIFYONLY Statement

SQL Server Performance Tuning

 Question 1 - Name as many native SQL Server performance monitoring and tuning tools that
you know of and their associated value.
 o System objects - System objects such as sp_who2, sp_lock, fn_get_sql, etc.
provide a simple means to capture basic metrics related to locking, blocking,
executing code, etc.
 Additional information - SQL Server Command Line Tools To Manage
Your Server
o Profiler - In a nutshell, Profiler provides the lowest common denominator of
activity on a SQL Server instance. Profiler captures per session code with the
ability to filter the data collection based on database, login, host name,
application name, etc. in order to assess the IO, CPU usage, time needed, etc.
 Additional information - SQL Server Performance Statistics Using a
Server Side Trace
o Perfmon\System Monitor - Perfmon\System Monitor is responsible for macro
level metrics related to processes and sub systems.
 Additional information - Free Microsoft Tools to Help Setup and Maintain
PerfMon
o Dynamic Management Views and Functions - New to SQL Server 2005 and
beyond, the Dynamic Management Views and Functions offer a real time view
into the SQL Server sub systems.
 Additional information - Dynamic Management Views and Functions in
SQL Server 2005
o TYPEPERF.EXE - TYPEPERF.EXE is a command line tool included with the
Windows operating system that writes performance data to the command
window or to a file. It is necessary to capture performance data whenever you
are trying to diagnose performance issues on a server. Performance data
provides information on the server's utilization of the processor, memory, and
disk, as well as SQL Server-specific performance data.
 Additional information - How To Collect Performance Data With
TYPEPERF.EXE
o SQL Server Management Studio Built-in Performance Reports - As part of the
installation of SQL Server 2005 and beyond a number of performance-related
reports are installed. To get to these reports open the SQL Server
Management Studio (SSMS) and connect to a SQL Server instance. If you don't
have an instance of Reporting Services installed then the icon will be disabled.
 Additional information
 Built-In Performance Reports in SQL Server 2005
o Additional resources for this question:
 Tutorial - SQL Server Performance Monitoring and Tuning
 Tip Category - SQL Server Performance Tuning

Question 2 - How do you go about tuning a SQL Server query?

 Identify the query causing the issue.
 Review the query plan by issuing SHOWPLAN_TEXT, SHOWPLAN_ALL, Graphical
Query Plan or sys.dm_exec_query_stats.
 Review the individual query components to determine which components of the query
have the highest cost.
 Outline options to improve the query such as moving from cursor based logic to set
based logic or vice versa, changing the JOIN order, WHERE clause or ORDER BY
clause, adding indexes, removing indexes, creating covering indexes, etc.
 Test the options to determine the associated performance improvement.
Question 2 - Name 3 or more DBCC commands and their associated purpose.
 DBCC CACHESTATS - Displays information about the objects currently in the buffer
cache.
 DBCC CHECKDB - This will check the allocation of all pages in the database as
well as check for any integrity issues.
 DBCC CHECKTABLE - This will check the allocation of all pages for a specific table
or index as well as check for any integrity issues.
 DBCC DBREINDEX - This command will reindex your table. If the indexname is left
out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original
fillfactor when the table was created.
 DBCC PROCCACHE - This command will show you information about the procedure
cache and how much is being used.
 DBCC MEMORYSTATUS - Displays how the SQL Server buffer cache is divided up,
including buffer activity.
 DBCC SHOWCONTIG - This command gives you information about how much space
is used for a table and indexes. Information provided includes number of pages used as
well as how fragmented the data is in the database.
 DBCC SHOW_STATISTICS - This will show how statistics are laid out for an index. You
can see how distributed the data is and whether the index is really a good candidate or
not.
 DBCC SHRINKFILE - This will allow you to shrink one of the database files. This is
equivalent to doing a database shrink, but you can specify what file and the size to shrink
it to. Use the sp_helpdb command along with the database name to see the actual file
names used.
 DBCC SQLPERF - This command will show you much of the transaction logs are being
used.
 DBCC TRACEON - This command will turn on a trace flag to capture events in the error
log. Trace Flag 1204 captures Deadlock information.
 DBCC TRACEOFF - This command turns off a trace flag.

SQL Server DBA Interview Questions

Q.What are the different types of backups?

Answer: The SQL server offers 4 types of backups to suit the need of the administrator.

Complete backup :The complete back up is just zipping the content of the entire database in terms of the
different tables and procedures etc. This back up can server as an independent entity that can be restored in
different systems with just the base SQL server installed.
Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in
the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone back up
mechanism. But can save a lot of time if we already have the file system related to the DB backed up on the new
deployment server.

Differential backup: This is a subset of the complete backup, where only the modified datasets are backed up.
This can save the time when we are just trying to maintain a backup server to main server.

File backup: This is the quickest way to take the backup of entire database. Instead of taking in the data actually
stored in DB, the files are backed up and the file system thus obtained when combined with the transaction logs
of the original system will render the database that we are trying to back up.

.
Q What are the different levels of isolation?

Answer: The isolation represents the way of separating the database from the effects of network accesses,
thereby maintaining the consistency. The different levels of isolation are:

read committed: This level of isolation uses the shared locks and the reads to the database give the constant and
consistent values.

read uncommitted: No locks implemented. This is the least effective isolation level.

repeatable read: There are lock over the rows and values but the updates are maintained as a separate phantom
row which is the next set of values for the specific record. Values can change within a specific transaction of a SQL
function.

SERIALIZABLE reads: This is the implementation of pure lock mechanism where one specific transaction is not
allowed access to specific record before another one completes.

Q.What are the steps to take to improve performance of a poor performing query?
Answer: Maximum use of indexes, stored procures should be done. Avoid excessive use of complicated joins and
cursors. Avoid using conditional operators using columns of different tables. Make use of computed columns and
rewriting the query.
Q.What is normalization? Explain different levels of normalization?
Answer: Check out the article Q100139 from Microsoft knowledge base and of course, there's much more
information available in the net. It'll be a good idea to get a hold of any RDBMS fundamentals text book,
especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.
Q.What is denormalization and when would you go for it?
Answer: As the name indicates, denormalization is the reverse process of normalization. It's the controlled
introduction of redundancy in to the database design. It helps improve the query performance as the number of
joins could be reduced.
Q.How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Answer: One-to-One relationship can be implemented as a single table and rarely as two tables with primary and
foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with
primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table
with the keys from both the tables forming the composite primary key of the junction table. It will be a good idea
to read up a database designing fundamentals text book.
Q.What's the difference between a primary key and a unique key?
Answer: Both primary key and unique enforce uniqueness of the column on which they are defined. But by
default primary key creates a clustered index on the column, where are unique creates a nonclustered index by
default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
Q.What are user defined datatypes and when you should go for them?
Answer: User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name,
and format to the database. Take for example, in your database, there is a column called Flight_Num which
appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined
datatype called Flight_num_type of varchar(8) and use it across all your tables.
Q.What is bit datatype and what's the information that can be stored inside a bit column?
Answer: Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit
datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit
datatype can represent a third state, which is NULL.
Q.Define candidate key, alternate key, composite key.
Answer: A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes
the primary key of the table. If the table has more than one candidate key, one of them will become the primary
key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called
composite key.
Q.What are defaults? Is there a column to which a default can't be bound?
Answer: A default is a value that will be used by a column, if no value is supplied to that column while inserting
data. IDENTITY columns and timestamp columns can't have defaults bound to them. Q.What is a transaction and
what are ACID properties? Answer: A transaction is a logical unit of work in which, all the steps must be
performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a
transaction. For more information and explanation of these properties, see SQL Server books online or any
RDBMS fundamentals text book.
Q.Explain different isolation levels
Answer: An isolation level determines the degree of isolation of data between concurrent transactions. The
default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of
isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for
an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you
customize the isolation level at the connection level.
Q.What type of Index will get created after executing the above statement?
Answer: Non-clustered index. Important thing to note: By default a clustered index gets created on the primary
key, unless specified otherwise.
Q.What's the maximum size of a row?
Answer: 8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'.
Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
Q.What is lock escalation?
Answer: Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher
level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being
occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer
coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards
it's dynamically managed by SQL Server.
Q.What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
Answer: DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which
makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row,
instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE
can be rolled back.
Q.Explain the storage models of OLAP
Answer: Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more infomation. What are the new
features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What
changed between the previous version of SQL Server and the current version?
Q.What are constraints? Explain different types of constraints.
Answer: Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you
to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
Q.Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I
create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
Answer: Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered
index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only
one clustered index per table. Non-clustered indexes have their own storage separate from the table data
storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes
having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending
up on the absence or presence of clustered index on the table. If you create an index on each column of a table, it
improves the query performance, as the query optimizer can choose from all the existing indexes to come up
with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE,
DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another
disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used. Database
administration
Q.What is RAID and what are different types of RAID configurations?
Answer: RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database
servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has
some information about RAID levels and for detailed information, check out the RAID advisory board's homepage
Q.What are the steps you will take to improve performance of a poor performing query?
Answer: This is a very open ended question and there could be a lot of reasons behind the poor performance of a
query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of
date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET
NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage
of cursors and temporary tables. Some of the tools/ways that help you troubleshooting performance problems
are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows
NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.
Q.What are the steps you will take, if you are tasked with securing an SQL Server?
Answer: Again this is another open ended question. Here are some things you could talk about: Preferring NT
authentication, using server, databse and application roles to control access to the data, securing the physical
database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL
Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling
auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web
server etc.
Q.What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Answer: Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire
a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of
the user processes is terminated. SQL Server detects deadlocks and terminates one user's process. A livelock is
one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks
keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock
also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the
article Q169960 from Microsoft knowledge base.
Q.What is blocking and how would you troubleshoot it?
Answer: Blocking happens when one connection from an application holds a lock and a second connection
requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
Q.Explain CREATE DATABASE syntax
Answer: Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command:
CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the
other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why
being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for
more information.
Q.How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
Answer: SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very
important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user
mode and -f is used to start the SQL Server in minimal confuguration mode. Check out SQL Server books online
for more parameters and their explanations.
Q.As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
Answer: DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG,
DBCC SHRINKDATABASE, DBCC SHRINKFILE etc .
Q.What are statistics, under what circumstances they go out of date, how do you update them?
Answer: Statistics determine the selectivity of the indexes. If an indexed column has unique values then the
selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these
indexes in determining whether to choose an index or not while executing a query. Some situations under which
you should update statistics: 1) If there is significant change in the key values in the index 2) If a large amount of
data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has
changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated 3)
Database is upgraded from a previous version
Q.What are the different ways of moving data/databases between servers and databases in SQL Server?
Answer: There are lots of options available, you have to choose your option depending upon your requirements.
Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP,
logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.
Q.What is database replicaion? What are the different types of replication you can set up in SQL Server?
Answer: Replication is the process of copying/moving data between databases on the same or different servers.
SQL Server supports the following types of replication scenarios:

Snapshot replication
Transactional replication (with immediate updating subscribers, with queued updating subscribers)
Merge replication
See SQL Server books online for indepth coverage on replication. Be prepared to explain how different replication
agents function, what are the main system tables used in replication etc. How to determine the service pack
currently installed on SQL Server? The global variable @@Version stores the build number of the sqlservr.exe,
which is used to determine the service pack installed. To know more about this process visit SQL Server service
packs and versions. Database programming (top)
Q.What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you
avoid cursors?
Answer: Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only,
Keyset-driven. See books online for more information. Disadvantages of cursors: Each time you fetch a row from
the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip,
however large the resultset is. Cursors are also costly because they require more resources and temporary
storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be
used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Here is
an example: If you have to give a flat hike to your employees using the following criteria: Salary between 30000
and 40000 -- 5000 hike Salary between 40000 and 55000 -- 7000 hike Salary between 55000 and 65000 -- 9000
hike In this situation many developers tend to use a cursor, determine each employee's salary and update his
salary according to the above formula. But the same can be achieved by multiple update statements or can be
combined in a single UPDATE statement as shown below: UPDATE tbl_emp SET salary = CASE WHEN salary
BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END Sql Server DBA Interview Questions Sql
Server DBA Interview Questions and Answers
Q.Write down the general syntax for a SELECT statements covering all the options.
Answer: Here's the basic syntax: (Also checkout SELECT in books online for advanced syntax). SELECT select_list
FROM table_source ]
Q.What is a join and explain different types of joins.
Answer: Joins are used in queries to explain how different tables are related. Joins also let you select data from a
table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER
JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
Q.What is the system function to get the current user's user id?
Answer: USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER,
CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

Q. What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Answer: Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE
or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could
create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In
SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder Triggers can't be
invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on
the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers
can also be used to extend the referential integrity checks, but wherever possible, use constraints for this
purpose, instead of triggers, as constraints are much faster. Till SQL Server 7.0, triggers fire only after the data
modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could
create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers. Also check out books
online for 'inserted table', 'deleted table' and COLUMNS_UPDATED() There is a trigger defined for INSERT
operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly
insterted rows to it for some custom processing. What do you think of this implementation? Can this be
implemented better? Instantiating COM objects is a time consuming process and since you are doing it from
within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This
scenario can be better implemented by logging all the necessary data into a separate table, and have a job which
periodically checks this table and does the needful.
Q.What is a self join? Explain it with an example.
Answer: Self join is just like any other join, except that two instances of the same table will be joined in the query.
Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find
out the managers of all the employees, you need a self join. CREATE TABLE emp ( empid int, mgrid int, empname
char(10) ) INSERT emp SELECT 1,2,'Vyas' INSERT emp SELECT 2,3,'Mohan' INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar' INSERT emp SELECT 5,2,'Sourabh' SELECT t1.empname , t2.empname FROM
emp t1, emp t2 WHERE t1.mgrid = t2.empid Here's an advanced query using a LEFT OUTER JOIN that even returns
the employees without managers (super bosses) SELECT t1.empname , COALESCE(t2.empname, 'No manager')
FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid
Q.What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Answer: A deadlock occurs when two or more processes waits for a resource that is acquired by or is under the
control of another process. A live lock is similar to a deadlock except the process states keeps changing. The
result of such state is that none of the process will be complete. Deadlock detection finds and resolves deadlocks.
A WFG strategy is followed. WFG is wait for graph. In WFG, processes are represented by nodes while
dependencies are represented by edges. Thus, if process A is waiting for a resource held by process B, there is an
edge in the WFG from the node for process A to the node for process B. a cycle is this graph is a deadlock. WFG
constantly checks for cycles or when a process is blocked and adds a new edge to the WFG. When a cycle is
found, a victim is selected and aborted.
Q.What is blocking and how would you troubleshoot it?
Answer: Blocking occurs when two or more rows are locked by one SQL connection and a second connection to
the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the
first lock is released. Troubleshooting blocking: SQL scripts can be written that constantly monitor the state of
locking and blocking on SQL Server The common blocking scenarios must be identified and resolved. The scripts
output must be checked constantly, The SQL profilers data must be examined regularly to detect blocking.
Q.Explain the different types of BACKUPs available in SQL Server.
Answer: Complete database backup: This type of backup will backup all the information in the database. Used
most commonly for disaster recovery and takes the longest time to backup. Differential databse backup: The
database is divided into partitions that have been modified since last complete backup. Most suitable for large
databases. The most recent differential backup contains the changes from previous backups. Transaction log
backups: Backups only the changes logged in the transaction log. The transaction log has all changes logged about
a database. Once the changes are accommodated on the database, the log is truncated or backed up. File/File
Group backups: used to recover individual files or file groups. Each filegroup can be individually backed up. This
helps in recovery only the required file or filegroup for disaster recovery.
Q.What is database isolation in SQL Server?
Answer: Isolation in database defines how and when changes made by one transaction can be visible to other
transactions. Different isolation levels are: Serializable Repeatable read Read committed Read uncommitted
Q.What is a Schema in SQL Server ? Explain how to create a new Schema in a Database?
Answer: A schema is used to create database objects. It can be created using CREATE SCHEMA statement. The
objects created can be moved between schemas. Multiple database users can share a single default schema.
CREATE SCHEMA sample; Table creation Create table sample.sampleinfo { id int primary key, name varchar(20) }
Q.Explain how to create a Scrollable Cursor with the SCROLL Option.
Answer: Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee; The active set of the cursor is stored
can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for
select and function cursors but not insert or update statements.
Q.Explain how to create a Dynamic Cursor with the DYNAMIC Option.
Answer: When a cursor is declared as DYNAMIC, the cursor reflects all changes made to the base tables as the
cursor is scrolled around. Declare cursor_name cursor FOR select_statement The dynamic option does not
support ABSOLUTE FETCH.
Q.What are database files and filegroups?
Answer: Database files are used for mapping the database over some operating system files. Data and log
information are separate. SQL server database has three types of database files: Primary: starting point of a
database. It also points to other files in database. Extension: .mdf Secondary: All data files except primary data
file is a part of secondary files. Extension: .ndf Log files: All log information used to recover database.
Extension: .ldf
Q.Describe in brief Databases and SQL Server Databases Architecture.
Answer: SQL Server consists of a set of various components which fulfill data storage and data analysis needs for
enterprise applications. Database architecture: All the data is stored in databases which is organized into logical
components visible to the end users. It’s only the administrator who needs to actually deal with the physical
storage aspect of the databases, whereas users only deal with database tables. Every SQL Server instance has
primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created
databases as per their needs and requirements. A single SQL Server instance is capable of handling thousands of
users working on multiple databases.
Q.How can you start the SQL Server in the single user mode and the minimal configuration mode?
Answer: The SQLServer.exe is the executable which can be called in the command prompt with the parameters -
m and -f. These are the options that will start the SQL server in the user mode and minimal configuration mode
respectively.
Q.How can you know that statistics should be updated?
Answer: Statistics represent the uniqueness for the indexes that are being used for selecting the records. This can
make the query execution pretty efficient. The tables that we are dealing with if truncated and repopulated,
there is a good chance that the indexes and statistics are out of sync and this is when we have to update the
statistics. There are also other situations like when the table has been modified and lot of rows have been added
recently or like when a server has been updated with different version of software. These also give us the reason
to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it accordingly.
Q.What is replication in SQL Server?
Answer: Replication refers to the moving or copying of the database elements from one system to another. This
can be done in the SQL Server in one of the following methods:

Transactional.
Snapshop.
Merge replication.
Q.Can we initiate a external COM object from within SQL?
Answer: Yes we can use the stored procedure sp_OACreate to initiate the external COM object from the T-SQL.
Q.What is a schema? How is it useful in SQL Serers?
Answer: The Schema refers to the overall structure of the database with all related information like users, access
privileges, interaction information between the different tables etc. The CREATE SCHEMA is the command that
can be used to create the schema in the SQL Server. This when done can be used to re deploy the same database
in another system for demonstrative or test purposes. This holds intact the underlying framework over which the
database has been built.
Q.What is a write-ahead log?
Answer: The write-ahead log is the logging system that just updates the buffer cache of the database for the
transactions and updates the logs and only then the actual changes are incorporated in the actual database. This
is the reason why it is called “write ahead”. This helps in maintaining the consistency in the database. This can
also be useful in getting the actual database values even in case of failures.
Q.What is the use of check points in the transaction logs?
Answer: The check points are restoration points that indicate the specific state of the database. When there is
some failure int he database that is occurring before the next check point, the database can be reverted back to
the previous check point and thus the database would still be consistent.
Q.What is a column with identity?
Answer: The column with a defined identity in turn means that there is an unique value that the system assigns to
the specific column. This is similar to the AUTONumber property of the Access backend.

Q.What are the different components that constitute the SQL Server architecture?

Answer: The Logical component consists of the overall database structure definitions, users, groups, access
permissions, collations etc. The Physical component is the one that contains the physically represented form of
the database i.e the actual files. There are three types of files:

Primary data files: These contain the links to the other files. This file is the representation of the higher level of
the database. (.mdf)

Secondary data files: These contain the files that contain the values and actual data as in database.

Log files: These represent the different types of the logging output of the SQL Server, including the transaction
logs.

Q.What are scrollable cursors? How are they created?

Answer: The scrollable cursors are the ones that can get the entire set of rows as single entity, within which all
the rows present can be accessed in any order without the open/close of cursor done for every row access. The
scrollable cursors are created with the keyword SCROLL added to the CREATE Cursor statements. The scrollable
cursors are useful for the access of information from different rows but not for the delete/insert of new rows.
Q.What is RAID? How does it help storage of databases?
Answer: The RAID stands for Redundant Array of Independent Disks. With its own RAID controllers, the RAID
implements a fail-safe storage mechanism with its own backup mechanisms. There are different configurations of
the RAID that all give us the ACID properties of storage along with other such facilities. This kind of storage will
make the SQL Server database to be fail-safe and stable. This can sometimes mean that the backup mechanisms
and other such reliability measures can be taken off from the SQL Server level of operations.
Q.How can you identify the version number of the SQL Server installed?
The global variable @@version has the build and version information for the SQL Server and the service packs.

Q. What are the different types of Indexes available in SQL Server?

Answer: The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes
what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

Q. What is the difference between Clustered and Non-Clustered Index?

In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created
on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one
Clustered index on a table. In a Non-Clustered index, the leaf level pages does not contain data pages instread it
contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
Q.How do you troubleshoot errors in a SQL Server Agent Job?
Answer: Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity
monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right
click and choose view history from the drop down menu. The execution history of the job is displayed and you
may choose the execution time (if the job failed multiple times during the same day). There would information
such as the time it took to execute that Job and details about the error occurred.
Q.What is the default Port No on which SQL Server listens?
Answer: 1433
Q. How many files can a Database contain in SQL Server?How many types of data files exists in SQL Server? How
many of those files can exist for a single database?
Answer: A Database can contain a maximum of 32,767 files. There are Primarily 2 types of data files Primary data
file and Secondary data file(s). There can be only one Primary data file and multiple secondary data files as long
as the total # of files is less than 32,767 files.
Q. What is DCL?
Answer: DCL stands for Data Control Language.
Q.What are the commands used in DCL?
Answer: GRANT, DENY and REVOKE.
Q.What is Fill Factor?
Answer: Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how
much data is written to an index page when it is created / rebuilt.
Q.What is the default fill factor value?
Answer: By default the fill factor value is set to 0.
Q.Where do you find the default Index fill factor and how to change it?
Answer: The easiest way to find and change the default fill factor value is from Management Studio, Right click
the Server and choose properties. In the Server Properties, choose Database Settings. you should see the default
fill factor value in the top section. You can change to a desired value there. The other of viewing and changing
this value is using sp_configure.
Q.What is SQL Profiler. What are the default templates with it?
Answer: SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database
Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
Q.What are the DMVs?
Answer: Dynamic Management Views (DMV) return server state information that can be used to monitor the
health of a server instance, diagnose problems, and tune performance.
Q.What is the syntax to execute the sys.dm_db_missing_index_details?
Answer: Select * from sys.dm_db_missing_index_details

DATABASE - SQL SERVER

SQL Server Interview Question and Answers
1. What is Sql server?
SQL - Structured query language is the standard command set used to communicate with the
relational database management system.

Sql server - is commonly used as the backend system for websites and corporate CRMs and can support
thousands of concurrent users.SQL Server is much more robust and scalable than a desktop database
management system such as Microsoft Access.
2. What are the System Database in Sql server 2005?

Master - Stores system level information such as user accounts, configuration settings, and info on all other
databases.
Model - database is used as a template for all other databases that are created
Msdb - Used by the SQL Server Agent for configuring alerts and scheduled jobs etc
Tempdb - Holds all temporary tables, temporary stored procedures, and any other temporary storage
requirements generated by SQL Server.

3. What is the difference between TRUNCATE and DELETE commands?

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation
can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with
DELETE and not with TRUNCATE.
4. What is OLTP?
Online Transaction Processing (OLTP) relational databases are optimal for managing changing data.
When several users are performing transactions at the same time, OLTP databases are designed to let
transactional applications write only the data needed to handle a single transaction as quickly as possible.
5. Define Normalisation?
Normalisation is an essential part of database design. A good understanding of the semantic of data
helps the designer to built efficient design using the concept of normalization

6. What are the difference between clustered and a non-clustered index?

A Clustered index is a special type of index that reorders the way in which each records in the table
are physically stored.
A Non clustered index is a special type of index in which the logical order of the index does not match the
physical stored order of the rows on disk.
7. What are the System Database in Sql server 2008?

Master
Model
Msdb
Tempdb
Resource
8. What are the different locks in Sql Server?

Intent
Shared
Update
Exclusive
Schema
Bulk Update
9. What are the different types of Sub-Queries?

Single row subquery

Multiple row subquery
Correralted row subquery
10. What are constraints? Explain different types of constraints?

Constraints is a rule or restriction concerning a piece of data that is enforced at the data level. A Constraint
clause can constrain a single column or group of columns in a table.
There are five types of Constraint namely
Null / Not Null
Primary Key
Unique
Check or Validation
Foreign Key or References Key

1. What are the different types of BACKUPs avaialabe in SQL Server 2005?

In SQL Server 2005 Backup Types are

Full
Transaction Log
Differential
Partial
Differential Partial
File and Filegroup
Copy Only Database Backups.
12. What are Data files?
This is the physical storage for all of the data on disk. Pages are read into the buffer cache when users
request data for viewing or modification. After data has been modified in memory (the buffer cache), it is
written back to the data file during the checkpoint process.

13. Define Primary Key?

The primary key is the columns used to uniquely identify each row of a table.
A table can have only one primary key.
No primary key value can appear in more than one row in the table.
14. What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis,
instead of the typical SQL commands that operate on all the rows in the set at one time.
15. What is SQL Profiler?
 SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of
Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze
later.

16. What is the difference between DELETE and TRUNCATE statement?

A DELETE statement enables you to selectively remove data from a table, whereas
The TRUNCATE statement unconditionally removes all rows from a table.
17. What are the types of transaction levels in SQL SERVER?

There are four transaction levels in SQL SERVER.

Read committed
Read uncommitted
Repeatable read
Serializable

18. What is the difference between a DDL trigger and a DML trigger?

A DDL trigger executes in response to a change to the structure of a database (for example, CREATE,
ALTER, DROP).
A DML trigger executes in response to a change in data (INSERT, UPDATE, DELETE).
19. What database does SQL Server use for temporary tables?
TempDB.
20. What is a linked server?
A linked server enables you to work with other SQL Servers as well as databases other than SQL
Server databases, right from within Management Studio.

21. What are the transaction properties?

Atomicity
Consistency
Isolation
Durability
22. Define Synonym?
Synonym is an alternative method to creating a view that includes the entire table or view from
another user it to create a synonym.
A synonym is a name assigned to a table or view that may thereafter be used to refer to it.

23. What is an active database?

Active database is a database that includes active rules, mostly in the form of ECA rules(Event
Condition rules). Active database systems enhance traditional database functionality with powerful rule
processing cabalities, providing a uniform and efficient mechanism for database system applications.
24. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY
clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically
used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before
they are part of the GROUP BY function in a query.
25. What are the purpose of Normalisation?
Minimize redundancy in data.
Remove insert, delete and update anamoly during the database activities.
Reduce the need to reorganize data it is modified or enhanced.
Normalisation reduces a complex user view to a set of small and stable subgroups of fields or relations.

26. What are the types of database recovery models?

Full
Simple
Bulk Logged
27. What the difference between UNION and UNIONALL?
Union will remove the duplicate rows from the result set while Union all does’nt.

28. What is the building block of a relational database?

The table
29. What is the difference Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as
Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF›s can be though of as views that take parameters and can be used in JOINs and other Rowset
operations.
30. What is the difference between a local and a global variable?
A Local temporary table exists only for the duration of a connection or, if defined inside a compound
statement, for the duration of the compound statement.
A Global temporary table remains in the database permanently, but the rows exist only within a given
connection. When connection are closed, the data in the global temporary table disappears. However, the table
definition remains with the database for access when database is opened next time.

31. What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints
are used to enforce domain integrity, as the check constraints.
32. What is log shipping?
Log shipping is the process of automating the backup of database and transaction log files on a
production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log
shipping. In log shipping the transactional log file from one server is automatically updated into the backup
database on the other server.

33. What is Cross Join?

A cross join that does not have a WHERE clause produces the Cartesian product of the tables
involved in the join. The size of a Cartesian product result set is the number of rows in the first table
multiplied by the number of rows in the second table.
34. What is Self Join?
A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in
that it involves a relationship with only one table.
35. What is a default constraint?
A default constraint is a value that SQL Server automatically places in a particular field in a table.
36. What is the difference between Triggers and Stored Procedure?
 Stored Procedures are called by the programmer wherever it wants to fire but triggers fired
automatically when insert,delete,update occured. And triggers can be implemented to tables & views only
where as stored procedure used in the database independently.
37. What are the properties of Sub–Query?

A subquery must be enclosed in the parenthesis.

A subquery must be put in the right hand of the comparison operator
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

38. Where are SQL server users names and passwords are stored in sql server?
They get stored in master db in the sysxlogins table.
39. What are the types of subscriptions in SQL Server replication?

There are two types of replication in sql server are :

Push
Pull
40. What does REVERT do in SQL Server 2005?
Restores your previous execution context.If you have changed your execution context with
EXECUTE AS, the REVERT statement will restore the last context prior to the EXECUTE AS.

41. What is the difference between system objects and user objects?

SQL Server creates system objects during the installation process.

They are part of the system, and most of them are necessary for SQL Server to function properly.
Whereas system objects are part of the SQL Server system, you create user objects.
User objects include the databases, stored procedures, functions, and other system objects that you build.
42. Who is responsible for backing up SQL Server databases?
SQL Server administrator.

43. What is a Server Role?

A server role is pre-defined by SQL Server. It possesses a pre-defined set of rights.
44. What is the name of the system variable that returns the number of rows affected by a SQL statement?
@@ROWCOUNT.
45. What keyword do you use to return a value from a stored procedure?
The OUTPUT keyword.

46. What are different type of Collation Sensitivity?

The different phases of transaction are :

Case sensitivity
Accent sensitivity
Kana Sensitivity
Width sensitivity
47. What is the difference between a check constraint and a rule?
Check constraints and rules limit the range of values that a user can enter into a column. Whereas
check constraints apply only to the table for which you enter them, you can apply rules to multiple tables.
48. What function extracts specified characters from a string?
The SUBSTRING function extracts specified characters from a string.
49. What is the difference between TRUNCATE and DROP?
TRUNCATE removes all data from the table while retaining the table structure, whereas DROP
removes the table from the database.
50. What are the different types of cursor?

There are three types of cursors :

Implicit cursors
Explicit cursors
Paramaeteried cursors

51. Define Unique Key?

Unique key is a one or more column that must be unique for each row of the table.
It is similar to primary key. Primary key column will not accept a null. Whereas the unique key column will
accept a null values.
52. Define Joins?
A Join combines columns and data from two or more tables (and in rare cases, of one table with
itself).

53. Define Indexes?

Index is a general term for an Oracle/SQL features used to primarily to speed execution and imposes
uniqueness upon certain data.
The most important of an index is to ensure uniqueness of rows and help in speedy retrieval of data.
54. What are the difference between primary keys and foreign keys?
The primary key is the columns used to uniquely identify each row of a table.A table can have only
one primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between
tables.
55. Define Clusters?
Clustering is a method of storing tables that are intimately related and often joined together into the
same area on disk.
A cluster contains one or more tables, which have one or more column in common among them.

56. What are some of the objects held in a database?

Tables
Columns
Data types
Stored procedures
Triggers
Rules
Keys
Constraints
Defaults
Indexes
57. What is the difference between the HAVING clause and the WHERE clause?
SQL Server Express applies the HAVING clause after it summarizes the data, whereas it applies the
WHERE clause before it summarizes the data.
58. Name the three types of user-defined functions?

Scalar
Inline table–valued
Multi-statement table–valued.
59. What is an identity column?
Identity columns provide an autoincrementing value for a table.
60. What component installs the OLEDB and ODBC drivers?
The MDACs (Microsoft Data Access Components).

61. What is denormalization and when would you go for it?

Denormalization is the process of attempting to optimize the performance of a database by adding
redundant data or by grouping data.Denormalization is the reverse process of normalization.
62. What keyword do you use to assign a value to a variable?
The SELECT keyword.

63. What are the advantages of SQL Server 2005 Express?

SQL Server 2005 Express is free and is easy to use.

It provides the same protection and information management provided by more sophisticated versions of SQL
Server.
It is easy to install, provides rich database functionality, and sports deep integration with Visual Studio 2005.
64. What function finds the difference between two dates?
The DATEDIFF function.
65. What does the RETURN statement do?
The RETURN statement unconditionally exits a stored procedure without executing any other
statements.

Collation In database:

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties to data. A collation
defines bit patterns that represent each character in metadata of database. SQL Server supports storing objects
that have different collations in database.

SQL DB is in recovery Pending:

ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [DBName] set multi_user
GO

SQL DB is in Restoring Mode:

RESTORE DATABASE <database name> WITH RECOVERY

(Or)

RESTORE DATABASE YourDB FROM DISK = 'Z:\YourDB.bak'

WITH NORECOVERY
GO
RESTORE LOG YourDB FROM DISK = 'Z:\YourDB.trn'
WITH RECOVERY
GO

SQL DB is in Suspect Mode:

USE master
GO
ALTER DATABASE BPO SET EMERGENCY
GO
DBCC CHECKDB (BPO)
GO
ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE BPO SET MULTI_USER
GO

SQL DB is in Emergency Mode:

ALTER DATABASE Test SET SINGLE_USER;

GO
DBCC CHECKDB (Test, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
Go
Alter DATABASE test set MULTI_USER;
Stored Procedure :

A stored procedure in SQL is a group of SQL statements that are stored together in a database. Based on the
statements in the procedure and the parameters you pass, it can perform one or multiple DML operations on the
database, and return value, if any. Thus, it allows you to pass the same statements multiple times, thereby,
enabling reusability.

OLE:
Object Linking and Embedding Database (OLE DB) is a group of APIs (Application Programming Interface) used to
facilitate and abstract access to application data of different file formats, including spreadsheets, structured
query language (SQL)-based database management systems (DBMS), indexed-sequential files, and personal
databases.

OLE DB is based on the Component Object Model (COM) and is part of the Microsoft Data Access Components
(MDAC) software package, which is used to read and write data.

What is check point in SQL Server:

A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log
information from memory to disk and, also records the information in the transaction log.

The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal.