Sql Server Interview Questions and Answers (1758) - Page 87

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

What is the benefit of using SQL Profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. We can capture and save data about each event to a file or table to analyze later.

What’s wrong in the following query? SELECT Name, MaxSal = Max(Salary) FROM tblEmployees;

It doesn’t have a GROUP BY clause. The Name should be in the GROUP BY clause. The correct query is

SELECT Name, MaxSal = Max(Salary) 

FROM tblEmployees

Group By Name

- OR -

Select Name, MaxSal = MAX(Salary) OVER (PARTITION BY Name) 

FROM tblEmployees

What’s wrong in the following query? SELECT Name, MaxSal = Max(Salary) FROM tblEmployees WHERE Max(Salary) > 5000 GROUP BY Name;

The query is wrong because we cannot use Aggregate Function for filtering in a WHERE clause. Instead we need to use HAVING. The correct query is

SELECT Name, MaxSal = Max(Salary) 

FROM tblEmployees

GROUP BY Name

HAVING Max(Salary) > 5000;

What does UNION do? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

What is the output of this query? select case when null = null then 'Yes' else 'No' end as Result;

NOTE: This is objective type question, Please click question title for correct answer.

Write a SQL query to find the 5th highest employee salary from an Employee table.

SELECT TOP (1) Salary FROM

(

    SELECT DISTINCT TOP (5) Salary FROM Employee ORDER BY Salary DESC

) AS Emp ORDER BY Salary

First, the SELECT DISTINCT TOP (5) Salary FROM Employee ORDER BY Salary DESC query will select the top 5 salaried employees in the table. However, those salaries will be listed in descending order. That was necessary for the first query to work, but now picking the top 1 from that list will give you the highest salary not the the 5th highest salary.

Therefore, the second query reorders the 5 records in ascending order (which the default sort order) and then selects the top record (which will now be the lowest of those 5 salaries).

Not all databases support the TOP keyword.

For example, MySQL and PostreSQL use the LIMIT keyword, as follows:

SELECT Salary FROM

(

    SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 5

) AS Emp ORDER BY Salary LIMIT 1;

What is the difference between the RANK() and DENSE_RANK() functions?

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a equal values or TIE.

Example:
Consider the set {25, 25, 50, 75, 75, 100}.
For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped),
whereas DENSE_RANK() will return {1,1,2,3,3,4}.

What is MDX in SSAS(Microsoft SQL Server Analysis Services) ?

NOTE: This is objective type question, Please click question title for correct answer.

What is Transparent Data Encryption (TDE)?

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.

What will be the output of the below TSQL Script? DECLARE @Data VARCHAR(MAX) SET @Data = 'hello!DNF.' ;WITH numberCTE AS ( SELECT 1 AS Rn UNION ALL SELECT Rn+1 FROM numberCTE WHERE Rn<LEN(@Data) ) SELECT [Serial Number] = Rn FROM numberCTE

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of the following? DECLARE @String VARCHAR(MAX) DECLARE @Noise TABLE(Noise VARCHAR(100),ReplaceChars VARCHAR(10)) SET @String = 'hello! DNF.!' INSERT INTO @Noise(Noise,ReplaceChars) SELECT '!',SPACE(1) UNION ALL SELECT '@',SPACE(1) UNION ALL SELECT '#',SPACE(1) UNION ALL SELECT '$',SPACE(1) UNION ALL SELECT '%',SPACE(1) UNION ALL SELECT '^',SPACE(1) UNION ALL SELECT '&',SPACE(1) UNION ALL SELECT '*',SPACE(1) UNION ALL SELECT '(',SPACE(1) UNION ALL SELECT ')',SPACE(1) UNION ALL SELECT '{',SPACE(1) UNION ALL SELECT '}',SPACE(1) UNION ALL SELECT '<',SPACE(1) UNION ALL SELECT '>',SPACE(1) UNION ALL SELECT ':',SPACE(1) SELECT @String = REPLACE(@String, Noise, ReplaceChars) FROM @Noise SELECT @String Data

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of the below TSQL Script? DECLARE @String VARCHAR(MAX) SET @String = 'DNF' ;WITH numberCTE AS ( SELECT 1 AS Rn UNION ALL SELECT Rn+1 FROM numberCTE WHERE Rn<LEN(@String) ) SELECT Characters = SUBSTRING(@String,rn,1) FROM numberCTE

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of the below TSQL Script? DECLARE @t TABLE(Chars VARCHAR(20)) INSERT INTO @T SELECT 'D' UNION ALL SELECT 'N' UNION ALL SELECT 'F' SELECT Chars Data FROM @T FOR XML PATH('')

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of DECLARE @string VARCHAR(20) = 'Hello how r u?' SELECT STUFF(REPLICATE(',' + LEFT(@string,10),LEN(LEFT(@string,10)) - LEN(REPLACE(LEFT(@string,10), 'o', ''))),1,1,'')

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of --Input Script --Declare a sample table variable DECLARE @tblAssemblyVersion TABLE(Version VARCHAR(20)) --Insert some records to it INSERT INTO @tblAssemblyVersion VALUES ('6.0.0.0'),('3.0.0.0'),('4.5.1.0'),('4.5.8.0'),('4.5.11.0'),('1.1.2.14234'),('1.11.21.14234'),('1.8.2.14234'),('6.0.0.1') SELECT * FROM @tblAssemblyVersion ORDER BY CAST('/'+REPLACE(Version,'.','/')+'/' AS hierarchyID)

The answer is

        Version

	--------

	1.1.2.14234

	1.8.2.14234

	1.11.21.14234

	3.0.0.0

	4.5.1.0

	4.5.8.0

	4.5.11.0

	6.0.0.0

	6.0.0.1

The hierarchyid data type values represents a position in a tree hierarchy and it performs Depth-first search of the tree traversal.That indicates that for any two hierarchy id values e.g. x and y, if x and y are in a relationship of x<y, then in the case of Depth-first search, x will always appear before y. And hence the answer.