Sql-Server

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

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