Find NULL in any column

-- Database by Doug

-- Douglas Kline

-- May 23 2024

-- Find records that have a NULL in any field of the record

-- in other words, return all records that contain a NULL in any field

-- review:

-- NULL is an odd beast - it means "an unknown value"

-- unknown values are not equal to anything

-- unknown values are not "not equal" to anything

-- think of NULL as a system defined value that cannot be compared to anything

SELECT 1

WHERE '' = NULL

OR '' > NULL

OR '' < NULL

OR 0 = NULL

OR 0 < NULL

OR 0 > NULL

OR NULL = NULL

OR NULL <> NULL

-- let's try the special unary comparison operator, IS NULL

SELECT 1

WHERE (NULL) IS NULL

-- let's use the class Northwind Database

USE Northwind;

-- and see what's in the products table

SELECT *

FROM Products

-- notice that I've put a couple of records in so we have some

-- records with NULL values

SELECT *

FROM Products

WHERE ProductID > 77

SELECT *

FROM Products

WHERE CategoryID IS NULL

OR QuantityPerUnit IS NULL

-- results show Doug's Spicy Mustard, Doug's Bold Ketchup

-- but what if I wanted to test for a NULL in ANY column?

-- can I do this?

SELECT *

FROM Products

WHERE * IS NULL

-- kinda makes sense, but * can only be used in SELECT

-- sounds like maybe you could use the ANY operator modifier,

-- but unfortunately, ANY requires a *comparison*, e.g., =, <, >, <>, etc.

-- and NULL comparisons always return FALSE

-- and ANY operates across rows, on a single column

-- not across columns on a single row... :-(

-- here is how it would look for the Products table

SELECT *

FROM Products

WHERE ProductID IS NULL

OR ProductName IS NULL

OR SupplierID IS NULL

OR CategoryID IS NULL

OR QuantityPerUnit IS NULL

OR UnitPrice IS NULL

OR UnitsInStock IS NULL

OR UnitsOnOrder IS NULL

OR ReorderLevel IS NULL

OR Discontinued IS NULL

-- but this would be a bit tedious with hundreds of columns

-- but if my requirement is a straightforward static SQL statement,

-- I just need to find a good / fast way to build it

-- I can find a list of all columns from sys.columns

SELECT name, is_nullable

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- this would be helpful in writing my WHERE clause with lots of ORs

-- adding some string concatenation

SELECT name

+ ' IS NULL'

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- now let's put it all in one string using string_agg

SELECT STRING_AGG(

name

+ ' IS NULL',

' OR '

)

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- and to be complete, let's add the rest of the SELECT

SELECT 'SELECT * FROM Products WHERE '

+ STRING_AGG(

name

+ ' IS NULL',

' OR '

)

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- now copy and paste, and reformat

SELECT * FROM Products WHERE SupplierID IS NULL OR CategoryID IS NULL OR QuantityPerUnit IS NULL OR UnitPrice IS NULL OR UnitsInStock IS NULL OR UnitsOnOrder IS NULL OR ReorderLevel IS NULL

SELECT * FROM Products WHERE SupplierID IS NULL OR CategoryID IS NULL OR QuantityPerUnit IS NULL OR UnitPrice IS NULL OR UnitsInStock IS NULL OR UnitsOnOrder IS NULL OR ReorderLevel IS NULL

SELECT *

FROM Products

WHERE SupplierID IS NULL

OR CategoryID IS NULL

OR QuantityPerUnit IS NULL

OR UnitPrice IS NULL

OR UnitsInStock IS NULL

OR UnitsOnOrder IS NULL

OR ReorderLevel IS NULL

-- Summary

-- you need to find records that have a NULL in any column

-- you have lots of columns

-- you have to write a static SQL statement

-- use some SQL to write your statement

-- drawing the columns from sys.columns

-- Database by Doug

-- Douglas Kline

-- May 23 2024

-- Find records that have a NULL in any field of the record

Azure SQL Edge Instance on a Macbook Pro M1 with Docker

This is an update to this post from 2020.

I work on a MacBook Pro M1 with a 16" display that has been a great workhorse for me for years.  Using a containerized image of Microsoft SQL Server makes it possible to run an entire SQL Server on my aging MacBook.

As in 2020, it's very handy to have a SQL instance on your local machine. I do this kind of stuff with a local sql database instance:

• data modeling
• demonstrations
• development
• quick proof of concept
• sandbox development
• disconnected work

The main difference in 2024 versus 2020, is that I'll be using Azure SQL Edge, rather than a full version of SQL Server. For most of my uses, this is sufficient. Besides, it requires less memory and disk space. 

The particular image we'll use is published by Microsoft, and can be found here on DockerHub. This capability to run SQL Server on Macbooks comes from Microsoft's SQL Server on Linux efforts. Thank you.

Be aware that you can get a free Azure SQL database in Microsoft's Azure cloud, which might server your needs better. For me, this has a slightly different set of capabilities, and would be inconvenient with a spotty wifi connection, or while completely disconnected. (me at a coffee shop)

I did this several years ago, and used my local SQL instance extensively. I was able to get a full version of SQL Server 2019 running in about 5 minutes. I followed Bob Ward's  Take the SQL Server Mac Challenge while I was listening to him speak at SQL Intersection in Las Vegas.

If you follow these instructions, it should be very easy. It wasn't as easy for me - I found out all the ways that don't work :-) I couldn't get a full SQL Server running, and I'm fairly certain it was because of Docker not fully supporting the M1 Apple Chip, even with latest version of Docker and Rosetta 2 emulation. 

Hopefully my experience will save you some time. At the end, I'll talk about the choice of Azure SQL Edge, and its limitations.

Instructions

Here's the overview:

1. Get Docker
2. Open Terminal
3. Execute a long-ish docker command
4. Verify via Docker Desktop
5. Connect to the instance from a client in the host machine

1. Docker Installation

Head over to Docker and download the Docker Desktop for Mac. It offers both Intel and Mac chip versions of the product. I always have to think about this and look - my M1 has an Apple chip. I confirm this by clicking the Apple image in the upper left corner of my desktop and choosing About This Mac.

<brief gif demo here>

Get the stable version for whichever platform you are on. I used version 4.30.0.

Follow the straightforward instructions.

2. Open a terminal window

Open up a Mac terminal window. 

<brief gif demo here>

3. Run Docker Command

All the pieces of this command are necessary. This command fetches the container image and uses it to create a container, all in one command.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=ABCabc123" -p 1433:1433 --name sqledge --hostname sqledge -d mcr.microsoft.com/azure-sql-edge

To show what's going on, I'll break it down. Here's the basic command:

docker run mcr.microsoft.com/azure-sql-edge

This basic command will execute without errors, but will not do what we want without the other specified options. This basic command says "create a container from the image at this location". If you haven't already downloaded it, it will go fetch the image.

Here's the full command again, broken into multiple lines. Note the forward slash continuation character at the end of each line:

docker run /

   -e "ACCEPT_EULA=Y" /

   -e "MSSQL_SA_PASSWORD=ABCabc123" / 

   -p 1433:1433 /

   --name sqledge / 

   --hostname sqledge / 

   -d /

   mcr.microsoft.com/azure-sql-edge

You can see that the first and last line above contain the basic command. All the other lines are options. The "-e" options are options that are passed to the "environment", i.e., they are passed into the container. The other options are for Docker at the host level to say how we want the container set up.

-e "ACCEPT_EULA=Y"

This option indicates your acceptance of the End User License Agreement for Azure SQL Edge. You gotta accept to use it :-) Note that Docker ignores this option - it just passes it into the container.

-e "MSSQL_SA_PASSWORD=ABCabc123"

This option sets the password for the database's system administrator account. This will be how we log in to the database in the container. Choose whatever password you would like.

If you're not familiar with SQL Server, traditionally, a user with username "sa" was set up to be the "root" or "highly privileged" user of the database. In today's world with actual production servers, this is a sufficiently secure way to operate. But for a local non-production network-disconnected instance, this is sufficient. And we won't have to set up the tighter security mechanisms. Again, Docker ignores this option - it just passes it into the container.

-p 1433:1433

This option maps port 1433 inside the container to port 1433 in the host. 

Remember that there are two machines involved. The host (my Macbook running Docker), and the virtual machine (the container with Azure SQL Edge running in it.) Inside the container, Azure SQL Edge will be listening on port 1433 (that's the default port). Docker will connect the host's port 1433 to the client's port 1433. So if I connect a database client like Azure Data Studio to my Macbook's port 1433, I will be able to communicate between Azure Data Studio (in the host) and Azure SQL Edge (inside the container).

In a more complex setup, it is possible to set up multiple containers with SQL instances (or other servers) and map them at the host level with Docker to create a system of servers.

-name sqledge

This gives the container a Docker name. You can now refer to this container by name with Docker commands.

--hostname sqledge

This sets the hostname inside the container. Note that this does not change anything at the host level (in this case, my Macbook). 

You might ask, well isn't this an environment thing? Why doesn't this start with "-e"?

Well, sort of. All virtual machines will need a hostname, so Docker provides a standard way to set it. However, not all virtual machines will need an sa password. So items that are specific to a particular container image are passed as -e options.

-d

This tells Docker to run this as a "disconnected" virtual machine, which does not have a user interface or window. We won't see a window for this container on the host. It will be running in the background.

4. Verify via Docker Desktop

If you've run the command in the previous section, you should have a running container aka virtual machine based on the mcr.microsoft.com/azure-sql-edge image.

You should be able to see this listed in Docker Desktop.

And you should also be able to see it by running this command in the terminal.

docker ps

Which gives as output:

CONTAINER ID   IMAGE                              COMMAND                  CREATED STATUS       PORTS                              NAMES

79e76f16d903   mcr.microsoft.com/azure-sql-edge   "/opt/mssql/bin/perm…"   4 hours ago   Up 4 hours   1401/tcp, 0.0.0.0:1433->1433/tcp   sqledge

Which gives this info, reorganized:

CONTAINER ID 79e76f16d903

IMAGE        mcr.microsoft.com/azure-sql-edge

COMMAND      "/opt/mssql/bin/perm…" 

CREATED      4 hours ago

STATUS       Up 4 hours    

PORTS        1401/tcp, 0.0.0.0:1433->1433/tcp                         

NAMES        sqledge

5. Connect using a Database Client

I'll use Microsoft's Azure Data Studio (ADS), which runs on Windows, MacOS, and Linux. Here's the "new connection" dialog in ADS:

A couple of things to point out in this connection dialog:

• the Server has a comma between the IP and the port, i.e., localhost, 1433
• the Authentication type is SQL login, which would not be very secure for a production server
• the User name is sa
• the Password will need to be the password from the docker command. in our case "ABCabc123"
• the Server Group is a convenience for ADS, which allows you to put connections in groups
• the other entries have been left at the defaults

And here is a query that has been executed with ADS:

Just to be clear about what is going on:

• Azure Data Studio (ADS) is running on the host, my Macbook
• ADS is connected to port 1433 on the localhost IP address, localhost is my Macbook (localhost IP is typically 127.0.0.1, which works also)
• Two-way communication is mapped from localhost:1433 to/from sqledge:1433
• The SQL Server in the container is listening to/sending on port 1433

6. Shut it down

You can, of course, leave this container running in the background and use it whenever you need it. But it does continue to use memory and a bit of cpu, even when idle. 

You can stop and start the container using the Docker Desktop application. Or, you can issue these commands in a terminal window:

docker stop sqledge

docker stop sqledge

To completely remove the container from the host machine (it gets deleted from the hard drive) you can issue this command:

docker rm sqledge

Azure SQL Edge local vs SQL Server local vs Azure SQL Server 

I thought quite a bit about what I needed to do, and which setup(s) would do everything I need. Here's my thoughts on capabilities and effort with each of these setups.

My main concern with the free Azure SQL Server (managed) database is the security overhead and need for constant connectivity. It's just easier to use SQL Authentication than the authentication required by Azure/AWS. 

Here's what I like about a local Azure SQL Edge instance:

• simple authentication
• can create, start, stop, remove, clone containers easily
• can do these things: 
• all data definition language commands (create, alter, drop)
• all data manipulation language commands (SELECT, INSERT, UPDATE, etc.)
• all basic T-SQL commands
• data stream

Here's what I can't do (from this source):

• special data types such as HierarchyID, Spatial, Full Text, FileStreams, In Memory OLTP, JSON (limitations), etc.
• special features such as Replication Polybase, Snapshots, Linked Servers, CLR, High Availability, etc.
• auxiliary server product capabilities: Analysis Services, Reporting Services, etc.

Summary

I think this will serve my needs for doing basic demos, T-SQL programming, data modeling and proof-of-concept experimentation. I hope this is helpful to you!

Installing the Northwind Sample Database on your SQL Server Instance

 This post shows how to run a SQL Server Instance on about any computer using Docker Containers. Your next step might be to get a sample database into that SQL Server Instance. 

Thanks to Microsoft, you can get their sample databases as T-SQL scripts. You can use these to install these databases on whatever server you are connected to, including your "containerized" SQL Server instance.

Let's do the Northwind database. We'll use Azure Data Studio, since it works on Win/Linux/Mac.

Here are the steps:

1. Click on this link, which will open up the text of the instnwnd.sql file.
2. Copy all that code - I used ctrl-a to select it all, then ctrl-c to copy all of that into the clipboard.
3. Open up Azure Data Studio (ADS)
4. Right-click on your database server, and choose New Query
5. Paste all the code into the query window - I used ctrl-v to do the paste from the clipboard
6. Now click Run

The image above shows my docker container instance running on localhost port 1401, which is mapped to my docker container's localhost port 1433 (as explained in the prior post.)

Depending on your machine, this may take several minutes to run, and will give many output messages. Depending on the version of SQL Server that you are running, you are likely to get a few harmless error messages and warnings like "Tokenization is skipped for long lines ...".

Now right-click on your Databases folder and choose Refresh to see the Northwind database, with all its data.

You can do this same procedure with any of the sample databases available here. 

Running a SQL Server Instance in Docker

I've found it very convenient to have a local instance of SQL Server. I use it for:

• quick tests
• performance testing
• demonstrations
• development
• testing
• work around limitations of a cloud instance

This has become really easy with containers. Even if you have the ability to install SQL Server natively on the host (Windows or Linux), it is much quicker and easier to use containers. 

This means that you can run an instance of SQL Server on Windows, Linux, and Mac. 

The first time I did this, I followed Bob Ward's Take the SQL Server Mac challenge, and had a SQL instance running on my Macbook, while in the audience listening to Bob's presentation. Yep, under 5 minutes.

So here is my pared-down How-To. I've tried to do a minimal setup that should work on Win, Linux, Mac, with very little adjustment.

Here's the overview:

1. Download, install, and run Docker
2. Get to an (elevated?) command prompt
3. Run a long-ish docker command
4. Verify via Docker
5. Connect from Host with client 
6. Shut it down

1. Docker Installation

Head over to Docker and download Docker Desktop. I recommend the Stable version for whichever platform you are on:

• Docker for Windows Stable Version
• Docker for Mac Stable Version
• Docker for various Linux flavors

Follow the installation instructions for your platform. This varies slightly for the platform you are on. If you can install a program on your machine, you can do this.

2. Command Prompt

For Mac, use the Terminal Application to get a command line. Here's how.

For Windows, type cmd in the search area, and choose "Run As Administrator". Here's how. Administrator privileges are not required for everything, but use it this first time to make sure you can get it working.

3. Run Docker Command

This is a long-ish command, but all the pieces are necessary. There are various ways to do this, but i'm using a "quick" method found in Microsoft's documentation. This method both pulls the SQL Server 2019 container image, and runs it with the correct parameters. Here's the command that I used:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=123ABCabc" -p 1401:1433 --name sqlsrvr1 -h -sqlsrvr1 -d mcr.microsoft.com/mssql/server:2019-latest

Here's the code again, broken into multiple lines. Note the Windows continuation character ^ (caret) at the end of each line:

docker run -e "ACCEPT_EULA=Y" ^
  -e "SA_PASSWORD=123ABCabc" ^
  -p 1401:1433 ^
  --name sqlsrvr1 ^
  -h sqlsrvr1 ^
  -d ^
  mcr.microsoft.com/mssql/server:2019-latest

Either of the above should work via cut-and-paste, but I find it easier to read and visually verify the one-parameter-per-line version.

The basic command (without options, which won't work) is:

docker run mcr.microsoft.com/mssql/server:2019-latest

This creates a process that runs the image mcr.microsoft.com/mssql/server:2019-latest. If that image happens to be on the local machine, great. If not, it will download the image from mcr.microsoft.com.

Note the "tag" at the end of the image url: 2019-latest. Microsoft releases many container images from which you can choose. Find them all on DockerHub. With this tag, are basically saying, get the latest stable release of SQL Server 2019. 

That basic command will not create a usable sql server container. You need some additional pieces:

• -e "ACCEPT_EULA=Y"
• passes "Y" to SQL Server, indicating acceptance of the End User License Agreement (EULA)
• -e "SA_PASSWORD=123ABCabc"
• passes the "sa" user password to SQL Server
• you will log in to this server using SQL Authentication, with "sa" as the username, and "123ABCabc" as the password
• I'm using "123ABCabc" because it satisfies the password policy requirements (9 chars, upper and lower, digits"
• "sa" is short for system administrator, and is basically the root user on the server instance that you are running
• -p 1401:1433
• maps the container's port 1433 to your machine's port 1401
• SQL Server "listens" for connections on this port by default
• So if you connect to your host machine's port 1401, you will be connected to the container's port 1433
• in my situation, I am running a SQL instance on the host that is already using the default port 1433.
• --name sqlsrvr1
• this gives the container your desired name, rather than a docker-generated name
• when issuing commands on the host, you can refer to the container by this name
• -h sqlsrvr1
• this sets the HOSTNAME environment variable inside the container, rather than the docker-generated name
• when issuing commands inside the container, you can refer the system inside the container by this name
• -d
• this runs the container "detached"
• this means that the container will run in the background rather than the foreground

4. Verify via Docker 

If you've run the command in the previous section, you should have a running container based on the mcr.microsoft.com/mssql/server:2019-latest image. Run this at the command line to see it:

docker ps

This command gives a list of the running containers. You should see results something like this:

CONTAINER ID   IMAGE                                      COMMAND                CREATED        STATUS        PORTS                     NAMES
d325650811b1   mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 43 minutes ago Up 43 minutes 0.0.0.0:1401->1433/tcp sqlsrvr1

The results indicate that the container is running, with the proper name, and listening on the proper port

Verify via Connection

Now fire up a SQL client to connect to the container, and use it. I'm using Azure Data Studio (ADS), which will run on Windows, MacOS, and Linux. Here's the "new connection" dialog in ADS

Just to be clear:

• ADS is running on the host
• it is connecting to the host IP address (localhost)
• it is connecting to the host Port 1401
• Docker has mapped host port 1401 to the container's port 1433, which is the default port for SQL Server
• So ADS will be connected to the container's port 1433
• Note the Server text box has "url, port" - there's a comma between the url and the port

You should see that your container is shown in the Server Explorer on the left-hand side of the ADS window. 

6. Shut it down

You can, of course, leave this container running in the background and use it whenever you need it. But what if you want to shut it down? Or entirely get rid of the container? Here's how to do it:

docker stop sqlsrvr1

This stops the container. It will no longer be running in the background. The container is still there, and can be restarted like this:

docker start sqlsrvr1

To entirely remove the container from the host machine (it cannot be started, and will no longer take up space on your host's drive), you would issue this command on the stopped container:

docker rm sqlsrvr1

Summary

Docker can seem overwhelming at first, especially if you are not comfortable working at the command line. But if you are looking to run a local instance of SQL Server, running a Docker container is the fastest path.