Voice of the DBA

Tag: Redgate

Summarizing a Script with SQL Prompt

I have never used this feature, but someone was asking for feedback on Prompt, and I noticed this in the menu: Summarize script.

I had guessed that it might look at the code and give me some outline, which is what it does, but I wasn’t sure how it might work. I decided to try it on a few scripts.

I had a demo script for a customer, and I ran it there. I got what I expected, an ordering of various operations.

Useful in some sense. I can see I cleaned up the CREATE with the DROP. However, I could also see that easily if I looked at the script. Depending on length, this might be helpful to remind me or let me see if I’ve dropped all the code I expected.

I picked a longer script from some of the Advent of Code stuff I’ve been slowly working on. In this one, there is some looping, as it’s a looping type of problem (to me). In this case, I see something more complex.

Not a lot of information from the SELECTs, but I do see some looping. The actual code is a bunch of math changes, and I could have used SET, which might have helped here. This let’s me see that the code is more of a procedural construct, which looks like this:

What about other types of code? I looked at a CTE, which wasn’t that helpful. I can’t see the base tables here, which isn’t useful.

However, in AdventureWorks, there is a procedure that shows me a TRY CATCH. In a long set of code, this might help me make sure I’ve actually included the CATCH, among other things.

I’m not sure how useful this feature is, but it’s now in my mind to try a few times and see what I think. What is the outline and structure of my code.

If you have other ideas, I’m sure Redgate would appreciate suggestions. Otherwise, give it a try and let me know if it works.

If you don’t have SQL Prompt, it’s an amazing developer productivity tool, and we now offer it as a subscription, so you can try it for a bit longer than than evaluation without committing.

March 17, 2021
Rebuilding SQL Saturday–Picking a Board of Directors
With Redgate planning to donating the SQL Saturday brand, trademarks, and domain to a non-profit foundation, there is a need to build a group of individuals to voluntarily manage the organization. Redgate has tasked me with the initial work, and as I work through this process, I am looking to provide some transparency into how this works for now.

NOTE: This is for the initial board, as someone has to make the first decisions. I expect the board themselves to decide how future directors are chosen, with input from the community.

There are a lot of people, including myself, who have been unhappy with the way that the previous organization’s board of directors functioned. The combination of a contracted managing director and restrictive bylaws resulted in far too little transparency over the years. At least, that is my view.

Moving forward, my vision for the foundation is to not recreate an organization that mandates, but rather serves the community, in an open, transparent way. There should be a minimal budget, and no compensation for directors, and no full-time staff.

I am trying to think aloud here, as I work through this process. None of these items have been decided, and I welcome advice and thoughts from others.

I also welcome interest and nominations. If you are interested, please leave a comment, or contact me. If you think someone else deserves nomination, please contact them first and ask them to contact me. I do not want to pressure anyone to serve that might not wish to or be able to donate time.

Qualities for Directors

I want to outline a few things that I think are important in finding individuals that can serve the foundation and steer it into the future.

Transparency

One of the things that bothered me with the previous organization is that there was not enough guidance on what directors would do on a regular basis, and certainly not enough information on what they had done. To me, this means that one of the main qualities for anyone serving this foundation is that they freely and willingly share information.

My goal is that as close to 100% of debate and discussion, as well as financial information, be publicly available.

Diversity

Much of my career has been US focused, though that has really changed in the last decade. When we started SQL Saturday, we didn’t think about the world outside the US, but many events under the SQL Saturday banner have been outside the US.

We need diverse thought on the issues that organizers and events face.

This means a diversity of not only geography, but gender, race, orientation, and more. We need to understand that each of us only sees a small portion of the world, and only from our perspective. With that in mind, I aim initially aiming for a breakdown something like this:
- US – 3-4 directors (knowing I am representing Redgate, 1 of these spots is filled)
- EU – 2-3 directors
- APAC – 2-3 directors
- Rest of world – 1-2 directors
I would like to have a number of women on the board, as I truly value the different perspectives they bring. I’d also like to have someone of a different race, ethnicity, or orientation than myself.

If you know of someone that you feel fits this goal, please ask them to contact me.

Financials

I have read Steph Locke’s Lessons learnt on the PASS Board, which I think anyone who cares about this should read. She outlines a number of items, which are important in most boards. While I think that the need to fund the organization is important, when this is the main goal, this becomes a problem. Directors should have some business sense, having either run their own company, or served as a high level executive in some company, however large or small, so that they do treat financials with the appropriate importance.

The goal of this foundation is to run itself on less than US$10,000/year. Any sponsorship funds will go towards events, not expenses.

Community Drive

This foundation will exist to further SQL Saturday events, providing resources and assistance where possible. Directors should be oriented towards doing good for others by supporting education and networking, giving back to the world, and driving events forward. While they will not do the work, they will be the voice and inspiration for many through this foundation.

A director ought to be thinking: how do I get more organizers excited enough to create an event? How can we better support speakers? How do I attract and serve attendees to learn and grow their careers? How can we ensure events feel successful, whether physical or virtual, whether there are 50 or 500 attendees, whether there is one track or 12?

These are the goals of the foundation.

While I started my list thinking about people that are thoughtful, measured in their words, and giving of themselves, that was really the bar of what I was looking for. Finding other qualities outside of these are likely more important.

Candidates

I have asked a few people if they are interested, to start getting a short list together. Ultimately myself and a few at Redgate will have to make some decisions about who to choose. We’ll appoint an initial board and then let them run the foundation as they see fit, including deciding on how the next set of directors are chosen.

I haven’t named any names, as I’m not sure it’s fair, and I don’t want to put any pressure on anyone.

If you are interested in shaping the future of SQL Saturday, please let me know. I have a list, but I’m sure there are people I haven’t considered. If you think someone is a good candidate, encourage them to contact me.

Above all, remember this is a community first effort, and we’re looking for people that feel the same way.
March 15, 2021
Quick NoLock with SQL Prompt

First, please, please, please, avoid NoLock. You can lose data, or get strange results, as Jason Strate demonstrates (blog | video). Before you read further or try this, read his post and look at Kendra’s video.

I had a customer request an easy way to add NOLOCK to tables in SQL Prompt. This person wanted to be able to highlight a table and make this happen. Fortunately, this is easy in Prompt.

A snippet will allow you do this on demand. I’ll explain how.

First, open the Snippet manager from the SQL Prompt menu in SSMS.

Click “New” to create a new snippet.

When the form appears, fill it out as shown. Feel free to change the snippet code if you want. The $SELECTEDTEXT$ is the key. This allows me to have this snippet available when you highlight a table name.

Save this, and then when you highlight a table, you can have Prompt add nolock by pressing CTRL and then typing your snippet name. It will be in the popup list.

I also have an animated gif to show this:

March 10, 2021
Using Data Compare with Recent Data Only
This is a post that looks at how to compare data changes in recent data. A customer recently asked me about looking at a table, and choosing specific data to compare. In this case, the data they were looking to compare was the most recent data.

Scenario

I decided to set up a quick scenario to showcase this for the customer. I created a table that has some data:
```
CREATE TABLE [dbo].[DataWithTime](
     [myid] [int] IDENTITY(1,1) NOT NULL,
     [Mydata] [varchar](20) NULL,
     [mytime] [datetime] NULL,
  CONSTRAINT [DataWithTimePK] PRIMARY KEY CLUSTERED 

(
     [myid] ASC

)

GO

INSERT INTO dbo.DataWithTime (Mydata, mytime) 

VALUES

( 'A', N'2021-01-22T12:42:33.213' ), 

( 'B', N'2021-01-22T12:52:33.213' ), 

( 'C', N'2021-01-22T13:02:33.213' ), 

( 'D', N'2021-01-22T13:07:33.213' ), 

( 'E', N'2021-01-22T13:12:33.213' )
```
I put this in my sandbox database. I wanted a second copy of this same table, but with less data, in another database. I edited the insert statement to look like this:
```
INSERT INTO dbo.DataWithTime (Mydata, mytime) 

VALUES

( 'A', N'2021-01-20T12:42:33.213' ), 

( 'B', N'2021-01-21T12:52:33.213' ), 

( 'CC', N'2021-01-23T13:02:33.213' ), 

( 'D', N'2021-01-24T13:07:33.213' ), 

( 'EE', N'2021-01-25T13:12:33.213' ),

( 'F', N'2021-01-26T13:07:33.213' )
```
Now I have two copies of my table, with disparate data. What’s different?

Data Comparison Filters

If I open SQL Data Compare, you get the default comparison. I’ll set this up with my two test tables:

When I do the comparison, I see the differences between the tables. As you can see, I edited two rows and added one.

That’s great, and in a table of a few rows, this isn’t an issue. What if this table has a million rows? Or a billion? I don’t want to scan everything.I want to limit things.

I can, if I click “Edit Project”.

and then choose Tables and Views. I’ll see my table listed.

I can select the row with my table, DataWithTime, and then I can click the “Where clause” link in the upper right.

This pops up a dialog where I can enter a WHERE clause to be used for the table. I can set the same clause for both the source and target, or use separate ones. I’ll use the same one here.

I can click OK for this and then Compare now to re-run the project. This gives me the data compared, but without looking at any data before the 25th of Jan. Notice only two rows below instead of 3.

Am I sure this still didn’t impact my SQL Server with a large query? This works great with 5 rows, but what about 1billion? Well, I ran the XEvent Profiler while I was editing the project, and then filtered this down to the SQL tools. When I do that, I see this:

The query being issued has my WHERE clause, which filters out data at the query processing level. This doesn’t guarantee a seek or limited reads, but if I have the column indexed, then I would get an efficient a plan as I could get.

SQL Data Compare is fantastic tool for finding data differences. Comparing large volumes of data can be slow, but if you use filters, you can dramatically speed things up. If you haven’t tried SQL Data Compare, download an evaluation today and see what you think.
February 8, 2021