Introduction

SQL Server is a relational database management supporting a wide variety of transaction processing, business intelligence and more. SQL Server is the Microsoft name for its own RDBMS product. Be it a beginner, or an intermediate or an expert of SQL Server, this write-up will aid you to boost your confidence and knowledge of SQL. The questions provided here will level up your knowledge and learning of SQL Server, alongside providing step-by-step explanations for every question that will help you understand the concepts in detail. With SQL Server interview questions by your side, you can be positive about your preparation for the upcoming interview.

SQL Server Interview Questions and Answers

Beginner

1. What is the difference between UNION and UNION ALL?

UNION blends the contents of two structurally-compatible tables into a solitary joined table. The distinction among UNION and UNION ALL is that UNION will discard duplicate records through UNION ALL will incorporate duplicate records. Note that the presentation of UNION ALL will commonly be superior to UNION since UNION requires the server to do the extra work of expelling any duplicate. In this way, in situations where there is a surety that there won't be any copies, or where having copies isn't an issue, utilization of UNION ALL eventual suggested for performance reasons. Let's have a look at the below examples explaining the usage of both. In the first, we have used UNION and in the second we have explained UNION ALL.

Union example code

2. List all joins supported in SQL?

Inner Join- This is the simplest one and most widely used JOIN. This is default one if we do not specify any JOIN between tables. It returns all matching records from both tables where the matching condition is satisfied.
LEFT JOIN- We call this LEFT OUTER JOIN as well. When we have situations like we want all columns from one table and only matching records from another table, we go for this type of JOIN. There are two types of same. The LEFT one is the first type where we would like to have all records from LEFT table and only matching records from RIGHT one then we go for this type of JOIN. When we do not have any matching condition in the right table then all columns from the right one will be NULL while LEFT table will have all the records.
RIGHT JOIN- We call this RIGHT OUTER JOIN as well. This is just the reverse of what we discussed for LEFT JOIN. The result set will be having all records from the right table but only matching records from the left one. Even if the ON clause which gives matching record fails, it will ensure rows are returned from the right table and corresponding columns from the LEFT table with NULL values.
FULL JOIN- It is also called FULL OUTER JOIN. It is having characteristics of both LEFT /RIGHT outer join. The result set will be having rows whenever we have the match in any of the tables either LEFT or RIGHT one. We can also say that it gives the same result if we apply UNION on LEFT and RIGHT OUTER JOIN resultset.
CROSS JOIN- This is a cartesian product of two tables where each row from the primary table is joined with each and every row of the secondary table. Even if we use SELECT statement on two tables separated by a comma and without any WHERE condition, it will give the same result as we get from applying CROSS JOIN.

3. How many system databases we have in the SQL server?

Master: It contains framework catalogs that keep data about disk space, record portions, use, system-wide setup settings, login accounts, the presence of other databases, and the presence of other SQL Servers (for appropriate activities). If this database does not exist or corrupted then the SQL Server instance cannot start. Although we user objects in the master database, it is not advised to do so. This database should always remain as static as possible. In the case of a master database being rebuilt, all user objects will be lost.
Model: It is basically a template database. Each time you make another database, SQL Server makes a duplicate of a model to frame the premise of the new database. Any changes made to this database, related DB size, collation, recovery model, and any other configurations, are applied to any new database created afterward.
Tempdb: Temporary database, tempdb, is a workspace. SQL Server tempdb database is one of a kind among every single other database since it is reproduced not recuperated each time SQL Server is started.
Msdb: This database is utilized by the SQL Server Agent Service, which performs planned exercises, for example, backup and replication assignments.

system databases

4. what is Normalization and denormalization in SQL?

Normalization and denormalization are the strategies utilized in databases. The terms are differential where Normalization is a procedure of limiting the addition, removal and update peculiarities through disposing of the redundant information. Then again, Denormalization is the reverse procedure of Normalization where the repetition is added to the information to improve the exhibition of the particular application and information integrity. Normalization prevents the disk space wastage by limiting or disposing of the redundancy.

Normalization v/s denormalization in SQL

5. What is the difference between clustered and non clustered index?

The indexing is required to quicken search results in the database. If we compare index in our real world then page number of books and keywords mostly on the back side of book work similar as Index. We can quickly go to respective pages if we know the page number and also if we have an idea of keywords, we are looking into the book then just visiting keywords section will make our job easier as keywords are linked with page numbers. There are two types of indexes that exist in SQL database. One is called clustered while other is called non-clustered. The page number of the book is similar to the clustered index while the keyword section of the book represents non-clustered indexes. They exist in the database as a B-Tree structure. Let's go into the details of each index.

Clustered Index

We can have only one clustered index per table. Once we create a primary key, by default it ends up creating a clustered index on that key. We can also specify the index type as well in case we would like to have non clustered index as well. The table is called heap if it does not have any clustered index. As we said earlier that indexes are B-Tree structures. The leaf node is mapped to data pages of the table in case of a clustered index. The data is physically sorted based on the clustered index. This is the reason why it is quickest in two of the indexes.

Non-clustered Index

Earlier it was possible to have only 249 non clustered indexes on the table but after SQL server 2008 we can have 999 non clustered indexes. It gets created by default when we create unique key constraint. We can also decide index type as well while creating Unique constraint. It is not mandatory to have any non clustered index. The leaf nodes of non clustered index map to index pages having details of clustering key or RID to get the actual row. In case the table does not have any clustered index, leaf nodes are mapped to the physical location of the row which is called as RID and if present leaf nodes mapped to clustering key. It is much quicker for DML operations like adding /deleting and updating records.

8. When to use SQL temp tables vs table variables?

There are several scenarios in which we use temp tables. In case we have no DDL or DML access to a table. You can utilize your current read access to maneuver the information into a SQL Server temp table and make modifications from that point. Or on the other hand, you don't have the authorization to make a table in the current database, you can make a SQL Server temp table that you can control. At long last, you may be in a circumstance where you need the information to be available just in the present session.

The temp tables have "hashtag" followed by the table name. For instance: #Table_name. SQL temp tables are made in the tempdb database. A local SQL Server temp table is just available to the present session. It can't be accessed or utilized by procedures or queries outside of the session. A standout frequently utilized situation for SQL Server temp tables is when we use a loop in the query. For instance, you need to process information for a SQL query and we need a spot to store information for our loop to peruse. It gives a speedy and productive way to do as such. Another motivation to utilize SQL Server temp tables is when we have to execute some expensive processing. Suppose that we make a join, and each time we have to pull records from that result-set then it needs to process this join again and again. Why not simply process this outcome set once and toss the records into a SQL temp table? At that point, you can have the remainder of the SQL query allude to the SQL temp table name. In addition to the fact that this saves costly processing, it might even make our code look a little cleaner.

The SQL temp table is dropped or demolished once the session drops. Most of the time we will see: DROP #tbl command but it is not mandatory. Temp tables are always created in the tempdb database. It resolves name conflict by adding a suffix in case of a similar name for temp tables.

SQL temp tables vs table variables

Global SQL temp tables are helpful when we need to reference tables in all sessions. Anybody can add, modify, or delete records from the table. Additionally, note that anybody can DROP the table. Like Local SQL Server temp tables, they are dropped once the session drops and there are never again any more references to the table. We can generally utilize the "DROP" command to tidy it up manually.

Table variables are more like some other variables. It is a common understanding that table variables exist just in memory, yet that is basically not correct. They live in the tempdb database just like local SQL Server temp tables. Additionally like local SQL temp tables, table variables are accessible just inside the session in which they are declared. The table variable is just available inside the present batch.

Code

If performance is criterion then table variables are helpful with modest quantities of data. Generally, a SQL Server temp table is helpful when filtering through a lot of information. So for most times, we will in all probability observe the utilization of a SQL Server temp table rather than a table variable.

10. What are the Advantages of Using CTE?

CTE is virtually created temporary storage which holds query output created by SQL syntax SELECT, INSERT, UPDATE, DELETE OR CREATE VIEW statement. It holds the data until the time of the query session. It is similar to the derived table only. We can also use the CTE in case of VIEW in some scenarios:

With common table expression

WITH
expression _name
column_name
AS
CTE_query_definition
WITH common_table_expression  Railroad diagram

expression_name: Is a valid identifier for the common table expression. It must be different from the others defined within the same WITH clause, but it can be the same as the name of a base table or view. Any reference to it in the query uses the common table expression and not the base object
column_name: Specifies a unique column name in the common table expression. The number of column names specified must match the number of columns in CTE _query_definition

If the query definition supplies distinct names for all columns then the column names are optional

CTE_query_definition: This is a SELECT statement whose result set populates the common table expression. This must meet the same requirements as for creating a view except that a CTE cannot define another CTE

Let me list down some benefits of using CTE :

When we have a recursive query we can use CTE. It holds a query output in an area that is defined during CTE definition. It is more beneficial when we need to work on the queries query. It holds query data till the time the SQL query is running. It is beneficial for holding filter data which is needed for subsequent processing.
It improves the readability of queries without affecting performance.
It can be referenced multiple times in the SQL query.
It can be used instead of a view where metadata information does not require to be stored.

There are two types of CTE :

Recursive CTE: This type of reference itself within CTE. This is useful while working with hierarchical data since it executes until the time the whole hierarchy gets returned. A recursive query must contain two SQL query statements joined by UNION ALL, UNION, INTERSECT, or EXCEPT operator. In the below example we have Employee table having hierarchical data (Employee and Manager relationship)

Recursive CTE code

Non-Recursive CTE: This type does not reference itself. This is much simpler than other types of CTE.

Non-Recursive CTE code

25. How to monitor latency in replication?

There are three methods.

Replication monitor
Replication commands
Tracer Tokens

Replication Monitor: In the replication monitor from the list of all subscriptions just double click on the desired subscription. There we find three tabs.
- Publisher to Distributor History
- Distributor to Subscriber History
- Undistributed commands
Replication Commands:
- Publisher.SP_ReplTran: Checks the pending transactions at p
- Distributor.MSReplCommands and MSReplTransactions: Gives the transactions and commands details. Actual T_SQL data is in binary format. From the entry time, we can estimate the latency.
- Distributor.SP_BrowseReplCmds: It shows the eaxct_seqno along with the corresponding T-SQL command
- sp_replmonitorsubscriptionpendingcmds: It shows the total number of pending commands to be applied at subscriber along with the estimated time.
Tracer Tokens:

Available from Replication Monitor or via TSQL statements, Tracer Tokens are special timestamp transactions written to the Publisher’s Transaction Log and picked up by the Log Reader. They are then read by the Distribution Agent and written to the Subscriber. Timestamps for each step are recorded in tracking tables in the Distribution Database and can be displayed in Replication Monitor or via TSQL statements.

When Log Reader picks up Token it records time in MStracer_tokens table in the Distribution database. The Distribution Agent then picks up the Token and records Subscriber(s) write time in the MStracer_history tables also in the Distribution database.

Below is the T-SQL code to use Tracer tokens to troubleshoot the latency issues.

–A SQL Agent JOB to insert a new Tracer Token in the publication database.
USE [AdventureWorks]
Go
EXEC sys.sp_posttracertoken @publication = <PublicationName>
Go
–Token Tracking Tables
USE Distribution
Go
–publisher_commit
SELECT Top 20 * FROM MStracer_tokens Order by tracer_id desc
–subscriber_commit
SELECT Top 20 * FROM MStracer_history Order by parent_tracer_id desc

It's no surprise that this one pops up often in SQL multiple choice questions.

Intermediate

1. Explain page, A fundamental unit of storage in SQL Server and different types of pages.

A page is a fundamental unit of storage that stores data. The page is the size of 8KB and we have 8 types of pages in SQL Server.

Data
Index
Text/Image (LOB, ROW_OVERFLOW, XML)
GAM (Global Allocation Map)
SGAM (Shared Global Allocation Map)
PFS (Page Free Space)
IAM (Index Allocation Map)
BCM (Bulk Change Map)
DCM (Differential Change Map)

2. Explain the difference between Sequence vs Identity?

An identity column in the table has auto-generate & auto increase value with each new row insert. The user cannot insert value in the identity column.

The sequence is a new feature introduced with SQL Server 2012 similar to Oracle’s sequence objects. A sequence object generates a sequence of unique numeric values as per the specifications mentioned. Next VALUE for a SEQUENCE object can be generated using the NEXT VALUE FOR clause.

IDENTITY is column level property & tied to a particular table. This cannot be shared among multiple tables.
SEQUENCE is an object defined by the user with specific details and can be shared by multiple tables. This is not tied to any particular table.
IDENTITY property cannot be reset to its initial value but the SEQUENCE object can be reset to initial value any time.
Maximum value cannot be defined for IDENTITY whereas this can be done for SEQUENCE object.

3. What are the dynamic management views and share any 5 that you are using on a regular basis?

The DMV (Dynamic Management Views) is a set of system views introduced with SQL Server 2005. DMV’s are a new tool of DBA to get internal information of the system and it’s working.

sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_os_buffer_descriptors
sys.dm_tran_locks - Locking and blocking
sys.dm_os_wait_stats - Wait stats
sys.dm_exec_requests Percentage – For Percentage complete for a process
Sys.dm_exec_sessions

This is a frequently asked SQL Server interview question.

4. Name all Available isolation levels in SQL Server?

SQL Server supports different types of isolation level.

Read Uncommitted – Read Uncommitted is the lowest isolation level allow dirty reads. Here, Changes does by one transaction is visible to other transactions before committing.
Read Committed – Read Committed s isolation does not allow dirty read. Any data read by the transaction is 100% committed. Till the time transaction is updating the record, it holds exclusive locks on it.
Repeatable Read – Repeatable read is the most restrictive isolation level which holds locks on the table even with read transactions. Table data cannot be modified from any other sessions until the transaction is completed.
Serializable – This is the Highest isolation level similar to repeatable read with the prevention of Phantom Read. It ensures transaction referencing same records must run in serially.
Snapshot – This also known as RCSI (Read Committed Snapshot Isolation). It’s similar to Serializable Isolation but the only difference is Snapshot does not hold a lock on the table during the transaction so that the table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data called Row Versioning.

5. Explain any 5 differences between CheckPoint & LazyWriter?

Checkpoint

Checkpoint occur on Recovery time interval, Backup/Detach Command & on the execution of DDL command.
Checkpoint writes only dirty pages to the disk. Checkpoint does not release any memory.
The checkpoint is responsible for DB recovery point.
Checkpoint always mark entry in T-log before it executes either SQL engine or manually
Checkpoint occurrence can be monitored using performance monitor “SQL Server Buffer Manager Checkpoint/sec”.
You need SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR rights to execute checkpoint manually

Lazy Writer

Lazy Writer release buffer pool memory when memory pressure occurs to ensure enough free memory.
Lazy Writer looks for LRU, least recently used (“cold” = least recently read or written, not accessed in recent time) pages in the buffer pool and releases the memory taken by them. Lazy writer releases both dirty and clean pages. Clean pages can be released without writing to disk and dirty pages release after written to the disk.
Lazy Writer is only responsible for managing free buffer space. The lazy writer does not affect or manage database recovery.
The lazy writer doesn’t mark any entry in T-log.
Lazy writer occurrence can be monitored using performance monitor “SQL Server Buffer Manager Lazy writes/sec”.
SQL server executes its own. User cannot run it manually

Advanced

1. Explain Physical Database Architecture In Brief?

The physical database architecture is a description of the way the database and files are organized in a SQL server.

Pages and extents- A page is the size of 8KB and set of 8 Pages are called extent. This is the fundamental unit where data is stored.
Physical Database Files and Filegroups- Database files visible at file system to store data and logs.
Table and Index Architecture- Database objects inside the database to store & access data.
Database- Database is a set of data & Log file which resides over the filesystem and managed by the operating system.
SQL Instance- SQL instance is a logical entity controlled databases. One SQL instance can have multiple databases. Security and accessibility is also part of the SQL instance.

2. Explain One to One (1:1), One to Many (1:N) and Man to Many (N:N) mapping with an example?

One to One(1:1) – For each instance, in the first entity, there is one and only one in the second entity and vice versa. Like - Employee Name and Employee ID, One employee can have only one Employee ID and one Employee ID can be allocated to one person only.
One to Many(1:N) – For each instance, in the first entity, there can be one or more in the second entity but for each instance, in the second entity, there can be one and only one instance in the first entity. Like - Manager & Employee, One Manager can have many employees but one employee can have only one Manager.
Many to Many(N:N) –For each instance, in the first entity there can be one or more instance in the second entity and vice versa. Like – Employee & Project, One Employee can work on multiple projects and One Project can have multiple employees to work.

3. Explain the Write-Ahead Transaction Log (WAL) protocol?

Write-ahead transaction log controls & defined recording of data modifications to disk. To maintain ACID (Atomicity, Consistency, Isolation, and Durability) property of DBMS, SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk.

Write-ahead log work as below:-

SQL Server maintains a buffer cache to maintain data pages on retrieval
When a page is modified in the buffer cache, it marked as dirty. The page is not immediately written back to disk.
A data page can be modified multiple times before written to disk but maintain separate log entry a transaction log record.
The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk.

This is a regular feature in SQL query interview questions for experienced professionals, be ready to tackle it.

4. What is Row Versioning?

To understand Row versioning, you should first understand RCSI (Read Committed Snapshot Isolation). RCSI does not hold a lock on the table during the transaction so that the table can be modified in other sessions, eliminate the use of shared locks on read operations.

RCSI isolation maintains versioning in Tempdb for old data called Row Versioning. Row versioning increases overall system performance by reducing the resources used to manage locks.

When any transaction updates the row in the table, New row version was generated. With each upcoming update, If DB is already having the previous version of this row, the previous version of the row is stored in the version store and the new row version contains a pointer to the old version of the row in the version store.

SQL Server keeps running clean up task to remove old versions which are not in use. Until the transaction is open, all versions of rows that have been modified by that transaction must be kept in the version store. Long-running open transactions and multiple old row versions can cause a huge tempDB issue.

5. Is it possible to import data using T-SQL commands without using SQL Server Integration Services? If yes, please share commands?

Sometimes we have a situation when we cannot perform such activities using SSIS due to multiple types of databases, Different domains, Older version, etc.

We have several commands available to import using T-SQL.

BCP – BCP is Bulk copy program used to import a large number of rows into SQL server or to text\csv files.
Bulk Insert – Bulk insert is used to data files (text \ csv \ excel) into a database table in a user-specified format.
OpenRowSet – OpenRowSet is used to access remote data from an OLE DB data source. This is an alternative method of Linked server for the one-time or ad-hoc connection.
OPENDATASOURCE - OPENDATASOURCE is used to access remote data on an ad-hoc basis with 4 part object name without using a linked server name.
OPENQUERY – OPENQUERY is used to execute a specified query on the specified linked server. OPENQUERY can be referenced in from clause with INSERT, UPDATE, or DELETE statement on the target table.
Linked Servers – Linked Servers are configured to access data outside SQL Server from another SQL instance or DB types (Like Oracle \ DB2, etc.). It has the ability to issue queries & transactions on heterogeneous data sources.

This is a frequently asked SQL server interview question for experienced professionals.

32. What are the points to be considered for creating a fast performing stored procedure?

The performance of any application largely depends upon the backend process which extracts data. If it is faster then the application will be also responsive. The designing of the stored procedure is very critical and need to be careful to remove any We can consider the following points while designing stored procedure :

Variables: We should minimize the use of variables as it is stored in the cache.
Dynamic queries: We should minimize the use of dynamic query as dynamic query gets recompiled every time parameter gets changed.
The stored procedure should be always called with fully qualified names database_name.schema_name.sp_name , this avoids the search of an execution plan in the procedure cache.
We should always use SET NOCOUNT ON which suppresses rows affected to improve the performance of the query. This is very critical in case SP is called frequently as not using the above syntax can load the network.
We should not use sp_ in stored procedure name as it is used for system procedure so it causes an extra trip to the master database to see if any system procedure matches with the user-defined procedure
We can use sp_executeSQL or KEEPFIXED PLAN to get away with recompilation of stored procedures even if we have parameterized dynamic queries.

KEEPFIXED PLAN code

We should be careful in choosing WHERE condition as it triggers index seek. Sometimes we might end up doing full table scans if were conditions not chosen carefully.

We should avoid using IN operators as it checks for NULL values as well. We should use EXISTS as it does not consider NULL values. The below query second will give faster results as compared to the first one.

Code

If not necessary we should avoid using DISTINCT and ORDER BY clause as it is an additional load on the database engine.

We should avoid CURSORS. Instead of CURSORS, we should use temp tables /table variables inside the loop to achieve the desired result set.

This is a frequently asked SQL Server interview question.

35. How many models of Locks being supported in the SQL server?

The locks which get applied on various resources of SQL server can be classified into below models :

Exclusive (X): This lock type, when forced, will guarantee that a page or row will be available only for the transaction that forced the lock, as long as the transaction is running. The X lock will be forced by the transaction when it needs to manipulate the page or row data by DML operations like inserting, modifying and deleting. This lock can be forced to a page or row just if there is no other shared or exclusive lock forced as of now on the resources. This ensures that only one exclusive lock can be applied and no other lock can be applied afterward till the time the previous lock gets removed.
Shared (S): This lock type, when forced, will save a page or row to be accessible just for reading, which implies that some other transaction will be not allowed to manipulate record till the time lock remains active. Nonetheless, a shared lock can be forced by multiple transactions concurrently over a similar page or row and in that manner, multiple transactions can share the capacity for data reading. A shared lock will permit writing tasks, yet no DDL changes will be permitted
Update (U): an update lock is like an exclusive lock however is intended to be more adaptable. An update lock can be forced on a record that as of now has a shared lock. In such a case, the update lock will force another shared lock on the intended When the transaction that holds the update lock is prepared to change the data, the update lock (U) will be changed to an exclusive lock (X). While the update lock can be forced on a record that has the shared lock but the shared lock can't be forced on the record that as of now has the update lock
Intent (I): The idea behind such a lock is to guarantee data modification to be executed appropriately by stopping another transaction to gain a lock on the next in the hierarchy object. Generally, when a transaction needs to obtain a lock on the row, it will gain an intent lock on a table, which is a higher chain of the intended object. By obtaining the intent lock, the transaction won't enable other transactions to procure the exclusive lock on that table.
Schema (Sch): This lock is applied on a table or index when we want to manipulate any changes in that resource. We can have only one Schema lock at a given point of time. This lock gets applied when we perform operations that depend on the schema of an object.
Bulk update (BU): This lock is required when bulk operations need to perform. At the point when a bulk update lock is gained, different transactions won't most likely access a table during the mass load execution. Be that as it may, a bulk update lock won't avoid another bulk update to be executed in parallel.

Locking hierarchy of database objects can be understood by the below diagram and lock is always obtained from top to bottom:

hierarchy of database

Below is the lock compatibility matrix between different modes of lock available:

39. Consider a situation where publisher database log file has been increasing and there is just a few MB available on disk. As an experienced professional how do you react to this situation? Remember no disk space available and also we can’t create a new log file on another drive

Essentially we have to identify the bottleneck which is filling the log file.

As a quick resolution check all possible solutions as below:

Resolve if there are any errors in the log reader agent/distribution agent
Fix if there are any connectivity issues either between publisher-distributor or distributor
Fix if there are any issues with I/O at any level
Check if there is any huge number of transactions pending from publisher
Check if there are any large number of VLFs (USE DBCC Loginfo)which slows the log reader agent work.
Check all database statistics are up-to-date at the distributor. Usually, we do switch off this “Auto Update Stats” by default.
To find and resolve these issues we can use “Replication Monitor”, “DBCC Commands”, “SQL Profiler”, “System Tables / SP / Function”.

If in case we can’t resolve just by providing a simple solution we have to shrink the transaction log file. Below are two methods.

To shrink the transaction log file:

Backup the log — So transactions in vlf’s are marked as inactive
Shrink the log file using DBCC SHRINKFILE – Inactive VLF’s would be removed
If you find no difference in size repeat the above steps 1 and 2

To truncate the transaction log file:

In any case, we are not able to provide the solution against the increasing logfile the final solution is to disable the replication, truncate the log and reinitialize the subscribers.

Disable replication jobs
Execute the SP_ReplDone procedure. It disables the replication and mark as “Replicate done” for all pending transactions at the publisher.
Backup the transaction log “WITH TRUNCATE” option.
Shrink the log file using “DBCC SHRINKFILE”
Flues the article cache using “sp_replflush”.
Go to distributor database and truncate the table MSRepl_Commands
Connect to replication monitor and reinitialize all subscriptions by generating a new snapshot.
Enable all replication-related jobs.

Want to Know More?

Full Name*

Email*

+91

Phone Number*

United States +1

India +91

Canada +1

Australia +61

Singapore +65

New Zealand +64

Germany +49

United Arab Emirates +971

Hong Kong +852

Ireland +353

Afghanistan +93

Aland Islands +358

Albania +355

Algeria +213

AmericanSamoa +1684

Andorra +376

Angola +244

Anguilla +1264

Antarctica +672

Antigua and Barbuda +1268

Argentina +54

Armenia +374

Aruba +297

Ascension Island +247

Austria +43

Azerbaijan +994

Bahamas +1242

Bahrain +973

Bangladesh +880

Barbados +1246

Belarus +375

Belgium +32

Belize +501

Benin +229

Bermuda +1441

Bhutan +975

Bolivia +591

Bosnia and Herzegovina +387

Botswana +267

Brazil +55

British Indian Ocean Territory +246

Brunei Darussalam +673

Bulgaria +359

Burkina Faso +226

Burundi +257

Cambodia +855

Cameroon +237

Cape Verde +238

Cayman Islands +1345

Central African Republic +236

Chad +235

Chile +56

China +86

Christmas Island +61

Cocos (Keeling) Islands +61

Colombia +57

Comoros +269

Congo +242

Cook Islands +682

Costa Rica +506

Cote d'Ivoire +225

Croatia +385

Cuba +53

Cyprus +357

Czech Republic +420

Democratic Republic of the Congo +243

Denmark +45

Djibouti +253

Dominica +1767

Dominican Republic +1849

Ecuador +593

Egypt +20

El Salvador +503

Equatorial Guinea +240

Eritrea +291

Estonia +372

Eswatini +268

Ethiopia +251

Falkland Islands (Malvinas) +500

Faroe Islands +298

Fiji +679

Finland +358

France +33

French Guiana +594

French Polynesia +689

Gabon +241

Gambia +220

Georgia +995

Ghana +233

Gibraltar +350

Greece +30

Greenland +299

Grenada +1473

Guadeloupe +590

Guam +1671

Guatemala +502

Guernsey +44

Guinea +224

Guinea-Bissau +245

Guyana +592

Haiti +509

Holy See (Vatican City State) +379

Honduras +504

Hungary +36

Iceland +354

Indonesia +62

Iran +98

Iraq +964

Isle of Man +44

Israel +972

Italy +39

Jamaica +1876

Japan +81

Jersey +44

Jordan +962

Kazakhstan +77

Kenya +254

Kiribati +686

Korea, Democratic People's Republic of Korea +850

Korea, Republic of South Korea +82

Kosovo +383

Kyrgyzstan +996

Laos +856

Latvia +371

Lebanon +961

Lesotho +266

Liberia +231

Libya +218

Liechtenstein +423

Lithuania +370

Luxembourg +352

Macau +853

Madagascar +261

Malawi +265

Malaysia +60

Maldives +960

Mali +223

Malta +356

Marshall Islands +692

Martinique +596

Mauritania +222

Mauritius +230

Mayotte +262

Mexico +52

Micronesia, Federated States of Micronesia +691

Moldova +373

Monaco +377

Mongolia +976

Montenegro +382

Montserrat +1664

Morocco +212

Mozambique +258

Myanmar +95

Namibia +264

Nauru +674

Nepal +977

Netherlands +31

New Caledonia +687

Nicaragua +505

Niger +227

Nigeria +234

Niue +683

Norfolk Island +672

North Macedonia +389

Northern Mariana Islands +1670

Norway +47

Oman +968

Pakistan +92

Palau +680

Palestine +970

Papua New Guinea +675

Paraguay +595

Peru +51

Philippines +63

Pitcairn +872

Poland +48

Portugal +351

Puerto Rico +1939

Qatar +974

Reunion +262

Romania +40

Russia +7

Rwanda +250

Saint Barthelemy +590

Saint Helena, Ascension and Tristan Da Cunha +290

Saint Kitts and Nevis +1869

Saint Lucia +1758

Saint Martin +590

Saint Pierre and Miquelon +508

Saint Vincent and the Grenadines +1784

Samoa +685

San Marino +378

Sao Tome and Principe +239

Saudi Arabia +966

Senegal +221

Serbia +381

Seychelles +248

Sierra Leone +232

Sint Maarten +1721

Slovakia +421

Slovenia +386

Solomon Islands +677

Somalia +252

South Africa +27

South Georgia and the South Sandwich Islands +500

South Sudan +211

Spain +34

Sri Lanka +94

Sudan +249

Suriname +597

Svalbard and Jan Mayen +47

Sweden +46

Switzerland +41

Syrian Arab Republic +963

Taiwan +886

Tajikistan +992

Tanzania, United Republic of Tanzania +255

Thailand +66

Timor-Leste +670

Togo +228

Tokelau +690

Tonga +676

Trinidad and Tobago +1868

Tunisia +216

Turkey +90

Turkmenistan +993

Turks and Caicos Islands +1649

Tuvalu +688

Uganda +256

Ukraine +380

United Kingdom +44

Uruguay +598

Uzbekistan +998

Vanuatu +678

Venezuela, Bolivarian Republic of Venezuela +58

Vietnam +84

Virgin Islands, British +1284

Virgin Islands, U.S. +1340

Wallis and Futuna +681

Yemen +967

Zambia +260

Zimbabwe +263

By Signing up, you agree to ourTerms & Conditionsand ourPrivacy and Policy

10% OFF

Coupon Code "LEVEL10"

Coupon Expires 13/04

Copy

SQL Server

Introduction

Beginner

Intermediate

Advanced

1. What is the difference between UNION and UNION ALL?

2. List all joins supported in SQL?

3. How many system databases we have in the SQL server?

4. what is Normalization and denormalization in SQL?

5. What is the difference between clustered and non clustered index?

6. What are Magic tables in the SQL server?

7. How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?

8. When to use SQL temp tables vs table variables?

9. What is self join, explain with example?

10. What are the Advantages of Using CTE?

11. Explain dirty read in SQL server?

12. What is the difference between Function and Stored procedure?

13. What is ACID property in a database?

14. What are the different normalization forms?

15. What is the primary key and foreign key?

16. What is UDF in SQL and how many different types exist for UDF?

17. List down the difference between DELETE and TRUNCATE command?

18. List down the difference between WHERE and HAVING clause.

19. You find SP is not applied to all the nodes across the cluster. How to apply SP only on the required nodes?

20. How to apply service pack on Active / Active cluster Nodes?

21. What are the main events and columns helpful in troubleshooting performance issues using a profiler?

22. What are the agents in replication?

23. Can we configure log shipping in the replicated database?

24. What are the best RAID levels to use with SQL Server?

25. How to monitor latency in replication?

1. Explain page, A fundamental unit of storage in SQL Server and different types of pages.

2. Explain the difference between Sequence vs Identity?

3. What are the dynamic management views and share any 5 that you are using on a regular basis?

4. Name all Available isolation levels in SQL Server?

5. Explain any 5 differences between CheckPoint & LazyWriter?

6. Name any 5 components can be installed with the SQL Server 2016 installation?

7. How many types of SQL Server database backups and Recovery Models are there?

8. What’s the impact if we enable SQL Server Trace Flag 3042 for compressed backups?

9. What is pseudo-simple Recovery Model?

11. What types of functionality does SQL Server Agent provide?

12. What are SQL Server Agent Fixed Database Roles with the definition?

13. What is the Guest user account? Explain in brief.

14. What are SQL Injection and its problems?

15. What are the primary differences between an index reorganization and an index rebuild?

16. Can the rebuilding cluster index rebuild the nonclustered index on the table?

17. List down best practices of tempDB configuration?

18. What are the different types of SQL Server replication?

19. What is the difference between Push and Pull Subscription?

20. How log shipping works?

21. Which are the basic system tables to track the information about the Log Shipping?

22. Explain Principal, Mirror And Witness Servers in DB Mirroring?

23. When I configure database mirroring I’m receiving the below error,“One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again.”

1. Explain Physical Database Architecture In Brief?

2. Explain One to One (1:1), One to Many (1:N) and Man to Many (N:N) mapping with an example?

3. Explain the Write-Ahead Transaction Log (WAL) protocol?

4. What is Row Versioning?

5. Is it possible to import data using T-SQL commands without using SQL Server Integration Services? If yes, please share commands?

6. We have one 3 Node cluster with 1 SQL 2005 and 1 SQL 2008 R2 instance. How many minimum installations are needed to patch all SQL instances in the cluster?

7. If a transaction starts before backup start time and transaction committed before the completion of the full backup. Please explain which data would be included in the full backup.

8. What is DCM & BCM Page?

10. Explain multi-server administration and its uses.

11. What are the SQL Server Agent Proxy and its sub-system?

12. How can SQL Server instances be hidden?

13. How can SQL Injection be stopped / Prevent?

14. How Powershell is useful for DBA? Give any 5 things that you have done using PowerShell?

15. Why we execute Update Statistics?

16. If a column is having,>50 % null then which index we should choose?

17. What is force parametrization?

18. What are different replication agents and what's their purpose?

19. How to add a new article in existing replication?

20. What are the common reasons which cause Log Shipping issues?

21. What is TUF file and it contains?

22. Explain DB Mirroring operating modes?

23. How to bring mirror DB online if Principle is down?

24. What are the benefits of Readable Secondary Replicas?

25. What is the use of RPC in the cluster?

26. What is an execution plan in SQL server?

27. How can SQL Server instances be hidden?

28. Is it possible to delete rows from views?

29. What is a hot and cold backup in SQL server?

23. When I configure database mirroring I’m receiving the below error,
“One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again.”