Voice of the DBA

Tag: T-SQL

Loading a Text File from T-SQL

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

One of the interesting things I’ve had to work on with the Advent of Code puzzles is loading files into SQL Server. Some of the inputs are large strings, but many are files with lines of code that need to be loaded into SQL Server.

I thought this might be a nice, simple SQLNewBlogger post. Do you know how to load a text file? Certainly the Import/Export wizard can work, but can you quickly load a file from T-SQL itself?

If you can’t, go work that out. If you get stuck, come back or search for help.

Loading a Text File

Obviously you need a place to load the file. I created a table for each puzzle, and here is the table for Day 2.

create table Day2_WrappingPresents
( dimensions varchar(12)
)
go

Now ordering doesn’t matter for this puzzle, so I have a very simple table. If ordering mattered, I’d have to do this differently.

To load this file, I’ll use the BULK INSERT command. This takes a table as a target, and optionally has a number of parameters. Since this is a simple load of a simple file with one column of data to a table with one column of data, I can use the defaults.

bulk insert Day2_WrappingPresents
from ‘C:\Users\Steve\Documents\GitHub\AdventofCode\Day 2 – Wrapping\input.txt’

In this case, the insert will load all 1000 rows into the table. A simple query shows this works:

Now I can get on with the rest of my puzzle solution.

SQLNewBlogger

This is a great example of a simple thing that we might not need to do often, but we may need to do at times. Knowing how to do this, a simple operation, showcases that you are improving your SQL Server skills. This post took me about 5 minutes to write.

February 1, 2016
Advent of Code–Day 2
I’ve continued working along, and while I found Day 2 to be straightforward in Python and PowerShell, I thought it was tricky in SQL I decided this one was worth a post, since I had to futz around a few times to solve it, and I managed a slightly different way than some others.

If you haven’t solved it, then try. Come back here later and compare solutions, but give it a try first.

Solution coming below, so don’t scroll if you don’t need the solution.

But first,

Missteps

I had a misstep in this one. I loaded the entire list of packages as separate lines into separate rows into a single column table. My challenge to myself was not to use ETL work to break this apart, or updates. I wanted a simple solution, thinking I didn’t want to take up extra space in the database.

As a result, I wanted a single query from a single string column that had the package size stored as one column, ‘2x3x4’ as an example.

My first attempt used the Moden Splitter function, which seemed to work well. I got three rows for each package. I then used a WIndow function to grab that data, order by the sizes, and then start performing calculations. When I didn’t get the right result, I started digging in.

One of the first things I saw was that I had multple packages with the same sizes. So I had two 22x3x1 packages, and when I used a partition based on the dimensions, I had calculation problems. That’s because the window partition doesn’t know that three rows are one package and three are another.

I could have fixed this with some other value to capture the package, maybe a row_number even, but I decided not to go down this route.

My Solution

I decided to break this down, and I used a series of CTEs to do this. I haven’t gone back to optimize things, or combine CTEs, which is possible, but instead left the CTEs as I wrote them to solve parts of the puzzle. Multiple CTEs are east, and they help examine the problem in pieces.

My first step was to parse the string. I don’t love this solution as it is limited to a three dimension package, but it does seem to be the easiest way to break down the dimensions of the package. My query looks to find the string positions for:
- end of the first dimension
- start of the second dimension
- start of the third dimension.
This gives me the simple query:

with cteSplit (d, el, sw, sh)
as
(
select
   dimensions
, endlength = charindex(‘x’, dimensions) – 1
, startwidth = charindex(‘x’, substring(dimensions, charindex(‘x’, dimensions),20)) + charindex(‘x’, dimensions)
, startheight = len(dimensions) – charindex(‘x’, reverse(dimensions)) + 2
from day2_wrappingpresents d
)

Once I had these values, a little math gives me the length, width, and height.

, cteDimensions
as
(select
   d
   , l = cast(substring(d, 1, el) as int)
   , w = cast(substring(d, sw, sh-sw-1) as int)
   , h = cast(substring(d, sh, len(d)) as int)
from cteSplit d
)

Now I’m in business. These two queries were fairly simple, despite all the nested functions. I’ve got integers with the dimensions of each package.

Now the tricky part. I want these ordered. They’re columns, not rows, and I can’t put an ORDER BY in the CTE, so I need to use some comparisons.

, cteOrder
as
( select
   d
, small = case
            when l <= w and l <= h then l
            when w <= l and w <= h then w
            when h <= l and h <= w then h
        end
, middle = case
            when (l >= w and l <= h) or (l <= w and l >= h) then l
            when (w >= l and w <= h) or (w <= l and w >= h) then w
            when (h >= l and h <= w) or (h <= l and h >= w) then h
        end
, large = case
            when l >= w and l >= h then l
            when w >= l and w >= h then w
            when h >= l and h >= w then h
        end
from cteDimensions
)

Not the prettiest code, and perhaps there are better ways to determine this, but this passed all my tests, and seemed to work.

I could have put the next part in the final query, but I decided to make this a separate CTE to easily read the math. I know some people don’t like lots of CTEs, but in this case, I think they make the query very readable. I should look back at this in six months and see what I think.

, cteFinal
as
(
select
d
, area = (2 * small * middle) +
           (2 * small * large) +
           (2 * middle * large)
, slack = (small * middle)
from cteOrder
)

Now I use a final outer query to sum things up.

select
sum(area + slack)
from cteFinal

The other thing I noticed here is that when I needed to solve the second part, I only had to change the math in the cteFinal to get the new values. It took longer to re-read the second part than to change the code and solve it.

I looked over how Wayne Sheffield and Andy Warren solved this in T-SQL, and I thought their approaches were interesting. I didn’t want to PIVOT or UNPIVOT anywhere, nor did I look at performance here. This runs so quickly, I’m not sure it matters, though I wonder if we were calculating across 1mm rows, would one be better?

I may look, but for now, I’ll leave that to someone else.
January 20, 2016
T-SQL Tuesday #74–The Changes
It’s T-SQL Tuesday time, the monthly blog party on the second Tuesday of the month. This is the first T-SQL Tuesday of 2016, and all you have to do to participate is write a blog post. It’s supposed to be today, but write one anytime you can and look for the invitation next month.

I’ve got a list of topics here, but watch the #tsql2sday tag on Twitter.

This month’s topic comes from @SLQSoldier, Robert Davis. The topic is Be the Change, and it’s a good one.

Quick Changes

I’m going to write about SQLServerCentral here. Years ago we were updating our email system to send a high volume of email in two ways. At the time we’d considered purchasing software from others, but found the cost to be significant at our volumes (5-6 figures a year). Instead we needed to handle emails stored in our SQL Server database in two ways:
- Thousands of bulk emails sent overnight, as quickly as possible
- Quick, high priority emails sent in response to actions
These two conflicting requirements meant that a simple queue of emails to send wasn’t easy for us to design around. We also needed to deal with the issues of scaling, so we wanted to have mutliple separate machines that could help spread the load. We were building a small .NET process that would run every minute and send a series of emails.

Our design process led us to the need to build in priority levels into our table. We couldn’t think of more priorities, but we allowed for them with a smallint. Our bulk emails were inserted with a priority of 2, and the registration emails, forum notes, etc, were stored with priority 1.

Once we had a separaton of emails, we needed a way to determine what was sent already. To do this, we used a NULL date for the sending date. This allowed each process to determine when new information had been inserted into the table, and needed to be processed.

This worked well for a single machine. The process would:
- query for xx priority 1 emails
- send priority 1 emails
- update sent priority 1 emails with the sent date/time.
- query for yy priority 2 emails
- send priority 2 emails
- update priority 2 emails with sent date/time.
The updates actually occurred for each email sent, so we could easily track the time/order of sends for troubleshooting purposes. We would query a few hundred emails each minute, let’s say 500, knowing that was the rate at which we could send emails. We wanted all priority 1 emails to go, so our value for yy would be 500 – xx.

As we worked to scale things out, we also needed to track what items were queried by which client. Our solution here was to add a machine name to the data, which was blank when emails were inserted, but would be updated by a client with its name as it queried rows. Since we were looking to determine which emails to send, we’d update xx rows with the name of a client process and then query back those rows. The query used the sent date of NULL with the client name to get the correct rows.

Using a combination of the date sent, the client name, and the priority, we could easily manage detecting and working with changes to this table and build a high volume queue that worked extremely well on SQL Server 2000, and all versions since.
January 12, 2016
Advent of Code Day 1, Puzzle B
As I continue through the Advent of Code, albeit slowly, I’m solving each puzzle 3 days. I worked through 6 of them in Python before moving on to other languages, and this is my chance to catch up with both PowerShell and T-SQL. I likely won’t post all solutions, but I was having fun rewriting code, so here are the ways I looked at things.

Note: You should try this on your own. I logged into the AventofCode with GitHub and things worked great for me.

Go on, give it a try.

I’ll wait.

Solutions coming.

Python

I started here, using iterations, which are very powerful in Python. In this case, I took advantage of the multiple variable assignment in Python to enumerate the array and get each value and index. I think use a comparison to determine if I add or subtract one. Finally, an IF returns the current index if I hit –1. I should probably have a break in there for efficiency as well.
```
def calculate_negative(directions):
    start = 0
    for i, c in enumerate(directions):
        if c == '(':
            start += 1
        else:
            start -= 1
        if start == -1:
            print(i)
```
PowerShell

This was a bit trickier for me. I wasn’t sure how to work with a string and pull out values. I did some searching and ran across the .ToCharArray function. That doesn’t feel like the best way to do this, but I decided to use it.

The rest of the function is similar and gave me the correct answer, so there you go.
```
$count = 0
$floor = 0
foreach ($c in $input.ToCharArray())
  { 
   if ($c -eq '(') {$floor += 1} 
   elseif ($c -eq ')') {$floor -= 1 }
   $count += 1
   if ($floor -eq -1)
     {
     $count
     break
     }
  }
```
T-SQL

A more complex situation here, given that we need to work through a string, calculating a running total. I broke the input up using a tally table and a splitter with substring. This was fairly easy, and not complex as I was just getting individual charaters.

Then it was a simple running total of the CTE to get me the totals at each point in time. This worked well, but I needed the first –1 total.

Finally, I used the outer query to get the min value out of the code and use that, which gave me the lowest value where the –1 occurred.
```
WITH tally (n)
AS
( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)        -- 10
   CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)  -- x 10 = 100
   CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)  -- x 10 = 1000
   CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) d(n)  -- x 10 = 10000
)
, direction (n, d)
AS
(
SELECT tally.n
   , d = CASE WHEN SUBSTRING(@input, n, 1) = '(' THEN 1 ELSE -1 end
 FROM tally
), currfloor 
as
(SELECT 
   d.n
   , 'currentfloor' = SUM(d.d) OVER (ORDER BY d.n ROWS UNBOUNDED PRECEDING)
FROM direction d
)
SELECT MIN(currfloor.n)
 FROM currfloor
WHERE currentfloor = -1
```
January 8, 2016