Sql-Server

If you have ever started SQL Server Management Studio in an isolated environment (ie: one with no external Internet connectivity), you’ll find that it’s slower to start.

That’s because SQL Server uses signed assemblies, and whenever an application with signed assemblies starts, it needs to check whether or not the certificate that they were signed with has been revoked. It’s not good enough to just check if it’s a valid certificate.

2018-09-20

On occasions I have to work with binary strings in SQL Server using T-SQL.  The literal values are hexadecimal strings. T-SQL doesn’t have a simple function to just convert one of these character-pair strings to a character.

In our free SDU Tools for developers and DBAs, we added a function HexCharStringToChar to do just this.

You can see the outcome in the main image above.

You can see it in action here:

2018-09-19

In a recent post, I described the first reason why I think you should be using schemas apart from dbo in your SQL Server database designs. It was to provide a form of grouping like you use with files in your filesystem, and to avoid prefixes. Now we need to talk about the second main reason for using schemas.

Security Boundary

I need to start by saying that if you’re idea of database design and access is to have an ORM that builds queries on the fly and needs access to the entire database without any form of security control apart from what your application provides, you might as well stop reading this post here.

2018-09-17

One of my continuing projects is to run a series of podcasts with people that I think are interesting in some way, from the data community. I tend to do them in bursts, normally when there’s a new version of SQL Server coming. (I’m about to publish a series of SQL Server 2019 podcasts). One person that I interviewed early on though was Graeme Simsion. You can find the podcast here. It was show 10 back in December 2005. Graeme was a well-known Data Modeler.

2018-09-14

This is a quick tip but an important one. I see many people using SQL Server Management Studio (SSMS) and they aren’t aware of Activity Monitor.

While there are many clever things that we can do with queries, to interrogate the health of the system, don’t forget that there is quite a bit of useful information in Activity Monitor, and it’s easy to get to.

There are two basic ways to launch Activity Monitor. The first is to right-click the server in Object Explorer:

2018-09-13

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