T-SQL 101: #1 What is SQL?

For some time, I've been thinking that I should create a series of blog posts about the T-SQL language, from a beginner's perspective. For the next few months, my Monday posts will be intro-level T-SQL.

The obvious topic to start with then, is what is SQL in the first place?

When I started working with databases in the 1980's, there were many, many different types of database. Each database had its own language that was used to get information back out of the database, or to put the information into the database in the first place.

Just like with real-world human languages, having mixtures of languages makes it hard to learn to communicate.

The industry realized that having so many different languages was making it harder for people knew their information was in a database; they just didn't know how to get to it.

The SQL language, or what's more formally called Structured Query Language, was an attempt to create a single language that would let you query many different types of database. You'd learn the SQL language, it's key words, and syntax or rules, and you could use the same skills to work with any database that supported SQL.

Awesome image by John Schnobrich
Awesome image by John Schnobrich

It started just for retrieving information, and soon after got the ability to change the information, or data, as well.

Today, SQL is an ANSI standard language for what's known as relational databases. SQL Server is a relational database. It's called "relational" because what we commonly call a table today, was what Dr Codd from IBM called a relation back when it all started.

Over the years, I keep hearing how SQL is going to disappear. The biggest claims appeared when the NoSQL movement first appeared.

Mark Twain said "The report of my death was an exaggeration."

And so it is with SQL. Year by year, I just see SQL getting stronger and stronger, and people making a great living by being proficient at it. Even the NoSQL people have now redefined their term to stop meaning "No SQL" and now to mean "Not only SQL". And SQL interfaces are being added to almost every new technology.

It's worth your while becoming proficient in SQL.

Learning T-SQL

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

Learning Mandarin: Using measure words

Over the years, I've enjoyed attending trivia nights at local pubs and schools. It's fun to try to stretch your thinking, and of course, fun to meet up with lots of interesting people. I can't tell you how many times though, I've been asked about collective nouns for words in English.

"Group" is a common enough word, but if you use it all the time, you won't be considered very literate. While you can say "There is a group of dogs", it's more correct to say "There is pack of dogs". Instead of "There is a group of sheep", you say "There is a herd of sheep".

A similar thing applies to individual items within a collection. We could say "This is a paper", the meaning isn't the same as if we say "This is a piece of paper". The word "piece" is a type of word that's used to measure part of a collection i.e. it's a "measure word". "Three coffees" isn't quite as meaningful as "Three cups of coffee". If you said "Three pieces of coffee", you'd probably get strange looks.

A similar thing happens in Chinese, but it's even more pronounced. That's most likely because it doesn't have separate words for singular and plural nouns like we do. We know that when we say "goose" we mean one, and by "geese" we mean more than one.

The general word for a unit is 个 (gè). While you could use it for almost anything, you'd sound like you can't speak properly. So part of the trick is learning a bunch of measure words. So, instead of

那是一个狗。(Nà shì yīgè gǒu.) or "that is a dog"

you'd instead say 那是一只狗。(Nà shì yī zhǐ gǒu.)

or even 那是一条狗。(Nà shì yītiáo gǒu.)

Note that the measure word for dog is 只 (zhǐ) but 条 (tiáo) is also a generic measure word for long skinny things. It can be used for dogs, but also applies to snakes, fish, etc.

So, previously I mentioned "a piece of paper". That would be: 一张纸 (Yī zhāng zhǐ) In this case 张 (zhāng) is the measure word  or 量词 (Liàngcí) for paper (纸). So three pieces of paper is: 三张纸 (Sān zhāng zhǐ)

And while it's important to learn that 船 (Chuán) is a ship or boat, it's just as important to know that five ships is 五艘船 (Wǔ sōu chuán) where 艘 (sōu) is the measure word for ships. And two books is 两本书 (Liǎng běn shū) where 本 (běn) is the measure word for books. One computer is 一台电脑 (Yī tái diànnǎo) where 台 (tái) is the measure word for computers.

By the way, the word computer 电脑 (diànnǎo) is wonderful. It's literally "electric brain".

This is a pretty good reference for these measure words: 

http://www.languagerealm.com/chinese/chinese_measure_words.php

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 is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.

Business Intelligence: Success is about small starts leading to bigger things

I spend a lot of time on client sites and time and again, one of the mistakes that I see people making, is trying to start with large projects. I think one of my all time favorite quotes about IT is:

Any successful large IT system used to be a successful small IT system.

The next time you're thinking about creating a project that's going to have a big bang outcome, please remember this. The history of the industry is that it really is likely to be a big bang, and not in terms of being a big success like you'd hoped for.

A staggeringly high percentage of large IT projects fail.

This is even more important in business intelligence projects. The industry is littered with companies that have spent a fortune on BI projects, and almost no-one in those companies can, or do, use the outcomes. It's a sad but true statistic.

Asking users what they want, gathering a large amount of detail, then building it, testing it, and delivering it to them sounds good, but it almost never works. Unfortunately, it's how many larger organizations think projects need to be managed. They assume that creating an IT project is like building a bridge.

It's not.

The first problem is that the users don't know what they want. Don't blame them later because you think they didn't give you the right instructions. That's your fault for assuming they can describe exactly what they want. In many cases, until they see some of the outcomes, they won't start to understand what the system will do, and until then, they won't realize what they really need.

Second, no matter how clearly you document their requirements, it won't be what they need. One of my Kiwi buddies Dave Dustin was having a bad day once, and I remember he mentioned that he was going to just spend the day delivering exactly what people asked for. That was wonderful and beautifully insightful, because we all know that it would lead to a disaster. It's little surprise. They might have said "sales" but they really meant "profit", and so on.

Finally, the larger the project, the longer it will take to deliver, and by then, even if you'd done it perfectly, the users' needs will have changed, so it still won't be what they want.

When you're starting a BI project, I'd implore you to find a project that has these characteristics:

  • Small enough to be done in a couple of weeks at most
  • Based on mocked up data in front of the target users
  • Large enough to make a significant difference to someone's life or work
  • Targeted at someone who's important enough to be a champion for the rest of what you want to achieve
  • (Ideally) targeted at someone "who pays the bills"

Start by doing a great job, and building on that, once you have support.

Shortcut: Split query windows in SQL Server Management Studio

If you are working with really long script files in SQL Server Management Studio (SSMS), you might need to work on more than one part of the script at the same time. Perhaps you need to work on a function, and also on the code that calls the function.

On the Window menu, there is a Split option.

When you first do this, you'll see a split window with the same query at top and bottom:

You can then scroll each vertically and resize them independently, and work on different parts of the same script:

The easiest way that I've found to close this, is to double-click on the dark bar in the middle, but there is also a Remove Split option in the Window menu.

SDU Tools: Script Server Role members in SQL Server

In our free SDU Tools for developers and DBAs, there's another useful scripting tool. It scripts out the logins that are members of server roles. It's called ScriptServerRoleMembers.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

By default, it lists permissions for all logins, but you can also provide a list of the logins that you're interested in, as a comma-delimited list, to the @LoginsToScript parameter.

You can see an example of both in the main image above, and you can watch it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

Need to learn to write T-SQL queries for SQL Server? Or need to become more confident?

Do you (or someone you know) need to learn to write T-SQL properly? Or perhaps you need to be more confident with joins, aggregations, CTEs, and more?

Our latest online on-demand course Writing Queries for SQL Server is now available, and at introductory pricing of just $95 USD.

It has detailed and comprehensive intro level coverage of the T-SQL language, and follows professional coding standards throughout. It's also not just a set of videos; it includes extensive hands-on labs and quizzes to make sure the learning is great.

The T-SQL that the course covers is also up to date. It includes content up to SQL Server 2017, but is just as useful for users of earlier versions of SQL Server.

The course would suit developers, reporting analysts, business analysts, new DBAs, and more.

This is one of a set of new courses that we're making available. Our free 4 Steps to Faster SQL Server applications is already available, and so is our SQL Server Indexing for Developers. You'll find them all here:

http://training.sqldownunder.com

SQL: Filtered indexes in SQL Server can be wonderful but be careful !

Back to the transaction table

Two weeks ago, I wrote about the issues with a large transaction table where only a handful of the rows were unfinalized, and that we would never use an index to find all the rows that were finalized. But we'd certainly want an index defined for the ones that weren't. If you haven't read that post, I'd suggest you do so before continuing to read. You'll find it here.

Now one of the challenges is that indexes like this on a very big table, can also be large. Also, every row in the table has an entry in each index.

Why filtered indexes?

If you think about it, if all we're ever going to use is one part of the index, i.e. just the unfinalized rows, having an entry in there for every single row is quite wasteful, as although the vast majority of the index will never be used, it still has to be maintained.

So in SQL Server 2008, we got the ability to create a filtered index. Now these were actually added to support sparse columns. But on their own, they're incredibly useful anyway.

The idea is that we can have a WHERE clause on the index and the index only contains entries for the rows that match the WHERE clause predicate. These indexes are often much smaller and can also be much faster, but it's really important that you're very, very careful when using them.

In particular, you normally need to include the predicates when you use them in queries. Let me show you why.

Quick demo

Here in my Indexing database, I'm going to create a table called Transactions.

I've got a TransactionID as just an identity column, a date, an amount, and IsFinalized. So now I'll populate it. I'm going to just add 100 thousand rows.

But what I also did was to have it calculate the transaction date on the fly and I've got three rows where I'm going to set the fact that the transaction was unfinalized. All the other ones have it set as finalized.

What I'm going to do is create a normal index on that IsFinalized column.

Keep in mind that what that index will contain is the IsFinalized column, along with the clustering key which was TransactionID.

Now let's look at some query plans. Here are the queries:

Here are the query plans:

 

No huge surprise there. Note that it wasn't a brilliant index, because a lookup was needed to get the date. But the first one decided that the statistics were OK for a series of lookups, so it does that. The second one just complains there is no suitable index.

Now let's try a filtered index instead:

And we see that they're basically the same:

The difference is that it's picked up the filtered index in the first query. But it could only do that because it matched the predicate entirely, and was able to do so before it ran the query.

Parameter problems

Instead, if I declare a variable, and I use it in the predicate, it's the same logical query, but the outcome is entirely different. Here are the queries that use variables:

And here are the query plans:

Yep, now they're both broken. That's because at the time the query plan was created, SQL Server didn't know what value that variable would have. (In this case, you'd think it could work that out actually but it doesn't). And so it doesn't know if the value will match the filter predicate, so it also doesn't know if it can use that filtered index, so it doesn't.

The main thing is that if you use a filtered index, although they can be absolutely wonderful, it's really, really important that you match the filter predicates exactly, even if you also have other predicates in the query.

Learn about indexing

Want to learn more about indexing? Take our online on-demand course now:
https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

 

 

 

 

Learning Mandarin: Tone rules also apply

I mentioned in an earlier post that Mandarin was a tonal language and I described the four tones and the neutral tone. Well, while that's all true, things aren't quite a simple as that.

There are also tone rules that can change the pronounced tone for a word. Let me show you:

One rule says that if  you have two third tones in a row, the first character changes to second tone. (It will still be shown as 3rd tone in pinyin). An example is the simple greeting (literally "you good" but used as "hello") that is the first thing most people learn in Mandarin (although not all that many natives say it that often):

你好 (nǐ hǎo)

It's not pronounced with two descending and rising tones like the tones (both 3rd tone) would suggest. The first character is pronounced with a simple rising 2nd tone, and the second character is pronounced as expected.

One tricky rule deals with the character 一 (yī) that means "one". On its own, it's 1st tone. But then it gets messy. If the next character is 4th tone (a sharp fall), then this one is 2nd tone. But if the next character is any other tone, it becomes 4th tone. Here is an example:

一个 (yī gè)

The word ge is a general purpose measure word. But because it's 4th tone, yi is pronounced as a 2nd tone instead of its usual 1st tone. That helps to make the falling sound of the second character more emphatic.

Another rule for today is about the word 不 (bù) (which means "not"). It's normally 4th tone, but when it precedes another 4th tone, that would sound a bit odd, so it changes to a second tone. Again that makes the drop on the next character (which is the more important one for the meaning), more emphatic. Here is an example:

不错 (bù cuò)

This basically means "not bad" or more literally, "not wrong". 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 is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.

Book Review: No Ordinary Disruption

I mentioned in a previous post about how one of my colleagues Orin Thomas is a prolific reader and every time I talk to him, he suggests more books that I should read. Another one in that category was No Ordinary Disruption One by Richard Dobbs, James Manyika, and Jonathan Woetzel. The sub-title is The four forces breaking all the trends.

The authors claim that our intuition on how the world works could be very wrong. I suspect that the degree of change in the accuracy of our perception is a recent, and accelerating thing. In the book, they are summarizing years of research they have done at the McKinsey Global Institute.

It's amazing today how many businesses that have been large, solid, and around for a long time, are suddenly almost swept aside by relatively new competitors. Suddenly, the world feels different. It is going to be very, very different.

What I particularly like in this book is the way that they've analyzed what's going on in China. The rise and rise of Chinese technology is a passionate interest of mine, and I'm surprised how little attention it's getting in the West today. I think we ignore it at our extreme peril, particularly financially. So many things that I see going on in China are so very disruptive. I hear people scoff at some of the quality of what they're doing, but I heard exactly the same thing about Japanese products in the 1960's and 1970's. By the 1980's, everyone was taking notice of the Japanese.

If you don't realize that technologies that are now constructing skyscrapers at three stories per day (yes 57 stories in 19 days), and others that are 3D printing entire houses, are going to be here in the near future, you aren't watching what's going on.

What we in the West aren't seeing is where the real growth is going to come from in the future. An example they give is that a single regional city in China (Tianjin) will have a GDP as large as Sweden by 2025.

I loved the way that the authors note that cities that most executives would be hard pressed to even find on a map today, will have most of the economic growth in the next decade.

The four main trends that they've mentioned are a big shift away from North Atlantic trade, across to emerging markets; the way the world is aging; the effects of ongoing urbanization; and obviously, the impacts of technological change.

But it's not all about China and emerging markets. There are other significant disruptions taking place. For example, who, in the 1980's, would have tipped the USA as the world's largest oil producer, rather than some countries in the Middle East or Russia?

The first chapter contains the essence of the book. It's a fairly straightforward read, and covers the main points of the whole book. The rest of the book is a much heavier read, with all the statistics and analytics, but interesting nonetheless.

My main concern right now, is that the upcoming disruption is very different to anything we've seen in the past, and we're nowhere near ready for it.

Bottom line?

This book is interesting and it's another one that I'm glad Orin suggested to me. I have a deep interest in these things, and this was yet another set of opinions that were worth considering.

Greg's rating: 8 out of 10

Note: as an Amazon Associate I earn (a pittance) from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway 🙂

Shortcut: Pinned tabs in SQL Server Management Studio

When you get to a large number of query windows or other documents open as tabs in SQL Server Management Studio (SSMS), it can start to be difficult to keep track of them, and to find them when needed.

It's not too bad when you can immediately find the tab that you want in the drop-down list:

But if you have more tabs than are shown in this drop-down list or if, like me, you often end up with many of them without names (as they are temporary), it can get very hard to find the few that you are mainly referring to.

Just like you can with tool windows, you can pin tabs:

Once you do this, they stay against the left-hand side (by default). Now that's not bad but again if you have a few of them, there's another option that can help.

Once you configure that, another row of tabs appears in SSMS: