Sunday, September 11, 2016

FULL Joins

photo credit: Hold My Hand via photopin (license)
A FULL join are a great way to find data that missing in two joined tables. The full join is the combination of the LEFT and RIGHT join In the case of my example I am comparing inventory between two different tables where an item may be missing in either table.

If you run this SQL you will see that inventory1 is missing items divisible by 7, while inventory2 is missing items divisible by 8.

Sunday, July 17, 2016

Look Ma, I've got an open source project.

I was working on a follow-up post to my Command Line SQL Server Interpreter post  last week; I wanted to add some additional Powershell features I discovered. As I was working on the post, I came I realized I had a dilemma, do I just update the original code or do I create a new copy of the code with the changes?  If I updated the original code, the original post wouldn't make sense, but if I didn't I, someone could end up with the old version of  the code when the would benefit from the new version.  The key term here is 'version', which led me to version control, which led me to GitHub, the place where all the 'cool kids' put their code. So now I have a new and shiny open source project -- RunSQLCommand.  

Its based on the orginal script but I cleaned up the help and some of the comments to make it more usable (and fixed some typos). I've also added a help file. I changed the licence of the new program from MIT to BSD. The primary difference between  the licenses is that the BSD license requires copyright attribution if the code is used, MIT does not. The original script (in the gist)  is still covered by the MIT licence, so if you are using it, or want to use it, you don't need to worry. 

I found GitHub easy to use and they even have a tool to create a separate 'home page' for the project. Unfortunately, I haven't added the features the prompted this yet, so stay tuned.

Saturday, June 11, 2016

Fun with Unicode and SQL Server

I had to debug a situation recently where some exported data didn't work and I stumbled into the world of Unicode characters. Here are some of the fun facts that I learned

A brief (very loose) interpretation of the history of Unicode

In the beginning there were a bunch if people in the basement of some computer lab somewhere who needed to type words on a computer screen. The realized that with a 7 bit value they would have 128 options in which they could put the entire English alphabet. This included  upper and lower case, numbers, punctuation, and whitespace characters. They called it ASCII (American Standard Code for Information Interchange)  and they saw it was good.

One day they realized that they wanted to communicate with sell computers to people who spoke other languages so they added another bit that allowe them to have 256 characters so they could create the accented and special characters of the most of the European languages.They called it Extended ASCII, and they saw it was good.

But then the heard the cry's of their friends in countries that spoke languages like Japanese, Chinese, and Hebrew who wanted to communicate with buy computers from them and the invented Unicode. And here we are.

Notes about Unicode and SQL Sever

  1. There are lots of variants/encoding for Unicode based on my experimentation it appears that SQL server supports UTF-8 encoding with SQL_Latin1_General_CP1_CI_AS collation.
  2. You need to use a Unicode data types (nchar, nvarchar, ntext)
  3. You need to prefix your string with the UPPERCASE letter N  (ex. N'Some Unicode')
  4. All of the lower 256 ASCII characters are compatible with the Unicode (its backwards compatible)
  5. If you try to cast a Unicode char (not part of the 256 ASCII characters) as ASCII you will get a '?' and there is not way to tell if its not actually a question mark.
  6. Unicode data takes up twice the space that ASCII data takes up. ( varchar(8000) = nvarchar(4000) )
  7. Some of applications cannot import Unicode data files
  8. The way your text editor knows that a file has Unicode data is that the first character has special value (that varies depending of the type of encoding).
  9. Microsoft says that Unicode data processes faster.

Use this code snippet to have 'fun' with unicode


Comments, corrections, clarifications are always appreciated in the comments.

Saturday, June 4, 2016

Quick Tip: Using the Print Statement in SSMS to Check Output Formatting

SQL Server Management Studio (SSMS) is pretty good for developing and checking tabular data; but trying to use the results table to check formatting of text (varchar) output can be challenging. Luckily the T-SQL PRINT statement can help. The PRINT statement sends the formatted output to the Messages tab in SSMS. 

See my example below.

Output in the Results Tab table

Output in the Messages Tab

More information on the PRINT Statement can be found below:

Let me know in the comments if you found this helpful (or not).

Sunday, January 17, 2016

Using Regular Expressions to create Quoted lists in SSMS

This presentation is about a 'trick' i found in SSMS that makes it much easier to create quoted lists for for the 'in' statement

Sunday, January 10, 2016

Moving Data with Linked Databases

SSIS isn't the only game in town to move data between SQL Server databases. There are other options like BCP.exe, programs and scripts, and linked databases. I've created a an example solution to demonstrate how to use SQL server agent, linked database,s and some T-SQL to move data between databases.