Learning Mandarin: A little bit – yi dian vs you dian

The word 点 (Diǎn) is particularly useful. It basically means "a dot" like made with a writing brush, and from that, it means "a little bit".

I previously discussed how northerners (and Beijing folk) put "r" sounds on the end of many words. This is another one. So they'd often use 点儿  (Diǎn er) which is pronounced a bit like "dee-arrr".

There are two basic ways that点 gets used though.

Yīdiǎn

One common use is  一点 (Yīdiǎn).  The first character basically means "one" and this pair of characters is commonly used for "a little".

Yǒudiǎn

Another common use is 有点 (Yǒudiǎn). The first character in this case means "have" and this pair of characters also tends to mean "a bit".

So then, which is used when?

Let's see some examples:

今天我有点忙。(Jīntiān wǒ yǒudiǎn máng.) This means "today I'm a little busy". When 有点 is put in front of adjectives like "busy", it implies more of a negative connotation. It's almost like "today I'm a little too busy".

一点 can't be put in front of adjectives but it can be put after them.

请开快一点。(Qǐng kāi kuài yīdiǎn.) is "please drive a bit faster".

When 一点 is put in front of a noun, it means "a little" as a quantity.

请喝一点水。(Qǐng hè yīdiǎn shuǐ.) is "please drink a little water".

Slightly Tricky Negative Rules

There are also some rules for negatives. The most common "not" words are 不 (Bù) and 没 (Méi). (We'll talk another day about how they differ). But if you are putting 点 in front of either, it should be 有点.

他有点不高兴。(Tā yǒudiǎn bù gāoxìng.) is "he is a little unhappy".

However, if the sentence has 都不 (Dōu bù) or 也不 (Yě bù), then 一点 should be used instead.

她一点都不喜欢这音乐。(Tā yīdiǎn dōu bù xǐhuān zhè yīnyuè.) is "she doesn't like this music at all".

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.

Snowflake for SQL Server Users – Part 13 – Programmable objects

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.

Functions are called as expected, in place of an expression.

Both scalar functions and table-valued functions are supported. Scalar functions must return a value.

Stored Procedures

There is the ability to write stored procedures, but curiously, you can only do that in Javascript.

I have to say I've never worked with a SQL database engine before that supports stored procedures but won't let you write stored procedures in SQL. I think this is quite a shortcoming in the product.

Stored procedures are called using the CALL statement (not EXEC as in SQL Server). Another curious aspect is that even though the stored procedures support a return value, the syntax for calling stored procedures via CALL doesn't support retrieving a return value. I have to say, that's quite bizarre.

You can pass values back from stored procedures by using temporary tables. Or if the returned data is small enough, you might be able to stuff it into a variant data type object and return that.

Stored procedures can be nested.

Triggers

There is currently no concept of a trigger in Snowflake at this time. That means neither DML (INSERT/UPDATE/DELETE) triggers and DDL (CREATE/ALTER/DROP/LOGON) triggers.

As triggers are often a necessary evil in some applications, this again is a significant shortcoming of the product at this time.

 

 

SQL: Just like tables, IN clauses have no default order

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

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

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

It doesn't.

If you want a SELECT statement to return rows in a particular order, you need to use an ORDER BY clause, and, in this case, you need something to order the rows on in the first place.

I think the easiest way to do that is with a table variable that has an IDENTITY column like this:

I think that's about the simplest way to do it, particularly if there are a lot of values that could be in that IN clause.

Hope that helps someone.

 

SDU Tools: List user access to Reporting Services content items

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?

The database name will default to ReportServer which is the name that SSRS uses during install if you don't change it.

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: If you don't like answering questions, leave the forums

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.

It's about the people who need help.

There are a few personality types that I really want to call out:

The "you didn't search well enough before asking the question" type.

These people will berate others for asking questions that have been asked before. Get over it. People will ask the same questions again and again. Not every one of them is a search whiz. And who cares anyway? Just answer their question and let them get on with their lives. If you don't want to do that, just stop doing it.

Worse, I've seen examples lately where questions are closed as duplicates, and the person who jumped into to close it quickly, has totally misunderstood that it's really not actually a duplicate.

The "you're doing a dumb thing; I'm really clever but you should go back to school" type.

These people are more intent on trying to make themselves look clever, belittling the questioner, and basically telling them they're stupid for what they're trying to do. Yet in 99% of cases, they don't give them an answer to their question, or point them in the right direction. Just give the questioner a break; do a bit extra work in showing them what they should be doing.

The "I'm so bored with answering questions that I really can't be bothered" type.

These people will often have compiled a massive FAQ, and the answer to every question is that people will find the answer (somewhere) in the FAQ. If you have the answers so easily at hand, just copy and paste it in, to help the questioner. Don't make them play Where's Wally to find the answer.

Look, if you aren't spending time in the forums with a genuine desire to help people, thank you for your previous efforts but might be time for you to just take a break.

And one for the forum people too: Is it really necessary to remove human interaction and warmth from the responses? When I finish a response with something like "Hope this helps" as I normally would when talking to a human, invariably one of the rules enforcers edits my post and removes that comment. Really? I can see why many people find the forums pretty toxic.

 

 

T-SQL 101: #41 Using string literals in SQL Server

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:

My multi-byte Chinese characters get replaced by question marks.

However, if I include the N, I see this:

Without the N, we just get the ANSI characters, basically our normal English alphabet and numbers and punctuation and so on. The minute you need to use a language that has way more characters than we do, then you can't just use single quotes or the values won't be interpreted correctly.

One other thing worth noting is that sometimes you need to put a quote in the middle of a string. To do that, you use two quotes like this:

Notice that when I execute this, only one quote is output.

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: Common newbie mistake is to overuse "is"

In many ways, verbs in Mandarin are easier than the ones in English. There's no need to learn any equivalent of conjugation and tense.

In English, we have a verb like make but then we have to learn made, making, and how to use will make, has been made, etc.

In Chinese, there's pretty much a single form of the verb, and there's another character that is often used to indicate that an action is complete.

Do we even need the verb?

Even simpler, the verb is often omitted entirely and replaced by a type of adverb. Let me show you a concrete example:

是 (Shì) is the verb closest to "is". (As a verb, perhaps also "be" or "exist").

The general use of "is" is quite like in English.

我是澳大利亚人。(Wǒ shì àodàlìyǎ rén.) means "I am Australian". Literally, it's like "I is Australia person".

车是蓝色的。(Chē shì lán sè de)  means "the car is blue". Literally, it's like "car is blue colored".

Common Mistake

But the common mistake made by many new learners is to include the "is" when it's not needed.

Coming from English, if we try to translate "she is beautiful", it's tempting to try:

她是漂亮。(Tā shì piàoliang.) Literally "she is beautiful".

But that's incorrect. Instead, when combining a subject with an adjective, you normally use the word 很 (Hěn) which is pronounced somewhat like "hun" but has a falling and rising tone in the middle. It would normally be translated as "very".

So it's used like this instead:

她很漂亮。(Tā hěn piàoliang.)  Literally, this is "she very beautiful", which sounds odd in English but perfectly fine in Mandarin. And note, that unlike English where every sentence must have a verb, that sentence has no verb.

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.

Snowflake for SQL Server Users – Part 12 – Parallelism when loading data from files into tables

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.

Instead, you should consider having at least as many smaller files to load as you have available processor threads. This means that you should favour many small files, rather than a smaller number of larger files.

Breaking up large files

This means that it can be desirable to break up existing large files. When I was doing Snowflake training, a number of people asked about good options for breaking up large text files. We couldn't find a good option so I wrote one.

You can find details of SDU_FileSplit here. As well as breaking up the files, it can transfer the header rows into the split files and more.

On Linux, you could just use the split command line utility.

File Sizes

In terms of how large the files should be, the Snowflake documentation recommends 10MB to 100MB of compressed data. (Most people load zip files).

That also means that if your files are much smaller, you should aggregate them before loading them, to get them into the target file size.

 

 

SQL: Accessing SQL Server Express localdb from another computer

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).

localdb is a deployment method for SQL Server Express edition, where instead of being started as a service, the database is started as a child process to another process (or application).

First, you need to make sure that the localdb instance is fired up. localdb is normally started by an application that it's attached to, like the way it can be used with Visual Studio to provide a local version of a SQL Server database, that's easier to deploy than SQL Server Express.

Now that can be your application, if you only need others to connect while your application is running. Alternately, it can be via the SqlLocalDB Utility. See here: https://docs.microsoft.com/en-us/sql/tools/sqllocaldb-utility

The SqlLocalDB utility can be the host process that launches localdb. It has options to start and stop it along with a few additional options.

Second, you need to make sure that TCP is enabled. Details of setting up a shared instance are described here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb

Hope that helps someone.

SDU Tools: List user access to Reporting Services

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?

The database name will default to ReportServer which is the name that SSRS uses during install if you don't change it.

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