Tag Archives: SQL Common Interview Questions

SQL Server | Best way to check if data exists in a table ?

16 Wednesday Sep 2015

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

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Server | Best way to check if data exists in a table ?

There are some situations where you need to check if data exists in a specific table and based on the output of that check, you will perform some action. People do often write code which will force the optimizer to go with the sub optimal query plan and in the end we will face performance issues. In this post I will explain you a simple and extremely cool solution to this kind of problem. First let me just create a sample table and insert some data into it.

--

CREATE TABLE testCounts
(
	ID INT
)
GO

INSERT INTO testCounts VALUES ( 100 )
GO 10000

CREATE CLUSTERED INDEX Ix_testCounts ON testCounts(Id)
GO

--

Using above statement I have just created a table called testCounts and inserted 10000 rows into it. Now Let me just write some statement that I have seen over the years to check for existence of data inside the table.


--Method 1
DECLARE @Counter AS BIGINT = 0
SET @Counter = (SELECT COUNT(*) FROM dbo.testCounts)
If @Counter > 0
BEGIN
	PRINT 1
END 


--Method 2
If EXISTS (SELECT 1 FROM dbo.testCounts)
BEGIN
	PRINT 1
END


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

--

Now all the above methods will give the same logical result we were looking for. Check the screen shot below

Pawan Khowal - Check Data Existence

Pawan Khowal – Check Data Existence

Now comes the important question – Which one of these is the fastest. For that we will have fetch the actual execution plan.

Pawan Khowal - Check Data Existence ( Execution Plans )

Pawan Khowal – Check Data Existence ( Execution Plans )

By looking at the execution plans for all the methods we can clearly say that the Method 2 & 3 are quite good methods we have for checking data existence in a table.

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

SQL Server – COUNT(1) Vs COUNT(*) Vs COUNT(100) | Which one is fastest?

16 Wednesday Sep 2015

Posted by in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Server – COUNT(1) Vs COUNT(*) Vs COUNT(1000) | Which one is fastest?

Well I often get this question from interviewers, team mates and my friends. Still many people don’t know the answer to this question. Well they all are same. Let me just create some sample data to prove it.

--

CREATE TABLE testCounts
(
	ID INT
)
GO

INSERT INTO testCounts VALUES ( 100 )
GO 10000

--

In the above statements I have created a table and inserted 10000 rows into it. Now we have to write SQL statements to fetch total number of rows from the table. Some of them are provided below.

--

SELECT COUNT(1) FROM dbo.testCounts

SELECT COUNT(-1) FROM dbo.testCounts

SELECT COUNT(*) FROM dbo.testCounts

SELECT COUNT(100) FROM dbo.testCounts

SELECT COUNT(10001) FROM dbo.testCounts

SELECT COUNT('Pawan') FROM dbo.testCounts

--

All the above statements will give us the same result i.e. 10000 rows. Well you can just put any junk in there still it will give the proper result. Check out the screen shot below for some of the outputs.

Pawan Khowal - Output Results - 10000 Rows

Pawan Khowal – Output Results – 10000 Rows

Now comes the main question, which one of these is fastest. Well they all are same in terms of performance. All are going to perform in the same manner. Check out the execution plans for all the queries. All are giving exact same execution plan.

Pawan Khowal - Execution Plans for Counts

Pawan Khowal – Execution Plans for Counts

 

I hope now things are 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

SQL Server – What are Bags & Sets?

15 Tuesday Sep 2015

Posted by in SQL Concepts

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Server – What are Bags & Sets?

In this post I will talk about the foundation of relational model of database. The intention is to learn and understand what are sets & bags.

Well there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory).

A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title. Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345.

When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.

SQL Server technically has “bags” not “sets”. So let me just explain you some of the concept of relational theory like Sets, Bags, Tables.

1. Set – A set is an unordered collection of elements without duplicates.

2. Bags – A Bag is unordered collections of elements with duplicates. A bag is also called as MultiSets.

3. Tables – It is an associations of keys and values

In bags, the order of insertion is not important. Elements can be inserted and removed entirely at random. By using the name Bag to describe this abstract data type, the intent is to once again to suggest examples of collection that will be familiar to the user from their everyday experience. A bag of balls is a good example. 

Operations you can do with a bag are –

  • Inserting a new value
  • Removing a value
  • Testing if a value is present in the collection
  • Find number of elements in the collection

In addition, many problems require the ability to loop over the elements in the container. However, we want to be able to do this without exposing details about how the collection is organized (for example, whether it uses an array or a linked list).

A Set extends the bag in two important ways.

  • First the elements in a set must be unique;
  • Second the set adds a number of operations that combine two sets to produce a new set.
    • The union – It is the set of values that are present in either collection.
    • The intersection – It  is the set of values that appear in both collections.
    • A set difference includes values found in one set but
      not the other.

Finally, the subset test is used to determine if all the values found in one collection are also found in the second. Some implementations of a set allow elements to be repeated more than once. This is usually termed a multiset.

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

SQL Server – Logical Binding Order or Logical Query Processing Phases

15 Tuesday Sep 2015

Posted by in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Server – Logical Binding Order or Logical Query Processing Phases

Today I am going to talk about the logical binding order.  This question has been asked to me many times. Here I will try to explain the things in simple manner. Basically SQL is a Declarative language and is not fully relational. It is different from other languages like C# or VB where you have a direct relationship between what you write and how the program works internally, But here the case is different. First go through the Logical Binding Order or Logical Query Processing Phases below-

  • FROM
  • ON
  • JOIN
  • WHERE
  • GROUP BY
  • CUBE / ROLLUP
  • HAVING
  • SELECT
  • DISTINCT
  • ORDER BY
  • TOP

Also understand that this is different from how the things are executed physically or physical execution of the query. Optimizer may change your query internally to fetch the results in an effective manner. E.g. Push down the predicate to the leaf or rewrite the sub query using joins, etc. SQL Engine works row by row mode like what we have in Cursor. Basically, Data flows from RIGHT (TOP) to LEFT in the query plan. That is it data centric view of the query plan. Logic in the query plan flows from LEFT to RIGHT. This is logic centric view of the query plan.

All the operators used in the execution plan are called physical operators or Iterators. Let’s go through an example below-

Pawan Kumar Khowal - How to properly read execution plan

Pawan Kumar Khowal – How to properly read execution plan

We know that select and top operator cannot have rows from their own. They are dependent on Clustered Index scan in this case. So when the execution starts the “Select” operator asks the “Top” operator that hey do you have any rows for me, Top operator says that I don’t have rows let me ask “Clustered Index Scan”. Hey “Clustered Index scan” do you have any rows for me and “Clustered index scan” says yes I do have row for you. This goes on till the parent consumer iterator asks for rows.

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

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

10 Thursday Sep 2015

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

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Last week, I have given a technical interview for a company based out from Gurgaon. So with out further delay I will list the questions-

  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?
  2. What are Attunity Driver and why do we user in SSIS?
  3. How many type of protection level in SSIS package?
  4. Difference between Cached Report and Snapshot Report
  5. What are check point and how they work? How check works in for loop?
  6. Define below transformation in DFD?
    a. Character Map
    b. Merge vs. merge join Vs. Union ALL
    c. Cache transform
  7. How to show “No Data Found” Message to end user?
  8. Can we apply style sheet in SSRS and how?
  9. What are Partition processing options?
  10. Can we debug SSIS package. If Yes, How?
  11. 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.?
  12. How to create Temporary Table using SSIS?
  13. What are Lazy aggregations?
  14. How we can ignore failure and continue loop in SSIS?
  15. How to make connection with a FTP server?
  16. Difference between Report Server and Report Manager
  17. I need to build custom Logging for our all SSIS Packages. We want to insert some information in a table at start of the SSIS Package and then at the end we want to update the record with “Package Completed successfully” or “Failure”. What you do?
  18. What is the difference between NON EMPTY keyword and NONEMPTY() function?
  19. 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. What would you do?

Shall update the answers very soon !

Cheers, Thanks for reading !

-Pawan Khowal
MSBISkills.com