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

T-SQL 101: #39 Numeric operators in SQL Server

Today, I'm continuing the discussion on the variety of data types supported by SQL Server. I'll round out the discussion by looking at the operators that are used with the numeric data types. Here are the operators:

Some of these are fairly obvious but even then, they can have hidden complexity.

We use the plus sign + to add numbers, and we use the minus sign (or dash) – to subtract numbers. No surprise there.

Multiplication uses the asterisk * and is also easy enough. However, keep in mind that when you use multiplication, you probably should also be thinking about rounding if you are working with decimal values, and not just integers.

When you multiply an integer by a decimal, the result is promoted to being a decimal (i.e. the higher data type).

Division uses the slash / sign. What confuses most people is how it applies to data types. For example, what would you expect to see output from this query:

SELECT 10 / 4;

If you said 2.5, you wouldn't be alone, but you'd also be wrong. It would output the value 2.

When you divide an integer by an integer, the result is an integer, not a decimal. Now if you executed this instead:

SELECT 10.0 / 4.0;

you would see 2.5 as the answer. A decimal divided by a decimal returns a decimal.

But what would happen to this one:

SELECT 10 / 4.0;

Again, you'd get 2.5 as the answer. An integer divided by a decimal will return the higher data type i.e. a decimal.

Finally, the % sign is the modulo operator. You can think of modulo as the remainder after a division. So this query:

SELECT 10 / 3;

would return the value 1. That's because as integers, 10 divided by 3 is 3, with a remainder of 1.

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: Chinese belief in luck

I need to start by coming right out and saying that I don't believe in things being lucky or unlucky. Many things that appear to have come from luck just haven't. Sometimes things go the way you hope, and other times they don't go the way you hope. And bad things can just happen for no good reason. What you can do is put yourself in a position where you increase your chances of a good thing happening. That's making your own luck.

I think that a belief in things being lucky or not, grew from an age when we just understood far less about how the world works. The rituals followed by cargo cults are a great example.

Belief in luck in Asian Cultures

However, in Chinese culture (and in most Asian cultures), there is a deep-seated belief in luck, and it's reflected all through superstitions and in the language.

I've previously described why you'll have a hard time selling a house numbered 4 to a Chinese person, and even harder time selling one numbered 24.

And there are so many actions that are considered lucky or unlucky:

Throwing baby's teeth that have fallen out on the roof (good luck)
Washing or cutting your hair the last or first day of the year (bad luck)
Giving someone a clock as a present (bad luck)
Clip your nails at night (bad luck for inviting ghosts to that place)
Keeping pet turtles (bad luck for slowing down fortune)
Sweeping during New Year (bad luck for sweeping away fortune)
Red color (lucky)

Now the Chinese are not alone on this. Many westerners hold strong superstitions as well. I remember the time I put shoes on a table when I was young. The problem wasn't dirt (they were brand new shoes); the problem was my father's belief that it brings death.

Literally translated, luck is 运气 (Yùnqì).

The first character 运 (Yùn) has a number of meanings, and one is related to luck. The second character 气 (Qì) usually relates a bit to gas or spirit.

The down side

丰富 (Fēngfù) means rich or plentiful and you'll often see it shown in casinos and gambling places that Asian people frequent.

As a final note, I should add that this continuing belief in luck has a dark side as well. Gambling addiction is very prevalent in Asian cultures. Casinos here in Australia and in other countries make no secret that their top market is "Asian High Rollers".

Learning Mandarin

I'll write more soon on the best methods for learning. 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.

Snowflake for SQL Server Users – Part 10 – Working with file formats

One thing that I quite like about Snowflake is the way it cleanly works with a wide variety of file formats.

Obviously this changes over time but at the time of writing, you could COPY from the following source file formats:

CSV
JSON
AVRO
ORC
PARQUET
XML

There are also quite a number of options for configuring how these are used. Apart from the obvious options like record and field delimiters, skipping rows, etc, one of the most important of these options is compression. You can currently choose these options for compression:

AUTO
GZIP
BZ2
BROTLI
ZSTD
DEFLATE
RAW_DEFLATE
NONE

I had good outcomes using the AUTO setting. Mostly I'm using zip'd input files.

There are a few options for transforming data while loading it, but they are very limited. For example, you can alias a column or change its position.

Named File Format

Similar to the way you can avoid specifying full file locations every time by creating an external stage, you can avoid specifying all your file format details every time by creating a named file format.

No surprise, that's done with the CREATE FILE FORMAT command.

Data Export

When you export data, you again use the COPY command, but currently there are less file format options available than there are for input. You can use:

CSV
JSON
PARQUET

I would be surprised if the others aren't added soon.

SQL: How to control access to SQL Server tables by entries in another table

There was an interesting question in the forums the other day. The poster wanted to be able to put entries in a table to determine who could access data in the other tables in the database.

There are two basic ways to do this. If you want an error thrown, you'd be best just using GRANT/DENY/REVOKE as permissions and not using your own table to control it. However, if you want no error, but just no data, then the Row Level Security (RLS) added in SQL Server 2016 could do the job. Let's take a look:

Example

We'll start by creating two tables: one that I'll later be able to access and one that I won't:

USE tempdb;
GO

CREATE TABLE dbo.OKTable
(
    OKTableID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_dbo_OKTable PRIMARY KEY,
    SecuredInfo varchar(100)
);
GO

INSERT dbo.OKTable (SecuredInfo)
VALUES ('Very'), ('Secret'), ('Stuff');
GO

CREATE TABLE dbo.NotOKTable
(
    NotOKTableID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_dbo_NotOKTable PRIMARY KEY,
    SecuredInfo varchar(100)
);
GO

INSERT dbo.NotOKTable (SecuredInfo)
VALUES ('Other'), ('Important'), ('Things');
GO

USE tempdb;

CREATE TABLE dbo.OKTable

(

OKTableID int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_dbo_OKTable PRIMARY KEY,

SecuredInfo varchar(100)

);

INSERT dbo.OKTable (SecuredInfo)

VALUES ('Very'), ('Secret'), ('Stuff');

CREATE TABLE dbo.NotOKTable

(

NotOKTableID int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_dbo_NotOKTable PRIMARY KEY,

SecuredInfo varchar(100)

);

INSERT dbo.NotOKTable (SecuredInfo)

VALUES ('Other'), ('Important'), ('Things');

Next we need to create and populate the table of who can access what:

CREATE SCHEMA [Security] AUTHORIZATION dbo;
GO

CREATE TABLE [Security].PermittedTableUsers
(
    PermittedTableUsers int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_Security_PermittedTableUsers 
        PRIMARY KEY,
    UserName sysname,
    SchemaName sysname,
    TableName sysname
);
GO

INSERT [Security].PermittedTableUsers (UserName, SchemaName, TableName)
VALUES (N'dbo', N'dbo', 'OKTable');
GO

CREATE SCHEMA [Security] AUTHORIZATION dbo;

CREATE TABLE [Security].PermittedTableUsers

(

PermittedTableUsers int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_Security_PermittedTableUsers

PRIMARY KEY,

UserName sysname,

SchemaName sysname,

TableName sysname

);

INSERT [Security].PermittedTableUsers (UserName, SchemaName, TableName)

VALUES (N'dbo', N'dbo', 'OKTable');

Notice that I've placed it in a separate Security schema to make it easier to control who has access to all the security-related code and objects.

Then I create a function that determines if permission is granted:

CREATE FUNCTION [Security].CheckUserAccess
(
    @SchemaName AS sysname,
    @TableName AS sysname
)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS CheckUserAccessOutcome
           WHERE EXISTS (SELECT 1 FROM [Security].PermittedTableUsers AS ptu
                                  WHERE ptu.UserName = USER_NAME()
                                  AND ptu.SchemaName = @SchemaName
                                  AND ptu.TableName = @TableName);
GO

CREATE FUNCTION [Security].CheckUserAccess

(

@SchemaName AS sysname,

@TableName AS sysname

)

RETURNS TABLE

WITH SCHEMABINDING

RETURN SELECT 1 AS CheckUserAccessOutcome

WHERE EXISTS (SELECT 1 FROM [Security].PermittedTableUsers AS ptu

WHERE ptu.UserName = USER_NAME()

AND ptu.SchemaName = @SchemaName

AND ptu.TableName = @TableName);

The function checks if the logged on user has an entry for the table (schema name and table name used to identify the table).

Note that I've used the USER_NAME() function to find out who's logged on. It can be susceptible to impersonation issues so you might consider making it logon names instead of user names and using the ORIGINAL_LOGIN() function instead. But this will do for now.

Next, I apply the RLS security policy to every table:

CREATE SECURITY POLICY OKTableAccessFilter 
ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'OKTable')
ON dbo.OKTable  
WITH (STATE = ON); 
GO
CREATE SECURITY POLICY NotOKTableAccessFilter 
ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'NotOKTable')
ON dbo.NotOKTable  
WITH (STATE = ON); 
GO

CREATE SECURITY POLICY OKTableAccessFilter

ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'OKTable')

ON dbo.OKTable

WITH (STATE = ON);

CREATE SECURITY POLICY NotOKTableAccessFilter

ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'NotOKTable')

ON dbo.NotOKTable

WITH (STATE = ON);

And then we're done. I'm logged on a dbo user, so if I try to access the tables, I see the outcome in the main image above. I can see the data in the OKTable but I can't see the data in the NotOKTable.

You can find out more about Row Level Security here.

SQL: When inserting SQL Server data in other languages doesn't work as expected

This post relates to another question I got on Stack Overflow recently.

The poster was saying that he was having no luck inserting data from teh Gujarati language, even though he was using nvarchar as a data type.

The most common problem that I see when people aren't getting the outcome they want when inserting into an nvarchar column is that they aren't putting N in front of their string values. (N is National Characters Set). Imagine a table like this:

CREATE TABLE dbo.Test 
( 
    TestID int IDENTITY(1,1) PRIMARY KEY, 
    TestValue nvarchar(100) 
);

CREATE TABLE dbo.Test

(

TestID int IDENTITY(1,1) PRIMARY KEY,

TestValue nvarchar(100)

);

Here's a Chinese example that won't work (because of multi-byte string values):

INSERT dbo.Test (TestValue) VALUES ('Hello 你好');

1	INSERT dbo.Test (TestValue) VALUES ('Hello 你好');

You won't get an error on the insert, but if you then query it, you'll see this:

With just single quotes, it's just an ANSI string, basically made up of ASCII values, and so your multibyte characters get messed up.

Let's try that with and without the N using Gujarati:

TRUNCATE TABLE dbo.Test;

INSERT dbo.Test (TestValue)
VALUES ('lakhnār'), ('vikǎselũ');

INSERT dbo.Test (TestValue)
VALUES (N'lakhnār'), (N'vikǎselũ');

SELECT * FROM dbo.Test;

TRUNCATE TABLE dbo.Test;

INSERT dbo.Test (TestValue)

VALUES ('lakhnār'), ('vikǎselũ');

INSERT dbo.Test (TestValue)

VALUES (N'lakhnār'), (N'vikǎselũ');

SELECT * FROM dbo.Test;

You can see the output in the main image above. Note that in rows 1 and 2, the characters are almost what was input. In this case, there is a basic mapping to single byte characters that worked out not too bad, but often you'll just see ?? instead.

In rows 3 and 4, where we used the N for the string definition, all worked as expected.

SDU Tools: SQL Server Reporting Services Catalog Types

Lately, I've been needing to write queries against the SQL Server Reporting Services catalog. And if you've ever tried that, you'll find that items in the catalog have a type, but there's no table or view that turns that type (a number) into a name.

So, in our free SDU Tools for developers and DBAs, we added a simple view that does just that. It's called RSCatalogTypes.

Find out more

You can see it in action in the main image above, and in the video here:

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

Opinion: Data professionals shouldn't be quick to mock Excel and Power Query

Knocking Access was a popular sport over the last decade or more. Many data professionals saw Access as a real problem. Lots of silos of unmanaged data grew up across organizations and things could get out of hand pretty quickly. I saw all the expected problems that come from a lack of centralized management of data.

Some issues were quite nasty. I remember doing work for a company that did aircraft maintenance and had depots all over the country. Every depot had a copy of an Access database, but every single one had then modified it in different, and in many cases, conflicting ways. Then they decided to centralize the data, and oh what a pain.

I still see lots of SQL Server people mocking Access.

The upside of Access

However, I think that many have also missed the core advantage that tools like Access brought. Power users were often fed up waiting for IT development teams to build what they needed, and so often, what was then built wasn't what was needed.

Today, I can see so many great SQL Server applications that would never have existed if the power users hadn't started them in Access.

Excel and Power Query

Now, I'm seeing exactly the same reticence regarding Excel and Power Query. IT teams are worried that so many unmanaged silos of information are growing up around their companies, and that Excel and Power Query are at the center of it.

I've been in the industry a long time. It's really, really rare for me to look at a new tool and be inspired. But Power Query had that effect on me.

Most organizations have a bunch of people that spend their days in Excel. It's pointless fighting that. And the success rate of introducing brand new BI tooling to those people is abysmally low. What I liked about the first incarnations of parts of Power BI is that if you took a person who already used Excel well, and just added more features to it, you were drastically more likely to see them actually use it.

And that's the beauty of Power Query. It lets those power users import and massage data into the shape they want. And, importantly, they will in many cases, create the start of important new applications that the organization needs.

I'm not talking about dabblers who aren't at the power user level, who could just spend endless hours not getting anywhere. I'm talking about the people who can make progress and start to create applications.

If their work becomes important, there are good options for IT to take over those applications and professionalize them. But without these tools, those applications would probably never exist in the first place, so don't be quick to mock them as though they're not "real" applications.

T-SQL 101: #38 Approximate numbers in SQL Server

Today, I'm continuing the discussion on the variety of data types supported by SQL Server. Last time I mentioned exact decimal numbers, but there are also inexact (or approximate) decimal numbers. Here are the available types:

These two data types float and real are approximate data types.

You probably realize that in decimal, there are values we can't store exactly like 1/3. No matter how many 3's we write when we write 0.33333333, we are still never going to have the exact value.

And the same happens in binary, just with different numbers. A simple example is ten cents. A value of 0.1 can't be stored exactly in float or real.

For that reason, they should only rarely ever appear in business applications. Using them for things like amounts of money is a really common newbie mistake when working with SQL Server.

There are places where float and real make sense but they are mostly in scientific and technical applications.

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: Northerners and er – Pirate speak?

I've found that most countries have different ways of saying things in different areas of the country or in nearby countries. As an Australian, I can immediately detect a Kiwi (New Zealander) if they say "fish", as I hear them say "fush" instead. We pronounce "tomato" like "tomarto" but the US folk say it like it's "tomayto". We say "banana" like "banarna" and they say it like "bananna".

And of course the same thing happens in China.

I have friends that describe northerners as using "Pirate Speak" because they add the sound "R" to the end of many words. (Pirates are renowned for saying "Arrrgh".

Most in the southern regions don't do this, so a northerner is easily detected.

Here are a few examples:

Instead of 一点 (Yīdiǎn) which means "a little bit" being pronounced like "ee dee enn", they say 一点儿 (Yīdiǎn er) which sounds like "ee deearr". the "n" sound is gone.

Instead of 一会 (Yī huǐ) which means "a little while" being pronounced like "ee hway", they say 一会儿 (Yīhuǐ'er) which sounds like "ee hwarr".

Next time you hear someone speaking Chinese, see if you can pick the "arr".

Learning Mandarin

Snowflake for SQL Server Users – Part 9 – Stages

Snowflake has the normal options in its SQL language for using INSERT statements to add data to a table. But it also has bulk options for rapidly adding a lot of data to a table via the COPY command. The same command can be used for bulk export as well.

A curious aspect of this though, is that because it's a cloud-only database system, you can't just use COPY to get data to/from a local file system. COPY works to/from what's called a stage.

A stage is a cloud-based storage location, that's just used as a staging location for data.

There are two basic types of stages: the ones provided within Snowflake itself, and the ones that are located in public cloud providers. For the public cloud providers, you can currently choose one of:

AWS S3 bucket
Azure storage location
Google cloud storage (GCS)

When you work with these, you can specify them as an external location (where you put the full address), or you can create an external stage. An external stage is just a name that you give to details of the location. That lets you just use a name, and not have to repeat details of where the location is, all through your code.

The stages provided by Snowflake are as follows:

Each user has a default stage when they are connected. A user stage is referred to by the name @~ and is automatically created for you.
Each table in the database also has a default stage associated with the name of the table. You refer to a table stage as @%tablename and again, these are automatically created for you.
You can also ask Snowflake to create a stage for you. You create an internal named stage by using the CREATE STAGE command. These are normally permanent but you can also make them session-temporary by adding the word TEMPORARY when creating them.

Next time, we'll look at the types of files that Snowflake can import/export.