Interview Q&A

Database is an organized collection of objects – schemas, tables, indexes, views, stored procedures and so on.

An instance (an instance of the database engine) is an operating system service which manages databases. Each instance manages system databases and one or more user databases. It handles applications’ requests to work with their databases.

https://msdn.microsoft.com/en-us/library/hh231298.aspx

https://msdn.microsoft.com/en-us/library/ms187875.aspx

NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.

https://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx

DML-Data Manipulation Language is a vocabulary of statements which allows to work (query, add, modify, remove) with data in a database.
SELECT, INSERT, UPDATE, DELETE are examples of DML statements.

https://msdn.microsoft.com/en-us/library/ff848766.aspx

DDL-Data Definition Language is a vocabulary of statements which allows to define data structures in database.
CREATE, ALTER, DROP, TRUNCATE TABLE are examples of DDL statements.

https://msdn.microsoft.com/en-us/library/ff848799.aspx

[Inner] join – displays only the rows that have a match in both joined tables.

TableA                                     TableB                             The result of INNER JOIN

                                                                            
Left [outer] join – displays all rows from the first table in the JOIN clause.
For matched rows there will be corresponding rows from the
second table. Unmatched rows in the right (second) table do not appear (there
will be NULLs instead).

TableA                                     TableB                             The result of LEFT JOIN

                                                                            

 

 

Right [outer] join – displays all rows from the second table in the JOIN clause.
For matched rows there will be corresponding rows from the
first table. Unmatched rows in the left(first) table do not appear(there
will be NULLs instead).

TableA                            TableB                            The result of RIGHT JOIN

                                                                  

 

Full [outer] join – displays all rows in all joined tables are included, whether they are
matched or not. For rows which are matched to join criteria, there
will be corresponding row from other table. For rows which do not match
there will be NULLs instead.

TableA                                 TableB                           The result of FULL JOIN

                                                                       

 

 

Cross join – displays each possible pairing of rows from the two tables in other words
displays each row from the first table combined with each row from the second table.

TableA                                     TableB                             The result of FULL JOIN

                                                                           

 

https://msdn.microsoft.com/en-us/library/zt8wzxy4.aspx

Clustered table is a table which has a clustered index. The data rows in these tables
are stored in an ordered way based on clustered index key values.

Heap is a table which does not clustered index. Data rows in these tables are stored
in an unordered structure.

https://msdn.microsoft.com/en-us/library/ms190457.aspx

When clustered index is created on table or view, the data rows is sorted and physically
stored based on index’s key values. So, heap becomes a clustered table. Only one clustered
index can be created for table (because data rows can be sorted in only one order).

Creating a nonclustered index does not change table’s data row order. It is a
separate structure which stores nonclustered index key values sorted by index’s
key values and a pointer to the data row that contains the key value. In addition,
when table is a clustered table, the pointer is clustered index key. In case of heap
the pointer is row’s physical identifier. More than one nonclustered indexes can be
created for table (in SQL Server 2016 maximum 999 nonclustered indexes per table).
https://msdn.microsoft.com/en-us/library/ms190457.aspx

View is a virtual table which is made by predefined query that retrieve data from one or more tables.
Like tables, views have columns and data rows, however unlike tables, views (if they are not indexed) do not physically exist as a stored set of data values in a database.
Using views we can narrow the necessary data for specific users to make it more simple and understandable. Views also can be useful for summarizing data from one or more tables and continuously using it without writing the query every time. In addition, using views we can enhance security by granting users access to data without granting access to base tables.
https://msdn.microsoft.com/en-us/library/ms190174.aspx

A schema is a separate entity within the database, where database objects are created and contained. We can create a new schema by using CREATE SCHEMA statement. When creating a database object we should mention its schema name. If schema name is not mentioned, object is created within default schema. Schemas can be protected by appropriate permissions and in this way we regulate data access and prevent its objects from unwanted access. Also, we can use schemas to combine logically related entities which will facilitate database management process.

https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

A transaction is a series of operations which act as a single unit of work.
It means that all changes made by statements included in transaction are either
wholly committed and become a permanent part of the database or wholly rolled back
and all changes made within transaction are erased.

BEGIN TRANSACTION, COMMIT [TRANSACTION], ROLLBACK [TRANSACTION], SAVE TRANSACTION, etc are transaction statements.

https://msdn.microsoft.com/en-us/library/ms978457.aspx

https://msdn.microsoft.com/en-us/library/ms174377.aspx

Nested transaction is a transaction which starts inside the scope of an existing transaction.

https://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx

@@TRANCOUNT function returns the number of opened transactions on the current connection.
In other words @@TRANCOUNT function shows the current transaction’s nesting level.
Each BEGIN TRANSACTION statement increments @@TRANCOUNT by 1 and COMMIT statement decrements it by 1.
ROLLBACK statement sets @@TRANCOUNT to 0 (except for cases when transaction is rolled back to savepoint, which does not affect @@TRANCOUNT).
So, if ROLLBACK is issued, all nested transactions are rolled back.
If the value of @@TRANCOUNT is 0, you are not in a transaction.

https://msdn.microsoft.com/en-us/library/ms187967.aspx

A dirty read occurs when a transaction reads uncommitted data. Transaction with an isolation level READ UNCOMMITTED allows dirty reads. Also we can allow dirty reads using NOLOCK or READUNCOMMITTED table hints. Suppose one transaction inserts a huge amount of data into the TestTable table in one transaction which takes a lot of time and we need to periodically check how many rows have been inserted at that point of time. We can open another query and
run the following statement SELECT COUNT(*) FROM TestTable WITH (NOLOCK),
which returns TestTable’s rows count by the time it runs. Without allowing
dirty reads, the transaction which retrieves rows count, waits for the transaction  inserting
data to complete and then returns the rows’ final count.

https://msdn.microsoft.com/en-us/library/ms709374(v=vs.85).aspx

https://www.mssqltips.com/sqlservertip/4468/compare-sql-server-nolock-and-readpast-table-hints/

CHAR is a fixed-length data type. For CHAR(n) the storage size is n bytes.
VARCHAR is a Variable-length data type. For VARCHAR data type the storage size is the actual length of the data entered + 2 bytes. It is recommended to use CHAR when the sizes of the column data entries are consistent. VHARCHAR is recommended when the column data entries have variable sizes.

https://msdn.microsoft.com/en-us/library/ms176089.aspx

CHAR is a  fixed-length non-Unicode string type. For CHAR(n) the storage size is n bytes.
n defines the string length and must be a value from 1 through 8000.

NCHAR a is fixed-length Unicode string type. For NCHAR(n) the storage size is 2*n bytes (however
depending on the string, the storage size of n bytes can be less than the value specified for n).
n defines the string length and must be a value from 1 through 4000.

VARCHAR is a variable-length non-Unicode string type. For VARCHAR data type the storage size is the actual length of
the data entered + 2 bytes.n defines the string length and must be a value from 1 through 8000.

NVARCHAR is a variable-length Unicode string type. For NVARCHAR data type the storage size is two times the actual length of data entered + 2 bytes. n defines the string length and must be a value from 1 through 4000.

https://msdn.microsoft.com/en-us/library/ms176089.aspx

https://msdn.microsoft.com/en-us/library/ms186939.aspx

 

SQL Server user-defined functions are routines that accept parameters, perform some actions and return a result. The latest (latter) can either be a single scalar value or a result set.

Advantages: allowing modular programming and reuse of code,
faster execution,
reducing network traffic

Limitations: cannot modify the database state,
cannot contain an OUTPUT INTO clause that has a table as its target,
can not return multiple result sets,
does not support TRY…CATCH, @ERROR, RAISERROR,
cannot call a stored procedure (however they can call an extended stored procedure),
using SET statements, dynamic SQL or temp tables and FOR XML clause are not allowed,
their nesting level limit is 32,
some Service Broker statements cannot be included in their definition

Usage: can be called in Transact-SQL statements (SELECT, in IF-s condition and so on),
in applications which directly call the function,
in the definition of another user-defined function,
inside a view,
to define a column in a table,
to define a CHECK constraint on a column,
inline functions can be used as a filter predicate for a security policy.

https://msdn.microsoft.com/en-us/library/ms191007.aspx

https://msdn.microsoft.com/en-us/library/ms191320.aspx

https://msdn.microsoft.com/en-us/library/ms186755.aspx

 

T-SQL stored procedures are routines written in T-SQL code and stored in a database.
The code in stored procedure can include DML as well as DDL statements, transactions,
TRY..CATCH and so on. They can accept values by input parameters and return results
by output parameters as well as they can return an integer value – return code to
indicate the execution status of a procedure. Also procedures can return one or
more result sets.
Advantages: Reducing network traffic,
enhancing security,
allowing to reuse code,
facilitating code maintenance and encapsulating it,
improving performance
There are also other types of stored procedures- CLR, extended.

https://msdn.microsoft.com/en-us/library/ms190782.aspx

https://msdn.microsoft.com/en-us/library/ms188655.aspx

A trigger is a special kind of stored procedure that is automatically executed when an specific event takes place. There are three types of triggers-DML trigger, DDL trigger and Logon trigger. DML trigger is executed as the associated DML event occurs, DDL trigger – when the associated DDL statement occur and Logon trigger fires when logon event occurs.
Unlike stored procedures, triggers cannot be executed manually.
DML trigger can be an AFTER trigger which means that trigger is executed after DML statement or it can be an INSTEAD OF trigger meaning that DML trigger is executed instead of DML statement.
Triggers can be used for enforcing database integrity, for auditing and so on.

https://msdn.microsoft.com/en-us/library/ms189799.aspx

https://msdn.microsoft.com/en-us/library/ms178110.aspx

https://msdn.microsoft.com/en-us/library/ms175941.aspx

A recovery model is a database property that defines how transactions are logged,
the requirements for transaction log backup and possible restore operations.
The recovery models are the following:
Simple – Automatically cleans log space to optimize log space usage
No log backups are possible
It is not possible to recover changes after last Full backup
Bulk logged – Uses minimal logging for most bulk operations in this way
optimizing performance for bulk copy operations
Log backups are required
It is possible to recover to the end of any backup,
but point-in-time recovery is not supported.
Full – Log backups are required.
It is possible to do point-in-time recovery (in case if all backups are complete up to that point in time).

https://msdn.microsoft.com/en-us/library/ms189275.aspx

Firstly, we need to restore full backup WITH NORECOVERY, then differential backup also WITH
NORECOVERY, after that restore the first log backup again WITH NORECOVERY and finally, restore
the second-latest transaction log backup WITH RECOVERY.

https://msdn.microsoft.com/en-us/library/ms175477.aspx

https://msdn.microsoft.com/en-us/library/ms179451.aspx

It is possible to have 50 instances per computer *.
We can have only one default instance on the computer and it is not
necessary to have a default instance to be able to install named instances.
*-For SQL Server 2008, 2008 R2, 2012, 2014, 2016 50 instances per computer for all editions,
for SQL Server 2005 – 50 instances for Enterprise and Developer editions, and 16 for Standard, Workgroup and Express Editions)

https://msdn.microsoft.com/en-us/library/ms143432(v=sql.130).aspx

https://technet.microsoft.com/en-us/library/ms143432(SQL.100).aspx

https://technet.microsoft.com/en-us/library/ms143531(v=sql.90).aspx

There are two authentication modes in SQL Server – Windows and mixed authentication modes.
Windows authentication mode – default, integrated with Windows
Mixed authentication mode – authentication is possible both by Windows and by SQL Server.

Windows authentication mode is more secure and Microsoft recommends to use this mode wherever possible.
SQL Server authentication mode is less secure because when SQL Server logins are used, login names and passwords are passed across the network, which is vulnerable. Also Windows has additional password policies which enhance security.
https://msdn.microsoft.com/en-us/library/bb669066(v=vs.110).aspx

master, model, msdb, tempdb and resource databases
https://msdn.microsoft.com/en-us/library/ms178028.aspx

 

SQL Server databases have two types of data files – Primary and Secondary.
Each database has one primary data file (recommended, but not necessarily having the extension .mdf).
Secondary data files are optional (recommended, but not necessarily having the extension .ndf).

Each data file is logically divided into pages, that are  the minimum data for disk I/O operations.
SQL Server reads or writes the whole data pages. The page size is fixed – 8KB.

https://msdn.microsoft.com/en-us/library/ms189563.aspx

Yes SQL Server database can have more than one log file, but it is not possible to
use them in parallel. SQL Server uses only one log file until it’s full. So, there cannot
be performance gain. Usually the additional log files are not needed, however in
some cases they can be needed, for example for troubleshooting the database when the first log file
becomes full.
https://www.mssqltips.com/sqlservertip/4083/remove-unnecessary-sql-server-transaction-log-files/

A CHECKPOINT writes dirty pages (modified pages in memory) and transaction log data from memory
to physical files (data and log files correspondingly) and also records information about the transaction log.
There are four types of CHECKPOINTS: automatic, indirect, manual, and internal.
CHECKPOINTS occur depending on different factors.
For example automatic checkpoints occur depending on recovery interval of server configuration option.
Causes for internal checkpoint can be a database backup, adding or removing database files using ALTER DATABASE statement, stopping the SQL Server service, etc. .
https://msdn.microsoft.com/en-us/library/ms189573.aspx

A deadlock occurs when two or more transactions are trying to lock resource on which other transaction(s) have already placed locks and any of these transactions waits for others to release locks to be able to lock a resource. In this way transactions permanently block each other. The SQL Server Database Engine automatically detects deadlocks and terminates one of these transactions with an error to break the deadlock. Deadlocks can be monitored by enabling 1204 or 1222 trace flags and using captured information in SQl Server error log.
Also deadlocks can be monitored by using deadlock graph event in SQL Server Profiler.
https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

Yes, it is possible for both cases.

https://www.mssqltips.com/sqlservertip/4110/solve-common-sql-server-restore-issues/

We can start transaction log backups, while full backup is running and in case of failure
we can use TestDB_Full1.bak backup and transaction log backups to restore the database.

https://www.mssqltips.com/sqlservertip/4118/sql-server-backups-and-transaction-log-questions/

It is not possible to use differential backups with a COPY_ONLY Full backup for restoring,
so the answer for the first question is NO.

However we can use transaction log backups taken after COPY_ONLY Full backup
with the latter for restoring the database, so the answer for the second question is – YES.
https://www.mssqltips.com/sqlservertip/4110/solve-common-sql-server-restore-issues/

No, full backup does not truncate log file. Transaction log backup truncates log file,
however there are some exceptions when truncation does not occur immediately after log backup.

https://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx

https://www.mssqltips.com/sqlservertip/4118/sql-server-backups-and-transaction-log-questions/

Yes, it is possible.

https://msdn.microsoft.com/en-us/library/ms191458.aspx

Both are aimed to fix index fragmentation.
In case of rebuilding, index is dropped and re-created, also the new value for fill factor can be set.
We can rebuild index either online or offline.
Reorganizing does not drop and recreate an index. Its defragments the leaf level of
index and compacts the index pages according to the existing fill factor value (this value cannot be
changed in case of reorganizing, moreover other index options also cannot be specified). Reorganizing uses minimal resources and can be executed only online.

https://msdn.microsoft.com/en-us/library/ms189858.aspx

The fill-factor value defines the percentage of space on each leaf-level page to be filled with data
at the time of index creation or rebuilding. Remaining free space is for future usage.

As mentioned before, fill-factor value is being set when index is created or rebuilt. So, after index
creation or rebuilding the free space will be decreased depending on the index expansion.

Fill-factor allows to reduce page splits by reserving free space for index growth. Due to correctly
chosen fill-factor value INSERT statements on underlying table became faster. The UPDATE statements, that cause the size of the rows to grow, also become faster. However setting the value of fill-factor different than 100(0) affects the performance of SELECT statements. In addition, the index with nonzero fill-factor value, requires more storage space.

When all new data is added to the end of the table, the nonzero fill-factor value does not increase write performance, because the empty space in the index pages will not be filled. The typical example of that is an index on IDENTITY column. In this case the inserted key values are always increasing, so index rows are logically added to the end of the index.

https://msdn.microsoft.com/en-us/library/ms177459(v=sql.105).aspx

We can do it by creating SQL Server Agent jobs. It is also possible to create maintenance plans which are also created corresponding SQL Server Agent jobs.
However maintenance tasks have the same limitations and in some cases they are not that flexible (for example we cannot check index fragmentation level before rebuilding or reorganizing it,
mirrored backups are also not supported and so on) than the manually written code.
https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent

https://msdn.microsoft.com/en-us/library/ms187658.aspx

https://msdn.microsoft.com/en-us/library/ms187658.aspx

 

Yes, the principal database in a mirroring session can also serve as the primary database in a log shipping, or vice versa.
It it also possible to continue log shipping after failover by configuring mirror server as a primary server for log shipping with the same configuration as the principal database.

https://msdn.microsoft.com/en-us/library/ms187016.aspx

Transactional replication, Merge replication, Snapshot replication.
Transactional replication is implemented by the following SQL Server agents: Snapshot Agent, Log Reader Agent, and Distribution Agent.

https://msdn.microsoft.com/en-us/library/ms151176.aspx

https://msdn.microsoft.com/en-us/library/ms151176.aspx#HowWorks

A Failover Cluster Instance (FCI) is a single instance of SQL Server installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. FCI consists of a set of physical servers (nodes) with the same hardware and software configuration.
On the network, an FCI appears to be an instance of SQL Server running on a single computer. In case when the current node becomes unavailable FCI provides failover from one WSFC node to another.
Failover Cluster Instance (FCI) consists of a set of physical servers (nodes) that contain similar hardware configuration as well as identical software configuration that includes operating
system version and patch level, and SQL Server version, patch level, components, and instance name.
Identical software configuration is necessary to ensure that the FCI can be fully functional as it fails over between the nodes.

FCI protects from hardware failures, operating system failures, application or service failures.
Shared storage failure is not protected by FCI (shared storage can be the single point of failure).

https://msdn.microsoft.com/en-us/library/ms189134.aspx

Yes, it is.

https://www.mssqltips.com/sqlservertip/4182/setting-a-fixed-amount-of-memory-for-sql-server/

Yes, it is possible by using linked servers.

https://msdn.microsoft.com/en-us/library/ms188279.aspx

 

https://www.mssqltips.com/sqlservertip/4155/executing-large-scripts-in-sql-server-management-studio-with-insufficient-memory-failures/

sp_who, sp_lock, sp_spaceused, sp_monitor

https://msdn.microsoft.com/en-us/library/ms179428.aspx

There is no difference, they are absolutely the same.
Both are fixed precision and scale numbers. For both, the minimum precision is 1 and
the maximum is 38 (18 – by default). Both data types cover the range from -10^38+1 to 10^38-1.
Only their names are different and nothing more.

https://msdn.microsoft.com/en-us/library/ms187746.aspx

https://www.mssqltips.com/sqlservertip/4545/synchronizing-sql-server-data-using-rowversion/

https://www.mssqltips.com/sqlservertip/4270/difference-between-sql-server-unique-indexes-and-unique-constraints/

https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms177399(v=sql.105).aspx

https://www.mssqltips.com/sqlservertip/4031/sql-server-global-temporary-table-visibility/

https://www.mssqltips.com/sqlservertip/4061/exploring-sql-server-2014-table-variables/

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Local and global temporary tables as well as table variables are stored in tempdb database.
However, CTEs are not stored as an object, they exist only for the duration of the query,
in other words they are stored in memory (RAM).

https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms177399(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Yes, it is, but there are some limitations:

https://msdn.microsoft.com/en-us/library/ms187956.aspx

https://www.mssqltips.com/sqlservertip/4468/compare-sql-server-nolock-and-readpast-table-hints/

https://www.mssqltips.com/sqlservertip/4438/compare-repeatable-read-and-serializable-sql-server-transaction-isolation-levels/

https://www.mssqltips.com/sqlservertip/4120/comparison-of-sql-server-serializable-and-snapshot-isolation-levels/

Yes, it is possible with some exceptions.
Yes, TRUNCATE is a DDL command which can be included in transaction and truncated data will be rolled back in case of ROLLBACK.

https://www.mssqltips.com/sqlservertip/4591/ddl-commands-in-transactions-in-sql-server-versus-oracle/

As we know UNIQUE index allows only one NULL in a column, so we cannot create UNIQUE index in this case.
We can create UNIQUE FILTERED index instead:

CREATE UNIQUE NONCLUSTERED INDEX <index_name>
ON <table name>(<column_name>)
WHERE <column_name> IS NOT NULL
GO

https://msdn.microsoft.com/en-us/library/cc280372.aspx

 

It is possible to create non-clustered index which besides key columns also includes non key columns (usually all columns in the query).
In this case query optimizer can locate all the column values within the index, therefore table or clustered index data is not accessed that improves query performance.

https://msdn.microsoft.com/en-us/library/ms190806(v=sql.105).aspx

Yes it is possible.
Object name resolution errors, syntax errors that prevent a batch from running, warnings or informational messages with severity of 10 or lower and so on.

https://msdn.microsoft.com/en-us/library/ms175976.aspx

We need to set set XACT_ABORT to ON in our procedure.

https://www.mssqltips.com/sqlservertip/4018/sql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-commit-statements/

No, it is not possible to call stored procedure inside user-defined function.
Yes, user-defined functions can be nested up to 32 levels.

https://msdn.microsoft.com/en-us/library/ms186755.aspx

You can use SQL Server Profiler to catch procedure’s call and debug it in SQL Server Management Studio.

https://msdn.microsoft.com/en-us/library/ms181091.aspx

https://msdn.microsoft.com/en-us/library/hh272701(v=vs.103).aspx

https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

https://blogs.msdn.microsoft.com/craigfr/2006/06/26/scans-vs-seeks/

https://technet.microsoft.com/en-us/library/ms190400(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms190400(v=sql.105).aspx

We can do it either by sp_executesql stored procedure or EXEC statement.
sp_executesql stored procedure considered more secure because we can enhance security by using parametrization.

https://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

https://docs.microsoft.com/en-us/sql/odbc/reference/dynamic-sql