Voice of the DBA

Tag: syndicated

Foreign Keys in SQL Data Generator
A customer recently asked about using FKs in SQL Data Generator, and I decided to write a short post showing how these work.

The Scenario

I’ve got a copy of the Northwind sample database on my system, named Westwind so I can experiment. As you can see below, the Order Details table has a few FKs in it, one to Orders and one to Products.

This is a very standard setup in many databases, where there are links between tables. I’m also going to add another table with no FKs, but I want data in here from another table.
```
CREATE TABLE OrderHistory
(OrderID INT
, OrderDate DATETIME
, Complete BIT
)
GO
```
This should have an FK declared to dbo.Orders.OrderID, but as I often see, this wasn’t set up.

Using SQL Data Generator

I’ll create a new project in SQL Data Generator that points to this database. When this configures itself, I’ll deselect all tables except for Orders, Order Details, and OrderHistory.

When I do this, if I look at Order Details, I can see that in the preview, both OrderID and ProductID are listed as generated data using the keys. In this case, this means I’d get data from both the Orders and Products tables.

If I check OrderHistory below, you can see that the OrderID is set as an integer to be generated. However, that’s not what I want.

Fortunately, I can set a manual key here. If I select the Generator drop down at the top, I see there is a SQL Type generator, and one of the subtypes is a FK. We’ll pick that.

This changes the configuration and I need to pick a table and column. I’ll choose orders.

Now I can generate data. Before I do this, I check Orders and there are 840 orders in there. I’ll just generate 10 more. I’ll also generate 10 OrderHistory rows and not delete existing data. Once I generate the data, let’s look at some results.

Below, we’ve added 10 orders, which makes sense. My OrderHistory table has 10 rows, but when I join with Orders on the OrderID, I get all 10 rows back. Data generator has respected a manual, non-DRI, FK.

SQL Data Generator should detect your declared FKs, but even if you don’t have them or it doesn’t, you can add them into your generated dataset.

SQL Data Generator is part of the SQL Toolbelt, and fantastic set of productivity tools for SQL Server developers. If you’ve never used these, download an eval and give them a try today.

Video Walkthrough

I also have a video walkthrough of this post, if you’d rather see this in action.
February 26, 2024
A New Word: Proluctance

proluctance – n. the paradoxical urge to avoid doing something you’ve been looking forward to – opening a decisive letter, meeting up with a friend who’s finally back in town, reading a new book from your favorite author – perpetually waiting around for the right state of mind, stretching out the bliss of anticipation as long as you can.

Sometimes the anticipation of something, good or bad, is more impactful than the thing. I certainly can understand not wanting to take a call or open a letter because the result might not be positive, or what I wanted.

The one place I certainly see this is with books. I have a few authors that I just buy their new books, often pre-ordering them. Lee Child is one and I often pre-order his books weeks or months in advance. However, when they come, I don’t always start reading them.

Sometimes I might be in the middle of something else, but quite a few times I’ve saved the book for my next long flight. I tend to take a 4+ (or often 8+) hour flight every quarter, so I’ve saved a few books until I knew I’d be stuck in a seat. In fact, the latest one, The Secret, was one of those. It came in late Oct 2023, and I knew I had a few trips scheduled in the next few weeks, so I delayed reading this until I was headed to the PASS Summit in November.

To help build my own anticipation and feed my proluctance, I re-read a couple of his older books in the meantime

From the Dictionary of Obscure Sorrows

February 23, 2024
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
A New Word:Maugry

maugry – adj. afraid that you’ve been mentally deranged all your life and everybody around you knows, but none of them mention it to you directly because they feel it’s not their place.

I don’t know that I quite feel maugry, though I certainly sometimes wonder if I view the world much differently (and strangely) than others. I certainly think in polite society that others often won’t point out something that others do or say that is very strange.

I also like to think that often I have lots of empathy and understanding of how others view the world, though maybe that means I am actually deranged from others’ point of view and I should feel maugry.

From the Dictionary of Obscure Sorrows

February 16, 2024