John Paul Cook

Problems using BETWEEN

John Paul Cook — Sat, 27 Apr 2013 23:13:00 GMT

The BETWEEN operator is a handy SQL construct, but it can cause unexpected results when it isn’t understood. Consider the following code snippet: where x between .9 and 1.10 One of the questions you should ask is this: What is x? What if x has a float,...(read more)

Adding clocks when working globally

John Paul Cook — Fri, 29 Mar 2013 01:18:08 GMT

Since Windows Vista, Windows users have had the ability to add clocks. I’m in the U.S. Central time zone and have no problem thinking about scheduling meetings with people in the Eastern and Pacific time zones. Hyderabad is a different matter because...(read more)

BI Beginner: Data Explorer is a must have

John Paul Cook — Tue, 26 Mar 2013 03:50:42 GMT

Data Explorer will speed up and simplify your data analysis by at least an order of magnitude. It makes data just work for me. No fighting or struggling, it just works. Step 1. Go here and download the Data Explorer Preview for Excel 2013 or Excel 2010....(read more)

Diagnosing differences between production and development

John Paul Cook — Sun, 24 Mar 2013 23:43:17 GMT

There are many reasons you can have different results in your production environment than in your development environment. In database applications, two major causes are differences in database schemas and differences in the data. Application code and...(read more)

implicit data type conversion and COALESCE

John Paul Cook — Sun, 17 Mar 2013 20:11:00 GMT

Implicit data type conversion can cause both unexpected and undesirable results. Using the same design pattern for one data type and extending it to other data types can get you into trouble if implicit data type conversion occurs. The following code...(read more)

SSMS generating non-ANSI scripts

John Paul Cook — Thu, 14 Mar 2013 01:20:00 GMT

When using SSMS to script a database object that had been created with ANSI compliant syntax, I noticed that the generated script was not ANSI compliant. Have you noticed anything like this? It's more of an annoyance than a bug, but it is not the desired...(read more)

DISTINCT and ORDER BY

John Paul Cook — Sun, 10 Mar 2013 19:40:00 GMT

Using DISTINCT is another one of those examples where people's experiences don't always match the truth. Using DISTINCT without an ORDER BY probably returns the results in order. If your experience tells you that your results are ordered whenever you...(read more)

Free OCR

John Paul Cook — Sat, 09 Mar 2013 01:58:34 GMT

Today I saw something on the screen that was text. It was needed as text, but I couldn’t select it. OCR to the rescue! If you have OneNote, there’s an easy way to do OCR without any extra software. There are also free websites that can convert screen...(read more)

Views and ORDER BY

John Paul Cook — Thu, 07 Mar 2013 01:16:00 GMT

The general rule is that a view can't be created using a statement that contains an ORDER BY clause. There is a workaround that some people believe works. Similar to my recent post on using an ORDER BY with UNION ALL, the empirical facts don't always...(read more)

Using the Query Designer to convert non-ANSI joins to ANSI

John Paul Cook — Sat, 02 Mar 2013 18:35:00 GMT

If you have legacy code that you are upgrading, the Query Designer can convert old style joins to ANSI joins. You can invoke the Query Designer from the Query menu in the SSMS toolbar or by pressing the Ctrl Shift Q three key combination, which is what...(read more)

Don't let the facts interfere with the truth

John Paul Cook — Thu, 28 Feb 2013 04:22:00 GMT

When you are joining N tables, it is true that unless you have N-1 joins, you're going to have a Cartesian product. Today I saw a case where there were N-2 joins and no evidence of a Cartesian product in the result set. No additional rows in the result...(read more)

SQL Server 2012–what it is and what it isn’t

John Paul Cook — Tue, 26 Feb 2013 00:52:42 GMT

After SQL Azure was introduced, I made a side by side view of SSMS when connected to SQL Server and also when connected to SQL Azure. Somebody asked me if I was going to do the same to compare SQL Server 2008 R2 to SQL Server 2012, so here it is. Where...(read more)

BI Beginner: Diagram View in Excel PowerPivot

John Paul Cook — Sat, 23 Feb 2013 16:32:43 GMT

Excel 2013 and Excel 2010 offer a Diagram View in PowerPivot that isn’t generally well known beyond the community of BI professionals and Excel power users. In Diagram View, database tables appear as tables in PowerPivot, complete with referential integrity....(read more)

Visio Standard shapes for modeling

John Paul Cook — Sat, 16 Feb 2013 01:12:04 GMT

Microsoft has some free downloads of Visio stencils containing shapes you may find useful for modeling your database environment. These shapes are intended for adding to the standard edition. Professional edition users already have these shapes. The downloads...(read more)

Windows shell: commands

John Paul Cook — Thu, 14 Feb 2013 04:11:31 GMT

Windows shell: commands are like environment variables on steroids. If you haven’t been using them, you need to start because they will make your life easier. First, a quick review of what you can do with a few helpful environment variables. You can enter...(read more)