SQL Server Interview Questions – Set 9 (10 Questions )

Question 1 : What is the first Version of SQL Server?

Answer :

Microsoft SQL Server is a relational database management system developed by Microsoft. The First version of SQL Server was released in 1989.

In 1988 Microsoft joined Ashton-Tate and Sybase to create a variant of Sybase SQL Server for IBM OS/2 (then developed jointly with Microsoft), which was released the following year. This was the first version of Microsoft SQL Server, and served as Microsoft’s entry to the enterprise-level database market, competing against Oracle, IBM, and later, Sybase.

SQL Server 4.2 was shipped in 1992, bundled with OS/2 version 1.3, followed by version 4.21 for Windows NT, released alongside Windows NT 3.1. SQL Server 6.0 was the first version designed for NT, and did not include any direction from Sybase.

The Latest entry to the list is SQL Server 2016. Please find the history below.
(Ref – https://en.wikipedia.org/wiki/Microsoft_SQL_Server)

SQL Server History – Version Details

Question 2 : What is the maximum number of parameters we can declare in a Stored Procedure or Function in SQL Server 2016?

Answer :

The maximum number of parameters we can declare in a stored procedure or function is 2100. The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter.

Question 3 : What is a Latch?

Answer :

Latches are lightweight synchronization primitives that are used by the SQL Server engine to guarantee consistency of in-memory structures including; index, data pages and internal structures such as non-leaf pages in a B-Tree.

Latched are first introduced in SQL 7.0 for Data Pages. Well there are three types of latches in SQL Server.

• IO Latches
• Buffer Latches (BUF)
• Non-Buffer Latches (Non-BUF)

IO Latches & Buffer Latches (BUF)

SQL Server uses buffer latches to protect pages in the buffer pool and I/O latches to protect pages not yet loaded into the buffer pool. Whenever data is written to or read from a page in the SQL Server buffer pool a worker thread must first acquire a buffer latch for the page. There are various buffer latch types available for accessing pages in the buffer pool including exclusive latch (PAGELATCH_EX) and shared latch (PAGELATCH_SH).

Non-Buffer latches-

Latches are also used to protect access to internal memory structures other than buffer pool pages; these are known as Non-Buffer latches.

Latches are only controlled by SQL Server Engine. You can see the waiting times introduced with these types of latches in the DMV sys.dm_os_wait_stats in the following picture.

Latch Stats – Pawan Kumar Khowal

Question 4 : What is the difference between Latch and Lock?

Answer :

Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency. The following table compares latches to locks:

Question 5 : What are Spinlocks?

Answer :

Spinlocks are lightweight synchronization primitives which are used to protect access to data structures. Spinlocks are not unique to SQL Server. They are generally used when it is expected that access to a given data structure will need to be held for a very short period of time.

When a thread attempting to acquire a spinlock is unable to obtain access it executes in a loop periodically checking to determine if the resource is available instead of immediately yielding. After some period of time a thread waiting on a spinlock will yield before it is able to acquire the resource in order to allow other threads running on the same CPU to execute. This is known as a backoff.

On any busy high concurrency system, it is normal to see active contention on frequently accessed structures that are protected by spinlocks.

This is only considered problematic when the contention is such that it introduces significant CPU overhead.

Spinlock statistics are exposed by the sys.dm_os_spinlock_stats Dynamic Management View (DMV) within SQL Server. For example, this query yields the following output:

SYS.DM_OS_SPINLOCK_STATS

--


SELECT * FROM SYS.DM_OS_SPINLOCK_STATS
ORDER BY SPINS DESC

--

SpinLock Stats – Pawan Kumar Khowal

Question 6 : What is the difference between a Latch and a Spinlock?

Answer :

SQL Server utilizes spinlocks to protect access to some of its internal data structures. These are used within the engine to serialize access to certain data structures in a similar fashion to latches.

The main difference between a latch and a spinlock is the fact that spinlocks will spin (execute a loop) for a period of time checking for availability of a data structure while a thread attempting to acquire access to a structure protected by a latch will immediately yield if the resource is not available.

Yielding requires context switching of a thread off the CPU so that another thread can execute. This is a relatively expensive operation and for resources that are held for a very short duration it is more efficient overall to allow a thread to execute in a loop periodically checking for availability of the resource.

Question 7 : What is a SSD?

Answer :

A solid-state drive (SSD, also known as a solid-state disk although it contains neither an actual disk nor a drive motor to spin a disk) is a solid-state storage device that uses integrated circuit assemblies as memory to store data persistently.

SSDs don’t have any moving parts so when a read or write occurs I/O latency will be almost zero. The latency in a spinning drive comes from two things:
• Moving the disk head to the right track on the disk surface (known as the seek time)
• Waiting for the disk to spin to the right point on the track (known as the rotational latency)

This means that SSDs provide a big performance boost when there’s an I/O bottleneck.

Also note that SSD performance can start to degrade as the drive gets really full.

Question 8 : What you mean by CXPACKET?

Answer :

It is a common wait type. CXPACKET means there are queries running in parallel in our servers and we will always see CXPACKET waits for a parallel query. CXPACKET waits does NOT mean that we have a problematic parallelism. You have to dig deeper to determine whether you have an issue or not. The first thing one should look at is whether you expect parallelism for the query that’s using it. Shall explain this in detail in one of the upcoming posts.

Question 9 : Are views quicker than a SELECT Queries?

Answer :

View can be used for the following purposes:

• To focus, simplify, and customize the perception each user has of the database.
• As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
• To provide a backward compatible interface to emulate a table whose schema has changed.

Simple views are expanded inline & They DO NOT directly contribute to performance improvements. However, indexed views can dramatically improve performance. Indexed views have lot of restrictions, so be careful while using.

Question 10 :

--


SELECT ROUND( 109.87 , - 1 ) , ROUND( 104.99 , - 1 ) , ROUND( 150 , - 2 ) 

SELECT ROUND(748.58, -1)  , ROUND(748.58, -2), 	ROUND(751.58, -2)	

SELECT ROUND(444, -1) ,  ROUND(444, -2) , ROUND(748.58, -0), ROUND(748.58, -4)

--

Answer :

ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

ROUND returns a rounded numeric_expression, regardless of data type, when length is a negative number.

Round Function with -ve Values

That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with some more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com