SSIS Questions - Part 1

Guys,
Let's start a series of Q&A's on SSIS also. As these days its essential to learn SSIS or any other BI tool.
I felt SSIS is one of the best tools to start with. Let's see some of the basic questions asked on SSIS.


Q. What is the control flow in SSIS? 
ANS:
As we have MAIN in .Net, same way we have Control Flow in SSIS. 
Containers, Data flow tasks, Administration tasks, Precedence constraints, and Variables are elements of the control flow. 
The control flow is the highest-level of control process. It allows to orchestrate and manage the run-time process activities of data flow and other processes within a package. We can design a control flow by using an Execute Package task to manage the sequence of processing for a set of existing packages in a Master Package concept. This capability allows to combine individual packages into a highly manageable workflow process. Use precedence constraints to set the process rules and to specify sequence within the control flow. An SSIS package consists of a control flow and one or more objects. Data flow and event handler process control components are optional.

Following are some key points of Control Flow:

• Process is the key: precedence constraints control the project flow based on task completion, success or failure.
• Task 1 needs to complete before task 2 can begin.
• Smallest unit of the control flow is a task.
• Control flow does not move data from task to task.
• Tasks are run in series if connected with precedence or in parallel.
• Package control flow is made up of containers and tasks connected with precedence constraints to control package 

Q. what is a data flow in SSIS?

ANS.

The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package, control flow makes it possible for the package to extract, transform, and load data.

A data flow consists of at least one data flow component, but it is typically a set of connected data flow components: sources that extract data; transformations that modify, route, or summarize data; and destinations that load data. Components are connected in the data flow by paths. Each path specifies the two components that are the start and the end of the path.

At run time, the Data Flow task builds an execution plan from the data flow, and the data flow engine executes the plan. You can create a Data Flow task that has no data flow, but the task executes only if it includes at least one data flow.

A package can include multiple Data Flow tasks, and complex packages frequently do. For example, if a package requires that data flows be run in a specified sequence, or that other tasks be performed between the data flows, you must use a separate Data Flow task for each data flow.

The Data Flow task also manages error flows. At run time, row-level errors may occur when data flow components convert data, perform a lookup, or evaluate expressions. For example, a data column with a string value cannot be converted to an integer, or an expression tries to divide by zero. Both operations cause errors, and the rows that contain the errors can be processed separately using an error flow.

There are many types of transformations as the data moves from the data source to the description. Here are some of the transformations:

Copy Column - This transformation copies the data in a column and creates a new column with it.
Script Component - This uses a VB script to transform the data somehow. You can use programmatic means to access the data, and set this script up as a data source, destination, or a transformation. When you open up the script component, there is a button stated as Design Script, which invokes a Visual Studio Editor where you can code the script with intellisense support.
Sort - This transformation sorts the data based on one or more columns setup to sort in a specified order.
Pivot and Unpivot - New to SQL Server 2005 is the way to pivot/unpivot data rather easily and they are supported as transformations. Pivoting data means you can make the columns of the result set based on a distinct result from the data.
Merge/Union All – Merging allows you to veer two inputs into one output. You can specify the input/output parameters that the transformation will map to. The Merge transformation is a little more restrictive than the Union All transformation.
Conditional Split – Conditional Split works the opposite way; based on values within the data, you can setup statements to split the data if a condition matches. For instance, you can split one result based on the expression “ListPrice > 100” and any data that matches that result is returned via a specific data flow path.
OLE DB Command – This transformation executes a SQL statement for every row in the input source.
Lookup – This transformation looks up the value of a field in a lookup data source/table.
Derived Column - A way to add an additional column in the dataset.
Aggregate - This allow to group data.
Fuzzy Logic - This allows to perform text manpulation like upper case, lower case operations on string data.
etc

Technical Videos of SQL Server Denali

Hello Everyone,

Here are links of some of the videos that were recorded during Tech-Ed event in US.

Click on each to view the video.

What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot

What’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration Services

What’s New in Microsoft SQL Server Code-Named “Denali” for Reporting Services

Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 1

Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 2

What's New in Manageability for Microsoft SQL Server Code-Named Denali

Performance Tuning and Optimization in Microsoft SQL Server 2008 R2 and SQL Server Code-Named Denali

What's New in SQL Server "Denali" for Developers | DevDays 2011

What’s New in Security for Microsoft SQL Server Code-Named Denali

Microsoft SQL Server: The Data and BI Platform for Today and Tomorrow

What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named Denali

Happy Learning

Sudhir

SQL Server Questions Part II

Guys,

Today we will see some more generic questions on SQL Server.

Here are more questions:


Q. How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?

Ans: Use referential integrity / Foreign Key Constraint

Q. How can you reset Identity key value of a table?

Ans: Truncate Table will reset the Identity value of table.

Q. What can be used to ensure that a field in a table only accepts a certain range of values?

Ans: We can use various constraints that can be used to achieve this.

There are various constraints available in SQL Server.

Primary Key Constraint => to uniquely identify a row without using NULL

Foreign Key Constraint => to maintain referential integrity between 1 or more tables

Unique Key Constraint => to uniquely identify a row

CHECK Constraint => the value entered is validated and if it fails condition then don't allow entry of data

Default Constraint => If we don't supply any value for field then set value of field with specified value

Null Constraint => Whether field can accept NULL value or not.

Q. Our company recently introduced a policy where all names of objects must follow naming convention. For ex. Table must start with tbl_, procedure should start with prc_ etc. How can I ensure all new objects must adher to these policies and if not followed object should not be created?

Ans: 1 way to use DDL triggers. From 2008 onwards we can also refer to Policy Based Management to ensure this.

Q. Is it possible to drop multiple objects using single DROP statement?

Ans:
Drop Table a,b,c
Drop proc a,b,c

Q. If you have a stored procedure, you want to just check the syntax of the same rather than executing it, what will u do?

Ans: Press CONTROL + F5 to check for its syntax. Or click on button next to EXECUTE button.

Q. Which event (CHECK constraint, Foreign Key, Rule, Trigger, Primary Key check) will be performed last for an integrity check?

Ans: Trigger because everything is executed before data is inserted in the table & as soon as data is entered in Table, trigger is fired.

Q. When a new parameter is added to an SP, what steps to take to ensure existing code in an application does not break?

ANS: Give a default value to the newly added parameter. This will ensure that all existing code will not fail.

Q. When we drop a variable length column using ALTER TABLE DROP COLUMN statement, the space used by the column is not automatically claimed. What will u do to claim the space?

Ans: DBCC CLEANTABLE (database_name, table_name)

Q. I have a table Employee with following definition:


Employee (
EmpID int Not Null Identity (1,1),
EmpName varchar(100) default 'test',
Age int default 2)

What query you will write to insert default values in EMPLOYEE table?

Ans: Insert into Employee Default Values

Q.How to retrieve a single table from database backup?

Ans: It is not possible to directly restore 1 table from backup. Only option is to restore the database somewhere else & then load data of required table and transfer to destination.

Q. How do you flip rows to columns and vice-versa?

Ans: We can use PIVOT, UnPivot to flip rows to columns & vice-versa.

Q. What is WITH ENCRYPTION clause and where it is used?

Ans: This will encrypt the definition of object and it will not be visible whenever we use sp_helptext. This can be done with View, Stored Procedure, Function etc

Q. What will happen if I write following statement:
Select getdate()
go 10

Ans: Select Getdate() is executed 10 times because GO 10 acts as a loop.

Q. What is the purpose of the USE command?

Ans: The USE command helps select any available database, so that every query is executed on selected database else default database is used.

Do let me know if you have any comments or more questions.
 
Thanks
 
Sudhir

Visual Studio 2010 and .NET Framework 4 Training Kit

Guys,

Visual Studio 2010 and .NET Framework 4 Training Kit includes presentations, hands-on labs, and demos. This content is designed to help you learn how to utilize the Visual Studio 2010 features and a variety of framework technologies including:

• C# 4
• Visual Basic 10
• F#
• Parallel Extensions
• Windows Communication Foundation
• Windows Workflow
• Windows Presentation Foundation
• Silverlight 4
• ASP.NET 4
• Windows 7
• Entity Framework
• ADO.NET Data Services
• Managed Extensibility Framework
• Application Lifecycle Management
• Windows Azure

This version of the Training Kit works with Visual Studio 2010 and .NET Framework 4.


Download and launch the self-extracting package. The Training Kit will uncompress to the selected folder and launch a HTML browser for the content.


Click here to download the kit.


Happy Learning


Sudhir

SQL Server Questions PART - 1

Guys,


Let's discuss some of the basic questions that we might face anywhere. I will try to come up with as many as I can. It will be great to receive comments/feedback on the same.


Here we go:


Q. How many types of Indexes are there in SQL Server? What is the difference between them?


Ans: There are 2 type of indexes. Clustered and Non-Clustered. There can be only 1 Clustered index per table and 249 Non-Clustered Indexes per table. The index at the end of the book is a perfect example of Clustered Index.


In Clustered index, data is sorted & saved. Suppose there is a table Employee with Clustered Index created on Employee Name field. We have data like Abrahim, Jacob etc. Now whenever it tries to save new data with Colin, it will be inserted between Abrahim and Jacob. This is not applicable for Non-Clustered index, the data is not saved in sorted way.


Visit here for Clustered Indexes
msdn.microsoft.com/en-us/library/aa933131(v=SQL.80).aspx
Visit here for Non-Clustered Indexes
msdn.microsoft.com/en-us/library/aa933130(v=SQL.80).aspx


Q. What is the difference between UNION and UNION ALL?


Ans: There is a very small difference between UNION and UNION ALL. When we join 2 resultsets using UNION, it will internally add Distinct clause before returning final result. When we use UNION ALL, full set of data is retrieved.

To see this, go to SSMS and Enable Execution Plan, it will show the difference.


Visit here for more information:
msdn.microsoft.com/en-us/library/ms180026.aspx


Q. What is the difference between Primary key and Unique key?


Ans: We have tables & Rows and to identify each row we need Primary key. Someone can say that a Unique key can also help to identify each row. That’s true but there is a difference. Primary Key value can’t contain NULL, it must have a value whereas Unique Key can have 1 NULL value as its unique and because NULL is unknown its hard to identify/represent a row with NULL. That’s why Primary key doesn’t allow NULL.


Q. How Page Split occurs and why?


Ans. I am sure we all are aware of Indexes, especially Clustered Index because data is sorted before it is written to disk. As we have a 8K page size and further it depends on Fill factor and row size (length of each field) how much that page will be filled.


Suppose one page can accommodate maximum 2 rows in 1 page. We have same Employee table with Employee Name as primary Key. First page has 2 rows where Employee Name in 1st Row is Amar and 2nd Row is Carol. Now when we try to add a new row with Employee Name as Boris. As Boris comes before Carol so data will be inserted after Amar, but a page can hold only 2 rows so what will happen to Carol.


SQL Server pick handles/Link list end points for next page from 1st page. Create a new page, move Boris to this new page & store Boris on 1st page. Now reset the next page after 1st Page to New Page and New Page will point to the original 2nd page so that the Link list is updated.


sqlblogcasts.com/blogs/tonyrogerson/arch...appen-why-worry.aspx


Q. What are the disadvantages of having Indexes?


Ans. Insertion or saving become slow because data is sorted before it is saved and while doing so some page splits will occur and it will take its own time.


There should not be too many indexes else things will slow down heavily.


If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.


Q. What is a covering Index?


Ans. covering index, which is a form of a composite index, includes all of the columns referenced in the SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.


On the other hand, if the covering index gets too big (has too many columns), this could actually increase I/O and degrade performance. Generally, when creating covering indexes, follow these guidelines:
• If the query or queries you run using the covering index are seldom run, then the overhead of the covering index may outweigh the benefits it provides.
• The covering index should not add significantly to the size of the key. If it does, then it its use may outweigh the benefits it provides.
• The covering index must include all columns found in the SELECT list, the JOIN clause, and the WHERE clause.


www.simple-talk.com/sql/learn-sql-server...e-query-performance/


Q. Is it possible to use Order by clause in View? If yes, how?


Ans. Generally Order by clause is not allowed in a view. In case we need to use it then we need to use TOP clause.


Select top 10 * from ViewName
Order by Field

Or

Select top 100 Percent from ViewName
Order by Field

Q. Write a query that return Nth highest salary of employees.


SELECT TOP 1 UnitPrice
FROM (
SELECT TOP 12 UnitPrice
FROM Products
ORDER BY UnitPrice DESC) a
ORDER BY UnitPrice


OR


SELECT MIN(UnitPrice) FROM Products
WHERE UnitPrice IN
(SELECT TOP 12 UnitPrice FROM Products ORDER BY UnitPrice DESC)


 
Q. There are 2 tables Customers and Orders and CustomerID is primary key in Customers table and OrderID is primary key in Orders table. CustomerID is foreign key in Orders table.


Write a query to list all Customers who have ordered at least once.

Write a query to list all Customers who are have no orders.


ANS:

--Customers with at least 1 order

Select c.CustomerId, c.ContactName, Count(OrderID)
from Customers c inner join Orders d
on c.CustomerID = d.CustomerId
group by c.CustomerID, c.ContactName
having Count(*) >= 1


--Customers with no orders

select c.CustomerId, c.ContactName, Count(OrderID) NoOfOrders
from Customers c left join Orders d
on c.CustomerID = d.CustomerId
group by c.CustomerID, c.ContactName
having Count(OrderID) = 0

or

Select c.* from Customers c left join
Orders b on c.CustomerID = b.CustomerID
where b.OrderID is null


Q. There is a table Employees with fields like EmployeeID, ReportsTo and CityId. EmployeeID is Primary Key and ReportsTo as foreign Key in Employees table.


Write a query to retrieve all Employees who are Managers.

Write a query to retrieve all employees who live in same city as their managers


Ans.

--All Employees as Managers
Select a.employeeid, a.lastName from employees a inner join employees b
on a.Employeeid = b.Reportsto
group by a.EmployeeID, a.lastname
having count(*) >0

--All Employees who live in same city as their Manager
select a.EmployeeId, b.EmployeeId, a.LastName + ',' + a.FirstName, b.LastName + ',' + b.FirstName, a.City, b.City

From Employees a inner join Employees b
on a.EmployeeID = b.ReportsTo
Where a.City = b.City


Q. Write a query to identify duplicate records in a table.
2 CASES: If we have identity field and another when we don't have identity field



ANS:


create table testWithIdentity
(
ID int Identity(1,1),
Project varchar(10),
hours int,
Activity varchar(10))
Go

Insert into testWithIdentity
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST1'
go




Select * from testWithIdentity
go


Select project, hours, activity from test
group by project, hours, activity
having count(*) > 1


--This statement will work but what about ID field, let’s add ID in select statement.


Select id,project, hours, activity from test
group by id,project, hours, activity
having count(*) > 1


--This shows 0 records as duplicate.


Let’s write our query differently:


Select a.* from test a,
test b
where a.project = b.project and a.hours = b.hours
and a.activity = b.activity
and a.id < b.id


CASE 2:


create table testWithNoIdentity
(
Project varchar(10),
hours int,
Activity varchar(10))
go


Insert into testWithNoIdentity
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST1'
go


Select * from testWithNoIdentity
go


Select * from testWithNoIdentity
group by project, hours, activity
having count(*) > 1



Q. What do we call a table that has no index?


ANS: HEAP

Q. What is the data Page size (actual and available)?


ANS:

8KB = 8196 = TOTAL ROW SIZE


8060 = AVAILABLE ROW SIZE



Q. What are magic tables (hint: Triggers / OUTPUT clause)


ANS: INSERTED and DELETED also known as Special Tables



Q. What will be the output of following snippet:

Declare @val1 varchar(10)
Declare @val2 varchar(10)
Declare @val3 varchar(10)


set @val3=null
set @val2='adadasd'
set @val1=null


Select Coalsec(@val3, @val1, @val1)

The output will be NULL


If we rewrite our statement as Select Coalsec(@val3, @val1, @val2)


It will return


‘adadasd’




Q. What does NULL mean?


ANS: UNKNOWN






Q. If I have a view with definition as Select * from table and table has 3 columns. When I execute Select * from view I see 3 columns.


Now if I alter my table and add 1 more column & execute Select * from view how many columns will be returned & why?

Ans: As View is also an object and it has definition with columns retrieved. As soon as view is created, it creates entry for all fetched columns, so even if we add another field in base table and execute query as Select * from view, it will pick only those fields which are available in its metadata / sys.Columns or syscolumns table.


To update the view schema, execute


sp_refreshview ‘ViewName’


This will refresh the schema definition of the view.



Q. There are 5 records in a table with two columns i.e name and age.


Data in Name field can be anything but age are 20,30,null,40 and null.

What will be the output of following statements:

select avg(age) from table
select count(1) from table

select count(age) from table






Ans:


create table test
(name varchar(100),
age int null)
go


insert into test
select 'ax', 20
union all
select 'bw',30
union all
select 'bw1',null
union all
select 'abw',40
union all
select 'bw1a',null
go






select avg(age) from test – 30 because 20+30+40 = 90 and null columns are not counted for calculating avg, which is a numeric value


go


select count(1) from test –- 5 total records in table are 5


go


select count(age) from test –- 3 because null is excluded, so only 3 rows with number are returned.


Go


Q. How to pass Rows to stored procedure as parameter?


ANS: Use Table Value Parameter


Q. What is the difference in DEALLOCATE and CLOSE CURSOR?


Ans: Close Cursor will close the Cursor but it can be accessed again using Open Cursor. When Deallocate cursor is given, the cursor will be removed from memory. In case that cursor is required again, then we need to create fresh cursor with DECLARE statement again.


Appreciate your feedback. Will come up with more soon.
 
Thanks
 
Sudhir