Azure-Sql-Db

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

I often need to know if a date that I’m working with is a week day or a weekend. While T-SQL has some functions that help with that, you need to use a combination of them to get a reliable outcome that’s independent of the current session settings. (Based on Saturday / Sunday being weekends but easy for you to change if you have different weekends). To make it easy, we added two functions IsWeekday and IsWeekend to our free SDU Tools for developers and DBAs.

2019-06-12

Many of my friends have told me that they like how concise the PIVOT and UNPIVOT operators are, but they find the syntax confusing. I must admit that I wasn’t a big fan of these when they were added in SQL Server 2008 as they didn’t really add anything to the language that I couldn’t have done with CASE statements, etc. and there were so many other parts of the language that needed work.

2019-06-05

I often need to document the access that various users have to content stored in SQL Server Reporting Services (SSRS). Surprisingly, I didn’t find any built-in way to do that.

We had started to add some Reporting Services related options to our free SDU Tools for developers and DBAs, and, just for this, we added a new procedure RSListUserAccessToContent.

It takes two parameters:

@IsOrderedByUserName bit -> Is the output ordered by user (default yes else by item) @RSDatabaseName sysname -> Reporting Services DB name (default is ReportServer)

2019-05-29

I write a lot of utility code for SQL Server. Many of the system tables include values for LoginTypeID and UserTypeID but I’ve never found a view in SQL Server that returns a description of each of those values.

To make it easy, in our free SDU Tools for developers and DBAs, we added two views (LoginTypes and UserTypes) to help.

You can see the views in action in the image above, and in the video here:

2019-05-22

I carry out a lot of basic health checks on SQL Server databases. One issue that I come across fairly regularly is that the databases contain indexes that have been disabled.

Most of the time, when I investigate why that’s happened, the customer has used some sort of ETL process and has disabled the indexes to improve loading speed. But then something went wrong, and the indexes didn’t get rebuilt (i.e. re-enabled).

2019-05-15