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