SQL Server Interview Questions & Answers – SET 22(10 Questions & Answers)

These questions were asked to one of my friend in an recent technical encounter. Well he has got 10 years of experience 🙂

1. Can we use an ORDER BY clause in a CTE?

Ans:-

Yes you can with the help of TOP operator. Without TOP operator it is not possible to use order by inside a CTE. If you try you will get an error saying – Msg 1033, Level 15, State 1, Line 18
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. Please check out the example below.

Example – 1 | With TOP

--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(4),
(3)
GO

;WITH CTE AS
(
	SELECT TOP 100 * FROM FirstTab
	ORDER BY Id DESC
)
SELECT * FROM CTE
GO

--OUTPUT

ID
-----------
4
3
2
1

(4 rows affected)


--

Example – 2 | Without TOP

--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(4),
(3)
GO

;WITH CTE AS
(
	SELECT * FROM FirstTab
	ORDER BY Id DESC
)
SELECT * FROM CTE
GO

--OUTPUT

Msg 1033, Level 15, State 1, Line 18
The ORDER BY clause is invalid in views, inline functions, derived tables
, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.


--

2. Do you know – Natural join? How you can use Natural JOIN In SQL SERVER?

Ans:-

Natural Joins are not there in SQL SERVER. For the sake of concept I am gonna explain the concept here. I had leaned that while I was working in Oracle in 2009. They are also present in mySQL Server.

Natural Joins are not there in SQL SERVER. For the sake of concept I am gonna explain the concept here. I had leaned that while I was working in Oracle in 2009.

A NATURAL JOIN is a JOIN operation that creates an inner join between common columns in the two tables being joined. Common columns are columns that have the same name in both tables or you can also specify names by USING keyword.

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):-
All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not a common column.

If a common column is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.

Syntax

--

TableExpression NATURAL [ { LEFT | RIGHT } 
[ OUTER ] | INNER ] JOIN { TableViewOrFunctionExpression | ( TableExpression ) }

--

Notes –
1. With NATURAL JOINS columns with the same name of associate tables will appear once only in the output.

Refer below for more-

Oracle Natural JOIN
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturaljoin.html

mySQL Server Natural JOIN
https://dev.mysql.com/doc/refman/8.0/en/join.html

Examples below – From Oracle(http://rextester.com)

--                            

CREATE TABLE Pawant1
(
    Id INT
   ,Name VARCHAR2(20)
)
\\


INSERT INTO Pawant1 VALUES (1,'Pawan')
\\
INSERT INTO Pawant1 VALUES (2,'Avtaar')
\\
INSERT INTO Pawant1 VALUES (3,'Dil')
\\

CREATE TABLE Pawant2
(
    Id INT
   ,Name VARCHAR2(20)
)
\\

INSERT INTO Pawant2 VALUES (3,'Dil')
\\

--Example 1

SELECT * FROM Pawant1
NATURAL JOIN Pawant2

--OUTPUT 

 	ID	NAME
1	3	Dil


--Example 2 | By USING Keyword and removing Natural keyword

SELECT * FROM Pawant1
JOIN Pawant2
USING(Id)

--OUTPUT 

 	ID	NAME
1	3	Dil

--

Note – After executing the above commands you need to stop and start SQL Server.

3. Can you tell me few limitations of an IDENTITY column

Ans:-

You cannot update value of an identity once it is generated and you can use it on Int columns.

4. How you can get the primary key information for a single table using T-SQL query

Ans:-

You can use sp_pkeys or sp_primarykeys procedures. Please refer below for more details-

sp_pkeys – It returns the primary key information for a single table in the current environment.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-pkeys-transact-sql?view=sql-server-2017

sp_primarykeys – It returns the primary key columns, one row per key column, for the specified remote table.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-primarykeys-transact-sql?view=sql-server-2017

5. How many types of XML indexes in SQL Server?

Ans:-

XML indexes categories are of two types

1. Primary XML index
2. Secondary XML index

Primary XML index
The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table. SQL Server uses this primary key to correlate rows in the primary XML index with rows in the table that contains the XML column.

The primary XML index is a shredded and persisted representation of the XML BLOBs in the xml data type column. For each XML binary large object (BLOB) in the column, the index creates several rows of data. The number of rows in the index is approximately equal to the number of nodes in the XML binary large object. When a query retrieves the full XML instance, SQL Server provides the instance from the XML column. Queries within XML instances use the primary XML index, and can return scalar values or XML subtrees by using the index itself.

Secondary XML index

To enhance search performance, you can create secondary XML indexes.These are the types:
PATH secondary XML index
VALUE secondary XML index
PROPERTY secondary XML index

Read more from – https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-indexes-sql-server?view=sql-server-2017#secondary-xml-indexes

Note-
A primary XML index must first exist before you can create secondary indexes

Read more about the XML Indexes from below-
https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-indexes-sql-server?view=sql-server-2017

6. How can you import data into SQL Server using T-SQL without SSIS?

Ans:-

You can you use any one of the below commands. Also depends from where we are fetch the data.

1. Linked Servers
2. OPENQUERY
3. BCP
4. OPENDATASOURCE
5. OpenRowSet
6. Bulk Insert

7. Why we should not use ORDER BY NEWID() for getting the sample data from a large table?

Ans:-

This is because it will scan the entire table and calculate NEWID() for each row and after that sorts. You should use something else. 🙂

8. How to clear Plan Cache in SQL SERVER(on-premise) and On SQL Azure?

ON SQL Premise-
dbcc freeproccache

Read more from-
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

ON SQL Azure-
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Read more from-
https://msdn.microsoft.com/en-us/library/mt629158.aspx

9. Can we create a clustered index on a uniqueidentifier column

Ans:-

Yes you can but you should not

10. What will be the output of SELECT (1/2) * 10

Ans:-

I am leaving this answer for yourself.

Enjoy 🙂

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com