John Paul Cook

Problems using BETWEEN

2013-04-27T23:13:00Z

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, real, or double data type? These data types do not store exact representations of numbers, only approximations. When 0.9 is stored in a real column or variable, it may be between 0.9 and 1.1. Or it may not. When you set a real to 0.9, internally it...(read more)

Adding clocks when working globally

2013-03-29T01:18:08Z

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 of the half hour. London is easy except right now when the U.S. is on Daylight time and the U.K. is not. You can add two clocks using the Additional Clocks tab in Date and Time . Figure 1. Additional Clocks tab. Figure 2. Mouseover after adding clocks....(read more)

BI Beginner: Data Explorer is a must have

2013-03-26T03:50:42Z

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. Step 2. Install Data Explorer. Step 3. Start Excel. Don’t hold back, go all out and Enable Advanced Query Editing . Figure 1. Check Enable Advanced Query Editing . Step 4. Open a workbook. Step 5. Click DATA EXPLORER . Figure 2. New DATA EXPLORER...(read more)

Diagnosing differences between production and development

2013-03-24T23:43:17Z

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 user differences can also causes differences, but these are out of scope for this discussion. This discussion is limited to differences in the data and the database schema. Using nursing as a paradigm, before diagnosis comes assessment. A thorough assessment...(read more)

implicit data type conversion and COALESCE

2013-03-17T20:11:00Z

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 sample below was inspired by actual legacy code that was intended to build a string showing the values of variables in a stored procedure. The desired output would be something similar to this and was constructed using string concatenation: @xDATETIME...(read more)

SSMS generating non-ANSI scripts

2013-03-14T01:20:00Z

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 behavior. I opened Connect item 781321 . If you are aware of any similar problems, please add them to the Connect item so that they can all be addressed at once. Here's how to reproduce the problem. First, create a table with a default constraint...(read more)

DISTINCT and ORDER BY

2013-03-10T19:40:00Z

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 use DISTINCT without an ORDER BY, don't trust your experience. Just like I explained in my recent posts on UNION ALL and TOP .. ORDER queries in views, you must have an ORDER BY to guarantee ordered results. If you use DISTINCT and examine your actual...(read more)

Free OCR

2013-03-09T01:58:34Z

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 captures into text. But wait, there’s more! You can make the text in the image searchable in OneNote. Let’s begin with this sample screen capture. Once it is in your copy buffer, paste it into OneNote. Figure 1. Screen capture of text. After pasting...(read more)

Views and ORDER BY

2013-03-07T01:16:00Z

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 agree with the truth. An ORDER BY can be added to a view definition if the view contains a TOP statement. But adding an ORDER BY clause to a view definition does not guarantee the order of the results, although many people believe that it does, just...(read more)

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

2013-03-02T18:35:00Z

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 I recommend. If you select the query you want to convert before pressing Ctrl Shift Q, the query will already be in the Query Designer window when it pops up. Here's some sample code to experiment with: create table table1 ( a int , b int ); create...(read more)

Don't let the facts interfere with the truth

2013-02-28T04:22:00Z

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 set - that's a fact. But what about the truth, that a Cartesian product occurs when you have less than N-1 joins? In the code sample below where there are N-2 joins, a Cartesian product does occur, but the where clause prevents you from seeing it....(read more)

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

2013-02-26T00:52:42Z

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 there is a gap on one side and not on the other, that’s a feature unique to one of the two versions of SQL Server being compared. There are more differences than are shown here – these are the major differences. SQL Server has additional facets and...(read more)

BI Beginner: Diagram View in Excel PowerPivot

2013-02-23T16:32:43Z

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. If you have Excel 2010, you’ll need to download and install SQL Server 2012 Service Pack 1 PowerPivot for Excel 2010. Although the name suggests that your data source must be SQL Server 2012 Service Pack 1, that is not the case. It works with...(read more)

Visio Standard shapes for modeling

2013-02-16T01:12:04Z

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 and instructions are available here . Figure 1. Extract the stencils to your My Shapes folder. Figure 2. Use More Shapes to navigate to the shapes you added....(read more)

Windows shell: commands

2013-02-14T04:11:31Z

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 an environment variable such as %temp%, %appdata%, %systemroot%, or %programfiles% into the address bar in Windows Explorer and directly navigate to the corresponding location on your machine. It’s much faster than trying to click your way to your...(read more)