The Bit Bucket

I’m puzzled that so few people use script projects and solutions when working with SQL Server Management Studio (SSMS).

They are easy to use. Let’s see an example:

Instead of just starting to create scripts, from the File menu, click New, then Project. You are greeted with the new Project dialog which also allows you to create a solution.

I’ve selected SQL Server Scripts as the project template. Note there is also one for Analysis Services scripts. I’ve named the project, picked a location, and chosen to create a new solution. I might choose to create a solution with a different name if it will contain multiple projects. In this case, I’m not doing that.

2018-05-07

In early versions of SQL Server, the only way to try to track dependencies between tables, procedures, functions, etc. was to use the sp_depends stored procedure. And everyone thought it lied. The real problem was that it didn’t understand partial dependencies and deferred resolution of objects. For example, it got confused if you created a procedure that mentioned a table, then later created the table.

SQL Server 2012 introduced far superior dependency views, and SQL Server Management Studio (SSMS) now shows dependencies using those views under the covers.

2018-05-04

In an earlier post, I described how I didn’t particularly like all the colors that are shown in the scroll bar now in SQL Server Management Studio (SSMS):

In that post, I described how to turn them all off, or at least how to kill off some of them. But, of course they are there for a reason. Instead of turning them all off, you might decide to make sense of what they are there for.

2018-05-03

If you aren’t aware of the SQL Server data type called sql_variant, don’t feel bad; you’re not alone. Many people who’ve worked with SQL Server for a very long time haven’t used it.

sql_variant is a very special data type. It’s the data type that you use when you need to store other data but you’re not sure what type you will need to store. Once it is stored though, it has the appropriate data type.

2018-05-02

SQL Server 2005 introduced the NEWSEQUENTIALID() function, with some fanfare. I could never see it being interesting in any way, and I still don’t.

The argument was that there were so many performance problems being caused by developers using GUIDs as primary keys in tables, and those primary keys also ended up being the clustering keys for the tables (doesn’t have to be that way but that’s the default behavior). The random order was then causing big fragmentation issues when INSERT operations were performed.

2018-05-01

We’re coming into the season for another round of SQL Saturday events.

I can’t be at all of the local ones unfortunately. (Sad to miss Sydney but won’t be around then). I’ll be presenting sessions in Brisbane, and Auckland, hopefully also in Melbourne, then delivering a number of user group sessions around Switzerland.

In Brisbane, I’ll be speaking on SQL Server Management Studio tips and tricks: http://www.sqlsaturday.com/713/eventhome.aspx

That will cover off a number of the items from our new eBook: http://ssmsbook.sqldownunder.com .

2018-05-01

In a relational database like SQL Server, transactions are the mechanism used to ensure that entire operations either complete or are rolled back. The obvious example used to be that if you transfer funds from one place to another, that both the debit and the credit need to occur, or neither occurs.

Fair enough and straightforward enough.

Computers also try to give you the illusion that you are the only one using them. Concurrent transactions are a place where that illusion breaks. While you are working in a transaction, you are potentially affecting other users of the system. So we try to manage how long transactions are held open for. The aim is to always have transactions protect what’s needed but be as short as possible.

2018-04-30

One of the discussions that comes up from time to time when working with DevOps is branching strategy.

One of the main features of Git that’s often claimed is that it’s so good at branching and merging. And indeed, it’s pretty good at that. But the bigger question is whether lots of branching is desirable in the first place.

One argument says that if you are using branches (let’s say to build features), that you really aren’t doing Continuous Integration (CI). The downside of being features in separate branches is that at some point, you’ll have to merge the code back in, and there’s probably going to be nothing automated about that. One software house that I’ve been mentoring in has a very large number of active live branches.

2018-04-27

SQL Server Management Studio (SSMS) is a highly configurable tool. One of the areas that’s often ignored but which can be quite important is color configuration.

SSMS color codes SQL scripts (and other types of files that it understands) as you type.

This is really useful but I’ve found on some systems that some of the color selections aren’t great. Here’s an example:

On many systems that I work with, the color for sys.tables in the query above is quite a fluoro green and almost unreadable. But if you don’t like this, you can change it.

2018-04-26

Having up to date statistics is critical for SQL Server choosing appropriate query plans. Poor statistics can lead to poor query plan outcomes.

Generally, SQL Server manages this well by itself. As a rough rule, most versions auto-update statistics when the number of changes is about twenty percent of a count of the number of rows in the table. (Some recent changes have slightly altered how this works but the ball park is pretty good).

2018-04-25