February 2013 - Posts

• Don't let the facts interfere with the truth

  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... Posted Wednesday, February 27, 2013 11:22 PM by John Paul Cook | 3 Comments

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

  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... Posted Monday, February 25, 2013 7:52 PM by John Paul Cook | 1 Comments

• BI Beginner: Diagram View in Excel PowerPivot

  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... Posted Saturday, February 23, 2013 11:32 AM by John Paul Cook | 0 Comments

• Visio Standard shapes for modeling

  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... Posted Friday, February 15, 2013 8:12 PM by John Paul Cook | 0 Comments

• Windows shell: commands

  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... Posted Wednesday, February 13, 2013 11:11 PM by John Paul Cook | 1 Comments

• Copy as path instead of a send to program

  What do you do to capture the fully qualified name of a file from Windows Explorer? If you are using a newer version of Windows than XP, you can easily get the fully qualified file name without installing or reconfiguring anything. All you need to do Read More... Posted Tuesday, February 12, 2013 6:59 PM by John Paul Cook | 2 Comments

• UNION, UNION ALL and ORDER BY

  At every client where I've worked, there have always been questions about UNION and UNION ALL. They may return the same results or they may not. It all depends on the data. If you don't have any duplicates, you'll get the same number of rows returned Read More... Posted Friday, February 08, 2013 9:32 PM by John Paul Cook | 1 Comments

• Why to use COALESCE instead of ISNULL

  As of this writing, Books Online isn't completely clear about how ISNULL works. I submitted suggestion 778704 on Connect asking Microsoft to explicitly state that if the replacement_value is longer than the check_expression , it will be truncated to the Read More... Posted Wednesday, February 06, 2013 6:53 PM by John Paul Cook | 4 Comments

• Scope of SET TRANSACTION ISOLATION LEVEL

  To help you better understand the scope of SET TRANSACTION ISOLATION LEVEL, I wrote some code for you to run on your own machine to experiment with and see for yourself. I think you'll remember it better by running the script below and carefully analyzing Read More... Posted Tuesday, February 05, 2013 10:31 PM by John Paul Cook | 0 Comments