The Bit Bucket - Page 2 of 130 - Thoughts from Data Platform MVP and Microsoft RD

Book Review: Deep Medicine: How Artificial Intelligence Can Make Healthcare Human Again

I have a deep interest in artificial intelligence and how it will change the world. I regularly present sessions on what I see coming, technology-wise. Many examples in those sessions are based on breakthroughs related to medicine. So I was pleased to get to listen to Eric Topol's book Deep Medicine: How Artificial Intelligence Can Make Healthcare Human Again.

Eric spends time discussing how much of existing medicine is functional yet quite broken. Very few doctors now really connect with and relate to their patients. Worse, misdiagnoses are becoming far too frequent, arguably because of this disconnection.

In this book, Eric takes a realistic look at where we're at with AI in medicine, and suggests how AI based systems will revolutionize the practice of medicine. The issue is with how this will be implemented. Doctors who see their own roles as purely functional (e.g. a radiologist who reads scans and writes summaries all day long, without interacting with patients at all), will be basically replaced. Doctors who use the AI based systems to enhance their work and free them from procedural tasks so that they can focus on patient interactions will see great outcomes.

Eric hopes that AI based systems will bring real patient care back into the healthcare business, while reducing the number of mistakes that are made.

The Verdict ?

This is a great book. 9 out of 10. I found it compelling and compulsive listening. It was interesting to hear an assessment of medical AI from someone directly involved both in medicine, and in researching where we are with medical AI.

DevOps: Fix: Can't clone an Azure DevOps repository in Visual Studio and SSDT

I've been working at a site that uses proxy servers for Internet access. And we were unable to clone a Git repository in Azure DevOps (AzDO) from within Visual Studio (VS). It was quite frustrating.

Visual Studio has got proxy settings and I had configured those:

They live in the system.net node within the settings.

VS was able to get out to the Internet, and I could find and try to connect to projects in AzDO but I couldn't clone them from within there.

You might see timeouts, or if the connectivity changed after you already previously had it working, you might just see just "Pull operation failed":

Solution

When VS executes Git calls, it makes those calls to an underlying Git layer. Git has its own proxy, so even though you can appear to connect and find a project from within VS, you can't perform Git operations (like cloning) using that proxy setting.

You still need to configure the Git proxy as well.

Open a Git CMD window (i.e. not just a normal command window), and execute:

git config –global http.proxy http://999.999.999.999:9999

where http://999.999.999.999:9999 is of course the IP address of the proxy, and its (optional) port number.

T-SQL 101: #83 Determining if a string is a number or date with ISNUMERIC and ISDATE

Sometimes we need to determine whether a string is a date or whether it is a number.

In the first example above, I'm asking if the string '20190229' is a valid date. You can see from the response (0) that it isn't. That's because even though it's a valid date format, February in 2019 doesn't have a 29th day. It's not a leap year.

The value returned from the ISDATE function is a zero or a 1. Curiously, the return value is of data type int. You'd think that a function that starts with Is and tests something would return a bit data type instead. But that's just one of the curiosities of T-SQL.

In the second example, I'm asking if the string 'hello' is a date. Clearly it's not, so the return value is 0. And in the third example, '20190228' is a date.

Similarly, we might also need to check if a string is a valid number. The ISNUMERIC function is used to do that.

However, that function might work a bit differently to what you'd expect. ISNUMERIC returns 1 for some strings that contain values that aren't numbers like plus (+), minus (-), and currency symbols like ($).

If that's not suitable for you, you might need to use a different function. A TRY_CAST or TRY_COVERT might be better, depending upon what you're trying to achieve. I'll talk about them soon in an upcoming blog post.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Sentence word order – Part 4 – How an action happened

In my first word order post, I explained the basic Subject-Verb-Object sentence order. Then in a second post, I explained how time (when something happened) normally gets added to sentences. In the third post, I looked at adding where something happened. In this final post in this word order series, I'll look at how an action happened.

How an action happened

In English, we can do this a few ways:

1 – In the office this morning, I drank my coffee slowly.

2 – In the office, I drank my coffee this morning, slowly.

3 – I drank my coffee in the office this morning, slowly.

4 – In the office this morning, I slowly drank my coffee.

5 – I slowly drank my coffee in the office this morning.

I'd say that the first option is probably the most common, the "cleanest" and probably conveys the meaning the best. The third isn't too bad but the second is starting to sound odd. The fourth is a little odd, and what I think is also odd in the fifth one, is it's not that clear what you're emphasizing.

It's likely that the sentence is trying to emphasize the "slowly" part.

In English, "slowly" is an adverb. In Mandarin, adverbs are called 副词 (Fùcí) which is close to saying "auxiliary word". Google says "vice" for Fù but it's more like vice as in vice-captain.

As in English, adverbs are often closely related to adjectives.

The adjective for "slow" in Mandarin is 慢 (Màn).

I could say "I'm too slow" by writing:

我太慢了。(Wǒ tài mànle.) which is literally "I too slow" followed by the indicator that the action has completed in "le".

For our sentence about the coffee in the office, this is a likely outcome:

今天早上在办公室，我慢慢喝咖啡。
(Jīntiān zǎoshang zài bàngōngshì, wǒ màn man hē kāfēi.)

This is literally "This morning at the office I slowly slowly drank coffee".

Notice that the adjective and the adverb use the same character (慢) so it's the context that tells you what it is. This is much simpler than learning "slow" and "slowly" as different words in English.

The other interesting aspect is that a typical translation like the one above, doubles up the word (慢). This is a common thing to do, and I'll talk about that more another day.

Another way this could have been said is:

今天早上在办公室，我喝咖啡慢慢地。
(Jīntiān zǎoshang zài bàngōngshì, wǒ hē kāfēi màn man de.)

This is literally "This morning at the office, I drank coffee slowly". And apart from the order of the time and place, is pretty similar to our preferred English option.

What happened to "ly" in English ?

Curiously though, the "ly" seems to be disappearing in English nowadays. Can't say I like it, but I hear people all the time saying things like:

Drive safe.

When I was young, my teacher would have scolded me for saying that, yet you'll see it on even government publications here now.

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SDU Tools: COBOL-CASE in SQL Server T-SQL

I've had a lot of good feedback about the options that we've provided in our free SDU Tools for developers and DBAs for formatting strings. Someone recently asked for a format with all capitals and dashes in between. I realised we didn't have that, and we've added it. Generally, in the industry, this is referred to as Cobol Case.

So, we added a function CobolCase.

It takes one parameter:

@InputString – the string to be formatted

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE OR ALTER FUNCTION SDU_Tools.CobolCase
(
    @InputString nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN

-- Function:      Apply Cobol Casing to a string
-- Parameters:    @InputString varchar(max)
-- Action:        Apply Cobol Casing to a string (similar to programming identifiers)
-- Return:        nvarchar(max)
-- Refer to this video: https://youtu.be/i1rnVlOR760
--
-- Test examples: 
/*

SELECT SDU_Tools.CobolCase(N'the  quick   brown fox consumed a macrib at mcdonalds');
SELECT SDU_Tools.CobolCase(N'janet mcdermott');
SELECT SDU_Tools.CobolCase(N'the case of sherlock holmes and the curly-Haired  company');

*/
    DECLARE @Response nvarchar(max) = N'';
    DECLARE @StringToProcess nvarchar(max);
    DECLARE @CharacterCounter int = 0;
    DECLARE @WordCounter int = 0;
    DECLARE @Character nchar(1);
    DECLARE @InAWord bit;
    DECLARE @CurrentWord nvarchar(max);
    DECLARE @NumberOfWords int;
    
    DECLARE @Words TABLE
    (
        WordNumber int IDENTITY(1,1),
        Word nvarchar(max)
    );
    
    SET @StringToProcess = LOWER(LTRIM(RTRIM(@InputString)));
    SET @InAWord = 0;
    SET @CurrentWord = N'';
    
    WHILE @CharacterCounter < LEN(@StringToProcess)
    BEGIN
        SET @CharacterCounter += 1;
        SET @Character = SUBSTRING(@StringToProcess, @CharacterCounter, 1);
        IF @Character IN (N' ', N'-', NCHAR(9)) -- whitespace or hyphens
        BEGIN
            IF @InAWord <> 0
            BEGIN
                SET @InAWord = 0;
                INSERT @Words VALUES (@CurrentWord);
                SET @CurrentWord = N'';
            END;
        END ELSE BEGIN -- not whitespace
            IF @InAWord = 0 -- start of a word
            BEGIN
                SET @InAWord = 1;
                SET @CurrentWord = @Character;
            END ELSE BEGIN -- part of a word
                SET @CurrentWord += @Character;
            END;
        END;
    END;
    IF @InAWord <> 0 
    BEGIN
        INSERT @Words VALUES (@CurrentWord);
    END;
    
    SET @NumberOfWords = (SELECT COUNT(*) FROM @Words);
    SET @WordCounter = 0;
    
    WHILE @WordCounter < @NumberOfWords
    BEGIN
        SET @WordCounter += 1;
        SET @CurrentWord = (SELECT Word FROM @Words WHERE WordNumber = @WordCounter);
        SET @Response += CASE WHEN @WordCounter > 1 THEN N'-' ELSE N'' END + @CurrentWord;
    END;
    
    RETURN UPPER(@Response);
END;
GO

CREATE OR ALTER FUNCTION SDU_Tools.CobolCase

(

@InputString nvarchar(max)

)

RETURNS nvarchar(max)

BEGIN

-- Function: Apply Cobol Casing to a string

-- Parameters: @InputString varchar(max)

-- Action: Apply Cobol Casing to a string (similar to programming identifiers)

-- Return: nvarchar(max)

-- Refer to this video: https://youtu.be/i1rnVlOR760

-- Test examples:

SELECT SDU_Tools.CobolCase(N'the quick brown fox consumed a macrib at mcdonalds');

SELECT SDU_Tools.CobolCase(N'janet mcdermott');

SELECT SDU_Tools.CobolCase(N'the case of sherlock holmes and the curly-Haired company');

DECLARE @Response nvarchar(max) = N'';

DECLARE @StringToProcess nvarchar(max);

DECLARE @CharacterCounter int = 0;

DECLARE @WordCounter int = 0;

DECLARE @Character nchar(1);

DECLARE @InAWord bit;

DECLARE @CurrentWord nvarchar(max);

DECLARE @NumberOfWords int;

DECLARE @Words TABLE

(

WordNumber int IDENTITY(1,1),

Word nvarchar(max)

);

SET @StringToProcess = LOWER(LTRIM(RTRIM(@InputString)));

SET @InAWord = 0;

SET @CurrentWord = N'';

WHILE @CharacterCounter < LEN(@StringToProcess)

BEGIN

SET @CharacterCounter += 1;

SET @Character = SUBSTRING(@StringToProcess, @CharacterCounter, 1);

IF @Character IN (N' ', N'-', NCHAR(9)) -- whitespace or hyphens

BEGIN

IF @InAWord <> 0

BEGIN

SET @InAWord = 0;

INSERT @Words VALUES (@CurrentWord);

SET @CurrentWord = N'';

END;

END ELSE BEGIN -- not whitespace

IF @InAWord = 0 -- start of a word

BEGIN

SET @InAWord = 1;

SET @CurrentWord = @Character;

END ELSE BEGIN -- part of a word

SET @CurrentWord += @Character;

END;

IF @InAWord <> 0

BEGIN

INSERT @Words VALUES (@CurrentWord);

END;

SET @NumberOfWords = (SELECT COUNT(*) FROM @Words);

SET @WordCounter = 0;

WHILE @WordCounter < @NumberOfWords

BEGIN

SET @WordCounter += 1;

SET @CurrentWord = (SELECT Word FROM @Words WHERE WordNumber = @WordCounter);

SET @Response += CASE WHEN @WordCounter > 1 THEN N'-' ELSE N'' END + @CurrentWord;

END;

RETURN UPPER(@Response);

END;

Book Review: Atomic Habits

Another book that I listened to recently, also fits into the "I nearly didn't get past the first chapter" category. It was Atomic Habits by James Clear. By the end of this book though, I realised just how much I'd enjoyed it.

James makes a really interesting study into habits. Far more than anything I'd ever read before.

He makes it so clear how the compound effect of hundreds of small decisions leads to profound life changes. That was already pretty obvious to me but I loved the way he made this so practical. He calls the habits that are formed Atomic Habits.

As soon as I posted on Facebook about having enjoyed it, I was surprised by how many of my friends told me they'd loved it too.

I wasn't expecting to find a deep discussion on habits and the science behind how they form and how to change them so interesting, and I'm so glad I didn't give up near the beginning. The endless examples are particularly engaging.

The verdict ?

Have to say I loved it. 9 of out 10. If you need a bit of motivation after a year of covid-19, this might help.

ADF: Keep linked service names the same across environments for Azure Data Factory

I recently recorded a podcast with Kamil Nowinsky. Kamil is well-known for creating some tools that make it easy to publish individual Azure Data Factory pipelines. It's a far better method that the standard ARM (Azure Resource Manager) template deployment that's provided by Microsoft.

However, if you use this method (and I recommend you do), you'll be publishing pipelines separate to the linked services that your datasets connect to.

One mistake I've seen a few clients making lately, is that when they created the linked services in different environments, they did so with slightly different names. Because of that, they needed to modify the deployed objects after deployment. You don't want to be doing that.

Data Factory datasets use the name of the linked service to identify it, so it's really important that when you're creating the linked services that you keep the name of the linked services consistent across the different environments where you'll be deploying pipelines and datasets.

Even though the linked services will be pointing to different servers, etc. in different environments, keeping the names of the linked services consistent will make your life much easier.

SQL: Work arounds for multi-column IN queries in T-SQL

We all use IN when writing queries:

SELECT ProductID, ProductName, Color, Size

FROM dbo.Products

WHERE Size IN ('370ml', '220ml');

A challenge comes up though, when you want to find pairs of values using IN. For example, if I have the following pairs of values:

Size Color
370ml Blue
370ml Red
220ml Blue

How do I find those when using IN?

Other databases do allow you to have pairs:

SELECT ProductID, ProductName, Color, Size

FROM dbo.Products

WHERE (Size, Color)

IN (('370ml', 'Blue'), ('370ml', 'Red'), ('220ml', 'Blue'));

I wish T-SQL had that option but it doesn't. It would be particularly useful when those values in the IN clause are coming from a sub-query.

Using CONCAT

One option is to use CONCAT to concatenate the strings. It takes a list of values, ignores NULL values, implicitly casts all values to strings, and concatenates the results.

SELECT ProductID, ProductName, Color, Size

FROM dbo.Products

WHERE CONCAT(Size, Color)

(

CONCAT('370ml', 'Blue'),

CONCAT('370ml', 'Red'),

CONCAT('220ml', 'Blue')

);

That's the solution that looks closer to what you were trying to achieve, but it does more work than needed.

Using VALUES and a join

The other way is to just create a table expression using row constructors and to then join to it:

SELECT p.ProductID, p.ProductName, p.Color, p.Size

FROM dbo.Products AS p

INNER JOIN

(

VALUES('370ml', 'Blue'), ('370ml', 'Red'), ('220ml', 'Blue')

) AS c(Size, Color)

ON c.Size = p.Size

AND c.Color = p.Color;

Using EXISTS

Tiago Rente reminded me in the comments on LinkedIn that of course we could have used EXISTS rather than an INNER JOIN. I actually prefer the EXISTS as it keeps the filtering in a single predicate. It could look like this:

SELECT p.ProductID, p.ProductName, p.Color, p.Size

FROM dbo.Products

WHERE EXISTS (SELECT 1 FROM

( VALUES('370ml', 'Blue'),

('370ml', 'Red'),

('220ml', 'Blue') ) AS Selected(Size, Color)

WHERE Selected.Size = p.Size

AND Selected.Color = p.Color);

Using a CTE for Clarity

Anthony Duguid also mentioned in the LinkedIn comments that he'd like a CTE in there, to allow for a clearer name for the subquery. In fact, I think I'd like the combination of a CTE and EXISTS the best:

WITH SelectedProducts

(

SELECT Size, Color

FROM ( VALUES('370ml', 'Blue'),

('370ml', 'Red'),

('220ml', 'Blue')

) AS sp(Size, Color)

)

SELECT p.ProductID, p.ProductName, p.Color, p.Size

FROM dbo.Products

WHERE EXISTS (SELECT 1 FROM SelectedProducts AS sp

WHERE sp.Size = p.Size

AND sp.Color = p.Color);

T-SQL 101: #82 Using PARSE to convert between data types

If you are using a string that was provided by another system, it might not be in the format that you were hoping it would be. So for example, if I have this string that says 2/29/2016, I know that's a US date format, but if it was only 5/4/2016, there's no way I could just easily tell that.

Conversion functions use your session settings to determine how to convert the values. When I'm converting strings to dates (and/or times), I certainly don't want to change my settings so that I run with US configuration.

What I want to do is just have a way of saying look just temporally, use US settings to parse that string. And that's what the T-SQL PARSE statement does!

In the example above, I'm asking SQL Server to convert the string '2/29/2016' to a datetime2, but no matter what settings I currently have, it should interpret it with 'en-US' culture i.e. US date format. (en-US is English USA)

You might be puzzled by the output of the statement. Keep in mind that what's happened, is that SQL Server has taken that string, converted it to a datetime2 using US format, and then sent a datetime2 back to the client (SQL Server Management Studio). SSMS has then just displayed the datetime2 value in its default format.

The main thing is that it's interpreted in the input string using US format without me changing my session settings.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Sentence word order – Part 3 – Where an action happened

In my first word order post, I explained the basic Subject-Verb-Object sentence order. Then in a second post, I explained how time (when something happened) normally gets added to sentences. In this post, I'll look at how you show where something happened.

Where an action happened

In English, we can do this a few ways:

1 – This morning I drank coffee in the office.

2 – In the office, I drank coffee this morning.

3 – I drank coffee in the office this morning.

I'd say that the third option is probably the most common, and the "cleanest". The first isn't too bad but the second sounds odd.

Even though #3 is the one we'd probably use in English, it's not the way we'd say it in Mandarin. The location just doesn't go at the end of the sentence if it's not the object. It's usually straight after the subject.

Here's an example:

我今天早上在办公室喝咖啡。(Wǒ jīntiān zǎoshang zài bàngōngshì hē kāfēi.)

That's literally:

I, this morning in the office, drank coffee.

And yes, that sounds odd to us.

The word 在 (zài) has many uses but is usually translated as "at".

办公室 (bàngōngshì) is the office.

Last time I also mentioned that 喝了 (hēle) means "drank". It was the verb to drink, followed by an indication that the action had completed.

Yet even though it's the verb to drink again here, the 了 isn't part of the sentence. It's because the time component is there.

Learning Mandarin