The Bit Bucket

T-SQL 101: 80 Converting data types with CAST and CONVERT

The rich set of data types in SQL Server and the T-SQL language are great. But sometimes, you need to change a value from one data type to another. The output from the commands above is:

CAST

ANSI SQL provided a way to do this.

CAST is the ANSI SQL way to convert from one data type to another. The syntax is basically:

CAST(ValueToConvert AS NewDataType)

And you can use it where you would have used an expression or constant.

2021-01-11

Opinion: Reports aren't tables

I love the way that tools like Power BI have made creating analytics approachable for a much wider range of users. The skills acquired when doing this are a great start for using analytics in enterprises, but an area that I still see missing is data modelling.

What I see users doing is this:

  • Decide what the output reports, etc. should look like
  • Design tables that match the reports.

And this is where things go wrong.

2021-01-07

SDU Tools: Strip diacritics from strings in SQL Server T-SQL

I try to allow strings in my applications to use all the richness of the available character sets. If someone has an accent in their name, I really want to repeat it back that way. But I’ve been asked many times if there’s a simple way to strip out all the accents, graves, etc. in T-SQL. These are called diacritics, and we’ve created a function to help you remove them if you decide you really must. (But as I said, really consider keeping them).

2021-01-07

BI: Azure Data Factory Copy Activities Won't Start (Queued)

I love working with Azure Data Factory (ADF). There are times though, when I’m stuck wondering what’s going on, and it takes me a moment to discover it.

One that had me puzzled recently was a Copy activity that just wouldn’t seem to start. The pipeline had started ok. Previous activities in the pipeline had run just fine. But when it got to the Copy activity, it said “Queued” and just kept saying that seemingly endlessly.

2021-01-06

SQL: Create a list of columns for a table using T-SQL and STRING_AGG

I often need to create a list of columns for a table. Sometimes it’s because I’m generating scripts but there are other times that I need it for dynamic code.

Let’s use WideWorldImporters as a sample. Imagine I need to get a list of columns for the Sales.Orders table.

The wrong way

I’ve seen people doing this the wrong way. They try to use a SELECT statement that joins a variable to itself like this:

2021-01-05

T-SQL 101: 79 Replacing NULL values with ISNULL, COALESCE

The next set of introductory T-SQL topics that I want to talk about is how to change data and/or data types. The first issue is dealing with NULL values.

Once you get your head around the idea that NULL means the lack of a value (NULL isn’t a value), you might need to replace NULL values with something else. While you could just do that with a CASE statement, T-SQL provides two built-in functions for doing this.

2021-01-04

Happy new year: and a reflection on 2020

Hi Folks,

Just wanted to make a post to wish you all a happy new year for 2021.

I doubt there’s anyone much on the planet who didn’t have a very, very peculiar, or very, very difficult 2020, and while there’s a glimmer of hope with the virus now as vaccines arrive, I suspect that 2021 is still going to be dominated by the coronavirus, and a very tough year for so many.

2021-01-01

BI: DataWeek starting soon - don't miss it

I was really excited today to see details of the upcoming #dataweek. Anything with a name like that already has my interest.

In this case though, there’s a #CreateData event that’s happening as part of #dataweek. And the focus of that looks to be on Azure Synapse Analytics.

Azure Synapse Analytics

I know that many of my readers won’t have spent much time with Azure Synapse Analytics. The product has grown out of Azure SQL Data Warehouse (note not Azure SQL Database) but it’s now much more than just an upgraded Azure SQL DW.

2020-11-25

SDU Podcast: Show 80 with guest Pedro Lopes is now available

I was really pleased to get to record a SQL Down Under podcast with an old friend Pedro Lopes recently. Pedro is a Principal Program Manager with Microsoft in the Azure Data SQL Server area.

A year or so back, I had Joe Sack on the show telling us where Intelligent Query processing was heading, and Pedro now fills us in on where it’s got to.

We recorded the show earlier in this month but I couldn’t release it until after the PASS summit where some of the features were announced.

2020-11-23

ADF: Time zone support in Data Factory - a Small Change but so Important

I work with a lot of technology on a daily basis, much of it amazing. But I still get excited when relatively small enhancements are made, and they make my life or development much better.

Timezone support in schedule triggers in Azure Data Factory is one of those changes.

Schedule Triggers

In Data Factory, the most common type of trigger that we use is a schedule trigger. It’s used to run a pipeline at a specific time or series of times. But one of the most painful aspects of these triggers is that they didn’t have time zone support.

2020-11-04