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.

Windows Update

I only do Windows updates periodically. This latest time around, there were almost 40 updates to download/install. Most of them were security updates. This is good. I want the security holes patched on my machine. About 25% of the updates were related to Windows 7. Yes. I still roll with the Windows 7 operating system.

The big surprise was that over half of the updates were to the .NET Framework. What is the takeaway from this? Is .NET having a lot of security problems. Or perhaps a lot of Windows is now buried in the .NET Framework. To answer this, I would need to delve into each of the patches that got applied. Nobody has time for that.

Should I make time?