Sql-Server

Hi Folks,

We’ve just produce another new eBook that’s with our compliments, for anyone subscribed to our SDU Insiders list.

If you’ve wondered about implementing TDE and aren’t sure, this guide should help. If you’ve already implemented it and aren’t sure if you’ve done things right, again this should help.

The book also includes a frequently-asked questions section with questions we’ve commonly been asked by our clients.

You’ll find it here: http://tdebook.sqldownunder.com

2020-04-10

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

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

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

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

I recently posted about how we added Countries and Currencies to our free SDU Tools for developers and DBAs. I use those all the time in drop-down lists. But the other one that’s super-helpful, is to know which are the official currencies for each country. So we’ve added another view called CurrenciesByCountry.

It’s a simple view that returns details of the current list of official currencies for each country. You can see it here:

2020-03-25

I mentioned in earlier posts that there’s no standard way to write dates, so we end up having to write them as strings. Now that was a real problem in earlier versions where people would get that wrong.

SQL Server 2012 added an option to make that easier. DATEFROMPARTS allows you to specify a year, month, and a day to create a date, and always in that order.

Look at the following query:

2020-03-23