Shortcut: Query and results in separate tab in SQL Server Management Studio

Another simple tip for today. In SQL Server Management Studio (SSMS), query results are normally shown at the bottom of the query window.

This can greatly reduce the screen real estate both for the query, and for viewing the results.

In Tools, Options, Query Results, SQL Server, Results to Grid, there is an option to Display results in a separate tab. This can be very useful and generally you will also want to choose the extra option to Switch to results tab after the query executes.

There is a similar (but separate) option for working with results to text rather than grid.

One further option on this dialog that's worth mentioning is the Include the query in the result set. I find that useful when working with text output, not so much with grid output.

SDU Tools: Number to Roman Numerals in SQL Server

Ever since we've been shipping our free SDU Tools for developers and DBAs, we've been getting interesting requests from users for additions to the tools. Today's tool is one of the stranger requests but for the odd situation where you need it, I'm sure we will have saved you a lot of work. That tool is NumberToRomanNumerals.

The person who requested it was outputting values that need to go into media trailers, pretty much as you see Roman numerals in the tail end of many movies.

So we added a simple function. It takes a single parameter which is the number that you want to convert, and it outputs the Roman numerals as a string. I'm sure the Romans weren't really thinking about big numbers but we made it capable of dealing with quite large numbers.

You can see it executed in the main image above, and you can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: Developers need to stop rewriting history

I often deal with quite large databases. There are only two things about big databases that trouble me. One is queries that need to read the whole database to find anything. The other is big databases that are still designed like small databases.

Every week, I still see massive SQL Server databases where all of the data is in a single PRIMARY filegroup. That's a problem but often the people looking after the databases can't do much to improve that situation.

One of the most important things that's needed in a large database is an effective strategy for separating out old data. My preferred tool of choice for that is partitioning.

I was at a bank in Melbourne where they had a 1.9TB table that was being replicated to Singapore over a slow link. Things were ok, right up until it ever needed to be reinitialized. Then the issue was about how to get all that data up to the other end of the link. Their current solution was to back it up, hand it to a staff member, and say "Have fun in Singapore".

Awesome image by Chen Hu
Awesome image by Chen Hu

But ask yourself how much of that table is likely to ever be modified. We partitioned the table by month, made all the old partitions highly compressed and read-only, and the only read-write partition was a few hundred MB.

If they needed to reinitialize, they only need to copy up that last partition as they know that all the rest of the data is already at the other end, and is unchanged.

This is a great outcome but only works because we were able to freeze most of the table as read-only.

That needs good design work from the developers.

I've worked on other systems where large transaction tables could never be frozen like this. By design, if a transaction was reversed, they'd go back and change the original transaction to flag it as reversed.

This is not sensible.

Our systems should not be designed that way. It's important for developers of large tables to consider how these tables will be used.

An effective archiving strategy depends upon immutable historical data.

Winston Churchill said "History will be kind to me for I intend to write it." We, on the other hand, should not be rewriting history in our databases.

 

Shortcut: Viewing and configuring spatial data output in SSMS

SQL Server 2008 added the ability to work with spatial data by the additional of the geometry and geography data types. When they first were added, there was no tools support for working with them, and all we had was direct manipulation of their internal binary storage.

Here's an example:

I've defined a variable named @Shape of type GEOMETRY. I've then assigned a shape to it, based on a polygon formed by a set of points. If you look carefully, you'll notice that it's a square.

But when I select the value, what is returned is the internal value of the data type, as a binary string, written in hexadecimal for ease of reading 😊

And at first, that's all we had. But notice that there is now another results tab:

As soon as SSMS sees any spatial data in the results, it adds a tab to try to visualize it. Under the covers, it's actually the mapping control that Microsoft purchased from Dundas and put into Reporting Services.

On the right hand side, we can pick the column to be displayed, because we might have more than one, and we can overlay a label if another column is holding the label. In this case, let's modify the query to have two columns, one with the value, one with the label:

The mapping tool works out where to put the label so it's somewhere on the image. SQL Server has a function call to try to help with that. (It's easy for a square, but hard for say a donut).

If more than one row of data is returned, a shape is displayed in a different color for each value. This can lead to some awesome outcomes. Note what happens if we select all of the Application.Countries table from WideWorldImporters, choose the CountryName as the label, and zoom in slightly:

This is an awesome tool for visualizing data.

SDU Tools: List Primary Key Columns in a SQL Server Database

I'm often checking out what the primary keys are for tables that I'm working on. One of our free SDU Tools for developers and DBAs does just that. it's ListPrimaryKeyColumns.

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

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each primary key.

The columns returned are SchemaName, TableName, PrimaryKeyName, NumberOfColumns, ColumnList.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

Learn about indexing

Want to learn more about indexes? Take our online on-demand course now:  https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

SQL: Is there a need to check RI in data warehouses?

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.

It's a matter of trust.

The first and most simple example is that even if your OLTP application is perfect in terms of referential integrity, you could have a bug in your ETL process that's taking that OLTP data and putting in into your data warehouse. You need to find that out as soon as it starts happening.

A second reason is that data warehouses often get data from multiple source systems. You might be depending upon data in one system making references to data in the other system. Again, if this goes wrong, you need to know about it immediately.

A third reason is that you can't just trust the OLTP system. You could easily have bugs in it. When that happens, you don't want the incorrect data in that system, polluting the downstream systems. And that situation only gets worse if you have multiple source systems that are feeding data to the data warehouse.

A fourth reason is that because they often store versioning for data and that original data is no longer even available in the source systems, you can't always just reload a data warehouse from scratch if you let things get messed up.

There are many more reasons. In the end though, when I ask why people don't have RI in place, the #1 excuse is "performance". Yet when I ask them if they've actually tested it, I'm generally told that they haven't but that someone's brother's friend read it somewhere on the Internet once.

Don't be that person.

 

Shortcut: Set SQLCMD mode for all new query windows in SSMS

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.

Then I execute the following query:

Note the :CONNECT command is used to connect to another server.

Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That's easy to do.

In Tools, Options, Query Execution, SQL Server, then General, there is a checkbox to enable this.

 

 

SDU Tools: List Mismatched Database Collations in SQL Server

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.

But one of the things you might need to check is which databases have collations that are different to the server's collation. In our free SDU Tools for developers and DBAs,  we added a procedure ListMismatchedDatabaseCollations to do just that.

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

@ExcludeSystemDatabases bit – Do you want to omit any system databses from this list?
@ExcludeReportingServicesDatabases bit – Do you want to omit any SQL Server Reporting Services databases from this list?

One row is returned for each database.

The columns returned are DatabaseName, DatabaseCollation, and ServerCollation.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: When Performance Tuning SQL Server, Fix the Problem, not the Symptom

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.

Automated tuning systems like those being put into SQL Server currently achieve much the same outcome.

But I'd always contend that if that's all you can consider, you are often working on the wrong problem.

I was at a site some years back, where I found a single query that was loading a list of countries, but was being executed 12,000 times per minute and the system seemed constantly busy executing the query.

Some DBAs that I have worked with, would have just ignored the query. Each individual execution of the query took very few resources, and it wouldn't have made it through their filter of queries that need looking at, because it ran fast.

Other (better) DBAs might identify it as an important query because they aggregated the impacts of the queries. Even better if they aggregated the normalized version of the query. (See our free course on doing this if you aren't familiar with it). They would then spend their time working out if they could improve the performance of the query by indexing, looking at wait stats, reducing blocking, and other techniques.

This is all good stuff, but they're all still missing the real problem which is why is the application asking the same question 12,000 times a minute?

That's the actual problem that needs solving.

Awesome image by Annie Spratt
Awesome image by Annie Spratt

To really solve performance problems, you need to be prepared to step back, look at the bigger picture, and be prepared to go back into the application code with the developers. It's worth noting that many 3rd party application vendors won't be keen on you doing that, but it's the only way to actually solve the problems.

Now, in this case, it's pretty clear the list of countries isn't changing. (It's a few years since we got a new one in South Sudan).

Awesome image by Kyle Glenn
Awesome image by Kyle Glenn

So why on earth (pun intended) is the application constantly asking for this? Surely it should be caching that data.

And of course, when I checked with the development team, they had been caching the data. What went wrong though, is they had a typo in the caching code. Every time they checked the cache, they thought the values weren't there, so they reloaded them.

That was the real problem that needed to be solved. And no amount of fiddling with indexes, wait stats, locks, etc. was going to have a noticeable impact.

 

Shortcut: Set query shortcuts for favorite stored procedures in SSMS

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:

Here we can see how the Alt-F1 command is configured. By default, only three shortcuts are configured. It's easy to configure another one for your own use.