Voice of the DBA

Tag: syndicated

A New Word: the whipgraft delusion

whipgraft delusion – n. the phenomenon in which you catch your reflection in the mirror and get the sense that you’re peering into the eyes of a strange, as if you’re looking at a police sketch of your own face aged forward twenty years, which would imply the real you is out there somewhere, wandering the streets of your old neighborhood, still at large.

Twenty years? More like thirty.

I thought about this as I don’t really look at myself in the mirror often.I certainly see myself, putting in contacts, brushing teeth, shaving, but I don’t often look closely.

However, once in awhile I’ll look at myself and while I don’t always mentally feel or act my age, I don’t feel this old. However, if I look closely, I realize I look old. As old as my knees and back feel some days. Definitely some whipgraft delusion.

From the Dictionary of Obscure Sorrows

December 22, 2023
Creating a Scripts Folder in SQL Compare

While I was at a conference recently, someone asked me about the Scripts Folder feature in SQL Compare and how to set that up. This post just looks at how to get SQL Compare to set up a Scripts folder on a machine.

This is a part of a series of posts on SQL Compare on my blog. You can read other posts I’ve written by clicking the link.

Getting Started

I’ve got a D: drive on my machine that looks like this.

I’m going to create a folder, called “Compare Script Folders”, which you can see is empty. This is where I’ll store the scripts for various databases.

I also have a couple databases I use to test things on a local instance. You can see below my “compare1” database has a few things in it.

Now, I want to get a text copy of my schema from SQL Compare that I can use to check for changes, so let’s do that.

Creating a Scripts Folder

In order to create a scripts folder, I need to run SQL Compare. Once I do that, I see a screen like that shown below.

By default, SQL Compare looks to compare two databases. However, I can change the source. If I click the drop down next to Source, I see all of the options I have available. One of these is Scripts folder, which I’ll select.

Once I do this, my dialog changes. Now, I have the ability to compare a Scripts folder to a database. How do I get the Scripts folder? I click the Create link shown below.

I missed this a few times, so that’s part of why I wrote this post. Once I click that, I get a new dialog. This is the one that let’s me choose the way I want to create the folder.

Just like the comparison, I have a number of choices for the source. I can use various items, but in my case, I’ll choose database.

Once I do this, I enter the credentials to access the database. Note that I’ve clicked “Trust”, which is required for all modern versions of SQL Server. I have also selected the new folder I created.

Once I click Create, the engine will start to script out my database into separate files. This is the same process used when running a comparison, but in this case the results are just output to files rather than held in memory to compare with another set of objects.

Once the comparison completes, I see a new folder created below the folder that was in my dialog.

Inside this folder are all my objects, separated into different folders, which is the SQL Compare structure. This is the same structure used in SQL Source Control and Flyway.

Now I can use this as the basis for a source or target against another source or target in SQL Compare.

If you want to do this from the menu, there’s also an item in the File menu to get the Create Scripts folder dialog.

SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an evaluation today.

December 20, 2023
Using DATETRUNC–#SQLNewBlogger
I saw someone using DATETRUNC recently in some code and realized I hadn’t really looked at this function before. It’s one that was added in SQL Server 2022, though it’s been in other platforms for years.

This post looks at the basics of this function.

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

DATETRUNC

One of the challenges for years in SQL Server is dealing with dates. For years we had datetime, and we used this for everything. However, this includes dates and times. The DATE datatype was eventually added, but we have lots of legacy data that includes dates and times mixed together.

Since we don’t always want dates and times, or we want some cutoff, the DATETRUNC function was added to help us. This function takes two parameters, a datepart and a date.

The datepart is any sort of potion of a datetime value. This can be quarters, months, hours, minutes, milliseconds, etc. Of course, all as singular, not plural.

The date is any valid date type: smalldatetime, datetime, date, time, datetime2, datetimeoffset.

We use it like this:
```
SELECT GETDATE(), DATETRUNC(DAY, GETDATE())
```
That returns on my system:
```
----------------------- -----------------------
2023-12-13 18:23:00.337 2023-12-13 00:00:00.000
```
If you look, this has truncated the date at the day, replacing everything after this with zeros. In this case, the datetime output of getdate() is turned into a date value.

Another example, what if I want to get rid of seconds? I can do that easily like this:
```
SELECT GETDATE(), DATETRUNC(SECOND, GETDATE())
```
———————– ———————–
2023-12-13 18:24:19.557 2023-12-13 18:24:19.000
You can see that I have the same date and time for hours, minutes, and seconds, but I’ve gotten rid of the partial seconds.

Using This Function

This is a function, and using it in the WHERE clause (or ON) can impact performance. This often (maybe always) messes up your index usage. However, we often want to display something cleaner, and perhaps in the SELECT clause we want to just order things and show hours.

I might to show shipments during an hour and this code helps:
```
SELECT TOP 50
        o.OrderID
      , o.Customer
      , o.OrderDate
      , DATETRUNC (HOUR, o.OrderDate) AS OrdersByHour
FROM dbo.[Order] AS o
ORDER BY o.OrderDate desc;
```
770         0SW2LZ               2023-12-12 23:14:35.220 2023-12-12 23:00:00.000
830         X6SYVULIQQGMZLPN0LL 2023-12-12 23:08:22.450 2023-12-12 23:00:00.000
731         NB3                  2023-12-12 23:03:45.120 2023-12-12 23:00:00.000
883         UDPUS144L1SL1Z1KPD   2023-12-12 22:56:25.100 2023-12-12 22:00:00.000
171         M28F5EYLB            2023-12-12 22:56:07.950 2023-12-12 22:00:00.000
775         P9LET1EBNFN          2023-12-12 22:53:48.580 2023-12-12 22:00:00.000
209         S1I4Q04SUOP          2023-12-12 22:19:49.470 2023-12-12 22:00:00.000
654         5O4GBEWZZVDII        2023-12-12 22:14:53.420 2023-12-12 22:00:00.000
967         NWA9                 2023-12-12 22:06:04.400 2023-12-12 22:00:00.000
458         JYD4TZU0S35XPW3WD7   2023-12-12 22:01:14.350 2023-12-12 22:00:00.000
584         ZDQ2J348SRI6D3HW     2023-12-12 21:59:34.910 2023-12-12 21:00:00.000
718                              2023-12-12 21:54:32.740 2023-12-12 21:00:00.000
359         I4YDWI               2023-12-12 21:54:20.970 2023-12-12 21:00:00.000
If I look at these results, it’s cleaner to see the hours, and this certainly is easier than parsing our and combining years, months, days, and hours.

There are likely lots of uses for cleaning up output, or limiting input parameters to certain groups of date values. Definitely a function I can see myself using to simplify and group date data in new ways.

SQL New Blogger

This post took me about 15 minutes to write, including the mockup of some code and generating some data with SQL Data Generator. I did a basic exploration of this function, and wrote about it.

This is something you can easily do, and include your own thoughts on where you’d use this. Search your old code for DATEPART stuff and see if you can replace some complex expressions with DATETRUNC.
December 18, 2023
Friday Flyway Tips–Deploying Migrations with a Target

Recently I was working with Flyway Desktop (FWD) and helping a customer work on deploying part of their work. They weren’t sure how easy this could be, but this post follows what I showed them.

Using the ability to run Flyway commands in FWD, we can deploy some migrations and not others. This post shows how to configure this.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Picking Migrations

I’ve got a FWD project here, and you can see the migrations below. In this case, I’ve selected a target of my QA machine and we can see that I have migrations applied up to 5 and there are pending migrations from 6-8 (ignore the undo).

If I want to apply migration 6, but not 8, I can do that. First, I’ll click the Advanced settings on the right side. When I do that, I see text with a “add parameters” button.

If I click the Add parameters button, I get a drop down that is searchable.

I can start typing “ta” in here and you see matching items. “Target” is the last one and this is the parameter that you want.

The value of the target is the last migration you want to run. In this case, I can pick 6 and it will run only migration 6. If I pick 7, it will run 6 and 7.

Once I do this, I can click back (or add more parameters) and on the main screen I see that target is in blue, as a parameter added. In the command text box, I’ve highlighted this command as added to the CLI.

Note: This text is what you could run in a CI system or at a cmd/shell .prompt

When I click migrate, the command is run and I get output about which migrations ran.

If I close this, then I see 6 is successfully applied (after unchecking only show pending) and 7 and 8 are above target. In another post, I’ll explain those.

Try Flyway Desktop out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

December 15, 2023