SQL Server has a number of built-in database roles. The ones I see used the most are db_datareader and db_datawriter. They let you either read from all tables or read and write from all of them. Curiously, what has never been supplied is a db_procexecutor.
Developers and administrators who set up the use of these permissions think they are at least not using dbo or sa and while that’s true,
2025-12-16
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: Development Level: Medium
Question:
You have created a table named dbo.Customers. The column CustomerID has been declared as
INT NOT NULL IDENTITY(1, 1)
No contraints are defined on the table.
Can the CustomerID contain a duplicate value? If so, how?
Answer:
2025-12-15
When you create a new query file in SSMS, it automatically becomes part of a solution. You can view it in Solution Explorer. In this example, I have created two query files, and renamed the tabs without saving them, as Query 1 and Query 2:
They are in the Miscellaneous Files section as they weren’t part of a script project.
SSMS can now compare multiple files. If I highlight both the files, and right-click, I get this option:
2025-12-11
One surprise in the release plans for SQL Server 2025 is that SQL Server Reporting Services (SSRS) is being discontinued as a brand. If you have a paid license for SQL Server, you are now able to install Power BI Report Server. Previously, that option required you to have an Enterprise Edition license for SQL Server or a premium level license for Power BI.
But Reporting Services was also previously available for SQL Server Express. That will no longer be supplied, and there’s no option to use Power BI Report Server.
2025-12-10
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: Development Level: Medium
Question:
If you have two values where you want to return the first if it’s not NULL, and otherwise return the second, is there any difference at all between using the ISNULL and COALESCE functions?
2025-12-09
Anyone who has worked with SSMS for long, will have the experience of opening a number of query windows for short-term work, not wanting to save the scripts, and then being unable to find the one they want in the drop-down list of scripts:
The alternative to this was to save them all somewhere so you could give them a name. Then you need to clean them all up later.
2025-12-08
(Thanks to Klaus Oberdalhoff for this one)
Klaus noted that when EDIT TOP n is used, only the result is displayed. That’s different to the SELECT TOP n option where the query appears as well and can be edited.
However, after executing EDIT Top n, in the table displayed, right-click and you’ll see a menu item for Pane.
The sub-menu has the following options:
This option leads to a database diagram like this:
2025-12-07
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: Development Level: Medium
Question:
If you pass the following string to SQL Server, will it always reliably be understood as 7th October 2025 by the date, datetime, and datetime2 data types?
'2025-10-07'
If not, why not?
Answer:
No, while that is true for date, and datetime2, the value that will be cast for the datetime data type depends upon the regional settings. For example, in British English, a string in the format ‘XXXX-XX-XX’ will be read as ‘YYYY-DD-MM’.
2025-12-06
(Thanks to César F. for this one)
If you are working with many script files and they are all over your disk/storage, it can be painful to need to keep navigating whenever you want to open one.
However, when you use the File > Open > File option in SSMS, the dialog that opens up is capable of opening operating system shortcuts, and not just files. This means that you can have a folder of shortcuts and every time you need to open one of these files, you can just have it open the same location.
2025-12-05
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:
The table dbo.Customers has a clustered index CX_dbo_Customers on the CustomerID column. It also has two non-clustered indexes. One is on the CustomerName column and has an included column of PhoneNumber. The other non-clustered index is on the CityName column.
2025-12-03