The Bit Bucket

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) shows dependencies using those views under the covers.

2025-05-27

I decided I’d like to have a greater understanding of some medical topics. One of the areas I’ve been looking into is Cardiology.

I was looking around to find some books that explain it and I stumbled upon A Concise Summary of Cardiology by Kevin Carson .

For a tough topic, I was surprised that the book was so short.

The problem that I found with the book is that it’s not really for me. Instead of explaining the concepts, it’s really a how to guide for people who are studying for work in cardiology.

2025-05-26

I don’t use the table designer in SQL Server Management Studio. Sorry, but I just don’t like it, or the options that it chooses for me. I’d rather use T-SQL every time, but I’m also the first to admit that there are plenty of people who would use that designer.

And when they do, many run into an error that says:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

2025-05-25

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 filter rows being returned by a query.

When should you use a HAVING clause rather than a WHERE clause?

Answer:

A WHERE clause is used to filter the rows from the source (usually a table).

2025-05-24

Over the years, I’ve had a surprising number of questions on how to output all the data in a table as a series of INSERT statements. SQL Server Management Studio has had the ability to do this for a long time. Here’s an example.

In Object Explorer, I’ve expanded the WideWorldImporters database, then expanded Tables. Where people come unstuck is they right-click the table, and look at the scripting options:

2025-05-23

I recently wrote about the most common misconfiguration that I see for linked servers. It greatly affects performance.

The second most common linked server misconfiguration is related to security. I can’t tell you how many times I’ve seen this when reviewing SQL Server systems for clients.

The issue you need to consider is that when your local SQL Server connects to the remote SQL Server, which identity is used for the connection?

2025-05-22

I’ve mentioned a number of times how useful I think the Object Explorer Details panel is in SQL Server Management Studio.

One option in that panel that might not be so obvious is the sorted navigation. Here’s an example.

I’ve opened the WideWorldImporters database in Object Explorer, and clicked on the Tables node:

I then hit F7 to open the Object Explorer Details pane and clicked the Name heading to sort the table list. I like it ascending so you might need to click it twice:

2025-05-21

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:

You are considering adding an index to a table.

You have heard that adding indexes slows down UPDATE commands.

Does adding an index always slow down updates? If so, why? If not, why not?

2025-05-20

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

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