Sql-Server

In a recent post, I mentioned that I often need to do a quick check to see if the schema of two SQL Server databases is the same, and how our GetDBSchemaCoreComparison procedure can make that easy. On a similar vein, I often need to get a detailed comparison of two tables.

In our free SDU Tools for developers and DBAs, there is another stored procedure called GetTableSchemaComparison to make that easy as well. It takes the following parameters and also returns a rowset that’s easy to consume programmatically (or by just looking at it):

2018-09-12

In violation of Betteridge’s law of headlines, the answer is yes. When designing SQL Server databases, there are several reasons why you should be considering using schemas and not just having all your objects in the dbo schema.

This is a good example though, of where good advice regarding SQL Server dates. In SQL Server 2000 days, it was sound advice to keep all your objects owned by dbo (and at the time automatically part of the dbo schema), to avoid ownership chaining issues. That’s because ownership and the containing schema were one and the same concept.

2018-09-10

SQL Server Management Studio (SSMS) is a flexible tool. One thing that often surprises people is that it hosts a version of Microsoft Internet Explorer, right inside the application.

Why would SSMS have a web browser I hear you ask?

Well this web browser lets browse URLs, and reference links, without leaving the tool.

You can open it directly by choosing Web Browser from the View menu:

On my machine, it’s under the Other Windows section. In fact, it’s the only window there on my machine, which makes you wonder why it has a separate section in the first place.

2018-09-06

I often need to do a quick check to see if the schema of two SQL Server databases is the same.

In our free SDU Tools for developers and DBAs, there is a stored procedure called GetDBSchemaCoreComparison to make that easy. It takes the following parameters and returns a rowset that’s easy to consume programmatically (or by just looking at it):

@Database1 sysname -> name of the first database to check @Database2 sysname -> name of the second database to compare @IgnoreColumnID bit -> set to 1 if tables with the same columns but in different order are considered equivalent, otherwise set to 0 @IgnoreFillFactor bit -> set to 1 if index fillfactors are to be ignored, otherwise set to 0

2018-09-05

Most companies have some sort of ongoing maintenance processes that perform periodic backups of databases. They also have log backups scheduled for any databases that are online and in full recovery model. However, when you first create a database, it might be in full recovery model yet never have had a full backup performed. If your scheduled jobs then try to create a log backup prior to the first full backup, the backup will fail, and that might make your job fail.

2018-09-03

In a previous post, I mentioned that when a long running query completes, I might not have been waiting around for it, and so I wanted to know when it completed.

But sometimes I do wait around for a query to complete, yet I’m distracted by other things and don’t realize that the query has actually completed. That’s not surprising because if a query takes a long time, I’m probably going to go on with other work while that’s running.

2018-08-30

We’ve been building tools to create scripts for various SQL Server T-SQL objects for a long time. Part of scripting a table is the scripting of the data type. That means its datatype name, precision, scale, and maximum length.

In our free SDU Tools for developers and DBAs, we added a scalar function called FormatDataTypeName ot make that easy. It takes the following parameters:

@DataTypeName sysname - the name of the data type @Precision int - the decimal or numeric precision @Scale int - the scale for the value @MaximumLength - the maximum length of string values

2018-08-29

I’ve just finished delivering a bunch of presentations across New Zealand, and one of the sessions that I’ve delivered in several places is Things I Wish Developers Knew About SQL Server. In that session, I mentioned briefly that most single column SQL Server indexes that people create are at best pointless, and at worst counterproductive.

I often see people making a poor attempt at indexing, and when they don’t know what they need to do, they often add individual indexes on many columns in their tables. This isn’t a good idea.

2018-08-27

I commonly run into a few connection-related scenarios:

• I’m working on a large query and need to run it against several servers, not concurrently, but one after the other.
• I’ve just made a database connection, and got my query ready, only to discover that I’ve connected to the wrong server.

Either way, what I’ve seen people do in these scenarios is to:

• Select all the text in the current query
• Copy it
• Open a new query window
• Paste the code

That’s all good but SQL Server Management Studio (SSMS) has had a simpler way of doing this for quite a while.

2018-08-23

Fellow MVP Paul White and others have discussed the indexing of small segments of strings, to make fast indexes that I think are somewhat like full-text indexes but potentially more efficient. Three character substrings are common, and are known as trigrams.

I’ve experimented with these at a number of site and seen really interesting (good) results.

In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrigrams to help with this. You can pass it a string, and it will pull it apart for you, and return the set of trigrams. This would make it much easier for you to experiment with these types of indexes.

2018-08-22