Azure-Sql-Db

In SQL Server, both logins (access to the server) and users (access to a database) have a name and a security ID (SID). This leads to problem situations where names might match but SIDs don’t match.

Common Problem

I’ve lost count of the number of times I’ve seen a user restore a database from another server, and then realize that the SQL Server login they need wasn’t present. Then, they create a new login and end up in a lousy situation, because the new login’s SID doesn’t match the SID of the user in the database.  I call this a mismatched SID issue.

2019-08-21

If you ask SQL Server developers how to calculate the length of a string in T-SQL, most would tell you immediately that you need to use the LEN() function. What most don’t realize though, is that it doesn’t actually work properly.

The T-SQL LEN() function ignores any trailing spaces in a string.

That’s not how a string length function should work, or how it works in most languages. So, in our free SDU Tools for developers and DBAs, we added a simple tool that works properly. It’s called StringLength.

2019-08-15

In my last SDU Tools post, I described a tool for calculating date dimension columns. While dates are often enough, in many data warehouses, you also need to allocate time periods across each day as well. To make that very easy, in our free SDU Tools for developers and DBAs, we added a tool called TimePeriodDimensionColumns.

This tool is a table-valued function that takes a time (actual time of the day), and the length of each time period, in minutes. In the example in the main image above, I’ve asked for 8:34PM and I’ve said that each period is 15 minutes long.

2019-08-07

When you’re building a data warehouse using SQL Server, one of the first things that most people start to design is a Date dimension. To make that really, really easy, in our free SDU Tools for developers and DBAs, we added a tool called DateDimensionColumns.

This tool is a table-valued function that takes a date, and returns a set of columns that many will find enough for a good Date dimension. You can see the list of returned columns in the main image above.

2019-07-31

When you look at a SQL Server build number (actually a “Product Version”, you’ll see they have a format like this:

14.0.3256.1

The first number is the major version number. The second number is the minor version number. The third number is the build number. The fourth number is the release number.

These numbers are a little awkward to work with directly in T-SQL so, in our free SDU Tools for developers and DBAs, we added a set of tools that can do that for you.

2019-07-24

Most SQL Server tables should have a clustered index. There are some exceptions to this but it’s a pretty general rule, and if in doubt, you should follow it. (Note that this is not the same as having a primary key).

I regularly come across tables without clustered indexes for all the wrong reasons. So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that don’t have a clustered index. No surprise, it’s called ListUserHeapTables because a table without a clustered index is a heap.

2019-07-17

When you create a SQL Server stored procedure, some session-related settings are part of the configuration of the procedure. The two most common problems with this are related to ANSI_NULLS and QUOTED_IDENTIFIER.

When procedures are scripted, SQL Server tools also script the settings for the session, but what often happens is that the person creating the procedure, just executes the CREATE PROC statement and doesn’t execute the SET statements before it. When they do this, you can end up with procedures that don’t have the settings you expect.

2019-07-10

SQL Server tables should have a primary key. I’m not talking about a clustering key (that’s a different topic) but a key that logically identifies each row, and isn’t ever NULL.

I have SQL purist friends that argue that a table without a primary key really isn’t a table.

When I’m reviewing database designs, one of the things I look for is the lack of a primary key on tables. It’s not just a purist issue. Without a primary key, other features like replication are problematic.

2019-07-03

I’ve lost count of the number of times I’ve seen people searching for details on what the latest SQL Server builds are. In a previous post, I mentioned that our free SDU Tools for developers and DBAs had a view called SQLServerProductVersions that showed all the SQL Server builds.

But that shows all the builds. What if you just want the latest details? We added a new view that works that out for you. It’s called LatestSQLServerBuilds. You can see it in use in the main image above.

2019-06-26

In the last SDU Tools post, I mentioned new functions to determine if a day is a weekday or a weekend. (Based on Saturday / Sunday being weekends but easy for you to change if you have different weekends).  Another issue that I often have is the need to add a number of weekdays to a given date. To make it easy as well, we added a function AddWeekdays to our free SDU Tools for developers and DBAs.

2019-06-19