Voice of the DBA

Author: way0utwest

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
The Value of Doing Something

I live on a working horse ranch. My wife boards, trains, and trims horses and has employees. Occasionally I have to help out with chores, or more often, fixing things. I was talking with a coworker recently and showing some pictures. He was asking why I do things like tap a bolt instead of hiring someone to do it.

I caught this excerpt from a book on cooking, which got me thinking. I don’t love the flow here, as it wanders between cooking, economics, politics, social justice, and more. However, the core ideas of specialization and taking on a task was what I got out of the piece.

I work for a software company, and certainly, I make much more doing that than I save by cooking a meal or fixing a bolt. I could hire someone at much less than my hourly rate to do the work. Except, I don’t get paid more for working more. Maybe if I do more sales and get a quota, but for now an extra hour of work hasn’t helped me.

Then there’s the satisfaction of getting something done. I talk and write and move bits around all day; nothing that has a tangible result. It can be satisfying but in a very different way than cooking or physically finishing a task. I actually like getting my hands dirty and seeing the results of my work. Seeing a new fence go up, a tree come down, or a repaired bolt brings about pride. I feel good when my family appreciates a tasty recipe and everyone cleans their plates. It’s a nice challenge.

At the same time, routine maintenance, like changing oil, sometimes feels like doing an expense report. Needs to be done, it helps me, but there’s nothing very exciting about it.

In the era of specialization, or even the era of automation and AI tools, it might seem that we don’t need to learn or work on anything outside of our area of expertise. I think it’s important to practice, learn, and use a variety of different skills. Even if I don’t need to write PoSh often, it is helpful to sometimes tackle work that needs doing. Each bit of new knowledge brings new understanding and appreciation for the work others do.

It also helps me gain confidence in my ability to learn and use new technologies should the requirement arise. This has also served me in past jobs where I can step in and help others or even tackle a job when they aren’t available. This is something that I find far too many people struggling with. Either because of a lack of confidence or not having enough general skills that help them troubleshoot, build, or even support the vastly changing environments in which we work.

Doing something old, new, challenging, or easy is good for all of us. Try something new, while you also appreciate and use the skills you have. Believe in your ability to learn and prove it to yourself on a regular basis.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

July 12, 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