Category: Blog

  • Declaring a Complex PK in a CREATE TABLE: #SQLNewBlogger

    Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

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

    The Scenario

    I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

    For my friend, imagine a table like this one:

    CREATE TABLE [dbo].[salaries](
         [yearID] [int] NOT NULL,
         [teamID] [varchar](3) NOT NULL,
         [lgID] [varchar](2) NOT NULL,
         [playerID] [varchar](9) NOT NULL,
         [salary] [int] NULL,
    PRIMARY KEY CLUSTERED 
    (
         [yearID] ASC,
         [teamID] ASC,
         [lgID] ASC,
         [playerID] ASC
    )
    )

    If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

    2025-01_0258

    What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

    The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:

    CREATE TABLE [dbo].[salaries](
         [yearID] [int] NOT NULL,
         [teamID] [varchar](3) NOT NULL,
         [lgID] [varchar](2) NOT NULL,
         [playerID] [varchar](9) NOT NULL,
         [salary] [int] NULL,
    CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
    (
         [yearID] ASC,
         [teamID] ASC,
         [lgID] ASC,
         [playerID] ASC
    )
    )

    If I run this, then I have a better named PK.

    2025-01_0259

    Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

    SQL New Blogger

    This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

    Write this and maybe someone asks you how to do this in an interview.

  • AIs Struggle with Family

    This post looks at a logic problem asked to an AI, and which is struggles at finding an answer. This is based on an article noting this question stumps most AIs. The article is at: https://futurism.com/logic-question-stumps-ai

    This is part of a series of experiments with AI systems.

    The Setup

    I loaded a Deepseek model to compare with my Mistral model. I did this with the following commands:

    docker run ollama
    docker run -d -v ollama:/root/.ollama -p 11434:11434 --name ollama ollama/ollama
    docker exec -it ollama ollama pull deepseek-r1:1.5b
    docker exec -d ollama ollama run deepseek-r1:1.5b
    docker run -d -p 3000:8080 --add-host=host.docker.internal:host-gateway -v ollama-webui:/app/backend/data --name ollama-webui --restart always ghcr.io/ollama-webui/ollama-webui:main

    From there, I had a website.

    The Logic Problem

    In the article, we ask a question.  I asked: >Alice has [5] brothers and she also has [3] sisters. How many sisters does Alice’s brother have?

    The response was interesting, using the Deepseek-r1:1.5b model. Here it is, with the entire response in Italics.

    First, determine the total number of children in the family by adding Alice’s brothers (5) to her sisters (3). This gives a total of 8 children.

    Next, since Alice is one of those 8 children and she has 5 brothers, subtracting Alice from the total gives the number of brothers as 4. However, this calculation leads to a discrepancy because it doesn’t align with the original information provided.

    Recognizing that there might be an error in the initial assumption, consider that Alice has 3 sisters, meaning there are at least 4 girls (including herself). Adding her 5 brothers results in 9 children, which still does not match the given numbers.

    Finally, re-evaluating the problem, it’s clear that without additional context or information, determining the exact number of sisters a brother has is not possible with the provided details.

    Solution:

    Let’s break down the problem step by step.

    1. Total Number of Children in the Family:

      Alice has:

      • 5 brothers

      • 3 sisters

      Since Alice herself is one child, the total number of children is:

      [
      \text{Total children} = \text{Number of brothers} + \text{Number of sisters} + 1 = 5 + 3 + 1 = 9
      ]

    2. Number of Brothers:

      Alice has 5 brothers in her family.

    3. Number of Sisters Alice’s Brother Has:

      Since the total number of children is 9 and there are 5 brothers, we can determine the number of sisters each brother has by subtracting the number of brothers from the total number of children:

      [
      \text{Sisters per brother} = \frac{\text{Total children}}{\text{Number of brothers}} = \frac{9}{5}
      ]

      However, this division does not result in a whole number, which suggests that the problem might be interpreted differently.

    4. Alternative Interpretation:

      Considering that Alice has 3 sisters, it implies there are at least:

      • 1 (Alice) + 3 = 4 girls

      Including her own siblings, if each brother has an equal number of sisters, we can find the exact number by dividing the total number of children among all siblings:

      [
      \text{Sisters per brother} = \frac{\text{Total children}}{\text{Number of brothers} + 1} = \frac{9}{5 + 1} = \frac{9}{6} = 1.5
      ]

      This result still doesn’t make sense in the context of having whole sisters.

    5. Conclusion:

      Given the information provided, it’s not possible to determine an exact number of sisters that each brother has because:

      • The total number of children is fixed at 9.

      • Each brother has a different number of sisters based on their position in the family tree.

      Therefore, with the current details, there isn’t enough information to definitively state how many sisters Alice’s brother has.

    \boxed{\text{Cannot determine}}

    That’s interesting, but is this a Deepseek problem? Let’s try another.

    Mistral

    Mistral is the first model I played with locally. Let’s ask it the question. This response was shorter, so I’ll add it as an image:

    2025-01_0090

    It seems the AIs don’t quite understand family, and they’re making simple mistakes in putting things together. I could see humans making this mistake, but they’d catch it quickly if someone pointed something out.

    Let’s try one more thing.

    What’s a Family?

    I then ran this prompt: In a family, if I say Alice has 2 brothers and 1 sister, how many children are in the family?

    Easy, isn’t it? If I have 3 siblings, whether brothers or sisters, then there are 4 kids. How does the AI do?

    2025-01_0094

    I suppose Alice could be male (Alice Cooper), but even in that case, the AI seems to struggle to separate out Alice from the counts. For some reason, even listing the siblings out it counts 5 rather than 4. I guess 2+2=5 (for extremely confused AIs).

    Conclusion

    This isn’t to imply the AIs aren’t useful or helpful, but rather they aren’t intelligent in a human sense, or even logical in a human sense. They are very well trained search engines that can put things together in a way that we can’t easily program. They can predict the way to approach problems with mimicry of how humans think.

    However, they don’t have common sense. They are just working with patterns that are very complex, but aren’t intelligent.

    They are useful and can help you solve problems, however, they do make mistakes. Knowing something about the problem you’re asking them to solve is important. These are tools to help us, not tools that replace us.

  • Advice I Like: Why am I doing this?

    You are never too young to wonder “Why am I still doing this?” You need to have an excellent answer – from Excellent Advice for Living

    I’d say that you’re also never too old to ask these questions either. When you realize that you’re doing something out of habit or familiarity and not for other reasons, I think it’s worth re-evaluating the situation.

    It’s very easy to get into a rut and keep doing those things, even things you don’t like, from habit, obligation, or just because.

    Don’t do that. Instead, make sure you actively choose to do things. Things you want, things that you are obligated to do, committed to, or even things that matter to you. Don’t just keep doing something for no reason.

    I’ve been posting New Words on Fridays from a book I was reading, however, a friend thought they were a little depressing. They should be as they are obscure sorrows. I like them because they make me think.

    To counter-balance those, I’m adding in thoughts on advice, mostly from Kevin Kelley’s book. You can read all these posts under the advice tag.

  • A Quick Test Data Manager Eval with My Database Backup

    I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and it was a great post. In that one, the sample database Northwind was created and used. However, Alex Yates has modified the scripts to work with backup files, and I’ll show you how easy this is in just a few minutes.

    This is part of a series of posts on TDM. Check out the tag for other posts.

    The Setup

    I’ve filtered my SSMS to only show databases with BB in the name. You can see I have none.

    2025-01_0167

    I also have a backup file of a baseball database on my d: drive. My local instance has access to this folder as I use for backups and restores in dev/test work.

    2025-01_0168

    While I can pass these parameters in, it’s easy to just change the values in the file after cloning the repo. This way it’s easy to see what’s going on.

    2025-01_0169

    That’s it, now let’s fire up PowerShell.

    Running the Eval

    When I run the file, I see it start up and report the various values. You can see that it’s set the base database name to “BB” and I should see the two databases with the suffixes created. I also see my backup path.

    2025-01_0170

    This runs and in a few minutes, I see that the databases have been created and we are ready to subset.

    2025-01_0171

    Checking SSMS with a refresh, I see the databases.

    2025-01_0172

    If I type “y”, the subsetter runs, and very quickly. This isn’t a massive database, but it is thousands of rows, which makes it easy to play with.

    2025-01_0173

    If I run counts, I see this. The left is the full restore, which has 16k records. The subset, on the right, has about 10% of that, with 1644 rows. Pretty cool. So far, this has taken less than a couple of minutes.

    2025-01_0174

    Now let’s continue to press “y” and get the classification, mapping, and masking done. Two tables were found with PII (names) and masked.

    2025-01_0177

    If I query the tables, I see the results below. Notice that not all values were moved, as the first ID in the subset is 11, but we can see IDs 11 and 22 were masked.

    2025-01_0175

    This was a very quick look at running an eval with my own database backup, not a sample db. We’ve had a few people ask to do this for their own testing, and we modified the scripts to work with backups.

    Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.

    Video Walkthrough

    Check out a video of my demoing this below: