The Challenges of Splitting a Table

Posted on September 15, 2020 by way0utwest

I ran across a discussion on Reddit about splitting a table. In this case, the original post had to do with a vertical partition of data, which is a technique that can help you better manage data in your database. However, I haven’t often seen this technique employed in the real world.

I wonder how many of you have considered a vertical partition when you are modeling data. Often we may not think about this early in the lifecycle of an entity, but as it grows, you might think about reducing the amount of data you often query in some way, and a vertical partition can help.

Is there some criteria that you might use in deciding this? Or how you can evaluate if there is a need? I once worked on a system with a very hot table, lots of queries, lots of updates against this table from our online system. In response to some requests, the developers wanted to add some columns to the table. This was important, and we needed to capture the data.

These were valid columns, but they were large in terms of data size, and not every one would always be used. This was before the option of sparse columns, so that wasn’t an option. I had no interest in an EAV table, despite the fact that it might have worked well at this scale. Instead, this was a situation where I thought a vertical partition would work. In fact, I thought a few of the other columns in this entity could be moved as well, as they were rarely queried and contained significant data.

We split the table, and performance actually improved for the main table, as it had less data. Just like an index, we had more rows on every page and less IO for range queries, and even key lookups for data that wasn’t already indexed.

There are lots of good techniques in database development for dealing with the challenges we face in data modelling and with performance. I’d urge you to learn about some of them and understand when they can be useful. I would also practice implementing them, making changes to existing tables, and learning how you can deploy them if the need arises.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

Posted in Editorial | Tagged | 2 Comments

Daily Coping 15 Aug 2020

Posted on September 15, 2020 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to avoid saying “I ought to” or “I should” to yourself.

My wife has reminded me of this for years, to avoid using “should” as it is a cognitive, or thinking, trap. I remember this every time I hear myself using should, either to others or to myself.

As a result, I’m working to either decide to do something, or to let it go. I might drop it on a list for later, but I’m trying hard to not say I should do something. Do it or don’t.

I’m better at this with home projects and chores. I don’t try to set myself up with an excuse. I’ll just tackle something or defer it with certainty.

I still have work to do here with my job. I have too many “I should do xx today” conversations with myself.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 15 Aug 2020

Adding ApplicationIntent=readonly to SQL Data Compare

Posted on September 14, 2020 by way0utwest

Recently someone asked a good question about SQL Data Compare. How can they add applicationintent to the connection?

If you are using Data Compare, and you are reading from production systems, load is a concern. With Availability Groups (AG), we can point SQL Data Compare to the secondary replicas and limit the load on the writable primary. How can we do that? Well, it’s actually easy.

We have a doc page for encrypted connections, and we can use the same type of action to work with AGs.

In a new project, I typically see something like this:

2020-09-04 12_04_43-New project_

What I can do is edit the server connection on the right. Just click in there, and then add this:

Aristotle\SQL2017;applicationintent=readonly

This will add the option in the connection string.

2020-09-04 11_51_37-New project_

If I open the project file, I will see the XML, where I can also edit these connections if desired.

2020-09-04 12_09_42-E__Documents_SQL Data Compare_SharedProjects_(local)_SQL2017.SimpleTalk_1_Dev v

I can do this for any of the options I need for connection strings.

Posted in Blog | Tagged , , | Comments Off on Adding ApplicationIntent=readonly to SQL Data Compare

Choosing a VCS

Posted on September 14, 2020 by way0utwest

As someone that speaks and promotes DevOps, I get asked for recommendations and specifics all the time for tooling. One of the questions I’ll get asked regularly is about version control. First, use it. There’s no excuse for not using version control these days, especially as most of the software out there is free.

My view is that Git is really the choice these days. Most IDEs and software tools work with git, and if they don’t, likely they don’t support version control. While there are lots of choices out there, and I’ve used a lot in my career, it seems that Git has really won and is the default choice for so many organizations. What’s interesting is so many of the surveys and tracking of version control systems tend to rank the most often used hosting services, all of which use Git.

However, does that mean you should abandon your existing TFVC, SVN, or other system for Git? I wouldn’t necessarily recommend that, but I would start learning Git and considering it for new projects. Some people love the change, others see TFVC with more complexity, and many people recommend moving away from TFS. I see similar thoughts about SVN and other VCS systems. Even this svn v git site that links to repo stats shows the stated stats of 47% of projects on SVN v 38% for git is outdated. As I write this, it’s 71% on git. I think that’s a testament to the growth from 2016 to now in Git’s popularity.

What would I choose today? Git, hands down, for any project at any company. I might live with the existing system in the short term, but I’d be thinking git, if for no other reason than future hires and staff will likely be more familiar with git than anything else. I’d move in that direction. I don’t know I’d spend time converting existing repositories to git, but if the need arose, I’d be ready to do so.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

Posted in Editorial | Tagged , | 1 Comment