Voice of the DBA

Tag: SQLNewBlogger

Adding Git LFS Support – #SQLNewBlogger
I got this message recently while committing some changes:

This post shows my work in adding Git LFS support to GitHub.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. You can see all posts on Git as well.

Downloading Git LFS

The first thing to do is follow the URL from above: https://git-lfs.com/

Once there, I clicked the download link. I also saw this message: Git LFS security update: Windows users should update to 3.1.3 or newer. I had never installed this, but there is a security issue, so if you are using this, or know someone, let them know to patch.

This is a simple install, so I ran it and the installed quickly completed. There are no choices here, just accept the EULA.

Once this is done, I followed the instructions from the site. First, I had to close and re-open my CMD window to detect this. Once I did this, I could run
```
git lfs install
```
This worked fine.

Once this was done, I changed to one of my repos where I have a large PowerPoint file. I added this for a conference that didn’t have download support, but I may do this in other repos to make it easy for attendees to see code and PPT together.

In the repo, I added lfs tracking for all PPTXs. I likely will add a few other decks in here for different conferences, so I’ll includes this as a general tracking item.

This alters the .gitattributes file, so that needs to be tracked and added to the repo. I’ll do that and commit my changes.

There you go. I’m not commited and tracking large files, or at least PPTXs with lfs support. Hopefully my laptop survives as I did this on an airplane :).

What is LFS Support?

This is an extension to git that makes storing large binary files, and diffing them, more efficient. Large files can be inefficient to transfer and store compared to small files, and many file systems, and tools, sometimes adjust the way they work to manage these differently. SQL Server did this with FileStream/FileTable, which works well for some sizes of files, but it inefficient for others. In fact, if you have smaller files (< 256kb I think), those are better just stored in varbinary columns and streamed through TDS.

The extension says this should allow you to keep your repo at a more manageable size. I’m assuming this means some sort of compression and diff storage for binaries, as opposed to complete copies of the entire file(s). This should be faster to push/pull as well.

SQL New Blogger

This post was interesting to me. As soon as I realized this was something that might improve the way git works for me, I decide to shoot some screenshots as I enabled this. I downloaded the binary and sketched a few sentences on this post before I got on a plane.

I’d saved the web page for the extension, and so I took about 15 minutes to do the install, shoot the screens, and then type up the above description.

This is s a good example of adding something to my development toolbox that might benefit my team, and make it easier for others do deal with large binary files. You can do this as well to show you’re learning and growing. Even more bonus points if you go through your IT group for permission, document some of those interactions, and show that you know how to work with other teams.

Fortunately, I don’t need to do that as this isn’t a domain managed laptop 😉
February 21, 2024
Knowing String Defaults in T-SQL–#SQLNewBlogger
For years I’ve assumed I knew the string defaults, but I realized that’s not right. This post looks at what I learned.

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

Declaring VARCHAR variables

I learned a couple things. First, this is invalid code:

The parenthesis aren’t needed, and cause an error. But if I declare just the word, I can add a string. The string in this code is more than 30 characters, which I’ve always assumed is the default length.
```
DECLARE @s VARCHAR;

SELECT @s = 'this is a test of a fairly long string'

SELECT @s
```
When I run this, however, I only get one character back.

Why is that? Well, the default length is on, according to the docs.

When is it 20? When we use CAST/CONVERT. In that case, it’s 30. Code from the docs shows this:

I’ve known this happens with CAST, but I didn’t realize the default length was 1. That’s interesting, and hopefully something no one lets slip into production when it would cause a problem.

A good lesson is to always declare your length, and don’t make that MAX if you don’t need it.

SQL New Blogger

This post took me about 10 minutes to write, once I realized the issue. I spent a few minutes grabbing links, as I’d had some of the code written once I was testing what I’d read.

You could do the same thing. Show some learning, show some code, show how you change things.
February 14, 2024
Using the T-SQL Error Functions–#SQLNewBlogger
I was working with a customer that was doing some error handling in procs and helped them do some error tracking. As we were working through things, I realized that some of functions working with errors operated differently than I expected.

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

The Error Functions

There are a number of error functions available to you in modern SQL Server. We have:
All of these functions have the same clause in their docs, which says, “ xxx returns NULL when called outside of the scope of a CATCH block.”

That was something I didn’t realize. I’d assumed I could run this:
```
SELECT 1/0

SELECT ERROR_SEVERITY(), ERROR_MESSAGE(), ERROR_STATE()
```
However, if I run this, I get the error, but my results are NULL, NULL, NULL.

If I want the values, I need to do this:
```
BEGIN TRY
   SELECT 1/0
  

END TRY

BEGIN CATCH
   SELECT ERROR_SEVERITY(), ERROR_MESSAGE(), ERROR_STATE()

END CATCH;
```
This will return my 16, Divide by zero error encountered., 1

In general, you ought to be using TRY..CATCH blocks for error handling. We do want to ensure that we are doing our best to deal with problems in code and not just expect all errors will be managed by the application. As much as possible, we should try to gracefully fail and give the application or client something useful.

Along with TRY..CATCH, learn to use THROW, and ensure you’re adding some error handling to older code. This is an easy refactoring add to existing code, and it’s simple to enhance future code to make it more maintainable.

SQL New Blogger

This is a quick look at the functions that capture error information, and noting a limitation I didn’t realize. It’s short, simple, and took me about 10 minutes.

This is one of those topics that dev managers, especially front end based ones, appreciate. Doing a post on this topic on your blog might get someone to ask you about error handling, and with a little practice (and a few posts), you’ll be able to talk about this topic confidently.
February 7, 2024
Changing the Origin in Git–#SQLNewBlogger
I needed to show a customer how to migrate from Azure DevOps to GitHub recently, and to smooth this process, we needed to repoint the origin remote.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. You can see all posts on Git as well.

Changing the Remote

When you want to see remotes, you run this code:
```
git remote –v
```
As you can see, I have my origin pointed to Azure DevOps..

Now, I can delete and add back the origin remote if I want, but there is an easier way. I’ll use the set-url parameter with this code:
```
git remote set-url origin https://github.com/way0utwest/DatabaseDevOpsMS.git
```
This points the origin to my new remote in GitHub. Once I run this, I’ll set the branch and push, as the GitHub repo is empty. This pushes everything to GitHub and voila, I’ve got code there.

You can see this online

SQL New Blogger

I had to write this up in more detail for a customer as documentation. You might do this at work for someone, but I grabbed this focused, small piece of this and created my own blog, reusing a few screenshots.

Note: Don’t reuse screenshots of company assets. Recreate this in your own space.

This took me only about 5 minutes to write up after the other work was done. You could easily do that.
January 3, 2024