Voice of the DBA

Category: Blog

Creating a Speaking Failures Support Group

Earlier this week the emails went out to speakers who submitted to the PASS Data Community Summit 2023 conference. These were acceptances and rejections, letting people know the results of the volunteer review.

I got this one:

I had only submitted one session after being buried last year, and I assumed (we know what assume means) that this one would get picked. It wasn’t, and that’s OK.

A Speaking Failures Support Group

I know I’m not alone. I’ve had many speaking submissions rejected. From big conferences and little ones. It is disappointing, but I’ve learned not to take it personally.

I know this is a hard message to get, especially for less experienced speakers. So I decided to a) publicly disclose my rejection, and b) create a place to get support.

Since I am the editor of SQL Server Central, that’s a natural place for me to do this. I set up a forum for a Speaking Support Group, which you can use to post questions, ask for feedback on your submissions or ideas, get spell/grammar/phrasing help from others, commiserate with fellow speakers, and give them support/encouragement to try again.

Feel free to join me there, and comment on my first post, hopefully telling me to not take this too personally.

July 13, 2023
Assigning a Default Schema to a New User–#SQLNewBlogger
I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. Since this isn’t something that I (or many people) do often, I wanted to make a note about how to do this.

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

The Scenario

A user in a database needed to access certain objects, which were going to be located in a separate schema. There was a possibility that there would be objects in the new schema and in the dbo schema with the same name, so the concern was with developers writing code that might access the wrong object.

The Solution

When you add a user, this is a simple parameter as part of the CREATE USER DDL. In this case, you use the DEFAULT_SCHEMA parameter. I didn’t look this up at SQL Prompt hinted me to the WITH and the parameter.

In my case, we wanted to add a new user, which we will call APIUser and assign them to the WebAPI schema. We use this code:
```
CREATE USER APIUser FOR LOGIN APIUser WITH DEFAULT_SCHEMA=WebAPI
GO
```
Note a couple things. First, the schema name isn’t quoted. It’s treated as an identifier. Second, the WITH is used to add this parameter to the statement. Once we do this, if they user does not include a schema in an object reference, like the one below, they will still get data from the object in the WebAPI schema.
```
SELECT * FROM location
```
SQL New Blogger

This was a minor part of something else I was doing. In this case, setting up a different scenario, but I captured this slice of code, edited the names slightly, and then pasted them in here.

Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was < 10 minutes.

You can do this.
July 12, 2023
T-SQL Tuesday #164: Code that makes you feel
The invitation this month is from Erik Darling, and it’s a neat one. I like this thought, asking us to find code that impressed us or made us feel something. I tend to look at this as positive, but it could be negative.

In any case, I was on vacation from 1 Jul to 9 Jul, out of touch with the world and unwired. So I’m doing this as a quick post by the seat of my pants. I might have to come back and make a second post in the future.

Changing the way I think about T-SQL

I’m a decent T-SQL developer. Not amazing, or great even, but I am effective. I’ve learned a lot over the years and I’ve been able to get things done for my employers. I often look at other’s code and I try to improve how I view problem solving. I’ve learned a lot from Itzik and others over the years, though I have to admit that many of us solutions go over my head. I’m just not in those spaces where I need complex coded solutions very often.

That being said, years ago I got an article from Jeff Moden on the tally table. I hadn’t used this, and was fascinated. I know Itzik had written about numbers tables early on, but it hadn’t caught my attention. However, in a follow-up, Jeff wrote about a splitter function, which would use the tally table to split strings efficiently. This is the function (credit to Jeff in his article):
```
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
```
Now, note this is not the original code, but updated and improved code. I love that Jeff has maintained this.

What I found great is how Jeff approached the problem. This is limited to 8k strings, but that’s for performance reasons. One could easily enhance this to be larger if needed. I also like the tally table is quickly generated from simple code that gives us 10,000 numbers.

I also found the simplicity of the substring and the charindex to be something I think I not only understand, but could have written.

Could have. Certainly didn’t and might not have. This is great code that’s been helpful to me over the years in places where I wanted to break up code. I’ve used this in a number of demos for clients and I’ve referred people to this over the years as well.
July 11, 2023
Volunteering at Work: Teaching Others

I give a few talks on career topics, and one of these is Branding Yourself for a Dream Job. In the talk, I sometimes tell a story in the volunteering section. I wanted to summarize that here, as a way to get you to think how you can do more at work and grow your brand substantially.

A Young Worker

I was a newly hired worker on a network team at a power plant. We supported everything from the computer network to servers to desktops to sensor devices. There were hundreds of people, over 1000 nodes, and just 5 of us. Needless to say we were busy, we had to think on our feet, and we built a lot of protocols to help us.

We were the help desk and fielded a lot of calls from people, handling everything from password resets to reinstalling Windows to helping people work with various applications. I created a bunch of simple utilities (exes and batch scripts) that we kept on a share to help us solve common problems quickly. That was very helpful because we had a lot of repeated work.

There was one person, I’ll call her Sarah, that called us regularly. She was an assistant in another department dealing with supplies of some sort. She had a lot of basic questions on using WordPerfect (remember this? ) and Lotus 1-2-3, often moving information to and from places and producing reports for her boss.

We got the same questions over and over again, at least weekly if not daily. It got to the point where my entire team was so annoyed that we’d dodge her calls. This was 1991 and we had caller ID on the internal system. When she called, we’d pick up the call and immediately hand the phone to a coworker to deal with.

This wasn’t the only person that created stress for our team, but this was the worst one. We were annoyed, and she was annoyed with us. Our feelings showed through and we gave her the minimum level of service, as slow as we could.

Making a Difference

I was as annoyed as everyone, but like with my utilities, I was looking for a way to make our team work more smoothly.

At the time Windows was new and many people were intimidated with it. We had plenty of software that actually ran in a command window, like WordPerfect, while others ran as real windows (Lotus 1-2-3).

I decided to try and fix things. I walked over to a remote building where Sarah worked. I’ve been there before to help with something, and likely my annoyance showed as she wasn’t thrilled to have me at her desk unannounced.

I told her that I wanted to try and help her with some of the challenges with her position. I emphasized that a lot of people were struggling with these applications and I was trying to find ways to help them. I offered to bring her lunch and work through some things. At the time we were a remote station, and there was a Subway franchise that came in each day to do lunch. I said I’d buy sandwiches for a week.

She agreed and I took her order. That simple empathy helped disarm her anger at the computer and my group.

I bought lunch the next day and went over to Sarah’s desk. I watched her work, saw struggles, and then worked out some things to help her, created some notes she could keep around, and helped educate her.

It took a week or two, but at the end of the time, she could self-service a lot of her issues without calling us.

The Outcome

First, I reduced the workload on my group. Everyone appreciated less work, and more importantly, less annoying work.

Second, I empowered Sarah. She was able to get more work done, more quickly and felt more capable in her position. Her boss was happier with her, and I felt great.

Lastly, because she controlled supplies, I was able to get floppy disks and other small things easily when I called her. My boss couldn’t because she was annoyed with the way he and others had treated her.

The Investment

This was an investment for both of us. We both invested time, giving up our lunch hours. I think this was a week or two, but not longer. I also invested a bit of money, perhaps $5-6 a day back then, to provide lunch. Not nothing, but not a lot.

However, the investment payed off. I kept a note of this and reminded my boss at my review. I got a raise that year, not solely on this, but since I was competing with others for money, this was something that helped me stand out.

This also got me a good review from Sarah’s boss, which the overall manager of the station heard. That opened up opportunities for me as a developer position in the corporate headquarters opened up and I got recommendations from all three managers.

Look for Opportunities

These days many of us work remotely part of the time. We may not see our coworkers, and perhaps lunch isn’t easy, but we could invest time to solve something or train someone. Either online, written, or in person. A little investment to make your job (or others’) easier can pay off for the company.

It can also pay off for your personally if you document this and bring it up with your boss. It’s also a great story for interviews.

This is extra work. This is unpaid work. However, many people in many fields invest in their learning and advancement. From mechanics to doctors, people give up some of their time to grow their careers. You can do this as well.

July 10, 2023