Tag: SQLNewBlogger

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

I saw a post on using STRING_SPLIT() with cross apply recently, and as I was reading, I realized that I hadn’t adopted this very often in code. Mostly it’s not something I need to deal with, but recently my wife did give me some data and asked to reformat it, and I used STRING_SPLIT(). I thought it was a nice easy problem, so I decided to write about it.

Conflated Data

The dataset that she gave me was formatted something like this:

What she wanted was a report that provided data more like this:

This was more complex in the past, and hard in T-SQL as string manipulation isn’t great. However, STRING_SPLIT makes this easier. Not great, but easier.

STRING_SPLIT

While STRING_SPLIT is a function, it is a table-valued function, meaning that it is used as the source for a data set. Rather than being in the SELECT column list, we would use this in the FROM clause as another table. Or anyplace a table can be used.

The basics of this would be:

SELECT ss.value
  FROM dbo.ClassSchedule AS cs
   CROSS APPLY STRING_SPLIT(students, ',') AS ss

Using this code, I would get a list of students.

The STRING_SPLIT returns this single column, value, which is the string passed in split by the separator. I could see this more simply with this code:

SELECT * 
  FROM STRING_SPLIT('alpha,beta,delta,gamma',',') AS ss

This returns these items:

A few more examples:

The important thing to note here is that this is a simple substring based on the separator, with no other processing. Spaces or any other character outside the separator is left in the result.

There also is no ordering of the results. While this appears to be in the same order in these simple examples, this is not guaranteed, meaning the ordering of the substrings might not match the original order.

This is a good way to easily split up data that you have stuffed into a large character field, but it has limitations, so use it carefully.

SQLNewBlogger

This post took me about 15 minutes to write after spending 5 minutes setting up some test data. I was using string_split to clean up some data and decided to make a few notes, then built a new table with some strings in it to help me explain how it works.

A good example of a post you could write, perhaps noting when this was released or comparing this to a way of splitting these strings with a more complex T-SQL query.

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

I ran across the NULLIF() function recently, and I realized I’d never used it in code. It’s an interesting function, one that I didn’t think would be useful, but I found a couple places.

NULLIF Behavior

This function is essentially short for “return a null if these two values are equal.” There are two parameters you pass in and if they are equal, you get a NULL back. Somewhat strange function, but here are a few examples:

The interesting one is that 1 and NULL come back with the first value. We can’t determine if NULL is equal to 1, so we assume not.

Using This Function

When would you use this? As I said, I have never thought to use this, but I did find a couple interesting items. A mixture of NULL and a certain value is one place, if you can use the NULL. For example, let’s say I have some data in a table:

I have some blanks and some NULL values. Suppose I want to query and show the category, but if that is a NULL or blank string, show the SubCat instead. I can do this with a CASE, but that get’s ugly. NULLIF makes this easy to read.

The other interesting place I thought of here was with aggregates and potentially filtering out some values. Aggregates tend to ignore NULL, so what if I have this data:

Suppose I want the average sale, but not with the zero values. Those might be returns, and we don’t want to skew our average. I could use NULLIF to make this easy to code. Notice the short code below and the difference from the straight average:

I could use CASE, but which is easier to read?

I think NULLIF is, if you know how the function works.

SQLNewBlogger

This was a function I stumbled on and wasn’t sure how to read. I spent about 10-15 minutes searching around the Internet looking for a reason to use this code. I saved the link for them and added it into the post. I spent about 10 minutes creating a little code example and then running it.

I then wrote this post, which was about 10 minutes, mostly because I used screen shots for code, which were quick to grab and paste in.

This is a nice example of learning something, understanding how it works, and then thinking where it could be useful.

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

One of the rarely used commands for me is the WAITFOR command. This is a command that intentionally introduces a delay in the execution of your code. I sometimes use this when I need to pause code for a brief time, but I never remember how this structure works.

Hopefully this quick post helps me remember this in the future.

WAITFOR

This command does what it says; it waits for something. You have two choices here in what to wait for: a period of time or a specific time. The structure of the command is:

waitfor <type> <time>

The type can be either of the keywords TIME or DELAY. I often use DELAY, which is a period of time to wait for. When you use TIME, the execution stops until that specific time of day.

As an example, if I want to pause code for 5 seconds, I use this:

WAITFOR DELAY ‘00:00:05’

If I run this in SSMS or ADS, the query time for this will be 5 seconds. The default is ‘hh:mm’, so remember that if you want seconds, you need to include the hours and minutes.

For time, the parameter is a datetime format, so enter this as the time you would want to start code execution again.

Practical Usage

The main place I used this recently was in this code:

EXEC msdb.dbo.sp_start_job @job_name = ‘Second Job with Two Errors’, @step_name=’Fourth Step’
WAITFOR DELAY ’00:00:02′

I was testing some job tracking, and needed a job that failed. It usually runs in less than a second, but without the delay, sometimes the failure isn’t picked up from the job history table.

This is the type of place, often in testing or in some dependent process, where I want a delay.

 

SQLNewBlogger

This post too my about 10 minutes to write. I couldn’t remember how WAITFOR works, but SQL Prompt helped. As I worked through my testing, I stopped and took 10 minutes to write this up.

You can do the same thing. Show your example, and how you use it. Be creative and impress someone who might read your blog before they interview you.