Sql-Server

This post describes how the object dependency tracking views provide more reliable insights into object dependencies than previous methods such as the use of the sp_depends system stored procedure.

During a recent consulting engagement, I was asked about the best way to determine which stored procedures and views made use of a particular table. In the past, the methods available from within SQL Server were not very reliable. Way back in SQL Server 2008, significant improvements were made in this area, yet I see so few people using them, at least not directly. Many will use them indirectly via SSMS.

2025-11-14

The SQL Server team has been working towards making databases more standalone, and less dependent on the server, for quite a while. SQL Server 2012 was the first version that had a concept of a contained database. The thinking was that you should be able to move databases around to different servers and, in that case, users would follow with the database because they didn’t have a dependency on logins, which was the case up until then.

2025-11-11

This second article in a two-part series shows you how to control the execution context of stored procedures. This time, it’s by using digital signatures and code signing.

During a recent consulting engagement, I noticed that the client needed to control the security context a stored procedure was running under but was using a convoluted method to do so. Changing the security context that stored procedures run under is a common requirement, letting users execute code via stored procedures that they aren’t allowed to execute directly.

2025-11-10

As soon as I started working with a recent version of SSMS, I realized that I didn’t like how heavy the grid lines were in the output:

I really wanted the data to be the focus, not the lines. They seemed much darker than on the previous versions. Note that this is a personal preference. I can imagine some people preferring them as they now are. Fortunately, though, you can now change the color of the lines. In Tools, Options, Fonts and Colors, in the Grid Output section, there is now an entry for Grid Results Lines.

2025-11-07

This is part 1 of a two-part series that shows you how to use the EXECUTE AS clause to change and control security context for stored procedures. Up next month will be digital signatures.

During a recent consulting engagement, I noticed that the client needed to control the security context that a stored procedure was running under but was using a convoluted method to do so. Changing the security context that stored procedures run under is a common requirement, letting users execute code via stored procedures that they aren’t allowed to execute directly.

2025-11-06

One of the bigger changes in recent versions of SSMS has been the inclusion of connection encryption details on the main connection page:

Previously, this was on another page of the logon dialog. Once you make the connection though, there previously was no indication of what type of encryption was used. Now that appears in the query status bar as a lock. When you hover over the lock, it shows you the type of encryption that had been selected.

2025-11-04

Uniqueness can be implemented by primary keys, unique constraints and unique indexes. The use of primary keys is well understood but confusion exists about unique constraints and unique indexes.

This issue arose from a consulting engagement I was performing earlier in the month. During one of the architectural discussions I was having with the client, an interesting question was suddenly posed: When would you ever use a unique index?

After thinking about this for a moment, I suddenly realized that I almost never would use one and so I raised the topic on a technical discussion forum. An interesting discussion ensued and I’ve summarized the key thoughts in this article.

2025-11-02

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: Medium

Question:

You are trying to truncate a table but the command is failing.

Can you give some examples of what can cause a truncate command on a table to fail?

Answer:

One common reason is that the table is referenced by a FOREIGN KEY constraint. (But you can truncate a table that has a foreign key that references itself).

2025-10-31

When I’m editing large script files in SSMS, I sometimes find that the scrolling speed of my mouse wheel is too fast or too slow.

If you don’t like the current speed, SSMS has an option to adjust that.

It’s in the Text Editor, Advanced, section. It allows you to control how many lines you get per scroll (for vertical scrolling) or how many characters (for horizontal scrolling):

2025-10-30

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