Luna  Mosciski

Luna Mosciski


Using a Public Web Service to Consume SQL Server Build Numbers

Keeping SQL Server instances patched can be a time-consuming task for DBAs. In this article, Alejandro Cobar explains how he created a service in Azure that anyone can use to retrieve the build information for SQL Server.

During my time as a professional DBA, I have been responsible for quite a few SQL Server instances, all with their own set of characteristics, including different versions and patching levels, the topic of this article.

One of the primary duties that DBAs have is making sure that the SQL Server instances are patched and up to date, as Microsoft makes available all the corresponding Service Packs and/or Cumulative Updates. This responsibility, of course, applies for the on-premises deployments of SQL Server. Microsoft manages most database services for you in Azure, however, you will still be responsible for keeping Azure Virtual Machines up to date.

With this said, how can you make sure that your set of SQL Server instances are patched up to the latest available release by Microsoft? One obvious way is to manually check the current releases of SQL Server and compare that list against what you currently have, making a note of the current status of your environment and pointing out any differences. However, doing this can consume a lot of time, especially if you have quite a handful of instances to check.

I found that I wanted to know if my instances were up to date, but, eventually, I didn’t have enough time on my hands to be constantly checking for the available updates. I previously created and tried a solution that consumed and parsed a website to gather the information about the SQL Server build numbers. However, I decided to put that solution to rest because I realized that I don’t want to depend on the availability of an external site to keep things working fine for me.

After failing at many attempts to find a solution to automate this effort, I decided to build a public service that can surely help any SQL Server DBA fulfil this important duty.

Choosing the technology to build the public service

After several hours of thinking, I chose Azure (a solid decision by the way) by combining two of their “serverless” offerings, to help me reduce the overall costs. This article in no way is a deep dive into the technologies picked, so with that out of the way, let me explain why I picked Azure Serverless Functions and Azure SQL Database Serverless.

One of my first options was to spin a Virtual Machine, install a web server, a database, point a custom domain to the public IP assigned to the Virtual machine, and develop the service. However, by going this route, even if there’s no activity going on in the server, you still have to pay a minimum amount for the storage and virtual network assigned to your Virtual Machine.

With the serverless options, you can get a cost-effective and very convenient solution simply by paying for it when your stuff is really used.

Azure SQL Database Serverless

Nowadays, there’s an offering from Microsoft, for your Database-as-a-Service solution, called serverless. A convenient feature from this option is that, if your database hasn’t been used for a continuous amount of time (1hr is the minimum you can pick, up to 7 days), then it will auto-pause itself and, you guessed it, you will only be charged for the storage assigned to your database. Under normal conditions, Microsoft charges for the storage and compute resources used by your Azure SQL Database.

There is one important detail that should be kept in mind, and it is the fact that if your database is in a paused state, and a request tries to hit the database, then it will require some time (usually it’s several seconds) for it to “wake up” and serve the request. Therefore, there might be times where it seems that your service is slow, but it is very likely that it is just the database “waking up”. You can find more information here.

Azure Serverless Functions

Azure functions are an excellent option for quickly developing microservices, without worrying about the underlying infrastructure that powers them to run; hence the addition of the term serverless. It doesn’t mean that it doesn’t require a server to run behind the scenes. There are different service plans and configurations for your functions, but the convenient part for me is that there are free grants covered by month, and so far, I haven’t spent a single dime in Azure functions.

You can find more information here.

Details and usage of the public service

Before detailing the structure and usage of the service, I would like to express one important fact, and it is that, as of the time of this writing, the usage of this public service is entirely free for the end-user. I am personally financing the resources described in Azure (even if it’s a tiny bit currently) and will continue to do so for the foreseeable future unless something prevents me from doing so.

To consume the service, you have to issue an HTTP request, either through a web browser or programmatically through a script, in order to get the json response with the data. As of this writing, there is no restriction upon who can consume this service; however, this can eventually change if any maliciousness is detected, such as trying to bring the service down.

NOTE: You have to be 100% sure that the machine from where you trigger the request has internet access. It might be an obvious thing, but I have seen cases where the service seems to be failing, and it is just that extremely simple detail.

#database administration #homepage #monitor #database

What is GEEK

Buddha Community

Using a Public Web Service to Consume SQL Server Build Numbers
Cayla  Erdman

Cayla Erdman


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

Ray  Patel

Ray Patel


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


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

Michael  Hamill

Michael Hamill


Get Started with SQL Server Machine Learning Services

We know Humans learn from their past experiences. Mean while Machines follow Instructions given by Humans. But what if Human can train Machines to learn from the past data?. In simple, this is what Machine learning is !!!. SQL Server has capabilities of Machine Learning. In this article, we will discuss about the capabilities of Machine Learning in SQL Server.

#machine learning #sql server #machine learning with sql server #ml in sql server using python #python in sql server ml #sql server machine learning services

Brain  Crist

Brain Crist


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


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,

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

Cayla  Erdman

Cayla Erdman


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:

,(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]
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]
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]
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]
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]
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]
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