The Bit Bucket

I’ve been seeing quite a lot of unnecessary dynamic SQL code lately, that’s related to ALTER DATABASE statements.  It was part of code that was being scripted.

Generally, the code looks something like this:

DECLARE @SQL nvarchar(max);

SET @SQL = N'ALTER DATABASE ' 
           + DB_NAME() 
           + ' SET COMPATIBILITY_LEVEL = 150;';

EXEC (@SQL);

(I’ve used setting a db_compat level as an example)

Or if it’s slightly more reliable code, it says this:

2020-04-09

I do quite a bit of online shopping. One thing that many sites have implemented, is an attempt to recapture your attention when you’ve added items to a shopping cart, and then abandoned the cart.

This is seen as a feature in many implementations of carts for online stores.

Manipulate them?

Given it’s so common now, I’ve also found that many can be manipulated. For example, one clothing store that I like, will send me a reminder about my abandoned cart one day later. Often at that point, they’ll make an additional offer, like free shipping.

2020-04-07

The datetimeoffset data type was added in SQL Server 2012 and allowed us to not only store date and time values, but to also store a time zone offset (from -14 hours to +14 hours). When you’re using this data type though, you might need to change a value from one time zone offset to another.  That’s the purpose of the SWITCHOFFSET function.

Look at the following query:

SYSDATETIMEOFFSET is being used to return the current date, time, and time zone offset for the server, but we’re also asking for the equivalent with a +7 time zone offset. (That was the current time in Seattle when the query was run). You can see the result here:

2020-04-06

Hi Folks,

Just a heads-up that we’ve just released SQL Down Under podcast show 79 with Microsoft Principal Program Manager Mark Brown.

Mark is an old friend and has been around Microsoft a long time. He is a Principal Program Manager on the Cosmos DB team. Mark is feature PM for the replication and consistency features. That includes  its multi-master capabilities, and its management capabilities. He leads a team focused on customers and community relations.

2020-04-04

A simple question came up on a forum the other day. The poster was trying to work out why he couldn’t find square brackets (i.e. [ ] ) using LIKE in T-SQL.

The trick is that to find the opening bracket, you need to enclose it in a pair of square brackets. But you can just find the closing one directly.

Let’s see an example. I’ll create a table and populate it:

2020-04-02

I like to have my databases at the same database compatibility level as the server, whenever possible. But how do you know the maximum value that’s allowed? We recently added a tool to our free SDU Tools for developers and DBAs to solve this. It’s called ServerMaximumDBCompatibilityLevel.

It’s a simple scalar function that returns the maximum DB compatibility level that’s supported by the server. It takes no parameters.

If you want to know which server version (like 2017 or 2019) that the DB compatibility level represents, you can also combine it with our SQLServerVersionForCompatibilityLevel function.

2020-04-01

There’s another annoying trend that I want to call out. Every single day, I receive emails like this:

I’m sure others get them too. Now I’m sure Tatyana is a lovely lady who’s just trying to do her job and struggling to find business for her company. But I haven’t the slightest interest in what she’s offering.

Now I used to respond to these, and just say “No thanks” or “No interest”.

2020-03-31

The datetimeoffset data type was added in SQL Server 2012 and allowed us to not only store date and time values, but to also store a time zone offset (from -14 hours to +14 hours). When you’re using this data type though, you often have the datetime value and the offset separately, and need to combine them together to make a datetimeoffset value.

The TODATETIMEOFFSET function takes a datetime2 value (higher precision datetime) and a time zone offset, and returns a datetimeoffset data type.

2020-03-30

If you’ve done much work with Power BI, you’ll know that relative dates can be really useful. But if you’ve tried to use them, you’d also know that they aren’t as useful as they might at first seem. You configure them in Power BI Desktop and they look great. You deploy them to the Power BI Service, and you have a problem.

Why?

Because Power BI Desktop uses your local timezone and the Power BI Service doesn’t. So if the dates and/or times you have in the data you’re analysing don’t happen to be UTC based (or you live in England), you’re fresh out of luck. If you go looking for today’s data, you might well be seeing yesterday’s data.

2020-03-27

I use SQL Server Management Studio (SSMS) every day. When I first connect to a server, I’m presented with a list of servers to choose from:

Now this is really convenient, and in recent versions, it also remembers passwords for different ways of connecting. For example, I might have one server that I sometimes connect to using Windows credentials, and other times use a SQL credential for testing. It’s great that it remembers both.

2020-03-26