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
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
You’ll notice when we discuss strings that there are many data types compared to what we had with numbers:
char was the original data character data type. By fixed length, I mean that if it’s defined as 10 characters long, it always returns 10 characters, even if there are only 5 characters stored in the string.
Prior to SQL Server 2019, char could only be used with ANSI characters (i.e. single byte characters). From 2019 onwards, char can also be used to store multi-byte characters by using UTF-8 encoding. I’ll write more about that another day.
2019-10-21
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.
2019-10-14
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.
2019-10-07
Today, I’m continuing the discussion on the variety of data types supported by SQL Server. Last time I mentioned exact whole numbers, but there are also exact decimal numbers. Here are the available types:
When I need to work with numbers with decimal places in them, the data type that I normally use is decimal.
It has a fixed precision and scale so for example, if I say:
decimal(18,3)
2019-09-30
I previously mentioned that SQL Server supports a wide variety of data types. The first of these are what we would consider exact numbers. Here are the available types:
So there’s a tinyint that’s basically one byte in size. One byte (8 bits) gives us 2^8 possible values, so we have 256 possible values. The first one is zero, so we have values up to 255.
Note: tinyint doesn’t allow for negative values.
2019-09-23
When you store data in a database, individual values are stored in columns. Now columns have 2 basic characteristics:
Another common characteristic is:
The first of these, the data type of the column, determines what types of value can be stored in the column. For example, I might have an email address and I might decide that that is a string of characters, but I might also have a price and might decide that that needs to be a number of some type.
2019-09-16
While it might be obvious that it’s important to format your T-SQL code for readability, it might be less obvious that there’s no agreed standard for how to format that code.
Everybody has their own style. The main thing. People will tell you all the time is just to be consistent. But then they’ll tell you they don’t like the format you’ve used.
Take a reasonable style and then to just keep applying it.
2019-09-09
It’s really important when you’re writing SQL Server code (or T-SQL in particular) that you add comments to the code where something isn’t obvious to someone who’s reading it.
Here’s an example of comments being used:
There are two ways that comments can be added:
If you put a double-dash on a line, anything after it is a comment.
If you need to comment a block of code, you can put a /* to start the comment and */ to end it. I’m not a fan of block comments in T-SQL because you can’t nest one comment inside another comment. With the double-dash method, that’s not a problem.
2019-09-02
