Language Comparison Euler Problem One

Question:

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

F#

F# Example One

or

F# Example Two

C#

C# Example One

or

C# Example Two

JavaScript

JavaScript Example One

SQL

or

PowerShell

or

You Get an Update, and You Get an Update, and You Get an Update

What’s the difference between

and

When they’ve completed, both ensure all the records in the table have the ReadyToProcess value set to 1. When looking strictly at the records in the database, there is no difference. In this example, the end result isn’t what’s important, but it’s how the system there. In the first one it updates all records regardless of what the end value was, and the second only makes an update if the value is different than what is being set. The first update statement isn’t telling SQL Server, “Hey I want the value to be this.” What it’s really telling SQL Server is, “I want you to update the field in this record to this. It may look the same as the previous value, but I still want you to update it.”

Depending on the number of records in the table, and what is affected by the table updating, it might not be much of a problem. The first is less efficient than the second, but it still accomplishes the task. It really starts to be an issue when updating a large number of records, or when something like a trigger is associated with the table. Every time SQL Server updates a record, the associated trigger fires, and this is true even if the new value and the old value are the same. An update is an update no matter what the end value is. If the system logs audit data for updates etc. the non-qualified statement is forcing the system to do a lot more work for absolutely no gain.

Please Spell For Me

One thing that always frustrated me about writing in technology (documents, resumes etc.) was the abnormal spelling of companies, technologies, and languages. Almost all modern document editors have spell check. Heck even browsers catch your spelling mistakes before you hit the send key. The problem is that they only catch the words loaded into their dictionary. Most spellcheckers by default don’t have words in them like JavaScript or SharePoint, or companies with strange spellings. In short documents, it’s easy to look over and find the words which look out of place, but as they get longer, it becomes increasingly tedious, and the possibility of making a mistake grows.

It should be really easy to add words to the spelling dictionary for Word to autocorrect along the way. It is, but what I really wanted to know if it would automatically format words to have bold or italics also. Now that the word processor has replaced the typewriter, formatting has found its way into the display of companies’ and technologies’ names, and many take it to be just as important as spelling the company name correctly.

To add a word to text replacement:

First go to File -> Options Option For Word

Then select Proofing Proofing Menu

The Auto Correct Options button at the top of the page will appear
autocorrect

Press it to go to the auto correct menu.
Autocorrect

If you notice, the Formatted Text option is grayed out. To enable it you, must select the word you want to format with the formatting already applied before you selected it. Then go through the preceding steps to add it to the auto correct list.
Formatted Text

Formatting menu

Where’s The Scope

When I started programming, it was customary to declare the variables at the top of the method. Many people stated that it was clearer to declare everything at the beginning and in the order it was used (or alphabetically for easier searching), and some languages, such as Delphi (Turbo Pascal) even required it. As the years passed over the past fifteen years, this has been a debated topic.

From my experience, the shift has been towards only declaring a variable just before needing it. In truth this makes a lot of sense, it allows the compiler to enforce scope and allows programmers the luxury of having it not compile when a variable is used when it shouldn’t be. This comes with the drawback that variable declarations become more difficult to find when searching through. If it’s declared at the top, then it’s always in the same place, although in truth modern IDEs alleviate this problem as it’s normally a single key stroke to find its declaration.

So, what is the correct answer between the two styles? As a blanket rule, the answer is: they’re both wrong. Where to declare variables is significantly different than what to name them. Most languages don’t care about what a variable is named. As long as they follow certain rules (don’t start with a number in some, or must start with a $ in others), the compiler/interpreter doesn’t really do much else with it. Where they are declared though can cause significant changes to how the system functions, and most developers forget that between languages this can make a huge difference.
Take C# and JavaScript for example. They look somewhat the same. Are they? No, they aren’t really even close. In C# variables are created at the branch level:

Running Example

Non-running example

In JavaScript they are declared at the function level:

Running Example

The second alert shows “undefined” instead of “I’m Global” even though it’s at the top of the function, because JavaScript moves all variable declaration to be at the top of the method regardless of where they are declared. (Instantiating the variable when it’s declared in the branch doesn’t initialize it until the branch either. Only the declaration is moved to the top of the function).

Believing that all languages work the same, because one can write similar syntax with error can cause major problems. The language that trips a lot of people up and how they should be declared is SQL. Take the following example:

Here’s the output:

Notice how it only prints out 10 even though the counter is being updated each loop? If the @LocalVariable was actually initialized each time the loop executed, it would have counted down to 1 because it would be set back to NULL each time.

What you have to remember, is that even though you wrote it a certain way doesn’t mean that the compiler/interpreter will output the exact same instruction set you thought it would. Assuming a variable will behave a certain way without testing or explicitly setting it, can be the difference between working code, and spending several hours tracking down a problem.

Find a Guid In a Haystack

A little while ago we had a problem when an unidentified Guid came up in an error email. Whoever wrote the original message, knew exactly where to look to find the associated record, but by now this person was long gone, and we were left with a problem on our hands. There was an error with an account, and we didn’t know where to look. Obviously, if there was only one table with a Guid as a field, it would be easy to find, but the problem becomes a lot larger when every table has one or multiple fields with it as a type. You’re left blankly staring at it, and ultimately facing the realization that you have to go through each table one at a time field by field.

We all agreed this was a waste, and we needed to speed up the process. One, we can’t stop development so everyone can look for it, and two, if it happens again, we’re back in the same spot. To handle this, I created the following script. It uses the system tables to look up each column type and build a select statement to search the database one field at a time.

Did it take a little time? Yep. Did it get the job done? Absolutely. It didn’t give us the table name, but it gave us all the associated fields to the record, and that was more than enough to find it. The real win was that none of us had to spend time doing something that a computer can do for us. Its time spent searching the database is nothing compared to how much money the company would spend if we had to look for it.

You can modify it to look for anything in the database by changing the type the cursor statement looks for tp.name = ‘uniqueidentifier’. You can also change this from = to like if you want to search in parts of character fields @ColumnName + ‘ like ”%’ + @SearchId + ‘%”’. This is what I did when I had to modify the script to search for password information a system was saving to the database when it had to be scrubbed out.

Non Failing Scripts

One of the most frustrating parts of database development is double applying scripts. If you are working directly on the production server, this problem is alleviated by making alterations to the SQL commands along the way. With doing development in a test environment first this becomes much more difficult. If you are creating a new stored procedure, the first time that it’s run you must use the CREATE command, and subsequently the ALTER command every time after. This makes handing the scripts off to a database team to run on the production servers difficult, because you have to account for the environment you are developing becoming out of sync with the environment your scripts will ultimately be run in.

Ideally, you could refresh your database environment to mirror production frequently to keep synchronicity, but in reality this is time consuming and many times not possible. The key becomes to write scripts so they can be applied to any environment either once or multiple times without causing errors. In my previous post I talked about how to use generators to make data scripts re-runnable without accidentally double inserting records. Using SQL Server’s system tables it’s relatively easy to do the same for tables, columns, stored procedures etc.

For stored procedures, most places just prepend a DROP PROCEDURE command and then create the stored procedure each time. The problem with this is that it removes permissions, and it’s easy to miss recreating them. I prefer to create a dummy procedure immediately before, and then using the alter command to change it to what it should be. If the procedure is already in the environment, it skips the step and immediately moves to the alter portion of the script.

The same thing can be done with adding or dropping columns

Virtually everything in MSSQL Server can be queried by the system tables:

  • views : sys.Views
  • constraints: sys.SysConstraints
  • schemas: sys.Schemas
    (you can link to this in your queries if your tables etc. aren’t under the default)

These are just a few of the system tables for querying data. If you need additional information about a database object, you can always link back to sys.objects. For Example, sys.SysConstraints doesn’t contain the constraint name, so to get it, all you need do is create a join.

The Book Of F#

The Book of F#

The book comes out this month, by Dave Fancher F# MVP. It’s a great book! It has an easy to read narrative and a fun style, and is designed for .Net Object Oriented people who want an introduction to both functional programming and to learn F#. It’s a great reference book and learning tool. Even if you don’t want to program F#, the functional approach to programming will teach you a lot, with great examples and easy to read explanations. I can honestly say the book never gets old. (I was the technical reviewer and got to read each chapter about 4 times. Think about how many technical books you would read 4 times without contemplating putting a fork in your eye. Honestly, with his I didn’t, and the inside sci-fi references are fun too!)

On top of this, the introduction is awesome, and it explains exactly why you want to learn functional programming and F#.

The book can be found on the No Starch Press site.

Brackets

When writing a language where

or

are optional, I always put them in. Some people say this takes up space when not required, but I find it useful to help clearly define branches in code.
This:

does not take up much more space than

Adding brackets also makes things more standard as they are still required for multi-line statements in branches. It also helps prevent the following scenario:

I worked on a project where this happened, and it took the team about 45 minutes to track down the problem instead of continuing with development (45 minutes doesn’t sound like a long time until you realize that it’s 45 minutes * 5 developers). In a pristine environment, this probably would be easily noticed and corrected, but most legacy code is far from it, and so I prefer every advantage I can have to make code do what I expect it to.
Some languages won’t even allow something akin to the following:

In truth, most do, but it doesn’t really matter. The brackets force the end scope of the branch, and don’t allow it accidentally affect the next statement in the program. A lot of people forget that

because it’s common to write the code like so:

and in a lot of languages with beginning and ending delimiters, white space is ignored by the compiler.