• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • Python Interview Puzzles
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Monthly Archives: September 2015

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

30 Wednesday Sep 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Server Interview Questions

≈ Leave a comment

Tags

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?

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.

Pawan Khowal - Report ( Export - Pdf )

Pawan Khowal – Report ( Export – Pdf )

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

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL Server Interview Questions and Answers # 7

30 Wednesday Sep 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Server Interview Questions

≈ Leave a comment

Tags

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 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


SQL Server Interview Questions # 7

One of my friend has given me few technical interview question. He has given interview for a company based out from Chandigarh. So with out further delay I will list the questions-

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

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

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

Question 4 : SQL Server – Could you please tell situations where it is fine to use cursors?

Question 5 : SQL Server – What is Parameter Sniffing?

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?

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?

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

--

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

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

Shall update the answers very soon !

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL Server – Never use Count(*) in If Exists() for data existence in a Table. Why?

26 Saturday Sep 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Performance Tuning, SQL Server Interview Questions

≈ Leave a comment

Tags

Advanced SQL tutorial pdf, Check if data exists, Check if record exists in table for tables - MSDN - Microsoft, check if table has records, Define below transformation in DFD?, Difference between Cached Report and Snapshot Report, 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, How to check if a specific record exists in a table in SQL, How to make connection with a FTP server?, How to show "No Data Found" Message to end user?, SQL, sql - Fastest way to determine if record exists, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, sql server - check if data exist in a table if yes do this else, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Server | Best way to check if data exists in a table ?, SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table), SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions


SQL Server – Never use Count(*) in If Exists() for data existence in a Table. Why?

Why? Simple because it doesn’t work.

In this post I will explain you the same. First let me just create a sample table and insert some data into it.

--
 
CREATE TABLE testCounts
(
    ID INT
)
GO

--

Now we have not inserted any rows means table is empty. now let me just run the code for you.

--


--Bad Method
If EXISTS (SELECT COUNT(*) FROM dbo.testCounts)
	BEGIN
		PRINT 1
	END
ELSE 
	BEGIN
		PRINT 0
	END

--

Now if you execute the above code you will always get 1, the number of rows doesn’t matter. Even we have 0 rows it is giving us 1. Folks always use below approach to check data existence in a table.(For details please refer – https://msbiskills.com/2015/09/16/sql-server-best-way-to-check-if-data-exists-in-a-table/)

--

If EXISTS (SELECT TOP 1 1 FROM dbo.testCounts)
BEGIN
    PRINT 1
END

--

Above query will give excellent execution plan. I hope now things are more clear. That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

T-SQL Query | [ Records not ending with a character Puzzle ] – BEST Approach

24 Thursday Sep 2015

Posted by Pawan Kumar Khowal in T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Divide rows into two columns, find records not ending with s, find records not ending with s puzzle, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, puzzle sql developer, Puzzles, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Query | [ The Complex Week Puzzle ], The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The Tree Puzzle, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, Week puzzle, What You Can (and Can't) Do With Filtered Indexes


T-SQL Query | [ Records not ending with a character Puzzle ] – BEST Approach

Yesterday I was going through one of the groups on Facebook and got this question. The question is very easy; You have to find out employees where name does not end with ‘s’ . Many people can easily answer this but didn’t get the internals of it.. I will explain the puzzle in detail. Let’s first go through the sample input and expected output below-

Pictorial presentation of the puzzle.(Pic taken from some Facebook group)

Pawan Kumar Khowal - Facebook Puzzle

Pawan Kumar Khowal – Facebook Puzzle

Sample Input

Id Name
100 Smith
101 Allen
103 Martin
102 Jones

Expected Output

Id Name
100 Smith
101 Allen
103 Martin

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

--

--Table Creation

CREATE TABLE SearchString
(
	 ID INT
	,Name VARCHAR(10)
)
GO

--Data insertion

INSERT INTO SearchString VALUES
(100, 'Smith'),
(101, 'Allen'),
(102, 'Jones'),
(103, 'Martin')
GO

--Create Indexes
CREATE UNIQUE CLUSTERED INDEX Ix_ID ON SearchString(ID)
GO

CREATE NONCLUSTERED INDEX Ix_Name ON SearchString(Name)
GO

--

Update Sep 24 | Solutions # – Pawan Khowal

--

--Solution 1

SELECT Id, Name FROM SearchString
where Name LIKE '%[^s]'

--Solution 2

SELECT Id, Name FROM SearchString
where LEFT(REVERSE(Name),1) <> 's'

--

Lets compare the execution plans of the the above solutions –

Pawan Kumar Khowal - Sub Optimal Solutions

Pawan Kumar Khowal – Sub Optimal Solutions

Both the approaches are sub optimal only and equal in terms of  the performance. Although i will prefer the first approach any day since we are not putting any function around column in the where clause. In the both the cases we are index scan but i want seek. So lets go through another solution. We need a little of word around for this.

--
--Solution 3 - The Best Approach

ALTER TABLE SearchString
	ADD CharName AS RIGHT(Name,1)

CREATE NONCLUSTERED INDEX Ix_CharName1 ON SearchString(CharName)
INCLUDE (ID,Name)


SELECT Id,Name FROM SearchString
WHERE CharName = 's'

SELECT Id, Name,right(Name,1) FROM SearchString
WHERE RIGHT(Name,1) <> 's'

--

In the above case we have added a computed column and then created a non clustered index on computed column.Lets go through the execution plan of newly rewritten queries.

Pawan Kumar Khowal - Optimal Solution

Pawan Kumar Khowal – Optimal Solution

This the best approach (Add a computed column and have index on it) we have, but sometimes we don’t have permissions to alter tables or you have very less data and you don’t care about the performance. In that case you can follow the first approach.

Thank Guys !

Add a comment if you have any other solution in mind. We all need to learn. Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Answers to SQL Server (SQL,SSIS,SSRS,SSAS) Interview Questions – # 6

23 Wednesday Sep 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, Integration Services, Reporting Services, SQL Server Interview Questions

≈ 1 Comment

Tags

Advanced SQL tutorial pdf, Advanced SSRS tutorial pdf, Are table variables only stored in memory - SQL Server Q&A, Can we apply style sheet in SSRS and how?, Can we debug SSIS package. If Yes, Count puzzle, Count(*) VS Count(ColumnName) VS Count(1), Define below transformation in DFD?, deployment mechanism for ​​SSRS deployment, Difference between Cached Report and Snapshot Report, Difference between NONEMPTY vs NON EMPTY IN MDX?, Difference between Report Server and Report Manager:?, 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 SSRS Questions, Download SSRS Server Interview Question in PDF, Download SSRS SERVER Interview questions, download SSRS server interview questions and answers, download SSRS server interview questions and answers pdf, download SSRS server interview questions by Pawan Khowal, download SSRS server interview questions by Pawan Kumar, download SSRS server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Download T-SSRS Interview Questions, Free Download SQL SERVER Interview questions, Free Download SSRS SERVER Interview questions, How check point works in for loop?, How many type of protection level in SSIS package?, How to create Temporary Table using SSIS?, How to design a Drilldown report?, How to make connection with a FTP server?, How to make connection with SFTP server?, How to render a report to a user email?, How to show "No Data Found" Message to end user?, How we can ignore failure and continue loop?, How?, I have a sql table that I need to split into more 90 excel sheet based on a code. I could create an ssis package and use conditional split and create more than 90 excel sheet. But creating more than 9, Sometime we need to debug out SSIS Package but we do not want to insert records in destination but still we want to use all the transformations and dump these all records in some dummy destination. Th, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL count puzzle, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL SERVER – Watching Table Variable Data in TempDB, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SSIS Interview questions, SSRS, SSRS Common Interview Questions, SSRS Common Interview Questions and answers, SSRS FAQ, SSRS FAQs, SSRS Interview Q & A, SSRS Interview Questions, SSRS Queries asked in interviews, SSRS questions, SSRS Server - General Interview Questions and Answers, SSRS Server developer Interview questions and answers, SSRS Server developer Interview questions with answers, SSRS SERVER Interview questions, SSRS SERVER Interview questions & Answers, SSRS SERVER Interview questions and Answers, SSRS Server Interview Questions and Answers - Free PDF, SSRS server interview questions and answers for net developers, SSRS SERVER Tips, SSRS Tips & Tricks, SSRS Tips and Tricks, SSRS Tricks, T-SQL Server Interview Questions, T-SSRS Server Interview Questions, Table Variable are created and stored in tempdb, Table Variables are Created in TempDB, Table Variables Are Only in Memory: Fact or Myth, table variables created and held in memory or in tempdb?, Tables Variables ARE Stored in tempdb, We get the files in our Source Folder all day long. Each file is appended copy of previous file. We want to create a SSIS Package that will load only the most recent file to our table.?, What are Attunity Driver and why do we user in SSIS?, What are check point and how they work?, What are Lazy aggregations?, What are Partition processing options?, What is a sub report?, What is the difference between Table and Matrix?, where is table variables stored in the database?, where table variables are stored in sql server


Answers to SQL Server (SQL,SSIS,SSRS,SSAS) Interview Questions – # 6

Question 1 : I have a sql table that I need to split into more 90 excel sheet based on a code. I could create an ssis package and use conditional split and create more than 90 excel sheet. But creating more than 90 excel sheet one at a time will be time consuming and if I have to use that package again for another table then I would have to make changes. Is there an easier/faster way to achieve split a table into more than 90 excel tabs? Is it possible to use foreachloop and dynamically split and create excel tab?

Answer :

1. Assuming that have some rules to split the data. Best way to save this information in a table.
2. Create a optimized stored procedure which will accept the input parameters (above) and return the data
2. Add a script task
2.1 Here connect to the DB
2.2 Create a for loop
2.2.1 Call the SP created in step 2 and get the data in the dataSet
2.2.2 Add a new worksheet to your workbook
2.2.3 Insert data to the new worksheet

Question 2 : What are Attunity Driver and why do we user in SSIS?

Answer :

Attunity provides 2 high speed connectors. One for Oracle and one for Teradata. They have been selected by Microsoft to be included with SQL Server 2008 Integration Services (SSIS) SQL 2008 Enterprise Edition. These drivers are highly optimized and very easy to use.

Optimized, best-in-class performance
The connectors deliver unparalleled throughput for extracting and loading data to and from Oracle and Teradata. Architected with Microsoft, the connectors use direct integration into internal SSIS buffering APIs, cutting through .NET and other layers, and also use the high speed load/extract utilities offered by Oracle and Teradata.

Ease-of-use
The connectors are fully integrated into the Business Intelligence Development Studio (BIDS), part of Microsoft Visual Studio 2008, offering a user experience similar to that of the SSIS generic OLEDB Connector, with intuitive capabilities including configuration, metadata browsing, and error routing.

Question 3 : How many type of protection level in SSIS package?

Answer :

It is a package level property. It is used to specify how sensitive information is saved inside the package. It also specify whether to encrypt the package or the sensitive portions of the package.

Each SSIS component designates that an attribute is sensitive by including Sensitive=”1″ in the package XML. When the package is saved, any property that is tagged with Sensitive=”1″ gets handled per the ProtectionLevel property setting in the SSIS package. The ProtectionLevel property can be selected from the following list of available options (click anywhere in the design area of the Control Flow tab in the SSIS designer to show the package properties):

  • DontSaveSensitive
  • EncryptSensitiveWithUserKey
  • EncryptSensitiveWithPassword
  • EncryptAllWithPassword
  • EncryptAllWithUserKey
  • ServerStorage

Question 4 : Difference between Cached Report and Snapshot Report?

Answer :

Cached Report

Here the system will save the last executed report. It is saved in the temp DB. It is not persisted. It has a lifetime e.g. 1 hour or so. We can have 1 only one “instance” per report (if you have parameters, you will have one per combination of parameter)

Snapshot Report

It is a persisted copy of the report. It is stored for good on the report database. You can have as many as you want. You can configure for example to save a snapshot of a report per day, so if you want to see how was your data 1 month ago, you just access the snapshot of that day.

When to use which one

Most of my reports, I cache them for 2 hours, so the first user who runs it will experience a small delay and the next will get the report on demand (with the data from when the report was ran, of course)

For large reports, execute them at night and configure them to be run from a snapshot (option “Render this report from a report execution snapshot”).

Question 5 : What are check point and how they work?

Answer :

SSIS 2005 included a feature called checkpoints, which allows you to restart the package if it fails for any reason. During package execution, the last successfully completed task or container is noted in a checkpoint file, and the checkpoint file is removed if the package completes successfully. But if the package fails before completing, the checkpoint file remains available as a reference to the location from which to restart the package.

You need to set three package properties:

CheckpointFileName. For this property, you need to provide a path and filename for the checkpoint file. If you plan to keep checkpoints implemented when you put a package into production, it’s a good idea to use a Universal Naming Convention (UNC) path.

CheckpointUsage. This property has three possible values: Never, Always, and IfExists. The default is Never, which prevents checkpoint creation. When you specify the Always option, the package uses the checkpoint file if it exists. If it doesn’t exist, the package fails. Therefore, the Always option isn’t recommended for a package in production because the package shouldn’t be failing regularly. (A package failure is the only way a checkpoint file gets created. Once the package completes successfully, the checkpoint file is removed.) The best option to use is IfExists. When you select this option, the package uses the checkpoint file if it exists. If it doesn’t exist, the program starts from the beginning of the package.

SaveCheckpoints. This property must be set to True. Otherwise, the previous settings won’t have any effect. By default, it’s set to False.

For details please refer – http://sqlmag.com/sql-server-2008/use-checkpoints-restart-failed-ssis-packages

Question 6 : How check point works in for loop?

Answer :

The Foreach Loop container is another atomic unit of work that can be restarted. However, the checkpoint file does not contain information about the work completed by the child containers, and the Foreach Loop container and its child containers run again when the package restarts.

Question 7 : Can we apply style sheet in SSRS and how?

Answer :

There are two ways to apply style sheet in SSRS
1. Hard Code in RDL File
2. Dynamically – You can save your style in DB and pull that in a DataSet and then apply.

For details please refer – http://www.keepitsimpleandfast.com/2011/11/how-to-implement-style-sheets-in-your.html

Question 8 : How to show “No Data Found” Message to end user?

Answer :

Add a text box. Set expression of the text box = IIF(Count(,”DataSet1″)=0,”No Data Found”, Nothing)
and set the visibility of this text box = IIF(Count(,”DataSet1″)=0,False,True)

Question 9 : Sometime we need to debug out SSIS Package but we do not want to insert records in destination but still we want to use all the transformations and dump these all records in some dummy destination. The goal can be to check the extraction performance from source OR view data at different points of Package but we do not want to insert data in destination at all.

Answer :

In this kind of scenario you can use below solutions-

1. Multicast transformation
2. Row Count Transformation

Question 10 : Can we debug SSIS package. If Yes, How?

Answer :

You can debug a Package by Setting Breakpoints on a Task or a Container

To set breakpoints in a package, a task, or a container follow below-

  • In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
  • Double-click the package in which you want to set breakpoints.
    In SSIS Designer, do the following:
  • To set breakpoints in the package object, click the Control Flow tab, place the cursor anywhere on the background of the design surface, right-click, and then click Edit Breakpoints.
  • To set breakpoints in a package control flow, click the Control Flow tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.
  • To set breakpoints in an event handler, click the Event Handler tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.
  • In the Set Breakpoints dialog box, select the breakpoints to enable.
    Optionally, modify the hit count type and the hit count number for each breakpoint.
  • To save the package, click Save Selected Items on the File menu.

Question 11 : How to create Temporary Table using SSIS?

Answer :

You can use Execute SQL task to create temp table and set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

Question 12 : We get the files in our Source Folder all day long. Each file is appended copy of previous file. We want to create a SSIS Package that will load only the most recent file to our table.?

Answer :

Its simple create 2 variables, folderpath and filename. Now in script task, create a loop and find out the latest file. Now you can read the latest file in the script task itself and insert the data into the table.

for details please refer – http://www.techbrothersit.com/2013/12/ssis-how-to-get-most-recent-file-from.html

Question 13 : What are Lazy aggregations?

Answer :

Processing mode property of a partition/measure group determines how partitions will be available to users. Processing mode has two possible options – Regular and Lazy Aggregations.

Regular – Default. When set to regular, partitions will be available to users after data has been loaded and aggregations are created completely.

Lazy Aggregations – When set to lazy aggregations, partitions will be available to user queries immediately after data has been loaded. Aggregations will be created as a separate background process while users start to query the partition.

Process Full will internally executes Process Data and Process Index before the partition can be used for queries. If processing mode is set to Lazy Aggregations, partition will be released for user queries after Process Data is completed. Process Index will be executed in the background. As aggregations don’t exist while users begin to query the partition they may experience slow performance.

Question 14 : How we can ignore failure and continue loop?

Answer :

The Propagate variable in SSIS is used to determine whether an event is propagated to a higher level event handler. This allows package execution to continue once an event is handled in the container that has generated an Error.

https://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/

http://www.codeproject.com/Articles/384690/In-SSIS-how-to-continue-a-for-each-loop-container

Question 15 : What are Partition processing options?

Answer :

When you process objects in Microsoft SQL Server Analysis Services, you can select a processing option to control the type of processing that occurs for each object. Processing types differ from one object to another, and by changes that have occurred to the object since it was last processed. If you enable Analysis Services to automatically select a processing method, it will use the method that returns the object to a fully processed state in the least time.Processing settings let you control the objects that are processed, and the methods that are used to process those objects.

Mode Applies to Description
Process Default Cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions. Detects the process state of database objects, and performs processing necessary to deliver unprocessed or partially processed objects to a fully processed state. If you change a data binding, Process Default will do a Process Full on the affected object.
Process Full Cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions. Processes an Analysis Services object and all the objects that it contains. When Process Full is executed against an object that has already been processed, Analysis Services drops all data in the object, and then processes the object. This kind of processing is required when a structural change has been made to an object, for example, when an attribute hierarchy is added, deleted, or renamed.
Process Clear Cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions. Drops the data in the object specified and any lower-level constituent objects. After the data is dropped, it is not reloaded.
Process Data Dimensions, cubes, measure groups, and partitions. Processes data only without building aggregations or indexes. If there is data is in the partitions, it will be dropped before re-populating the partition with source data.
Process Add Dimensions, measure groups, and partitions. Process Add is not available for dimension processing in Management Studio, but you can write XMLA script performs this action. For dimensions, adds new members and updates dimension attribute captions and descriptions.
Process Update Dimensions Forces a re-read of data and an update of dimension attributes. Flexible aggregations and indexes on related partitions will be dropped.
Process Index Cubes, dimensions, measure groups, and partitions Creates or rebuilds indexes and aggregations for all processed partitions. For unprocessed objects, this option generates an error. Processing with this option is needed if you turn off Lazy Processing.
Process Structure Cubes and mining structures If the cube is unprocessed, Analysis Services will process, if it is necessary, all the cube’s dimensions. After that, Analysis Services will create only cube definitions. If this option is applied to a mining structure, it populates the mining structure with source data. The difference between this option and the Process Full option is that this option does not iterate the processing down to the mining models themselves.
Process Clear Structure Mining structures Removes all training data from a mining structure.

For details please refer – https://msdn.microsoft.com/en-us/library/ms174774.aspx

Question 16 : How to make connection with SFTP server?

Answer :

SFTP stands for Secure File Transfer Protocol which is a world wide accepted secure protocol to transfer and access files over a secure channel. The data and channel are encrypted in SFTP mode, which prevents unauthorized access by any intruders and it’s mainly used between companies to transfer secure and sensitive information.

You may be aware of the FTP task in SSIS which allows us to copy or paste files to/from a FTP site, but unfortunately SSIS doesn’t support communication over SFTP. A work around for this will be to use PSFTP through an Execute Process Task in SSIS to download the file to our local machine. PSFTP is a SFTP client tool provided by PuTTy (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html) to transfer files between computer systems using the SFTP protocol.

A solution for this scenario will include the following steps:

Download PSFTP.exe file to our destination folder
Create a batch file with logic to download the text file using Windows command language
Create a SSIS package with an Execute Process Task to run PSFTP.exe

For details please refer – https://www.mssqltips.com/sqlservertip/3435/using-sftp-with-sql-server-integration-services/

Question 17 : Difference between NONEMPTY vs NON EMPTY IN MDX?

Answer : Please refer follow link

http://thatmsftbiguy.com/nonemptymdx/

Question 18 : Difference between Report Server and Report Manager:?

Answer :

The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations. Processors are the hub of the report server.

Report Manager: Report Manager is a web based tool built using ASP.NET application to view/access SSRS reports. It is not available in SharePoint mode.

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email
← Older posts

Blog Stats

  • 1,074,522 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,131 other subscribers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

September 2015
M T W T F S S
 123456
78910111213
14151617181920
21222324252627
282930  
« Aug   Oct »

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…

Archives

  • October 2020 (29)
  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Python (29)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Python | The Print and Divide Puzzle October 30, 2020
  • Python | Count consecutive 1’s from a list of 0’s and 1’s October 30, 2020
  • Python | How to convert a number into a list of its digits October 26, 2020
  • Python | Validate an IP Address-IPV6(Internet Protocol version 6) October 26, 2020
  • Python | Print the first non-recurring element in a list October 26, 2020
  • Python | Print the most recurring element in a list October 26, 2020
  • Python | Find the cumulative sum of elements in a list October 26, 2020
  • Python | Check a character is present in a string or not October 26, 2020
  • Python | Check whether a string is palindrome or not October 26, 2020
  • Python | Find the missing number in the array of Ints October 26, 2020
  • Python | How would you delete duplicates in a list October 26, 2020
  • Python | Check whether an array is Monotonic or not October 26, 2020
  • Python | Check whether a number is prime or not October 26, 2020
  • Python | Print list of prime numbers up to a number October 26, 2020
  • Python | Print elements from odd positions in a list October 26, 2020
  • Python | Print positions of a string present in another string October 26, 2020
  • Python | How to sort an array in ascending order October 26, 2020
  • Python | How to reverse an array October 26, 2020
  • Python | Find un-common words from two strings October 26, 2020
  • Python | How to convert a string to a list October 26, 2020
  • Python | Find unique words from a string October 26, 2020
  • Python | Calculate average word length from a string October 26, 2020
  • Python | Find common words from two strings October 26, 2020
  • Python | Find the number of times a substring present in a string October 26, 2020
  • Python | Find maximum value from a list October 26, 2020
  • Python | How to find GCF of two numbers October 26, 2020
  • Python | How to find LCM of two numbers October 26, 2020
  • Python | How to convert a list to a string October 26, 2020
  • Python | Replace NONE by its previous NON None value October 26, 2020
  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018

Create a website or blog at WordPress.com

  • Follow Following
    • Improving my SQL BI Skills
    • Join 231 other followers
    • Already have a WordPress.com account? Log in now.
    • Improving my SQL BI Skills
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

You must be logged in to post a comment.