The Bit Bucket

You shouldn’t have to know how a view or stored procedure works or its internal code structure to use it. To that end, it’s time that stored procedures expose detailed contracts.

Many DBAs don’t allow users direct access to tables in SQL Server databases and instead give them access only via a series of views and stored procedures. One of the cited benefits of this approach—beyond providing a security boundary—is that the views and stored procedures provide a layer of abstraction over the underlying data.

2025-10-29

When I write T-SQL, my standard is to use upper-case names for built-in system functions. But not everyone likes that.

I’ve done a lot of work on systems where all these names are lower-case. I don’t have any great objection to that, but it’s painful if Intellisense keeps making them upper-case.

But there is a solution. In Text Editor, Transact-SQL, then Intellisense, there is an option for this:

2025-10-28

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: Administration Level: Advanced

Question:

You have two SQL Server instances. Each is on a separate virtual machine. Both virtual machines are on the same physical host.

You want to be able to create a transaction that spans both instances of SQL Server. What else needs to be installed?

2025-10-27

When you close SSMS, the default action is that it checks each open window and makes sure there are no open transactions before it closes.

This is to allow you to decide what to do if you do have an open transaction, and based on that, to avoid losing any work.

While this sounds like a great idea, I mostly now work with Azure SQL Database, and my SSMS windows have been idle for long enough that I’ve lost the connection. So, when I close SSMS, it tries to check the transaction status against connections that are already closed under the covers.

2025-10-26

It’s really common to see questions in the newsgroups about the inability to access a database that has been moved between servers. Invariably, the problem is that a SQL Server login (i.e., not a Windows login) can’t access the database.

By default, when you recreate a SQL Server login, you get a new security ID (SID), even though you have the same user name and password.

The problem then comes when you restore a database from another server. You can’t access it. If you try to create the user entry in the database, it says it already exists and fails. But if you try to list the users in the database, it also doesn’t show it. That’s often the point at which the administrator wants to throw their mouse through the screen but it doesn’t have to be a problem.

2025-10-25

When I first installed v21 of SSMS, I was puzzled that many of the connection options in Object Explorer were greyed out:

Because I had been working on a preview version, I thought it just wasn’t there yet. But when I checked the release notes, I saw it should be there. The issue is that I hadn’t added the Business Intelligence workload.

You don’t do that from within SSMS. Instead, you need to run the Visual Studio Installer:

2025-10-24

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: Administration Level: Advanced

Question:

Several trace flags are in use on a server you manage.

You are concerned that a behavior that you are seeing in a session is being caused by an enabled trace flag. How can you determine which trace flags are enabled for a session?

2025-10-23

I’ve been learning Mandarin (Chinese) for many, many years. I’m determined to become as fluent in speaking as I can, but to also be reasonable at reading and writing.

One important aspect of doing this, is to make sure you use the language you are learning as much as possible.

A great advantage of SSMS is that it is available for use in many languages. Yet when you do this, SSMS ends up supporting both the target language and English. That means that I can work part of the time in Chinese (to force myself to learn) and the rest of the time in English.

2025-10-22

We spend a lot of time doing performance tuning work for clients. Inappropriate indexing strategy is often high on the list of issues that we identify. Most of these issues can’t be fixed by SQL Server itself and require a detailed understanding of both how the tables are structured and how they are accessed.

However, there is one very common problem however that I believe SQL Server could assist with.

2025-10-21

SSMS has a concept of channels that are used to control releases of the product. You can find them described here .

You can have both Release and Preview channels installed on the same system.

Most people who are using the product in production scenarios want to have a supported stable release. They should use the Release Channel .

Other people, however, are keen to try the latest features as soon as they are available, even if they aren’t at a Release quality bar yet. They can use the Preview Channel .

2025-10-20