How to Use SQL Server HierarchyID Through Easy Examples

Do you still hold on to the parent/child design, or would like to try something new, like SQL Server hierarchyID? Well, it is really new because hierarchyID has been a part of SQL Server since 2008. Of course, the novelty itself is not a persuasive argument. But note that Microsoft added this feature to represent one-to-many relationships with multiple levels in a better way.

You may wonder what difference it makes and which benefits you get from using hierarchyID instead of the usual parent/child relationships. If you never explored this option, it might be surprising for you.

The truth is, I didn’t explore this option since it was released. However, when I finally did it, I found it a great innovation. It is a better-looking code, but it has much more in it. In this article, we are going to find out about all those excellent opportunities.

However, before we dive into the peculiarities of using SQL Server hierarchyID, let’s clarify its meaning and scope.

What is SQL Server HierarchyID?

SQL Server hierarchyID is a built-in data type designed to represent trees, which are the most common type of hierarchical data. Each item in a tree is called a node. In a table format, it is a row with a column of hierarchyID data type.

Usually, we demonstrate hierarchies using a table design. An ID column represents a node, and another column stands for the parent. With the SQL Server HierarchyID, we only need one column with a data type of hierarchyID.

When you query a table with a hierarchyID column, you see hexadecimal values. It is one of the visual images of a node. Another way is a string:

‘/’ stands for the root node;

‘/1/’, ‘/2/’, ‘/3/’ or ‘/n/’ stand for the children – direct descendants 1 to n;

‘/1/1/’ or ‘/1/2/’ are the “children of children – “grandchildren.” The string like ‘/1/2/’ means that the first child from the root has two children, which are, in their turn, two grandchildren of the root.

Here’s a sample of what it looks like:

Figure 1: A sample output showing nodes (1st column) and node strings (2nd column)

Unlike other data types, hierarchyID columns can take advantage of built-in methods. For example, if you have a hierarchyID column named RankNode, you can have the following syntax:

RankNode..

SQL Server HierarchyID Methods

One of the available methods is IsDescendantOf. It returns 1 if the current node is a descendant of a hierarchyID value.

You can write code with this method similar to the one below:

SELECT
 r.RankNode
,r.Rank
FROM dbo.Ranks r
WHERE r.RankNode.IsDescendantOf(0x58) = 1

Other methods used with hierarchyID are the following:

• GetRoot – the static method that returns the root of the tree.
• GetDescendant  – returns a child node of a parent.
• GetAncestor – returns a hierarchyID representing the nth ancestor of a given node.
• GetLevel – returns an integer that represents the depth of the node.
• ToString – returns the string with the logical representation of a node. ToString is called implicitly when the conversion from hierarchyID to the string type occurs.
• GetReparentedValue – moves a node from the old parent to the new parent.
• Parse – acts as the opposite of ToString. It converts the string view of a hierarchyID value to hexadecimal.

SQL Server HierarchyID Indexing Strategies

To ensure that queries for tables using hierarchyID run as fast as possible, you need to index the column. There are two indexing strategies:

DEPTH-FIRST

In a depth-first index, the subtree rows are closer to each other. It suits queries like finding a department, its subunits, and employees. Another example is a manager and its employees stored closer together.

In a table, you can implement a depth-first index by creating a clustered index for the nodes. Further, we perform one of our examples, just like that.

Figure 2: Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result set. The list is sorted based on each subtree.

BREADTH-FIRST

In a breadth-first index, the same level’s rows are closer together. It suits queries like finding all the manager’s directly reporting employees. If most of the queries are similar to this, create a clustered index based on (1) level and (2) node.

Figure 3: _Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale. _

It depends on your requirements if you need a depth-first index, a breadth-first, or both. You need to balance between the importance of the queries type and the DML statements you execute on the table.

SQL Server HierarchyID Limitations

Unfortunately, using hierarchyID can’t resolve all issues:

• SQL Server can’t guess what the child of a parent is. You have to define the tree in the table.
• If you don’t use a unique constraint, the generated hierarchyID value won’t be unique. Handling this problem is the developer’s responsibility.
• Relationships of a parent and child nodes are not enforced like a foreign key relationship. Hence, before deleting a node, query for any descendants existing.

Visualizing Hierarchies

Before we proceed, consider one more question. Looking at the result set with node strings, do you find the hierarchy visualizing hard for your eyes?

For me, it’s a big yes because I am not getting younger.

For this reason, we are going to use Power BI and Hierarchy Chart from Akvelon along with our database tables. They will help to display the hierarchy in an organizational chart. I hope it will make the job easier.

Now, let’s get down to business.

Uses of SQL Server HierarchyID

You can use HierarchyID with the following business scenarios:

• Organizational structure
• Folders, subfolders, and files
• Tasks and subtasks in a project
• Pages and subpages of a website
• Geographical data with countries, regions, and cities

Even if your business scenario is similar to the above, and you rarely query across the hierarchy sections, you don’t need hierarchyID.

For example, your organization processes payrolls for employees. Do you need to access the subtree to process someone’s payroll? Not at all. However, if you process commissions of people in a multi-level marketing system, it can be different.

In this post, we use the portion of the organizational structure and the chain of command on a cruise ship. The structure was adapted from the organizational chart from here. Take a look at it in Figure 4 below:

Figure 4: Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.

Now you can visualize the hierarchy in question. We use the below tables throughout this post:

• Vessels – is the table standing for the cruise ships’ list.
• Ranks – is the table of crew ranks. There we establish hierarchies using the hierarchyID.
• Crew – is the list of the crew of each vessel and their ranks.

The table structure of each case is as follows:

CREATE TABLE [dbo].[Vessel](
[VesselId] [int] IDENTITY(1,1) NOT NULL,
[VesselName] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Vessel] PRIMARY KEY CLUSTERED
(
[VesselId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Ranks](
[RankId] [int] IDENTITY(1,1) NOT NULL,
[Rank] [varchar](50) NOT NULL,
[RankNode] [hierarchyid] NOT NULL,
[RankLevel] [smallint] NOT NULL,
[ParentRankId] [int]   -- this is redundant but we will use this to compare        
                       -- with parent/child
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_RankId] ON [dbo].[Ranks]
(
[RankId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_RankNode] ON [dbo].[Ranks]
(
[RankNode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Crew](
[CrewId] [int] IDENTITY(1,1) NOT NULL,
[CrewName] [varchar](50) NOT NULL,
[DateHired] [date] NOT NULL,
[RankId] [int] NOT NULL,
[VesselId] [int] NOT NULL,
 CONSTRAINT [PK_Crew] PRIMARY KEY CLUSTERED
(
[CrewId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Ranks] FOREIGN KEY([RankId])
REFERENCES [dbo].[Ranks] ([RankId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Ranks]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Vessel] FOREIGN KEY([VesselId])
REFERENCES [dbo].[Vessel] ([VesselId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Vessel]
GO

#programming #sql server #how to #sql server #sql server hierarchyid #sql

What is GEEK

Buddha Community

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services

Introduction

When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information.

Python Packages

When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server,

• revoscalepy – This Microsoft Python package is used for remote compute contexts, streaming, parallel execution of rx functions for data import and transformation, modeling, visualization, and analysis.
• microsoftml – This is another Microsoft Python package which adds machine learning algorithms in Python.
• Anaconda 4.2 – Anaconda is an opensource Python package

#machine learning #sql server #executing python in sql server #machine learning using python #machine learning with sql server #ml in sql server using python #python in sql server ml #python packages #python packages for machine learning services #sql server machine learning services

How to Use SQL Server HierarchyID Through Easy Examples

Do you still hold on to the parent/child design, or would like to try something new, like SQL Server hierarchyID? Well, it is really new because hierarchyID has been a part of SQL Server since 2008. Of course, the novelty itself is not a persuasive argument. But note that Microsoft added this feature to represent one-to-many relationships with multiple levels in a better way.

You may wonder what difference it makes and which benefits you get from using hierarchyID instead of the usual parent/child relationships. If you never explored this option, it might be surprising for you.

The truth is, I didn’t explore this option since it was released. However, when I finally did it, I found it a great innovation. It is a better-looking code, but it has much more in it. In this article, we are going to find out about all those excellent opportunities.

However, before we dive into the peculiarities of using SQL Server hierarchyID, let’s clarify its meaning and scope.

What is SQL Server HierarchyID?

SQL Server hierarchyID is a built-in data type designed to represent trees, which are the most common type of hierarchical data. Each item in a tree is called a node. In a table format, it is a row with a column of hierarchyID data type.

Usually, we demonstrate hierarchies using a table design. An ID column represents a node, and another column stands for the parent. With the SQL Server HierarchyID, we only need one column with a data type of hierarchyID.

When you query a table with a hierarchyID column, you see hexadecimal values. It is one of the visual images of a node. Another way is a string:

‘/’ stands for the root node;

‘/1/’, ‘/2/’, ‘/3/’ or ‘/n/’ stand for the children – direct descendants 1 to n;

‘/1/1/’ or ‘/1/2/’ are the “children of children – “grandchildren.” The string like ‘/1/2/’ means that the first child from the root has two children, which are, in their turn, two grandchildren of the root.

Here’s a sample of what it looks like:

Figure 1: A sample output showing nodes (1st column) and node strings (2nd column)

Unlike other data types, hierarchyID columns can take advantage of built-in methods. For example, if you have a hierarchyID column named RankNode, you can have the following syntax:

RankNode..

SQL Server HierarchyID Methods

One of the available methods is IsDescendantOf. It returns 1 if the current node is a descendant of a hierarchyID value.

You can write code with this method similar to the one below:

SELECT
 r.RankNode
,r.Rank
FROM dbo.Ranks r
WHERE r.RankNode.IsDescendantOf(0x58) = 1

Other methods used with hierarchyID are the following:

• GetRoot – the static method that returns the root of the tree.
• GetDescendant  – returns a child node of a parent.
• GetAncestor – returns a hierarchyID representing the nth ancestor of a given node.
• GetLevel – returns an integer that represents the depth of the node.
• ToString – returns the string with the logical representation of a node. ToString is called implicitly when the conversion from hierarchyID to the string type occurs.
• GetReparentedValue – moves a node from the old parent to the new parent.
• Parse – acts as the opposite of ToString. It converts the string view of a hierarchyID value to hexadecimal.

SQL Server HierarchyID Indexing Strategies

To ensure that queries for tables using hierarchyID run as fast as possible, you need to index the column. There are two indexing strategies:

DEPTH-FIRST

In a depth-first index, the subtree rows are closer to each other. It suits queries like finding a department, its subunits, and employees. Another example is a manager and its employees stored closer together.

In a table, you can implement a depth-first index by creating a clustered index for the nodes. Further, we perform one of our examples, just like that.

Figure 2: Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result set. The list is sorted based on each subtree.

BREADTH-FIRST

In a breadth-first index, the same level’s rows are closer together. It suits queries like finding all the manager’s directly reporting employees. If most of the queries are similar to this, create a clustered index based on (1) level and (2) node.

Figure 3: _Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale. _

It depends on your requirements if you need a depth-first index, a breadth-first, or both. You need to balance between the importance of the queries type and the DML statements you execute on the table.

SQL Server HierarchyID Limitations

Unfortunately, using hierarchyID can’t resolve all issues:

• SQL Server can’t guess what the child of a parent is. You have to define the tree in the table.
• If you don’t use a unique constraint, the generated hierarchyID value won’t be unique. Handling this problem is the developer’s responsibility.
• Relationships of a parent and child nodes are not enforced like a foreign key relationship. Hence, before deleting a node, query for any descendants existing.

Visualizing Hierarchies

Before we proceed, consider one more question. Looking at the result set with node strings, do you find the hierarchy visualizing hard for your eyes?

For me, it’s a big yes because I am not getting younger.

For this reason, we are going to use Power BI and Hierarchy Chart from Akvelon along with our database tables. They will help to display the hierarchy in an organizational chart. I hope it will make the job easier.

Now, let’s get down to business.

Uses of SQL Server HierarchyID

You can use HierarchyID with the following business scenarios:

• Organizational structure
• Folders, subfolders, and files
• Tasks and subtasks in a project
• Pages and subpages of a website
• Geographical data with countries, regions, and cities

Even if your business scenario is similar to the above, and you rarely query across the hierarchy sections, you don’t need hierarchyID.

For example, your organization processes payrolls for employees. Do you need to access the subtree to process someone’s payroll? Not at all. However, if you process commissions of people in a multi-level marketing system, it can be different.

In this post, we use the portion of the organizational structure and the chain of command on a cruise ship. The structure was adapted from the organizational chart from here. Take a look at it in Figure 4 below:

Figure 4: Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.

Now you can visualize the hierarchy in question. We use the below tables throughout this post:

• Vessels – is the table standing for the cruise ships’ list.
• Ranks – is the table of crew ranks. There we establish hierarchies using the hierarchyID.
• Crew – is the list of the crew of each vessel and their ranks.

The table structure of each case is as follows:

CREATE TABLE [dbo].[Vessel](
[VesselId] [int] IDENTITY(1,1) NOT NULL,
[VesselName] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Vessel] PRIMARY KEY CLUSTERED
(
[VesselId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Ranks](
[RankId] [int] IDENTITY(1,1) NOT NULL,
[Rank] [varchar](50) NOT NULL,
[RankNode] [hierarchyid] NOT NULL,
[RankLevel] [smallint] NOT NULL,
[ParentRankId] [int]   -- this is redundant but we will use this to compare        
                       -- with parent/child
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_RankId] ON [dbo].[Ranks]
(
[RankId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_RankNode] ON [dbo].[Ranks]
(
[RankNode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Crew](
[CrewId] [int] IDENTITY(1,1) NOT NULL,
[CrewName] [varchar](50) NOT NULL,
[DateHired] [date] NOT NULL,
[RankId] [int] NOT NULL,
[VesselId] [int] NOT NULL,
 CONSTRAINT [PK_Crew] PRIMARY KEY CLUSTERED
(
[CrewId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Ranks] FOREIGN KEY([RankId])
REFERENCES [dbo].[Ranks] ([RankId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Ranks]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Vessel] FOREIGN KEY([VesselId])
REFERENCES [dbo].[Vessel] ([VesselId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Vessel]
GO

#programming #sql server #how to #sql server #sql server hierarchyid #sql

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

Introduction

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below,

• MS SQL Server – Zero to Hero Query Master – Part 1
• MS SQL Server – Zero to Hero Query Master – Part 2

A glimpse of previous articles

Part 1

In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.

Part 2

• We learned to create a database and maintain it using SQL statements.
• Best practice methods were also mentioned.

#sql server #benefits of schemas #create schema in sql #database schemas #how to create schema in sql server #schemas #schemas in sql server #sql server schemas #what is schema in sql server

The Easy Guide on How to Use Subqueries in SQL Server

Let’s say the chief credit and collections officer asks you to list down the names of people, their unpaid balances per month, and the current running balance and wants you to import this data array into Excel. The purpose is to analyze the data and come up with an offer making payments lighter to mitigate the effects of the COVID19 pandemic.

Do you opt to use a query and a nested subquery or a join? What decision will you make?

SQL Subqueries – What Are They?

Before we do a deep dive into syntax, performance impact, and caveats, why not define a subquery first?

In the simplest terms, a subquery is a query within a query. While a query that embodies a subquery is the outer query, we refer to a subquery as the inner query or inner select. And parentheses enclose a subquery similar to the structure below:

SELECT 
 col1
,col2
,(subquery) as col3
FROM table1
[JOIN table2 ON table1.col1 = table2.col2]
WHERE col1 <operator> (subquery)

We are going to look upon the following points in this post:

• SQL subquery syntax depending on different subquery types and operators.
• When and in what sort of statements one can use a subquery.
• Performance implications vs. JOINs.
• Common caveats when using SQL subqueries.

As is customary, we provide examples and illustrations to enhance understanding. But bear in mind that the main focus of this post is on subqueries in SQL Server.

Now, let’s get started.

Make SQL Subqueries That Are Self-Contained or Correlated

For one thing, subqueries are categorized based on their dependency on the outer query.

Let me describe what a self-contained subquery is.

Self-contained subqueries (or sometimes referred to as non-correlated or simple subqueries) are independent of the tables in the outer query. Let me illustrate this:

-- Get sales orders of customers from Southwest United States 
-- (TerritoryID = 4)

USE [AdventureWorks]
GO
SELECT CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (SELECT [CustomerID]
                     FROM [AdventureWorks].[Sales].[Customer]
                     WHERE TerritoryID = 4)

As demonstrated in the above code, the subquery (enclosed in parentheses below) has no references to any column in the outer query. Additionally, you can highlight the subquery in SQL Server Management Studio and execute it without getting any runtime errors.

Which, in turn, leads to easier debugging of self-contained subqueries.

The next thing to consider is correlated subqueries. Compared to its self-contained counterpart, this one has at least one column being referenced from the outer query. To clarify, I will provide an example:

USE [AdventureWorks]
GO
SELECT DISTINCT a.LastName, a.FirstName, b.BusinessEntityID
FROM Person.Person AS p
JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID
WHERE 1262000.00 IN
    (SELECT [SalesQuota]
    FROM Sales.SalesPersonQuotaHistory spq
    WHERE p.BusinessEntityID = spq.BusinessEntityID)

Were you attentive enough to notice the reference to BusinessEntityID from the Person table? Well done!

Once a column from the outer query is referenced in the subquery, it becomes a correlated subquery. One more point to consider: if you highlight a subquery and execute it, an error will occur.

And yes, you are absolutely right: this makes correlated subqueries pretty harder to debug.

To make debugging possible, follow these steps:

• isolate the subquery.
• replace the reference to the outer query with a constant value.

Isolating the subquery for debugging will make it look like this:

SELECT [SalesQuota]
    FROM Sales.SalesPersonQuotaHistory spq
    WHERE spq.BusinessEntityID = <constant value>

Now, let’s dig a little deeper into the output of subqueries.

Make SQL Subqueries With 3 Possible Returned Values

Well, first, let’s think of what returned values can we expect from SQL subqueries.

In fact, there are 3 possible outcomes:

• A single value
• Multiple values
• Whole tables

Single Value

Let’s start with single-valued output. This type of subquery can appear anywhere in the outer query where an expression is expected, like the WHERE clause.

-- Output a single value which is the maximum or last TransactionID
USE [AdventureWorks]
GO
SELECT TransactionID, ProductID, TransactionDate, Quantity
FROM Production.TransactionHistory
WHERE TransactionID = (SELECT MAX(t.TransactionID) 
                       FROM Production.TransactionHistory t)

When you use a MAX() function, you retrieve a single value. That’s exactly what happened to our subquery above. Using the equal (=) operator tells SQL Server that you expect a single value. Another thing: if the subquery returns multiple values using the equals (=) operator, you get an error, similar to the one below:

Msg 512, Level 16, State 1, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Multiple Values

Next, we examine the multi-valued output. This kind of subquery returns a list of values with a single column. Additionally, operators like IN and NOT IN will expect one or more values.

-- Output multiple values which is a list of customers with lastnames that --- start with 'I'

USE [AdventureWorks]
GO
SELECT [SalesOrderID], [OrderDate], [ShipDate], [CustomerID]
FROM Sales.SalesOrderHeader
WHERE [CustomerID] IN (SELECT c.[CustomerID] FROM Sales.Customer c
INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
WHERE p.lastname LIKE N'I%' AND p.PersonType='SC')

Whole Table Values

And last but not least, why not delve into whole table outputs.

-- Output a table of values based on sales orders
USE [AdventureWorks]
GO
SELECT [ShipYear],
COUNT(DISTINCT [CustomerID]) AS CustomerCount
FROM (SELECT YEAR([ShipDate]) AS [ShipYear], [CustomerID] 
      FROM Sales.SalesOrderHeader) AS Shipments
GROUP BY [ShipYear]
ORDER BY [ShipYear]

Have you noticed the FROM clause?

Instead of using a table, it used a subquery. This is called a derived table or a table subquery.

And now, let me present you some ground rules when using this sort of query:

• All columns in the subquery should have unique names. Much like a physical table, a derived table should have unique column names.
• ORDER BY is not allowed unless TOP is also specified. That’s because the derived table represents a relational table where rows have no defined order.

In this case, a derived table has the benefits of a physical table. That’s why in our example, we can use COUNT() in one of the columns of the derived table.

That’s about all regarding subquery outputs. But before we get any further, you may have noticed that the logic behind the example for multiple values and others as well can also be done using a JOIN.

-- Output multiple values which is a list of customers with lastnames that start with 'I'
USE [AdventureWorks]
GO
SELECT o.[SalesOrderID], o.[OrderDate], o.[ShipDate], o.[CustomerID]
FROM Sales.SalesOrderHeader o
INNER JOIN Sales.Customer c on o.CustomerID = c.CustomerID
INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
WHERE p.LastName LIKE N'I%' AND p.PersonType = 'SC'

In fact, the output will be the same. But which one performs better?

Before we get into that, let me tell you that I have dedicated a section to this hot topic. We’ll examine it with complete execution plans and have a look at illustrations.

So, bear with me for a moment. Let’s discuss another way to place your subqueries.

#sql server #sql query #sql server #sql subqueries #t-sql statements #sql