Sql-Server

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.

2018-11-29

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.

2018-11-28

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.

2018-11-26

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.

2018-11-22

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’

2018-11-21

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