Sql-Server

Betteridge’s law of headlines says that any headline that asks a question can be answered “no”. Well, contrary to that law, the TLDR answer to this is “yes”. I’m endlessly reading information that says that if your referential integrity is being checked in your OLTP application that’s providing data for your data warehouse, that you don’t need to then check it within the data warehouse. Sadly, this is often naïve thinking, for a number of reasons.

2018-11-19

SQLCMD mode changes how queries are executed in SQL Server Management Studio (SSMS). When using this mode, you can work with options that aren’t normally part of SQL Server T-SQL scripts.

Some installation scripts also require SQLCMD mode and will fail if it’s not enabled.

Here’s an example of running a query against 3 servers within the same script:

First we open a new query window, then on the Query menu, we choose SQLCMD Mode.

2018-11-15

Anyone who’s worked with SQL Server for any length of time has probably run into collation issues. They will have seen something like this:

SQL Server Error: Cannot resolve the collation conflict

In many cases, it will be a difference between the collation in tempdb and their own database, they’ve created temporary tables, and tried to join them to tables in their database. And it goes bang !

While you can fix it in the query by specifying a COLLATE clause, it’s a pain in the neck. If you use temp tables, you really should have this when working with them so your application isn’t dependent upon the server’s collation.

2018-11-14

I spend a lot of time working with teams of DBAs and developers who need to fix performance problems. One of the real issues that I see time and again though, is people fixing the symptom, not the problem.

Many DBAs spend their time tracing query activity, working out which queries the system is spending a lot of time on, and then working out how to make those important queries run faster.

2018-11-12

In an earlier post, I mentioned how useful the F1 key is. On its own, it provides syntax help, but when you highlight an object and hit Alt-F1, you get to see metadata about the object.

Under the covers, this just runs the sp_help stored procedure. Alt-F1 has been mapped to that.

You can see where this is configured, change it if required, and/or configure other procedures as well.

In Tools, Options, Environment, Keyboard, then Query Shortcuts, you can see this:

2018-11-08

When I’m working with a SQL Server database, I often need a simple list of all the indexes that are already in place. I want it in a concise form. In our free SDU Tools for developers and DBAs,  we added a procedure ListIndexes to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.

You can see how to execute it in the main image above. The procedure takes these parameters:

2018-11-07

I first started using SQL Server back in 1992 with version 4.2 I was running a software house and was hoping to use SQL Server as the back end of our updated application. 4.2 wasn’t up to it. Versions 6 and 6.5 were closer but I still thought they needed far too much administration for me to be comfortable deploying them to most customer sites. Version 7 though, changed the game. Rather than having to manage devices (which was painful), we now had just standard operating system files, and even had options for automatically growing and shrinking the files.

2018-11-05

In an earlier post, I mentioned that you can create a registered list of servers, either in Local Server Groups or stored in a Central Management Server.

What I didn’t really talk about though, is what you can do with these groups of servers, rather than just executing queries on an individual server.

I’ve created three local server groups, for my development, UAT, and production servers.

The Development Servers group has three database servers in it. If I right-click the group, rather than any individual server, we get these options:

2018-11-01

One of the challenges when you have foreign keys in place, is that there are restrictions on what you can do to tables that are the targets of the foreign keys. What’s often needed though is a list of all references to a particular table or set of tables.

In our free SDU Tools for developers and DBAs,  we added a function ListIncomingForeignKeys to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.

2018-10-31

I was at a client site the other day and they were puzzled by SQL Server object permissions. In particular, they couldn’t understand what the point of the REFERENCES permission is. It seemed pointless to them. It’s not. Let me explain why.

Keep in mind that if all the objects in the database have the same owner, then this isn’t an issue. But not all databases are like that. Many have different teams that control different parts of a large database.

2018-10-29