Sql-Server

If you go to drop a database, and you’re told you can’t because it’s being used for replication, you might be a little confused. For example, if you check your publications and subscriptions, you might see this:

Given there aren’t any, how could the database be being used for replication? Well, each database has a property that indicates if it’s enabled for replication, even if there aren’t any publications. That’s what’s stopping you from dropping the database.

2019-04-04

In our free SDU Tools for developers and DBAs, we have a large number of useful functions. The topic for today is two simple ones: StartOfMonth and EndOfMonth.

SQL Server 2012 added a function EOMONTH. It returns the end of the month date for any given date. I really, really don’t like abbreviations like this. There is no need to save three characters.

I asked why it wasn’t ENDOFMONTH but was told that EOMONTH was the name of the function in Excel. It’s sad to think that’s the guiding principle for T-SQL function names. I also don’t get why sometimes the words in the names are separated by underscores, and other times they aren’t. Surely END_OF_MONTH would be more readable.

2019-04-03

The most basic form of SELECT statement that we used to get information out of a table is the word SELECT followed by a list of columns or expressions that we want, and then details of where we need to get them from. Typically, that’s a table.

The basic syntax of a SELECT statement in SQL Server T-SQL is as follows:

Committees are curious things, and I can’t say that I always agree with their outcomes. I suspect if I’d been designing this, I would have had:

2019-04-01

Yesterday I had the pleasure of recording another new podcast on a SQL Server 2019 topic. Jakub Szymaszek is a PM on the SQL Server team, working around security.

In this podcast, Jakub and I discussed the current state of security and encryption in SQL Server, starting from cell-level encryption (CLE) and transparent database encryption (TDE) in SQL Server 2005. We then discussed the introduction of Always Encrypted and Data Masking in SQL Server 2016.

2019-03-30

We’re really excited that our latest online on-demand course is available. If you’ve ever wanted to get comfortable with SQL Server Replication, now’s the time to do it, from the comfort of your own place, and at your own pace.

The course Working with SQL Server Replication is being released at just $195 USD, but for my readers, there’s an extra special offer: Use coupon code REPLAPR19 to get another 25% off, but just till Apr 13.

2019-03-30

Even though I’ve worked with SQL Server for what feels like forever, I still constantly trick myself up with T-SQL commands that give me output that’s different to what I was expecting. This week, it was REPLICATE.

What should the output of the following statement be?

SELECT REPLICATE(‘Hello’, 10000);

If you said the word Hello repeated 10,000 times, or didn’t know at all, read on.

The clue comes if I execute the following statement:

2019-03-29

Blocking is a popular topic in SQL Server. Users are often trying to work out what’s blocking what. In our free SDU Tools for developers and DBAs, we have a tool to make that very easy to see. It’s called ShowCurrentBlocking.

ShowCurrentBlocking takes one following parameter:

@DatabaseName (sysname) is the database to report on

It returns two sets of data.

The first rowset is a list of all objects that are currently blocked, and the last query that was run on the connection for each of those objects.

2019-03-28

In the last post, when talking about querying a table, I mentioned that you should always include the name of the schema when you refer to a table. This is called using two-part names. But what are schemas in the first place?

In different database engines (e.g. Oracle, PostgreSQL, etc.), schemas are implemented and work differently i.e. there isn’t a consistent story across the industry. In SQL Server though, the closest analogy that most people would be familiar with, is a folder where you group things that are related in some way.

2019-03-25

Ever since it appeared back in earlier versions of SQL Server, I’ve normally launched SQL Server Configuration Manager from the Start menu in Windows. But every now and then, with some versions, it seemed to disappear. Now with the latest version of SQL Server installed on the latest version of Windows, it’s not there at all.

While it is possible to put it back in the menu, I think it’s time for us to get used to starting it in a different way.

2019-03-21

In our free SDU Tools for developers and DBAs, we have a number of functions and procedures that help you work with tables and indexes. One question that often arises is about how big tables are. Our tool ListUserTableSizes helps with that.

SQL Server Management Studio (SSMS) has a report that shows you user table sizes, but people often want a list of sizes that they can get programmatically, perhaps for a monitoring tool, or some sort of automation.

2019-03-20