The Bit Bucket - Thoughts from Data Platform MVP and Microsoft RD

Learning Mandarin: Words that mean the opposite to themselves

One thing I've come across in Mandarin that I don't recall striking often in English are words that have multiple meanings, and the meanings are the opposite of each other. They do exist in English, and are called Janus words or sometimes contronyms, antagonyms, or auto-antonyms.

An English example is the word "sanction". It can mean to give official permission or approval for (an action), but it can also mean to impose a penalty on. Another example is "seed". If you seed a lawn, you put seeds into it. If you seed a tomato, you remove the seeds from it. Others are less obvious like "trim" which could mean to add extras to the edge of something, or to remove them. But there aren't a lot of these.

In the main image above, you can see the Chinese word 借 (Jiè) and Google Translate shows the meaning as "borrow".

All good. But now check out the alternate meanings that Google provides:

So "borrow" and "lend" ?

When I first came across this word, I had no idea how I'd know what was going on. But as always, context is everything.

你借我一块钱。(Nǐ jiè wǒ yīkuài qián.)

Google says "You lend me a dollar". Now it's not really a dollar but the basic idea is there. A dollar would be more like 一美元 (yī měiyuán) for an American dollar, or 一澳元 (yī àoyuán) for an Australian dollar. kuài is sort of a "piece" and qián is "money". Here it would mean one Chinese Yuan or about 1/6th of a dollar.

Similarly, turning it around the other way is:

我借你一块钱。(Wǒ jiè nǐ yīkuài qián.)

"I lend you a dollar". So that didn't do the trick. However:

我从你借了一块钱。(Wǒ cóng nǐ jièle yīkuài qián.)

"I borrowed one dollar from you".

从 (cóng) is essentially "from".

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.

SQL Server Express and Reporting Services

I'm a fan of SQL Server Express edition. It's a fabulously functional free database that lets you work with up to 10GB of data per database. One aspect that's commonly misunderstood though, is that it also supports a limited version of SQL Server Reporting Services.

There are many scenarios where Express edition is the perfect fit. The Microsoft sales and marketing teams have had an ongoing strange relationship with Express edition because it's free. Much of the documentation tried to suggest that it was for hobbyists, and para-professionals. But Express edition has a great role to play in professional systems too. For example, if you needed a small database to run on a series of point of sale devices, it could well be the right answer, particularly if it's then connected to one of the higher editions as a central SQL Server system.

I used to use it many years ago as an easy way to provide a system test system for clients using one of our applications. That allowed them to get established quickly without SQL Server licensing. When they had been using it for a while, and their databases grew in size, that was the easiest upgrade you'd ever convince customers to do.

Reporting

What wasn't understood well early on, is that reporting was also needed. I remember arguing passionately about why Express edition needed to include some form of Reporting Services. The marketing teams didn't at first understand that if a software house needed a reporting solution, they weren't going to use something different on Express to what they used on other editions. They'd simply use a different reporting tool where they only had to write the code once.

Express edition regularly helped us to sell paid SQL Server licenses.

Fortunately, the teams eventually saw the light on this one, and a version of Express with Advanced Services was released. It included a limited form of Reporting Services. It was perfectly functional, but didn't allow you to report on any data, except from the Express edition it was running from. (This avoided Microsoft providing a free reporting tool for the world). I thought that was a great option.

So where it is now?

Lately though, I've been asked where Reporting Services for Express went. It's not in that Advanced Services option any more. Other good things are, like full text, but not Reporting Services.

The reason for that, is that since SQL Server 2016, Reporting Services has become a separate install. It's not directly bundled with SQL Server. And that applies to Express edition as well.

You need to install Reporting Services separately. It still understands deployment against Express edition.

You'll find details of installing it here.

One thing to note, is that since the 2016 change, multiple instances of Reporting Services on a single machine are not supported. (You could do that previously).

Isn't it all about Power BI now?

Power BI is outstanding, but there are many scenarios where Reporting Services is still the best tool. Standard reports from Express edition is a great example.

If you need to learn more about Reporting Services, we've got a course to help you learn to use it properly. You'll find details of it, and our other courses, here: https://training.sqldownunder.com.

Learning Mandarin: Asking questions

I learned Japanese for many years in my teens. And I certainly remember that the most common way to make a sentence into a question, or to recognize a question, was that it ends in "ka". Mandarin has a similar word in

吗 (ma)

It's often translated as a question mark. If you look carefully, you can see that the character has two characters (or radicals) within it. On the left is 口 (Kǒu) which means "mouth". That provides part of the meaning. On the right is 马 (Mǎ) which means "horse". It's used to provide the sound, not the meaning.

Let's see an example:

你有朋友。(Nǐ yǒu péngyǒu.)

That literally is "You have friend", but more likely "You have friends".

If we add the question mark:

你有朋友吗？(Nǐ yǒu péngyǒu ma?)

And it becomes "Do you have friends?"

Note that even in English, we could use the sentence "You have friends", as a question, just by changing the tone of how we say it.

Learning Mandarin

SDU Tools: Date of Orthodox Easter in SQL Server T-SQL

Some time back, we added DateOfEasterSunday to our free SDU Tools for developers and DBAs. Given it was the Christian Easter Sunday, almost immediately, I got a request for the Greek Orthodox Easter. That date isn't of course just the Greek one, so we've added a new function DateOfOrthodoxEaster.

It's based on a concept from Antonios Chatzipavlis. Thanks !

It takes a single parameter:

@Year int – the year to find the date for

And the return value is a date.

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.DateOfOrthodoxEaster
(
    @Year int
)
RETURNS date
AS
BEGIN

-- Function:      Returns the date of Orthodox Easter in a given year
-- Parameters:    @Year int  -> year number
-- Action:        Calculates the date of Orthodox Easter for
--                a given year, adapted from a code example 
--                courtesy of Antonios Chatzipavlis
-- Return:        date
-- Refer to this video: https://youtu.be/QbYx4k0ey8k
--
-- Test examples: 
/*

SELECT SDU_Tools.DateOfOrthodoxEaster(2020);
SELECT SDU_Tools.DateOfOrthodoxEaster(1958);

*/
    RETURN DATEADD(DAY, 13, CAST(CAST(@Year AS varchar(4))
                            + RIGHT('0' + CAST(((((19 * (@Year % 19) + 15) % 30) 
                                                + ((2 * (@Year % 4) + 4 * (@Year % 7) 
                                                - ((19 * (@Year % 19) + 15) % 30) +34) % 7) 
                                                + 114) / 31) AS varchar(2)), 2)
                            + RIGHT('0' + CAST((((((19 * (@Year % 19) + 15) % 30) 
                                                + ((2 * (@Year % 4) + 4 * (@Year % 7) 
                                                - ((19 * (@Year % 19) + 15) % 30) +34) % 7) 
                                                + 114) % 31) + 1) AS varchar(2)), 2) AS date));
END;
GO

CREATE OR ALTER FUNCTION SDU_Tools.DateOfOrthodoxEaster

(

@Year int

)

RETURNS date

BEGIN

-- Function: Returns the date of Orthodox Easter in a given year

-- Parameters: @Year int -> year number

-- Action: Calculates the date of Orthodox Easter for

-- a given year, adapted from a code example

-- courtesy of Antonios Chatzipavlis

-- Return: date

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

-- Test examples:

SELECT SDU_Tools.DateOfOrthodoxEaster(2020);

SELECT SDU_Tools.DateOfOrthodoxEaster(1958);

RETURN DATEADD(DAY, 13, CAST(CAST(@Year AS varchar(4))

+ RIGHT('0' + CAST(((((19 * (@Year % 19) + 15) % 30)

+ ((2 * (@Year % 4) + 4 * (@Year % 7)

- ((19 * (@Year % 19) + 15) % 30) +34) % 7)

+ 114) / 31) AS varchar(2)), 2)

+ RIGHT('0' + CAST((((((19 * (@Year % 19) + 15) % 30)

+ ((2 * (@Year % 4) + 4 * (@Year % 7)

- ((19 * (@Year % 19) + 15) % 30) +34) % 7)

+ 114) % 31) + 1) AS varchar(2)), 2) AS date));

END;

Making a cloud transformation, not just a migration – free course

In the dying days of PASS, one of the attempts at keeping the organization afloat was the introduction of the PASS Pro membership. One benefit of that was access to a series of Learning Experiences. Since the demise of PASS, our friends at Red-Gate have purchased the assets, and that includes those courses.

I was pleased to hear from the Red-Gate team that the courses have now been made available for free, and without any type of paywall/loginwall. That's just awesome.

The course that I built for them was on making a cloud transformation, instead of just a migration. So many times, I see CIOs/CTOs going on and on about the cloud transformation they are embarking on, yet I don't see them transforming anything. The cloud vendors love it, as it's an apparently quick way "to the cloud" for an organization. But for the organization, it often leads to lousy outcomes.

You can now see that course here.

The course is about 3 1/2 hours in length.

You'll find the other courses in that series here.

And did I mention free ?

T-SQL 101: #84 Avoiding data type conversion errors with TRY_CAST, TRY_CONVERT and TRY_PARSE

In a previous post, I showed how to use CAST and CONVERT. What I didn't mention before though, is what happens when the conversion will fail. If I try to convert the string 'hello' to an int, that just isn't going to work. Of course, what does happen, is the statement returns an error. Same thing happens if I try to convert the 30th February 2016 to a date. There aren't 30 days in February. Again, an error will be returned.

SQL Server 2012 though, added the ability to try to do a conversion, if it works, return the value, and if it fails, just return NULL. And so we got three versions of this:

TRY_CAST

TRY_CONVERT

TRY_PARSE

You can see in the example, that the first string isn't a valid date, so it returns NULL. The second one is valid, so we get back the converted value.

Now a common mistake that people make when writing T-SQL, is to write code in this type of format:

SELECT CAST(happened_when AS date)
FROM dbo.Events
WHERE ISDATE(happened_when) = 1;

SELECT CAST(happened_when AS date)

FROM dbo.Events

WHERE ISDATE(happened_when) = 1;

The problem with this, is that you might still get errors saying that happened_when couldn't be converted to a date. You'd think the WHERE clause would be applied first and would avoid that. But that might not happen.

Now you can deal with this in a robust way, by writing:

SELECT TRY_CAST(happened_when AS date) 
FROM dbo.Events 
WHERE ISDATE(happened_when) = 1;

SELECT TRY_CAST(happened_when AS date)

FROM dbo.Events

WHERE ISDATE(happened_when) = 1;

Even better, this would probably optimize simpler by just writing:

SELECT TRY_CAST(happened_when AS date) 
FROM dbo.Events 
WHERE TRY_CAST(happened_when AS date) IS NOT NULL;

SELECT TRY_CAST(happened_when AS date)

FROM dbo.Events

WHERE TRY_CAST(happened_when AS date) IS NOT NULL;

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.

Opinion: iPhone12 Pro is an interesting device but a lousy phone

I'd been using an iPhone6+ for quite a while, and it still worked fine. Lately though, I'd been running into things that needed a later version of iOS than what was supported on that phone. So I changed to an iPhone12 Pro.

So far, that's been a big mistake.

One thing I've seen over the years while working with technology, is that development teams often get enamoured with the new features. And they forget to make sure that core functionality works as expected. The core functions seem to get lost in the weeds.

The iPhone12 Pro is a fascinating device, and a wonder of technology. But it's a lousy phone.

The most basic functionality you'd require for anything that calls itself a "phone" is that it can make and receive phone calls. The iPhone12 Pro fails that simple test. So often now, I get an SMS from my carrier telling me that I missed a call and it went straight to voice mail. But the phone didn't ever ring in the first place.

I'm not alone. If you browse online for "iphone12 goes straight to voicemail", you'll find a large number of articles, some with many steps showing how people have tried to work around this issue. I've tried them all. In the end, I've concluded that it's just buggy. My previous iPhone worked fine in the same location, and on the same network. My wife's iPhone6S, sitting right beside me, works as expected.

Coverage

While investigating various issues, I've also been reading about all the people with basic connectivity issues. For some reason, the iPhone12 just doesn't maintain the same level of connection to a tower.

But so many people complaining that once they've changed to one, their phone coverage has really suffered.

Intelligence gone mad

I keep finding issues with this device (I won't call it a phone for the reasons above), where something doesn't work as expected. And when I search online, I find others who also were puzzled, and it turns out there's some new "intelligent" feature that is enabled by default, and makes the device act strangely.

Here's a simple example: If I plug in the device to charge at say 10PM, I'd expect it to have fully charged within an hour or so. But if I pick it up at 4AM because I need to work early that day, it's not fully charged. Why? Because the phone decided that I normally take it off the charger at 7:30, and decided to go into a slow charging mode (no doubt well-intentioned), that will make it ready by 7:30AM.

This is intelligence gone mad. Great to have an option for that if I want to choose it, but making it a default that I need to find a way to turn off? Not so much.

Appropriate change methods

The Apple (wrong) way to introduce these breaking "features" is to just enable what they think you'll want, even though it will often end up confusing the user, who hasn't got a clue what's happened.

The right way to introduce it would be for the feature to be off, and for the device to tell the user "hey I noticed you normally take this off the charger at 7:30AM. If I slowed down the charging rate so it's ready by then, your battery life would be longer. Do you want me to enable that?"

Appalling recovery options

These devices now come with a large amount of memory. Yet Apple hasn't worked out that devices with a large amount of memory need better backup/restore options.

For example, when they can't explain problems, the support people often suggest reinstalling the device from a backup. Or if they change the device, you need to do the same. I tried their option for iCloud backup/restore once. It took quite a while to back it up, but when I tried to restore it on the new device, even on a fast connection, it took 3 days. That's simply not fit for purpose.

Worse, when it finally finished, it just calmly said "Some files were not restored". No hint, no idea which files.

How did this ever become acceptable?

And if you have a device with 256GB+ memory, and you need to change to a new device, what are your options? You can backup/restore to a PC, but once you're done, you have a mess that takes ages to sort out. Most of your data (e.g. music, books, etc.) won't be there. They assume you need to download it all again.

How is that reasonable when you have them all sitting in a device right beside you?

Heaven forbid if you have a bunch of things like authentication apps that all need to be set up again.

Overall

I can't tell you how much time I've wasted with the iPhone12 Pro, just trying to get it to perform with basic functionality. Wish it wasn't so.

SDU Podcast #82 with guest Kamil Nowinski now released

I recorded another podcast with an old friend recently. I met Kamil Nowinski in Poland when I was invited to speak at SQL Day. That event is on again this year, but as a virtual event only.

In our consulting work, we use Azure Data Factory frequently. Deploying it, can be a real challenge. Microsoft has a supplied method with ARM templates but that has issues. Kamil produced free tooling that's in the Azure Marketplace and makes it much easier to work with.

In this podcast, Kamil describes the overall process, and how the challenges arise with the Microsoft-supplied mechanism, and the way his tooling makes it easier.

If you want to understand more about Azure Data Factory (ADF) and how it interacts with source control, and deployment, this is a great show to listen to.

You'll find the podcast with Kamil (and all our other podcasts) here: https://podcast.sqldownunder.com

SDU Tools: Check if Lock Pages in Memory is Enabled on SQL Server

In our free SDU Tools for developers and DBAs, we have a lot of procedures and functions for checking system and database configuration details. One that we were asked for a function for, was the ability to check if the LockPagesInMemory (LPIM) setting was enabled, so we added that.

The function is called IsLockPagesInMemoryEnabled.

It takes no parameters, and returns a bit to indicate if the value is enabled or not.

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.IsLockPagesInMemoryEnabled()
RETURNS bit
AS
BEGIN
/* 

-- Function:      IsLockPagesInMemoryEnabled
-- Parameters:    Nil
-- Action:        Checks if LPIM is enabled
-- Return:        bit
-- Refer to this video: https://youtu.be/QRgAwqSJysg
--
-- Test examples: 

SELECT SDU_Tools.IsLockPagesInMemoryEnabled();

*/
    RETURN CASE WHEN EXISTS (SELECT 1 
                             FROM sys.dm_os_process_memory WITH (NOLOCK)
                             WHERE locked_page_allocations_kb > 0)
                THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
           END;
END;
GO

CREATE OR ALTER Function SDU_Tools.IsLockPagesInMemoryEnabled()

RETURNS bit

BEGIN

-- Function: IsLockPagesInMemoryEnabled

-- Parameters: Nil

-- Action: Checks if LPIM is enabled

-- Return: bit

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

-- Test examples:

SELECT SDU_Tools.IsLockPagesInMemoryEnabled();

RETURN CASE WHEN EXISTS (SELECT 1

FROM sys.dm_os_process_memory WITH (NOLOCK)

WHERE locked_page_allocations_kb > 0)

THEN CAST(1 AS bit)

ELSE CAST(0 AS bit)

END;

SQL: Global temporary tables are almost never the answer in SQL Server

I was doing some consulting recently and reviewing a developer's code. I was surprised to find extensive use of global temporary tables. Let me start by saying this: global temporary tables are almost never what you should be using.

When I asked why he'd used them, he told me that he wanted the temporary tables that he created in his procedure, to be available to other procedures that were run from within the procedure. So in PROC-A, he was running PROC-B and PROC-C. A temporary table was created in PROC-A and he wanted to be able to use it in PROC-B and PROC-C.

Local vs Global Temporary Tables

Local temporary tables (i.e. tables with names starting with a single #) are available where they are created, and in any other procedures run from within that same scope. You don't need a global temporary table to do that.

Local temporary tables are dropped when they go out of scope (i.e. the session or context where they were created is dropped). And of course you can manually drop them with a DROP statement as well.

Ideally, you'd always drop them in your code rather than waiting for them to go out of scope. tempdb is a shared resource and you don't want it clogged up with unnecessary data for too long.

Global temporary tables (i.e. tables with names starting with ##) are visible to all users. These are dropped dropped when the session that created the table ends and all other tasks have stopped referencing them (ie. when other queries that are using them have completed). And yes, you can manually drop them with a DROP statement.

I'm not a fan of creating user objects in tempdb but if you really wanted a temporary table that stayed until the server restarts, one curious option would be to create a user table in tempdb automatically every time the system starts up. You could do that with either an Agent job (easiest) or by assigning a startup procedure (more reliable but also requires a configuration change).

Abstraction

One thing you need to decide early on though, is if temporary tables are the right way for you to pass data from one proc to another. They're certainly easy, and importantly, the data doesn't get duplicated when the sub-procedure is called.

However, for me the jury is out on whether this is a good idea or not. What you end up doing is making the sub-procedure much harder to test. It ends up referring to a table that doesn't exist anywhere in the procedure's code. That just breaks all concepts of abstraction pretty solidly.

As I said though, people like it for performance. You aren't copying the data.

An option to consider instead is creating a real table and putting the temporary data there, keyed off your session ID. Then the only issue is how it gets emptied again. That wouldn't have an automatic clean-up, and that's important if you have people creating temporary data and never removing it themselves; just letting it go out of scope.

Dropping and Recreating

Before creating temporary tables, it's common to make sure they don't already exist. In the past, you'd often see this type of code:

IF OBJECT_ID('tempdb..#SourceData') IS NOT NULL
BEGIN
    DROP TABLE #SourceData;
END;

CREATE TABLE #SourceData
(

IF OBJECT_ID('tempdb..#SourceData') IS NOT NULL

BEGIN

DROP TABLE #SourceData;

END;

CREATE TABLE #SourceData

(

Now you can just use:

DROP TABLE IF EXISTS #SourceData;

CREATE TABLE #SourceData
(

DROP TABLE IF EXISTS #SourceData;

CREATE TABLE #SourceData

(

NOTE: When you create these tables, you don't provide a schema name. In fact if you do, it's ignored.

When are global temp tables needed or useful ?

I almost never use them. However, they do have uses. For example, if I wanted to "snoop" on the data that's in a temporary table in another session, I could use a global temporary table for that.

I've seen them used for licensing schemes i.e. counting the number of active sessions. But nowadays, if you can, an in-memory non-durable table would probably work better.

In most cases that I see them used, a "real" table would actually be a better solution.