The Bit Bucket

In a recent consulting project, I needed to plot analytic values over postcodes. I couldn’t just do it with the built-in maps control, mostly because the system that we’re developing on doesn’t have Internet connectivity. I was keen to upload the mapping data directly into the report, and the Shape Map control (while still in preview) seemed to fit what I needed exactly.

However, when you load custom maps into the control, they need to be in TopoJSON format. Now TopoJSON is a specific extension over GeoJSON which has been pretty common for a while. I tried just using a GeoJSON file with the control, to no luck.

2019-11-05

Another important concept when you’re working with strings is the concept of Collations. If I write code like the first line here:

The question is how would I know if a capital A is treated the same as a lower case A. The answer to that is “it depends upon the collation”. Collations are sets of rules that determine how strings are compared. SQL Server supports lots of collations. You can see them all by executing:

2019-11-04

Similar to SQL Server, Snowflake has ways of creating programmable objects. But the way they work, and the way they are created is quite different.

Functions

Functions are the most similar. You can create them in two ways:

• Javascript
• T-SQL

I like the idea that you can choose which language to write code in, and that they both end up extending the Snowflake SQL language pretty much the same. Some code is better written in T-SQL and other code is better written in a higher-level language.

2019-11-01

In Stack Overflow, I saw a poster with a query like this:

select * from student where stud_id in (
'5',
'3',
'4'
)

And he was frustrated that he couldn’t reliably get an ordered output like this:

id|   name  |
5 |  John   |
3 |  Erik   |
4 | Michael |

The problem is that he was assuming that the IN clause had a specified order that was then obeyed by the SELECT statement.

2019-10-31

Finishing up my recent theme of running queries against the SQL Server Reporting Services catalog. I often need to list which users have access to which items in the SSRS catalog.

So, in our free SDU Tools for developers and DBAs, we added a procedure that does just that. It’s called RSListUserAccessToContent.

It takes two optional parameters:

@RSDatabaseName sysname - the name of your SSRS database

@IsOrderedByUserName bit - should the output be ordered by user name?

2019-10-30

Over the years, I have long periods where I avoid Q&A forums. Lately, I’ve been spending a bit of time back in some forums. And once again, I’ve seen the sorts of behavior that make me think about leaving again.

Here’s a simple message: If you don’t like answering questions in Q&A forums, then don’t. Leave. You might think it’s all about you and your attempts to gain reputation in the forums. It’s not.

2019-10-29

When we write string literal values, we need to surround them in single quotes. There are options in some SQL products we could make that double quotes. In general though, don’t do that. Just use single quotes.

If you have multi byte characters, they need to be preceded by the N prefix. I’ve previously mentioned that it stands for National Character Set. If I don’t use the N, when I execute the second query, I’d see this:

2019-10-28

When you are loading data into Snowflake, it’s really important to achieve the maximum parallelism that you can. You want as many files loading in parallel as you have. I mentioned in earlier posts that the number of servers that you have, and the size of each of those servers, will determine the number of processor threads that are available to you from your virtual warehouses.

It would be pointless to have 32 processor threads waiting to load your data, and you provide Snowflake with one large file to load.

2019-10-25

I saw a discussion on a forum the other day about whether or not you could access the localdb version of SQL Server Express from another computer. Lots of people were saying it’s only a local tool and you can’t access it from elsewhere.

But that’s not true.

You actually can connect to a localdb instance from other machines. It’s a perfectly valid configuration. (Although, if you’re really running it like a service, you’d be better off with SQL Server Express Edition instead).

2019-10-24

Continuing my recent theme of running queries against the SQL Server Reporting Services catalog, I often need to list which users have access to the service.

So, in our free SDU Tools for developers and DBAs, we added a procedure that does just that. It’s called RSListUserAccess.

It takes two optional parameters:

@RSDatabaseName sysname - the name of your SSRS database

@IsOrderedByUserName bit - should the output be ordered by user name?

2019-10-23