Daily Archives: September 15, 2015

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

T-SQL Output Query | [ The JOIN Puzzle – Part 4 ( Join with an Empty Set ) ]

15 Tuesday Sep 2015

Posted by in Tricky SQL Queries

≈ 2 Comments

Tags

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


T-SQL Output Query | [ The JOIN Puzzle – Part 4 ( Join with an Empty Set ) ]

–Let’s say we have 2 table TestJoins5 and TestJoins6. Both the tables having only one ID column. Here you have to provide the output of the queries given below. Before that please go through the input data below.

Update – Added Outputs below – 17 Sep 2015

Pawan Kumar Khowal - Join Table 1

Pawan Kumar Khowal – Join Table 1

Pawan Kumar Khowal - Join Table 2

Pawan Kumar Khowal – Join Table 2


--

CREATE TABLE TestJoins5
(
	ID INT 
)
GO

INSERT INTO TestJoins5 VALUES (NULL),(NULL)
GO

CREATE TABLE TestJoins6
(
	ID INT 
)
GO

--Query 1
SELECT t1.ID , t2.ID FROM [dbo].[TestJoins5] t1 INNER JOIN [dbo].[TestJoins6] t2  ON t1.ID = t2.ID
 
--Query 2
SELECT t1.ID , t2.ID FROM [dbo].[TestJoins5] t1 LEFT OUTER JOIN [dbo].[TestJoins6] t2  ON t1.ID = t2.ID
 
--Query 3
SELECT t1.ID , t2.ID FROM [dbo].[TestJoins5] t1 RIGHT OUTER JOIN [dbo].[TestJoins6] t2  ON t1.ID = t2.ID
 
--Query 4
SELECT t1.ID , t2.ID FROM [dbo].[TestJoins5] t1 FULL OUTER JOIN [dbo].[TestJoins6] t2  ON t1.ID = t2.ID

--Query 5
SELECT t1.ID , t2.ID FROM [dbo].[TestJoins5] t1 CROSS JOIN [dbo].[TestJoins6] t2

--

Output of the above puzzles is given below-

Pawan Khowal - Join Outputs

Pawan Khowal – Join Outputs

Please leave a comment if you need solution to the above puzzle

Keep Learning

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

A SQL Puzzle | What is the SUM & Count of an Empty Table?

15 Tuesday Sep 2015

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

≈ 2 Comments

Tags

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


A SQL Puzzle | What is the SUM and Count of an Empty Table?

Let’s start by creating some sample data.

--

CREATE TABLE testEmpty
(
	ID INT
)
GO

SELECT SUM(ID) Sums FROM testEmpty
SELECT COUNT(*) Cnts FROM testEmpty

CREATE TABLE testEmpty1
(
	ID INT NOT NULL
)
GO

SELECT COUNT(*) Cnts FROM testEmpty1
SELECT SUM(ID) Sums FROM testEmpty1

--

If you run the above statements for both the tables, What will be the sum value and the Count value for the above queries and their reasons ?

Please add answers in the comments section !

Cheers, Keep Learning !!

Pawan Kumar Khowal

MSBISKills.com

SQL Server – Best way to Handle NULLS in Joins

15 Tuesday Sep 2015

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

≈ Leave a comment

Tags

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


SQL Server – Best way to Handle NULLS in Joins

Well today we will talk about how to handle null values while joining tables, especially in case of inner joins when you want them to match. If you see online there some methods given but none of them mentioned which one is the best. It actually depends on the kind of query and the kind of data you have in your tables. I will show you some methods you can use to join null values while joining tables.

Let’s create some sample data.

--

CREATE TABLE testJoinswithNULLs1
(
	ID INT 
)
GO

INSERT INTO testJoinswithNULLs1 VALUES ( NULL )

CREATE CLUSTERED INDEX Ix_Id ON testJoinswithNULLs1(ID) 

CREATE TABLE testJoinswithNULLs2
(
	ID INT 
)
GO

INSERT INTO testJoinswithNULLs2 VALUES ( NULL )
GO 1000

INSERT INTO testJoinswithNULLs2 VALUES (1),(2),(3),(4),(5)

CREATE CLUSTERED INDEX Ix_Id ON testJoinswithNULLs2(ID)

--

Here both the tables have clustered keys associated with it. In first table we have a single null value and in the second table we have 1000 null values.

Method 1. Use of ISNULL function – Here we just capturing the NULL value using ISNULL() function @ run-time and replacing it by -1, -1 is just and example, You can use any value you want. The value you select should not be present in the table you are joining.

--

SELECT 
	a.ID , b.ID 
FROM 
	testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b
	ON ISNULL(a.ID,-1) = ISNULL(b.ID,-1)
GO

--

Method 2. Use of COALESCE function – Here we just capturing the NULL value using COALESCE() function @ run-time and replacing it by -1, -1 is just and example, You can use any value you want. The value you select should not be present in the table you are joining.

--


SELECT 
	a.ID , b.ID 
FROM 
	testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b
	ON COALESCE(a.ID,-1) = COALESCE(b.ID,-1)
GO

--

Method 3. Check for NULL while Joining – Here we are checking for NULL Values using OR condition

--

SELECT 
	a.ID , b.ID 
FROM 
	testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b
	ON a.ID = a.ID OR (a.ID IS NULL AND b.ID IS NULL)
GO

--

Now let’s check out the execution plans for all the three methods.

Pawan Khowal - Nulls & Joins

Pawan Khowal – Nulls & Joins

Now I know that the statistics are up to date, hence we can trust the cost. It is clearly evident that the last method is the best one we have. So choose wisely.:)

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com