SQL Server Interview Questions – Set 11 (10 Questions )

Question 1 : How do we get time difference between two date time variables in SQL Server?

Answer :

Query to get time difference between 2 date time variables

--


DECLARE @Start DATETIME = '6/24/2016 2:21'	
DECLARE @End DATETIME = '6/24/2016 12:02'
SELECT CONVERT(VARCHAR, @End - @Start, 108)  [ExactTime - Hr : Mins : SS]
GO

DECLARE @Start DATETIME = '6/18/2016 7:30'	
DECLARE @End DATETIME = '6/18/2016 16:37'
SELECT CAST((@End - @Start) AS TIME)  
[ExactTime - Hr : Mins : SS]
GO


--

Question 2 : How to get Time Part only from DateTime variable in Sql Server?

Answer :

https://msbiskills.com/2016/06/26/sql-puzzle-how-to-get-date-part-time-part-only-from-datetime-in-sql-server-multiple-methods/

Question 3 : Can you write a query to get file details of a database?

Answer :

Query to get Data and log file details of a database

--


USE TEMPDB
EXEC sp_helpfile
GO


USE Master
EXEC sp_helpfile
GO


--

TempDB Files (Data Files & Log Files)

Question 4 : Why index REBUILD does not reduce index fragmentation?

Answer :

DBAs use ALTER INDEX REBUILD to remove index fragmentation. In some cases, REBUILD does not remove this fragmentation. Now why it does not reduce index fragmentation. This case will appear when in the indexes are very small.

Now if an index is very small (that is less than 8 pages) it will use mixed extents. Therefore, it’ll appear as if there is still fragmentation remaining, as the host extent will contain pages from multiple indexes.

Because of this, and also the fact that in such a small index that fragmentation is typically negligible, you really should only be rebuilding indexes with a certain page threshold. It is best practices to rebuild fragmented indexes that are a minimum of 1000 pages.

Question 5 : How to move the TempDB to new drive when the drive is full?

Answer :

You will get below error when the tempDB is full.

The LOG FILE FOR DATABASE ‘tempdb’ IS FULL. Back up the TRANSACTION LOG FOR the DATABASE TO free Up SOME LOG SPACE

Script to move your temp database to new drive.

--


USE tempdb
GO
sp_helpfile
GO


USE MASTER
GO

ALTER DATABASE TempDB MODIFY FILE 
(NAME = tempdev, FILENAME = 'd:\Pawantempdb.mdf')
GO

ALTER DATABASE TempDB MODIFY FILE 
(NAME = templog, FILENAME = 'e:\Pawantemplog.ldf')
GO



--

When you execute above script you will see the message that query has run successfully. However, there will be no changes in how the temp database is configured. The location of the TempDB changed when SQL Server will restart services again. You will be able to see the changes only after the services are restarted.

Moving temp database to another file group which is on a different physical drive helps to improve database disk read.

Question 6 : How many tempDB data files you should have?

Answer :

Well it is a very difficult question to answer. People say different things about number of data files you should have in your tempDB.

As per Bod Ward’s (CTO-CSS Microsoft) Session in 2011.

a. If you have less than 8 Cores, then number of data files in tempDB should be equal to the number of cores.

b. If you have greater than 8 cores, then start with 8 data files in tempDB and if you still seeing tempdb contention than increase the data files in a block of 4.

You should not have too many data files. If you have too many data files and you have large memory spills that come out of tempDB then the performance can be slowed down.

Note – All the data files should be of the same size. Otherwise the largest size one will auto grow.

In SQL 2016 tempDB has following data files with same size.

TempDb Data & log Files – Pawan Khowal

Question 7 : How do you get count of logical cpu’s using SQL?

Answer :

--


SELECT scheduler_id, cpu_id, STATUS, is_online 
FROM sys.dm_os_schedulers 
WHERE status = 'VISIBLE ONLINE'
GO

SELECT cpu_count
FROM sys.dm_os_sys_info
GO


--

Question 8 : How to store pdf file in SQL Server?

Answer :

For this there are 2 options-

1. Create a column as type ‘blob’ in a table. Read the content of the file and save in ‘blob’ type column in a table.

2. Store them in a folder and establish the pointer to link them in the database.

Question 9 : What is the purpose of OPENXML clause SQL server stored procedure?

Answer :

Answer : OPENXML parses the XML data in SQL Server in an efficient manner. Its primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML. The path of the XML element needs to be specified by using ‘xpath’.

Example-

SampleXMLInWordFile

--

--Table Existance

IF OBJECT_ID ( N'[dbo].[Resources]' ) > 0
	DROP TABLE [dbo].[Resources]
GO


--Create Table Script

CREATE TABLE Resources
(
	[Data] XML	
)
GO

--Insert Data Script

INSERT INTO Resources
SELECT * FROM OPENROWSET (BULK 'E:\SampleXML.xml', SINGLE_BLOB) AS data
GO

DECLARE @XML AS XML, @hDoc AS INT

SELECT @XML = [Data] FROM Resources

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT 
		*
FROM 
		OPENXML(@hDoc, '/resource-data/record')
WITH 
(
		 EmployeeId INT 'EmployeeID'
		,DateofJoining VARCHAR(100) 'DOJ'
		,Skills VARCHAR(200) 'Skills'
		,DomainExperience VARCHAR(100) 'DomainExperience'
		,Rating VARCHAR(2) 'Rating'
		,CommunicationsRating VARCHAR(1) 'CommunicationsRating'
		,GP VARCHAR(1) 'NAGP'
		,YearsOfExperience INT 'YearsOfExperience'
		,CurrentRole VARCHAR(100) 'CurrentRole'
		,PreviousCustomerExperience VARCHAR(100)   'PreviousCustomerExperience'
		,AvailableFromDate VARCHAR(10) 'AvailableFromDate'
)

EXEC sp_xml_removedocument @hDoc

--

Question 10 : What is the output of query 1 and query 2?

--

CREATE TABLE CrossJoin1
(
	a INT IDENTITY(1,1)
)
GO

INSERT INTO CrossJoin1 DEFAULT VALUES
GO 10

CREATE TABLE CrossJoin2
(
	a INT IDENTITY(1,1)
)
GO

-------------- QUERY 1 ----------------------

SELECT 
	CrossJoin1.*
FROM 
	CrossJoin1,CrossJoin2


-------------- QUERY 2 ----------------------

SELECT 
	CrossJoin1.*
FROM 
	CrossJoin1 CROSS JOIN CrossJoin2


--

Answer :

Both the queries will return 0 rows.

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

Pawan Kumar Khowal

MSBISKILLS.COM

SQL Server Interview Questions – Set 7 (10 Questions )

Question 1 : What are Ghost Rows in an Index in SQL Server?

Answer :

At leaf level of an index we have actual data rows. Now when these rows are deleted, they’re marked as ghost records.
This means that the row stays on the page but a bit is changed in the row header to indicate that the row is really a ghost.

The page header also reflects the number of ghost records on a page.

When user fires the delete command, SQL returns to the user much faster because it does not have to wait for the records to be deleted physically. Internally they are marked as “ghosted”.

Ghost records are present only in the index leaf nodes.
The ghost records can be cleaned up in 3 ways:
• If a record of the same key value as the deleted record is inserted
• If the page needs to be split, the ghost records will be handled
• The Ghost clean-up task (scheduled to run once every 5 seconds). It asynchronously removes them from the leaf level of the index. This same thread carries out the automatic shrinking of databases if you have that option set.

The Ghost cleanup process divides the “ghost pages” into 2 categories:
• Hot Pages (frequently visited by scanning processes)
• Cold Pages

The Ghost cleanup thread is able to retrieve the list of Cold pages from the DBTABLE for that database, or the PFS Page for that interval. The cleanup task cleans up a maximum of 10 ghost pages at a time. Also, while searching for the ghost pages, if it covers 10 PFS Pages, it yields.
As far as hot ghost pages are concerned, the ghost cleanup strives to keep the number of such pages below a specified limit. Also, if the thread cleans up 10 hot ghost pages, it yields. However, if the number of hot ghost pages is above the specified (hard-coded) limit, the task runs non-stop till the count comes down below the threshold value.
If there is no CPU usage on the system, the Ghost cleanup task runs till there are no more ghost pages to clean up.
One of the most common (and quickest) resolutions for a ghost records issue is to restart SQL Server.

Question 2 : How temp DB is created or algorithm used to create tempDB?

Answer :

Creation / Birth of tempDB uses below algorithm-

1. First Master DB is open
2. Second Open Model DB
3. TempDB is created using Model DB properties
 Engine will first lock model DB and tempDB
 Connections are allowed at this point to the engine, since master DB is already gone.
 User DBs are started to recover
 Create the primary DB file for tempDB based on the properties of master
 Copy extents from model to primary DB file including objects
 Fix up primary DB file for tempDB
 Create the primary transaction log file
 Create and attach other files
 Notify that tempDB is ready
4. If the above process fails, we will shut down the server

Question 3 : What is Spatial Index?

Answer :

SQL Server’s spatial data type allows us to store spatial objects and make them available to an application.
SQL Server supports two spatial data types:

Geometry: Stores the X and Y coordinates that represents lines, points, or polygons.
Geography: Stores the latitude and longitude coordinates that represent lines, points, or polygons.

SQL Server uses a B+ tree structure, which is a variation of the B-tree index. B-tree is nothing but a data structure that keeps data sorted to support search operations, sequential access, and data modifications such as inserts and deletes. SQL Server spatial indexes are built on top of the B+ tree structure, which allows the indexes to use that structure and its access methods. The spatial indexes also use the fundamental principles of XML indexing. XML indexing was introduced in SQL Server 2005 and supports two basic types of indexes: primary and secondary. The primary XML index is a B+ tree that essentially contains one row for each node in the XML instance.
So how does SQL Server implement the spatial index? As already mentioned, SQL Server starts with a B+ tree structure, which organizes data into a linear fashion. Because of this, the indexes must have a way to represent the two-dimensional spatial information as linear data. For this, SQL Server uses a process referred to as the hierarchical uniform decomposition of space. When the index is created, the database engine decomposes, or refactors, the space into a collection of axes aligned along a four-level grid hierarchy.

https://msdn.microsoft.com/en-IN/library/bb895265.aspx

Question 4 : What is the default recursion level for a CTE in SQL SERVER?

Answer :

1. The default maximum recursion depth of a CTE is 100 and if your query go beyond that an error occurs.

2. One can control the maximum recursion depth using the MAXRECURSION query hint (a value between 1 and 32767) and if your query go beyond that an error occurs.

3. If one wants to go ahead without limit can use the MAXRECURSION query hint (a value of 0). In this case we may crash our server due to an infinite loop. So your code should have manually control the execution otherwise it will go into the infinite loop.

Example

--

WITH Looper AS
(
    SELECT 0 AS Levels

    UNION ALL

    SELECT (x.Levels + 1) AS RecursionLevel
    FROM Looper x
    WHERE (x.Levels + 1) <= 200
)
SELECT * FROM Looper
OPTION (MAXRECURSION 200)



--

Question 5 : Can you explain me Halloween Problem?

Answer :

As per Wikipedia (https://en.wikipedia.org/wiki/Halloween_Problem ) Halloween Problem refers to a phenomenon in databases in which an update operation causes a change in the physical location of a row, potentially allowing the row to be visited more than once during the operation. This could even cause an infinite loop in some cases where updates continually place the updated record ahead of the scan performing the update operation.

The potential for this database error was first discovered by Don Chamberlin, Pat Selinger, and Morton Astrahan in 1976, on Halloween day while working on a query that was supposed to give a ten percent raise to every employee who earned less than $25,000. This query would run successfully, with no errors, but when finished all the employees in the database earned at least $25,000, because it kept giving them a raise until they reached that level. The expectation was that the query would iterate over each of the employee records with a salary less than $25,000 precisely once. In fact, because even updated records were visible to the query execution engine and so continued to match the query’s criteria, salary records were matching multiple times and each time being given a 10% raise until they were all greater than $25,000.

SQL Optimizer uses a Spool Operator to overcome Halloween protection.

Example

--

CREATE TABLE testHalloween
(
	 a SMALLINT PRIMARY KEY
	,b INT
	,c INT
)
GO

INSERT INTO testHalloween VALUES
(1,1,1),
(2,2,2),
(3,3,3)
GO

CREATE NONCLUSTERED INDEX Ix_c ON testHalloween(c)
GO

--Optimizer will add a Table Spool for Halloween Protection

UPDATE testHalloween 
SET c = c * 2
FROM testHalloween WITH ( INDEX ( Ix_c ) )
WHERE c < 3
GO


--

Question 6 : Is it true that stored procedures compiled when they are created?

Answer :

No, it is NOT true that stored procedures compiled when they are created.

Stored procedure gets compiled when they get executed on the first RUN. When the user fires an EXEC or EXECUTE proc command first time, then the stored procedure gets complied.

You can easily check this using following steps-

Create an SP and check if you get anything in SQL Profiler. In the second step execute the stored procedure and then check the profiler. The events you should be checking are given below-
1. SP:CacheHit
2. SP:CacheInsert
3. SP:CacheMiss
4. SP:CacheRemove

Question 7 : Can we create a Trigger on a View?

Answer :

Yes, we can create a trigger on View. View can be simple or updateable.

We can only create “Instead of Trigger” on a view separately for each of the INSERT, UPDATE, DELETE operation.

INSTEAD OF triggers can be defined on either tables or views; however, INSTEAD OF triggers are most useful for extending the types of updates a view can support. For example, INSTEAD OF triggers can provide the logic to modify multiple base tables through a view or to modify base tables that contain the following columns:-

1. timestamp data type
2. Computed columns
3. Identity columns

Question 8 : Why Truncate Command is faster than Delete Command if we are removing all data from the table?

Answer :

DELETE and TRUNCATE statements can be used to delete all data from the table.
DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted.

TRUNCATE is also a logged operation (Fully & Efficiently logged), but in a different way. A TRUNCATE TABLE operation does a wholesale delete of all data in the table. The individual records are not deleted one-by-one, instead the data pages comprising the table are simply deallocated. The allocations are unhooked from the table and put onto a queue to be deallocated by a background task called the deferred-drop task. The deferred-drop task does the deallocations instead of them being done as part of the regular transaction so that no locks need to be acquired while deallocating entire extents.

The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

Question 9 : What kind of triggers are “DDL triggers”?

Answer :

There are 2 kinds of Triggers in SQL Server
• Instead of Triggers
• After Triggers

DDL triggers are implemented as AFTER triggers, which means the operation occurs and is then caught in the trigger (and optionally rolled-back, if you put a ROLLBACK statement in the trigger body).

This means they’re not quite as lightweight as you might think. DDL triggers are relatively expensive in nature. What would be better in this case is to specifically GRANT or DENY the ALTER permission. DDL triggers allow you to perform auditing of who did what, so I’m not saying they’re without use – just be careful.

Question 10 : How to find a column usage across a SQL Server DB?

Answer :

If changes need to be made to a column it is necessary to perform some impact assessment in order to determine what objects will be affected, meaning that SQL table column dependencies within a SQL Server database need to be found.

One of the ways is to use a SYSCOMMENTS table. It is a table which contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The column TEXT in the syscomments table contains the actual code for all these objects, and knowing it you can write a code to check the dependencies:

Example

--

SELECT Name
FROM syscomments c 
JOIN sysobjects o on c.id = o.id 
WHERE TEXT LIKE '%Person%' AND TEXT LIKE '%FirstName%'


--

The query above is used to determine which objects use the FirstName column of the Person table.

This is just a simplified example that contain some basic information. The downside with this method is that it will return all objects that merely mention words “Person” and “FirstName” without actually making a reference to Person.FirstName column

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