T-SQL Tuesday #97: Setting Learning Goals for 2018

This months T-SQL Tuesday is hosted by Malathi Mahadevan (@sqlmal | b).
Besides my strict learning goals I’ll also provide my retrospective for 2017.

Data Science Program

I recently started the Microsoft Professional Program for Data Science, of which I completed the first 3 courses already. I want to complete course 4 before the end of the year. That leaves me with 5 courses and the capstone project. Finishing this program in the Fall of 2018 seems doable, so I’ll put that in my calendar!

MCSE

Ow, already 3 years back I passed my final exam for my MCSA. Until recently the progress to my next goal (MCSE) kind of stopped. I already had one attempt at 70-464 this year, but I failed… I guess I underestimated that one a bit, so that’s one reason I’m not going to take my next exam too early. I just didn’t have too much time to work on it since my recent switch out of consultancy and to Van Lanschot Bankiers. Another reason is the fact I also started the Data Science Program, and the fact that this year is almost finished. I’d rather wait a few months and have my MCSE for 2018 already.

Podcasts

The fact that I made the switch to Van Lanschot also meant less commuting, which is a good thing of course, but not for my time listening to podcasts. I’m having trouble keeping up with all the feeds I’m in at the moment so I’ll have to either cut the feeds or find some other time (during running maybe?) to listen to the podcasts.

Books

As you can see on My Reading Journey page I made some steady progress on my reading goal. Although there have been days and weeks I didn’t read, since I started tracking my progress again in my spreadsheet I see I keep advancing more steadily. I still have some days I don’t read at all, but at least now this is visible and I can hold myself accountable for that. The books I read this year have been a good mix between technical, personal skills and process oriented topics. I’m going to switch my technical topics this year a little bit to more closely match my work at Van Lanschot. In the beginning of next year I’ll write a blog post about my first months here.

Running

As for my personal goals: I completed 2 half marathons this year in Venlo and Eindhoven, with a PR of 1:38:59 in Eindhoven this October! Now on to a PR on the 10 km, 40 minutes would be a great goal, as my PR is now 44:27.

@NickyvV

Round a datetime to the nearest minute (or down to the minute)

Last week I was doing a comparison of 2 datetime fields, but I only wanted to do the comparison on the minute level. So I needed something to round the datetime to the nearest minute or down to the minute.

I found this great answer on SO:

DECLARE @dt DATETIME
SET @dt = '09-22-2007 15:07:38.850'
SELECT DATEADD(mi, DATEDIFF(mi, 0, @dt), 0)

And if you want to round up or down, you can add 30 seconds to the parameter like this:

SELECT DATEADD(mi, DATEDIFF(mi, 0, DATEADD(s, 30, @dt)), 0)

Power Pivot: unable to convert a value to the data type requested

I just ran into an (for me strange) issue when I was updating a Power Pivot workbook.
I had 2 columns in a table, let's say "One" and "Two", where One has integer values and Two string values. In my model I created a calculated column Three to concatenate these values. Because One and Two are actually redundant and never used on its own, I decided to delete those columns and save some space (the tables contained just over 2.2 million records).
So I started with updating my source view to concatenate the values in T-SQL, but when I refreshed the table in Power Pivot I got the error:
"Unable to convert a value to the data type requested in table x for column Three"

This error occured because I renamed the column in the view One and I already had that column present in my PP earlier (although I deleted it before opening the Table Properties). So my column One was previous INT, but would now be VARCHAR, however PP keeps this setting in mind although the column was deleted.

I found an answer on StackOverflow:
http://stackoverflow.com/questions/6981688/powerpivot-sql-query-fails-unable-to-convert-a-value-to-the-data-type-requeste

My solution was to recreate the column One in my PP and leave it empty (this would get the data type INT), and then add the new column One from the source in the Table Properties. This gives a warning that the column is already present in your workbook, but solves it by renaming the new column to "One 1" when refreshing the changes. After that you can delete the old column One and rename "One 1" to One and you're done!

So in short:

1. Recreate the column (One) you deleted
2. Then add the newly added column from your source with the same name
3. PP renames the new column to "One 1"
4. Delete the old column (One) again and rename "One 1" to One, or the desired name

LastIndexOf in T-SQL: Get Filename out of Path

Just a quick post for a quick solution:

I'm trying to get some information about documents stored in a SQL Server 2005 database. The documents are stored in an nText field in a table (called archive), but metadata is stored in other fields and tables.
I have the original filepath, but I wanted to get only the filename (+extenstion).

So actually, I was looking for some sort of T-SQL equivalent of the LastIndexOf C#-function. I couldn't find it easily so I thought substrings and CHARINDEX will do.. :)
Filepath is the column that contains the original filepath in the beneath code example:

LastIndexOf in T-SQL

When filepath = 'C:\temp\new\1\document.doc' this returns 'document.doc'
It basically:

• reverses the column filepath
• searches for the first '\'-character (so actually the last)
• and returns the part to the right of the '\'.

@NickyvV

The slight difference between ISNULL and COALESCE

I ran into a little issue yesterday when I was solving an issue for a customer. An Slowly Changing Dimension was implemented for the Organization table and I was looking for the currently active records in that table. I'll explain the issue with the use of the well known AdventureWorks2012 database.

My goal is to select the currently active records in the [HumanResources].[EmployeeDepartmentHistory] table, so I take the next select statement:

This results in 0 rows returned. I'm stunned, because I saw a bunch of records which have a NULL for EndDate so I would expect at least those records to be returned.
I leave out the where clause and add a row so my output also shows the ISNULL(EndDate, GETDATE()) column as follows:









This results in the following output (abbreviated):





I now realize my GETDATE() is converted to a DATE instead of a DATETIME column. But why?
Well, you might say: it was designed that way... as BOL (ISNULL (Transact-SQL)) states:

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Because my EndDate column is of type DATE, the GETDATE() function (inside the ISNULL) will be converted to DATE also. So my where clause will actually look like this:





which is the 'same' as the following, technically spoken it's not the same, because the ISNULL will output a DATE data type, but for explaining my point I will use this :




This will only result in output when you execute the query at exactly 00:00:00.000 hours, which will *never* happen in real time.
Instead of ISNULL I will use COALESCE (Transact-SQL) which, according to BOL,

returns the data type of expression with the highest Data Type Precedence (Transact-SQL)  If all expressions are nonnullable, the result is typed as nonnullable.

This will result in the DATETIME data type for the last expression when the EndDate column is null and returns the correct result set for my goal.



There also is a slight difference when using non-null parameters:

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

Hopefully you found this useful and will also never make the same mistake again... :-)

Logical Query Processing

Well, that's been a while since my last post.. A new year, new chances!

Opposed to physical query processing which follows the order of the statements:
SELECT .. FROM .. WHERE.. GROUP BY ..)
logical query processing processes the statements in the following order:
FROM .. WHERE .. GROUP BY .. SELECT ..

Last week I saw this excellent poster from (who else then) Itzik Ben Gan, writing down the phases of logical query processing in T-SQL in a flow diagram.

You can download the original poster here.