Answers to SQL Server (SQL Server & SSRS) Interview Questions – # 7

Question 1 : SQL Server – Should you update statistics after rebuild indexes?

Answer : It depends. Basically there are two kinds of STATISTICS – Index & Column. Rebuilding an index using the ALTER INDEX … REBUILD statement, will update only index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update any column statistics. You need to use UPDATE STATISTICS to update column statistics. So it depends if you want to update the column statistics then you should do it explicitly.

Question 2 : SQL Server – Should you update statistics after reorganize indexes?

Answer : Yes you should update statistics after reorganize indexes. ALTER INDEX … REORGANIZE does not update any statistics.

Question 3 : SQL Server – Can you create filtered index on computed columns?

Answer : No you cannot.

Question 4 : SQL Server – Could you please tell the situation when it is fine to use Cursors?

Answer :

Well the best answer is that when there is no way to achieve the task using a set based approach & you have to follow row by row operation. Till date i have not been in the situation where i had to use a cursor. Also note that While loop is a cursor. It just that constructing a while loop is quite simpler and more straightforward than constructing a cursor.

Question 5 : SQL Server – What is Parameter Sniffing?

Answer :

Best possible answer – Excellent post by one of my favorite MVP( Paul White ) –
http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

Question 6 : SQL Server – You have a query which is working very slow when executed from application but working fine when executed from SSMS? What could be the reason?

Answer :

ARITHABORT set option could be the reason. This setting is one of the those things that the optimizer looks at when it is finding how to execute your query. Specifically in .net by default this setting is OFF and in SQL it is ON. (SET ARITHABORT ON). Hence if this setting is off, optimizer may choose sub optimal plan which can certainly hurt performance in a lot of cases.2 Options to fix this issue are-
a) Add OPTION (RECOMPILE)
b) SET ARITHABORT ON; Your Query

For more details please click here – http://www.sommarskog.se/query-plan-mysteries.html

Question 7 : Reporting Services – You have 100 columns in your SSRS report and you are exporting this report into PDF format, How do you manage this?

Answer :

You have to set two properties to handle above scenario. Right click on report designer and change below mentioned properties.

Question 8 : SQL Server – Provide output of below query.

-- Query 

SELECT X.X
FROM 
( SELECT 5 X ) X
INNER JOIN
( SELECT 5 X , 6 Y ) Y ON X.X = Y.Y

--

Answer : 0 rows

Question 9 : Can you create table in SQL Azure with out clustered index?

Answer : No you cannot. Every table in SQL Azure needs to have a clustered index.

Unlike SQL Server, every table in SQL Azure needs to have a clustered index. A clustered index is usually created on the primary key column of the table. Clustered indexes sort and store the data rows in the table based on their key values (columns in the index). There can only be one clustered index per table, because the data rows themselves can only be sorted in one order.

A simple table with a clustered index can be created like this:

--

CREATE TABLE Source 
(
	  Id int NOT NULL IDENTITY
	, [Name] nvarchar(max)
	, CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED( [Id] ASC )	
)

--

SQL Azure allows you to create tables without a clustered index; however, when you try to add rows to that table it throws this error:

Msg 40054, Level 16, State 1, Line 2

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

SQL Azure does not allow heap tables – heap tables, by definition, is a table that doesn’t have any clustered indexes. More about SQL Server indexes in this article on MSDN.

That is the rule for all permanent tables in your database; however, this is not the case for temporary tables.

You can create a temporary table in SQL Azure just as you do in SQL Server. Here is an example:

--

CREATE TABLE #Destination 
(
	Id INT NOT NULL, [Name] NVARCHAR(MAX)
)  -- Do Something  

DROP TABLE #Destination

--

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