Voice of the DBA

Tag: syndicated

Book Review: 100 SQL Server Mistakes

I was approached by Manning Publications and asked to review 100 SQL Server Mistakes and How to Avoid Them. They gave me a free copy of the book (and offered a second one as well), but didn’t put any conditions on my work.

This is a preliminary review of the EAP version of the book, which is still in progress as of now. If you buy the book, you can get digital chapters as they are written and edited, as well as the final book.

This is part of a series of book reviews I’ve done on my blog. You can see them all under the book reviews tag.

100 SQL Server Mistakes

The book is designed to give you 100 things that people commonly do wrong with a SQL Server instance and/or database, and starts with mistake 0 being that people think

From there, the book goes into an explanation of the 4Cs diagrams, which are ways of representing systems. This was mildly interesting to me, though less useful when I was reading on my mobile as seeing the details of the diagrams is hard.

The first few mistakes are on standards. Naming, prefixes, using sp_, and more. I liked this as I think that having some good basics communicate information between team members, or even users of your database for reporting. There are reasons given for why each of these is a mistake, as well as example code to showcase potential issues.

Data Types are the next set of mistakes, showing common things people do when designing their data model or objects. There is also a few mistakes on database design with common mistakes that people make.

There are sections for T-SQL mistakes, including error handling as well SSIS mistakes and installation problems. Each of these is grouped together with a variety of common issues that people may run into.

The version I have of the EAP is 8 chapters, with a few more to come. Overall, this is less a what you should do, and more of a what you shouldn’t do. I like this approach. Aaron Bertrand did something similar with his Worst Practices series. Often we are stuck with certain designs, and we may not be able to implement best practices. However, we should try to avoid worst practices.

I’d even say that if you have some worst practices, don’t continue them for the sake of continuity or consistency. Start refactoring or at least improving new development.

This book is a good reference for beginner to intermediate SQL Server developers and administrators, and might even be a good gift for welcoming employees early in their SQL Server journey. Many of these would be guidelines I’d want to implement for a team.

If you’re looking for some knowledge to help you avoid producing bad code, check out this book. It doesn’t have all the answers, but it has some good thoughts on code smells and ways to correct them. It might give you inspiration to fix some code in your shop.

If you want another view, Kevin Feasel has his own thoughts.

You can pick up the book here: https://www.manning.com/books/100-sql-server-mistakes-and-how-to-avoid-them

March 25, 2024
The PASS Data Community Summit 2024 Call for Speakers

The PASS Data Community Summit call for speakers and volunteers is open. You have until April 10 to submit something, and you can do that here: Data Community Summit CFS.

I have tended to submit every other year, though I did put in one session last year, which was rejected. See, it happens to everyone and it’s no big deal. The volunteers didn’t like my session and didn’t choose it.

I submitted a couple of sessions this week, and you should as well. If you’ve every presented at a user group, SQL Saturday, or even to your team internally, take a chance. Write an abstract and send it in. If you need help, the Speakers Library here has resources for you: https://www.newstarsofdata.com/speaker-improvement/

This is a great chance to start growing your brand and improving your career.

Take a chance and submit today

March 22, 2024
A New Word: Rubatosis

rubatosis– n. the unsettling awareness of your own heartbeat, whose tenuous muscular throbbing feels less like a metronome than a nervous ditty your heart is tapping to itself, as if you casually remind the outside world, I’m here, I’m here.

I practice yoga, breathing, and often my heart and breath are calm. My resting heart rate is about 51bpm across the last year. In general, even during some exercise, I don’t think or hear my heart.

However.

There are times when it’s pounding, and at weird times. I might wake up, and my heart isn’t racing, with the rate still in the 70s, but I can literally hear the pounding in my ears. I can feel it in my chest, like it’s letting me know that it’s there.

It’s rubatosis and it’s annoying at night since the noise sometimes stops me from falling back asleep quickly.

From the Dictionary of Obscure Sorrows

March 22, 2024
SQL Compare Force Column Order in a Table
I had a client that was concerned about SQL Compare behavior when a developer adds a column to the middle of a table. I wanted to reassure them, so I wrote this post to show how SQL Compare behaves by default.

This is part of a series of posts on SQL Compare.

Making a Table Change

Let’s assume I have this table in a database:
```
CREATE TABLE [dbo].[Product]
(
[ProductID] [int] NOT NULL,
[ProductName] [varchar] (50) NULL,
[ProductDesc] [varchar] (1000) NULL,
[ProductSize] [char] (1) NULL,
[ProductWeight] [int] NULL,
[ProductColor] [varchar] (20) NULL,
[StatusID] [int] NULL
)
GO
```
I want to add a column to this table, called ProductQtyPerUnit. However, I decide to add this before that StatusID column so all my product data is together.

Note: This shouldn’t be done. Don’t worry about order of columns. Deal with that in your INSERT/SELECT statements instead.

If I do this in the SSMS designer, I’ll right click the table and select INSERT Column.

Then I can add the column, as appropriate to my table.

Before I save this, I’ll create a scripts folder and compare things. As you can see, things are in synch.

Now I’ll save the change.

SQL Compare Behavior

Now I’ll refresh my project. When I do that, I see a difference, as I should. Note that SQL Compare detects the change, and shows the new column in the middle of the table.

I’ll click Deploy and generate the deployment script. When I do that, I see the script below. Note that SQL Compare has just added a column, not rebuilt the table.

This is controlled by the Force Column Order option, which is off by default. This is the way we’d like to have the tool behave, as rebuilding tables is unnecessary.

I’ll close this dialog and then click Edit Project and select the options tab. I can search for Force and see the option is off.

to show how this works, I’ll check the checkbox and then recompare. Now when I generate the deployment script, I see this. The deployment wizard opens to this warning.

If I view this script, you can see below that this part of the script creates a new table and then renames it after data is moved and the old table dropped.

In general, you should leave this option off all the time. The physical order of columns doesn’t matter.

If you haven’t used SQL Compare from Redgate, it’s the industry standard for SQL Server schema comparison and an amazing tool. Download an eval today and give it a try.
March 20, 2024