Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, Buy SQL Server Interview Questions Book Online at Low Price, Download SQL Questions, 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, Free Download SQL SERVER Interview questions, implementation and administration, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals, SQL Queries asked in interviews, SQL Questions, SQL Server - Common Interview Questions and Answers, SQL Server - General Interview Questions and Answers, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL server Questions and Answers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, SQLBI Interview Questions, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Server Interview Questions, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers
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?
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?
Best possible answer – Excellent post by one of my favorite MVP( Paul White ) –
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?
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?
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.
Pawan Kumar Khowal