Voice of the DBA

Tag: SQLNewBlogger

Checking Database Compatability = #SQLNewBlogger
Recently I needed to check the compatibility level of a database and SSMS didn’t work. This is what I did in T-SQL.https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBRloggers. Here are some hints to get started.

Querying the System

I would have assumed I could use the DatabaseProperyEX() function to get this, but as I look through the list of properties, the compat level isn’t in there.

When I look through the Docs for the compat level, I find that the page for database compatibility, the page mentions that querying a DMV is the way to check this. Seems strange, but I guess that’s what you do.

Here’s the query:
```
SELECT name, compatibility_level FROM sys.databases;
```
Y0u can filter this by database name if you need it.

I needed this as I was testing SQL Server 2022, but my SSMS version (18.10), didn’t recognize level 160. I assumed that was what should have been there, but I needed this query to verify.

Now, hopefully I’ll remember that I just need to query the DMV.

SQL New Blogger

This was a quick post, really just over 5 minutes to write. It’s not super technical, but it does show that I can research something and solve a problem. And I have an alternative when my main tool doesn’t work.

Good skills to showcase on a blog.
September 21, 2022
Replacing NULLs in a Left Join–#SQLNewBlogger
I saw someone ask a question on how to replace NULL in a left join and decided to write a post. I realized this is one of those simple things that people new to SQL might not get.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

A Left Join Example

Let’s create a table of customers and orders with a few values in each. This is common, where we have customers that we might add as prospects in some CRM type system. Then we link orders to customers.

Use this code:
```
DROP TABLE IF EXISTS dbo.Customer

GO

CREATE TABLE dbo.Customer

( CustomerID INT NOT NULL IDENTITY(1,1) CONSTRAINT CustomerPK PRIMARY KEY

, CustomerName VARCHAR(20)

)

GO

INSERT dbo.Customer (CustomerName) 

VALUES 
   ('Joe'),
   ('Bob'),
   ('Sally'),
   ('Amy')

GO

DROP TABLE IF EXISTS dbo.OrderHeader

GO

CREATE TABLE dbo.OrderHeader

( OrderID INT NOT NULL IDENTITY(1,1) CONSTRAINT OrderHeaderPK PRIMARY KEY

, CustomerID INT

, OrderNote VARCHAR(100)

)

GO

INSERT dbo.OrderHeader (CustomerID, OrderNote) 

VALUES 
   (1, 'Initial Order'),
   (1, 'Re-order'),
   (3, 'Initial Order')

GO
```
Potentially, we have customers without orders. If we use an inner join, we only see customers with orders. Using the left join below, we see all customers with their corresponding orders.
```
SELECT
   c.CustomerID

, c.CustomerName

, oh.OrderID

, oh.OrderNote

FROM
   dbo.Customer AS c
   LEFT JOIN dbo.OrderHeader AS oh
     ON oh.CustomerID = c.CustomerID;

GO
```
I see these results:

This works, but really, I’d like to clean up the results to show something better.

Looking for NULLs

I can use a couple of functions to look for a NULL value in my results. Both ISNULL and COALESCE can help here. ISNULL is for a single expression and replaces NULL with value, while COALESCE works by returning the first non-NULL expression. I’ll use ISNULL here and in another post look at COALESCE.

Here’s a better query that replaces one value with a NA and another with a blank.
```
SELECT
   c.CustomerID

, c.CustomerName

, ISNULL(oh.OrderID, 0) AS OrderID

, ISNULL(oh.OrderNote, 'No orders placed') AS OrderNote

FROM
   dbo.Customer AS c
   LEFT JOIN dbo.OrderHeader AS oh
     ON oh.CustomerID = c.CustomerID;

GO
```
Here are the results. Note that I return a 0 for the OrderID. This is because the result set is a numeric, and I need these types to match.

I could also return a string if I cast all OrderIDs to strings, as shown below.
```
SELECT
   c.CustomerID

, c.CustomerName

, ISNULL(CAST(oh.OrderID AS VARCHAR(20)), 'N/A') AS OrderID

, ISNULL(oh.OrderNote, 'No orders placed') AS OrderNote

FROM
   dbo.Customer AS c
   LEFT JOIN dbo.OrderHeader AS oh
     ON oh.CustomerID = c.CustomerID;

GO
```
This produces these results.

Both cases clean up the NULL values with something that makes more sense to a person looking at the data in a report.

SQLNewBlogger

This was a post inspired by a question I saw. This is how I’d solve the issue, and decided to share that knowledge more widely, both to help others and also provide an example of where I might have a hiring manager ask me about this from noticing my blog.

This post took about 15 minutes to write. You could easily do this on your blog.
September 7, 2022
Import a CSV with a Header Row using BCP–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

I was demoing something recently and needed to show someone how to grab some data from a CSV text file. Since this was a task the person needed to do regularly, but with different files, they wanted to ensure this was programmatic from a command line call outside of SQL Server. They knew the basics of bcp, but weren’t sure how to deal with a header row.

This is actually fairly simple as you will see.

BCP Basics

I have a simple file that looks like this:
```
Time,System Production (Wh)

08/01/2022,"58875"

08/02/2022,"61260"

08/03/2022,"60866"

08/04/2022,"66395"
```
I have a basic table of this structure:
```
CREATE TABLE [dbo].[Stage](
     [ProdTime] [varchar](20) NULL,
     [ProdValue] [varchar](100) NULL

) ON [PRIMARY]

GO
```
This is just a demo import from this sample file. If I run a basic bcp command, I’d typically run this:
```
bcp dbo.stage in export.csv -S Aristotle\SQL2017 -d way0utwest -T -t "," –c
```
This runs easily, as you see below:

However, this is my data:

That’s not right. The first row is a header row, and while I can quickly and easily fix this, it’s better not to have to process this. Easier to fix this on the import.

To do this, I need to look at the bcp documentation and include a flag. The –F flag is for the first row, which I want to set to 2. If I truncate the table and run this command:
```
bcp dbo.stage in export.csv -S Aristotle\SQL2017 -d way0utwest -T -t "," -c -F 2
```
I see these results:

No header row. There are still issues with this import, but this solves one problem, which is what the SQL New Blogger post is for.

SQLNewBlogger

This is a quick example of a post that is part of my daily work. I was showing a customer this, and I had to mock something up, so I grabbed a little sample data and did that.

I spent about 15 minutes around other work getting this post written and screenshots taken. You could do this as well and show how you import data in a cleaner fashion.
August 22, 2022
Using a Regular Expression to Detect a Number–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

I had a customer recently that was looking to work with Data Masker for SQL Server and had questions about how to handle some situations. In this case, they needed to detect a number type in a field that was overloaded with multiple types of data. Here’s an example of what they had in their “string” (varchar) field. Look at the stringvalue column below:

If the string was a “nnn nnn nnnn” number value, then they wanted to change it. If it had other values, then leave it alone. This is really a query problem and a WHERE clause to structure.

One would think this is where you use ISNUMERIC() and try that. If I run this, I get zero rows back.
```
SELECT d.stringvalue
FROM dbo.ddmdemo AS d
WHERE ISNUMERIC(d.stringvalue) = 1
```
This isn’t really a number, as the sequence has spaces. What if we try this:
```
SELECT d.stringvalue
FROM dbo.ddmdemo AS d
WHERE ISNUMERIC(REPLACE(' ', '', d.stringvalue)) = 1
```
It also returns no values.

Really, this appears to really be a regular expression type of query, so I could do this, using LIKE.
```
SELECT *
FROM dbo.ddmdemo AS d
WHERE d.stringvalue LIKE '[0-9]%'
```
That, however, gives me two rows in this set of data. I see these results:

The reason is that I am matching the first character only. The argument is a pattern and using square brackets implies a single character in a range. Since there are a lot of different patterns, and the “234223 Test” matches that, I ought to be more specific.

This particular pattern from the customer is 3 numbers, space, 3 numbers, space, 4 numbers. Anything else is non matching. Since there could be trailing spaces, I’d really want this:
```
SELECT d.stringvalue
FROM dbo.ddmdemo AS d
WHERE d.stringvalue LIKE '[0-9][0-9][0-9] [0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'
```
This returns my single row. It would match any row that is of the pattern “nnn nnn nnnn” where n is a numerical value from 0-9.

There are other considerations here, and certainly this is likely to be a complex set of masking rules, but this shows a relatively simple way to detect a numerical pattern in a string.

SQL New Blogger

This was an interesting case. I initially thought LIKE and an expression, but thought maybe there was a quicker way with isnumeric(). I didn’t find one, so I explained that and then the way that did work for me.

To me, this gives someone who glances at my blog a bit of insight into how I think and what I considered. This might be how they think, or someone on their team thinks. This might get me an interview.

Write about the problems you solve and how/why you do it.
June 15, 2022