Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Server Interview Questions Book, SQL Questions
SQL Server Interview Questions – Set 11 (10 Questions )
|Sr No||Download||Download URL|
|17||SQL Server Interview Questions AND Answers [SET 11](10 Questions & Answers)||Download|
Question 1 : How do we get time difference between two date time variables in SQL Server?
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?
Question 3 : Can you write a query to get file details of a database?
Query to get Data and log file details of a database
-- USE TEMPDB EXEC sp_helpfile GO USE Master EXEC sp_helpfile GO --
Question 4 : Why index REBUILD does not reduce index fragmentation?
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?
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?
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.
Question 7 : How do you get count of logical cpu’s using SQL?
-- 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?
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 : 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’.
-- --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 --
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