The Bit Bucket

SSMS Tips and Tricks 1-6: Filters in Object Explorer

If you are working with databases with large numbers of objects, the contents of Object Explorer in SQL Server Management Studio can start to become a bit overwhelming. I wish it offered an option to group by schema. That would be helpful.

But you can at least filter by things like schema, when you need to work with a specific set of objects. You’ll notice that if you click on the database name, that the filter in the toolbar is grayed out, but if you click on a node below that like Tables, you can click on the toolbar filter icon. You can also right-click the node and choose to filter:

2025-05-19

SQL: Why are your linked servers so slow?

I’ve recently been reading a few articles and discussions around linked servers, and I’ve noticed two things:

  • People consider them easy to configure
  • They have a bad reputation for introducing performance issues

I do think they’re pretty easy to configure, but they’re also easy to configure badly.

And that leads us to the main cause of the second issue: performance.

I review the configuration of a lot of systems, and the primary issue I see with how linked servers are configured, is that the Server Option Collation Compatible has been left at the default value.

2025-05-18

SSMS Tips and Tricks 1-5: Extended properties for objects

I started working with SQL Server in 1992, but all through the 1980’s and 1990’s, I was also working with Progress 4GL. I thought it was the best of the character-based 4GLs but unfortunately, they did a poor job of migrating to Windows and we decided to stop using the product.

One thing that I used to love with Progress though is that the metadata for each column in the database was much richer than what is present in SQL Server. In fact, Microsoft Access was probably closer to it in that regard. It’s something I really missed when moving to SQL Server. In Progress, when I defined a column, I could also define things like:

2025-05-17

SQL Interview: 48 Enabling RCSI for a database

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administrator Level: Medium

Question:

Your database application has been suffering from signficant blocking issues.

One developer has suggested enabling RCSI for the database.

If you do that, what’s different about how the database would operate?

What code changes are needed to use it?

2025-05-16

Opinion: Why is leaving so hard?

One thing that has annoyed me for a long time is why applications and systems make it so hard to disconnect yourself from a tenant that you don’t control. Here are two examples:

Stripe Connect

The first one that’s frustrated me lately is Stripe. Setting up your own account is straightforward, and leaving isn’t too hard.

But they have a service called Stripe Connect. In this case, the account is basically set up for you by a vendor you are dealing with, essentially on your behalf.

2025-05-15

Book Review: Building Web APIs with ASP.NET Core

Over the years, one area that I have a great interest in, and yet I think gets nowhere near the right amount of attention is API design. So I was really interested to hear fellow MVP Valerio De Sanctis’ ideas in the new book Building Web APIs With ASP.NET Core .

API Design and Standards

I can’t tell you how many times I’ve had to work with poorly designed APIs. It affects me when working with data, but also affects almost every aspect of computing.

2025-05-14

SSMS Tips and Tricks 1-4: Add columns to Object Explorer Details

I’ve mentioned in an earlier article about scripting multiple objects at once, how useful the Object Explorer Details window is, and how little understood it is.

Another useful option in it, is that the displayed columns can be changed. In particular, you can add columns that would be useful. Let’s look at an example.

In Object Explorer, I’ve expanded the WideWorldImporters database and clicked on the word Tables:

Next, I hit the F7 key, and the Object Explorer Details pane opens showing this:

2025-05-13

SQL Interview: 47 Extracting hour from a datetime value

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Developer Level: Intro

Question:

You need to extract the hour component from a datetime value.

Which function should you use?

Answer:

To do this in SQL Server, you should use the DATEPART function.

2025-05-12

SSMS Tips and Tricks 1-3: Script multiple objects at once

If you want to script all the indexes on a table (or all the tables, all the stored procedures, etc, etc.), you can do that the long way by using the Generate Scripts right-click option for a database, but there’s a better way.

Let’s use the Purchasing.PurchaseOrderLines table from WideWorldImporters as an example. Here are the indexes on it:

image

The scripting options are well-known. You right-click the object, and can navigate to the scripting option:

2025-05-11

SQL Interview: 46 Self-join requirements

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Developer Level: Intro

Question:

You have a table named HumanResources.Employees. Some employees are managers.

You want to join the table to itself to join employee rows with their manager rows.

What is required when you join a table to itself, that is not required for joining separate tables?

2025-05-10