Functions

CASE is an expression that lets you do some IF/THEN/ELSE logic to produce a value. COALESCE is a function that returns the first non-NULL value passed into it. If all parameters passed in are NULL, it returns NULL. The type returned by COALESCE is the type of the first non-NULL parameter.

ISNULL is a function that works like COALESCE. But it only takes two parameters. It is a Microsoft specific function. The return type is the type of the first parameter passed in. A related function is NULLIF, which will return NULL if the two parameters passed in are the same. Otherwise it returns the first parameter.

The IIF function is an unusual one. It is like the ternary operator in the C programming language. It accepts a BOOLEAN expression as the first parameter. If that evaluates to TRUE, then the second parameter is returned. Otherwise the third parameter is returned.

The CHOOSE function is another oddity. You pass it an index plus a list of values. It will return the value in the list at the location specified by the index. Not sure how valuable such a function is.

Character Data

When you are dealing with character (string) data types in SQL Server, you can concatenate multiple strings together with the plus operator. For example, you can write 'hi' + ' there' to get 'hi there'.

Be warned that when you do concatenate strings using this operator, if any pieces are NULL, the resulting string will be NULL. You can get around this by using the CONCAT function instead. It will treat NULLs as empty strings.

There are a lot of built in functions that work with strings. The SUBSTRING function will give you a part of a string. CHARINDEX will tell you where a certain substring is located in a bigger string. LEN will tell you how long a string is. DATALENGTH tells you how many bytes are used to store the string.

REPLACE will change every occurrence of a specified substring to another. REPLICATE will duplicate a substring a certain specified number of times. UPPER and LOWER will change the case of a string. And LTRIM and RTRIM will get rid of white space in the front and end of a string respectively.

Keys and Dates

There are times when you want a key in your table to uniquely identify a specific row of data. One data type in SQL Server for this is the UNIQUEIDENTIFIER. You use the NEWID function to get a new value of this type. The keys generated are not sequential. This data type takes up 16 bytes of space. If you do want the keys to be in sequence, you can instead call the NEWSEQUENCEIQLID function.

SQL Server supports the CURRENT_TIMESTAMP function. This is a SQL standard that returns the current date and time. The data type returned is DATETIME. SQL Server also has a proprietary function GET_DATE that does the same thing as CURRENT_TIMESTAMP.

There is also a SYSDATETIME function in SQL Server which will return a value of type DATETIME2. If you are only interested in a part of this value, such as only the date or only the time, you can use the CAST function on the result of the function to get what you want. If you want some very specific piece of the DATETIME, you can use the DATEPART function to extract, say, the ar.

Going the other way around, if you want to construct a DATETIME, you can use the DATEFROMPARTS function.

Data Types

I have found that most of the SQL data types I know from Oracle are also present in the SQL Server database. Some of the same rules apply to these data types as well. Character literals (strings) are enclosed in single quotes. The FLOAT/REAL data type lets you represent very large or very small numbers. But it is not precise.

You can change the data type of a value using the CAST function. You specify the original expression and the type you want to cast it to. There is also a CONVERT function that works like CAST. CONVERT also let’s you specify a style for the conversion. ,Finally there is a PARSE function that also works like CAST and CONVERT. For it you also specify a culture.

There are modified versions of these conversion functions which help handle scenarios where the conversion cannot be done. These functions are TRY_CAST, TRY_CONVERT, and TRY_PARSE. Each of these returns a NULL value if the conversion cannot be done.

Database Tables and Columns

When composing a SQL SELECT statement, you will specify tables and columns from that table. You could name those objects in your query by themselves (also known as one-part naming). However, it is better to use two-part naming. That means you prepend the tables with the database schema name. And you prepend the columns with the table name.

It is also good practice to provide a short alias for the table names in your query. Just note that you will then need to use that alias instead of the table name in the rest of your query. The table name become effectively hidden.

When you are designing a database, you will need to come up with descriptive names for the objects that you create in the database. There are rules for those names. They need to start with a letter, underscore, at sign, or pound sign.

In the SQL Server Management Studio (SSMS) GUI tool, there is a default database that it used when you connect. You have the ability to switch to a different database in SQL with the USE command.

SQL Standards

There are a whole lot of standards for generic SQL. These include ISO and ANSI. There are also revisions to the standards as new features are released. I have heard of the SQL-92 standard. But there are many more, the latest of which I have heard of is SQL-2011. These are standards for the SQL language itself, apart from any vendor implementation versions.

SQL itself is a declarative language. That means you tell SQL what you want, and it does it. To retrieve data, you issue the SELECT command with a specific order of keywords: SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY. The SQL engine will process those keywords in the following order: FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.

The processing order is important. One example is that aliases you define in the SELECT clause can only be referenced in it and the ORDER BY clause. All of the other parts of the full SQL statement are processed prior to the SELECT. There are some other specialized optional parts of a SELECT statement that are processed after the ORDER BY. These include TOP and OFFSET.

SQL 2016 Database Development

I have a lot of experience with the Oracle database. Got a couple certifications from Oracle. However, at work I have seen only Microsoft SQL Server jobs that looked interesting. So I think it is time to start learning SQL Server specifics. While I am at it, I might as well try to earn a Microsoft certification (MCSA) as well. The one that suits me best is a certification in SQL 2016 Database Development.

In order to get this certification, I need to pass two tests: (1) 70-761 “Querying Data with Transact-SQL” and (2) 70-762 “Developing SQL Databases”. Both exams cost $165. I found at least one Udemy video to help with the first exam. Microsoft sells prep guides for both exams as well.

I am going to start talking about the prep book for Exam 70-761. It is a whopping 700+ pages. It comes with a CD that will install a program to simulate the actual exam. I plan to taking that practice exam soon. There is also an eBook that comes with the print copy of the prep book. Let the journey begin.