THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

  • Problems using BETWEEN

    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 becomes something very close to but maybe not quite equal to 0.9. Even though you entered 0.9, it could be stored as something slightly less than 0.9.

    Now consider this:

    where x between 9 and 11

    If x is an integer and is set to 9, it really is 9 and it will pass the test. Because of data types, never let it be said that all numbers are created equal.

    Let's refactor the statement to be free of hardcoded values:

    where x between y and z

    What's wrong with that? I'll simplify it for you. Everything is an integer, x, y, and z are all declared with the integer data type. Once again, the question to ask is what are x, y, and z? Or to be a little clearer, ask what are the values of x, y, and z. The uncertainty arises over the potential values of y and z. Do you know that y will never be more than z? After performing substitutions, what if we have the following?

    where 10 between 9 and 11

    The statement would be true. But what if after performing substitutions we would have obtained this?

    where 10 between 11 and 9

    The statement would be false even though 10 is between 9 and 11. We see that order matters - no surprise if you've ever read the documentation. Order is easy to see and deal with when the values are hardcoded. But when you have variables, what if you really don't know a priori if y <= z? You need to think of BETWEEN as a number line. Look at these next two snippets to see what happens when the order of the BETWEEN values is changed: 

    where -10 between  -9 and -11 -- this does NOT work
    where -10 between -11 and  -9 -- this works

    Here is a workaround to this dilemma of not knowing the relative positions on y and z on the number line:

    where x between y and z or x between z and y

    I've left you with a workaround and a challenge. I like interactivity with my readers, so I ask you to post your workaround as a comment. Another workaround is to refactor the problem into checking for a +/- 10% change. Here's something you can copy and paste to get started:

    declare @x int = 10;
    declare @y int =  9;
    declare @z int = 11;

    select 'this works' where @x between @y and @z or @x between @z and @y;

    There is one more thing that about the first code snippet shown above that caught my attention. The two numbers .9 and 1.10 violate the Joint Commission's guidelines on numbers used in prescriptions. The Joint Commission issues guidelines for healthcare institutions to prevent errors and not kill patients, among other things. There is a "Do Not Use" list of abbreviations and formatting styles that have been proven to cause medical errors. A number such as .9 is more likely to be seen as 9 than 0.9 is. Numbers formatted as 1.10 or 1.0 are also dangerous. The 1.0 is more likely to be misinterpreted as 10 than 1. is. Misinterpretations of decimal numbers have repeatedly been proven to kill patients. These formatting guidelines were originally developed for handwritten prescriptions. Some people think the guidelines should also be applied to computerized forms. I'm not suggesting you change your T-SQL code, but I thought those of you who do user interface design might find this discussion interesting.





  • Adding clocks when working globally

    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.


    Figure 3. Left mouse click in System Tray after adding clocks.

    While the extra clocks are interesting, I personally find Hyderabad needs more visibility. Go to Gadgets and either double-click the clock or right-click and select Add to add it to your desktop. Set the Options for the clock to assign a name to it.


    Figure 4. Clock gadget.


    Figure 5. Use Options to put a name on your desktop clock. Either right-click the clock and select Options or click the wrench icon.

  • BI Beginner: Data Explorer is a must have

    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 tab in Excel 2013. Click Online Search to quickly find data.

    Step 6. Click Online Search. Search for something that interests you. I picked one of my research interests, infant mortality.


    Figure 3. Search results showing online data sources.

    Until you do this, you will not appreciate how ultra cool this really is. When you do a mouseover on any of your search results, the Preview tab shows you what the data will look like when pulled into Excel. True WYSIWYG.

    Notice that the search result that has mouse focus has a USE hyperlink. Click USE to actually bring the data into Excel. Again, until you do this, it’s difficult to appreciate how well this feature works. Go take a look here at the data I selected. The text on the Wikipedia page was consumed and loaded into Excel. That is extremely powerful and useful.

    Here’s how to use Data Explorer to navigate data on the web.



    Figure 4. Click From Web and enter the a URL of a web page containing data.

    The Query Navigator appears. Notice that it provides navigation to all of the consumable data sources on the page.


    Figure 5. Query Navigator.

    Easy. Powerful. Quick. Once Data Explorer was added to Excel, it took me much less than 2 minutes to search for my data, preview it, select it, import it, and then examine it further with the Navigator.

  • Diagnosing differences between production and development

    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 must be done before an accurate diagnosis can be made. A proper assessment requires the proper tools. There are tools for comparing database schemas and tools for comparing data. Today I’m focusing primarily on database schema comparisons. Schema comparisons are usually easier to assess quickly and thoroughly than data comparisons. Because of that, I recommend starting with a schema comparison before performing a data comparison.

    Ideally your database schema is version controlled, you have the latest version of Visual Studio, TFS, and maybe Red Gate’s SQL Source Control. You may even have Red Gate’s SQL Compare, a tool I have used extensively with great benefit at multiple clients. As Peter Schott points out below, using SSDT is a really great idea, assuming you are allowed to download it, which isn’t a given in environments with extremely tight control of admin rights. If you have all of these tools, you probably already know how to compare database schemas. As a consultant, I know that most shops aren’t doing version control of their databases. Many small shops don’t have Visual Studio at all. They also may not have a budget for any software purchases, no matter what the ROI is. Rational thought does not always prevail in budgetary matters.

    I’ve seen just about every workaround possible at low budget clients, not all of them good. You don’t have to have two instances of SSMS open side by side to look at the production machine’s version of a stored procedure and the development machine’s version. You can split SSMS into two vertical windows within a single instance of SSMS. Go to the SSMS menu bar and select the Window submenu to split the query window vertically. Most of you know this, but I’ve been asked “how did you do that?”. I respect anybody who asks a question in order to learn.


    Figure 1. SSMS with a vertical split showing development code on the left and production code on the right.

    The split window approach has limitations. You need to already know where you should be looking and which object(s) you should be comparing. If you are so lucky as to know that, then the amount of code needs to be very small. Visually scanning from left to right and back again ad nauseum is inefficient if the number of statements to compare is large. The risk of missing a difference is significant.

    If you don’t have any first class tools to compare your schemas (Visual Studio has a good facility for comparing code versions), I recommend saving the results to files. In the following example, I save each of the stored procedures above to files. Use the free WinMerge differencing tool to compare the files. It is available as a portable application in case you are prevented from installing applications on your machine.


    Figure 2. WinMerge comparison of the two script files from SSMS.

    It’s much easier to see differences when using a differencing tool. Your time is valuable and you need to use the right tools to be efficient and effective. Scripting an individual object to a file only works well when you have a small number of objects and you know which objects you need to compare. There is a feature to script your entire database schema from SSMS into one file. You can compare one schema’s file to another schema’s file.

    I have had people tell me that they tried to script their database schema to a file but it only scripted the database file creations statements and none of the objects inside the database. That’s not the option I’m talking about here. Scripting the objects that are in the database is a different menu option altogether.


    Figure 3. This is NOT the option to script all of the objects in a database.


    Figure 4. How to script all of the objects in a database.

    By default when database objects are created using this wizard, a descriptive header appears before each object. The header is a SQL comment that includes the date and time the object was scripted. That’s a problem because you won’t be creating all of the development object scripts at the exact same time you create the production object scripts. You’ll have a false positive difference for each object, which is quite annoying. Fortunately you can used the Advanced button on the third screen of the scripting wizard to prevent this problem from occurring in the first place.


    Figure 5. click the Advanced button to suppress the object creation comments.

    Locate the Include Descriptive Headers and set it to False.


    Figure 6. Set Include Descriptive Headers to False.

    Once you have a script for the development schema and another for the production schema, use WinMerge to do an entire database comparison, assuming you don’t have one of the first class comparison tools mentioned earlier.

    Just to clarify, you should use a proper database comparison tool to troubleshoot problems that may be caused by unintended database schema differences. But if you don’t have the proper tools or you’re not allowed to download and install SSDT (there really are companies that have such highly restrictive policies), scripting the schema to a file and using WinMerge is an acceptable alternative that works sufficiently well. I highly recommend that after deploying schema changes to a production database, you should use some sort of process to compare schemas to make certain that the changes made were exactly as intended. Your primary comparison should be to compare the production schema before the change to the production schema after the change. If you find unintended differences and can’t resolve the discrepancies quickly, you can obtain a resume template from here.

  • implicit data type conversion and COALESCE

    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 3-Mar-2013 @xINT 23 @xNVARCHAR something

    The problem with building SQL strings using string concatenation is that if any substring is NULL, the entire concatenated string is NULL. COALESCE can fix that problem when the data type is some type of string. The first SELECT demonstrates this.

    But the design pattern for dealing with a NULL string variable doesn't extend to other data types as the second and third SELECT statements demonstrate. The DATETIME and INT variables don't get converted to strings (an empty string in this example). Instead, the empty string gets converted to DATETIME or INT, respectively. This conversion yields some unexpected results. The SQL Server documentation here does provide a clue about what happens when an empty string is converted to DATETIME. When a date value is not provided during a data type conversion, SQL Server defaults to 1900-01-01. Clearly a NULL does not provide a date value. Similarly, when a time value is not provided during a conversion, SQL Server defaults to 00:00:00.

    I included explicit CAST statements to prove what happens when an empty string is converted to DATETIME or INT.

    The solution is to avoid the implicit data type conversions in the first place. CAST the variables to strings first and then pass those strings to COALESCE as shown in the last two statements.

    If you run the code, it's easy to see for yourself what's actually going on. 

    declare @xDATETIME DATETIME;

    declare @xINT      INT;

    declare @xNVARCHAR NVARCHAR(10);


    select COALESCE(@xNVARCHAR,'');  --returns an empty string


    --implicit data type conversions cause unexpected results

    select COALESCE(@xDATETIME,'');  --returns 1900-01-01 00:00:00.000

    select COALESCE(@xINT,'');       --returns 0

    --find out what data types the COALESCE statements become
    select ISDATE(COALESCE(@xDATETIME,''));  --returns 1 indicating a date value
    select ISNUMERIC(COALESCE(@xINT,''));    --returns 1 indicating a numeric value

    --explicit data type conversions
    select CAST('' AS DATETIME);  --returns 1900-01-01 00:00:00.000
    select CAST('' AS INT);       --returns 0

    --how to prevent COALESCE from doing  implicit data type conversion
    select COALESCE(CAST(@xDATETIME AS NVARCHAR(23)),'');  --returns an empty string
    select COALESCE(CAST(@xINT      AS NVARCHAR(23)),'');  --returns an empty string


  • SSMS generating non-ANSI scripts

    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 that uses CURRENT_TIMESTAMP for ANSI compliance.

    create table table1 (

    Second, go to the Object Explorer in SSMS and select the table. RIght-click it to generate a create table script. You'll get something like this:

    CREATE TABLE [dbo].[table1](
    [a] [datetime] NULL

    ALTER TABLE [dbo].[table1] ADD DEFAULT (getdate()) FOR [a]

    While the script will work just fine, getdate() is not ANSI compliant. The scripting tool shouldn't cause you to lose ANSI compliance.



    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 execution plan, you may see that a sort was done. Just because a sort was done internally doesn't mean that you are guaranteed perfectly ordered results. You'll see ordered results virtually every time you use DISTINCT without an ORDER BY. But if you really want ordered results absolutely positively each and every time, you must use an ORDER BY. I have seen real world queries that have a DISTINCT without an ORDER BY fail to return everything in order. Again, if order matters, you must specify ORDER BY.

    Quoting from the documentation:

    "The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified."

    Although the second and third execution plans appear identical, only the third query with the DISTINCT is guaranteed to return ordered results.


    table #dupes (a int);

    insert into #dupes (a) values (2);
    insert into #dupes (a) values (2);
    insert into #dupes (a) values (1);
    insert into #dupes (a) values (1);

    select          a from #dupes;

    select distinct a from #dupes;

    select distinct a from #dupes order by a;


    Figure 1. Although the query plans for Query 2 and Query 3 appear identical and they both show a sort, only Query 3 is always certain to return ordered results because it has ORDER BY.

    This is my third post about using ORDER BY. There is one case I've seen where ORDER BY doesn't make sense and shouldn't be used. Tables have no sense of order, so there is no point in using ORDER BY on an INSERT SELECT statement.

  • Free OCR

    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 the image into OneNote, right-click the image and select Copy Text from Picture.


    Figure 2. Invoking OneNote’s OCR capability with a right-click. Notice the menu option to make the text searchable.

    When you paste the text into Word, you’ll notice that it doesn’t have any formatting. Notice that although the text in the Word document below says “This is not text”, it really is. This is yet another example of what you observe empirically not matching the truth. What appears below really was text that was pasted into Word.


    Figure 3. Screen capture converted to raw text pasted into Word.


    If you select the OneNote option to Make Text in Image Searchable, you can add some really useful functionality to your document. Notice that OneNote’s search starts finding matches before you enter all of your search string. If you are going to rely on the search feature in OneNote and it doesn’t work quite like you expect, extract the text and see if OneNote accurately converted it to text. Sometimes the document being converted can’t be accurately converted to text, which can explain suboptimal search results.


    Figure 4. Searching text embedded within an image in OneNote.

    There are limitations to what OneNote OCR can do. Light text against a dark background may result in no OCR, no text. Look at the following example.


    Figure 5. Light text and dark background causes difficulties for OCR in OneNote.

    There is a workaround to enable OCR to work. Change the color scheme, which can be done easily with Paint. Paste the screen capture into Paint and right-click to bring up action menu that ends with Invert color. It will transform light to dark and dark to light. Copy the inverted image and paste it into OneNote to do the conversion.


    Figure 6. Inverting colors to enable OneNote to perform OCR on light text with a dark background.

    On the Insert tab, OneNote has a built-in screen clipping tool to make screen captures easy to do from within the tool.


    Figure 7. Screen Clipping feature in OneNote.

    If you don’t have OneNote, there are several websites that will convert image files into text. I like for converting images when I don’t have access to OneNote. You should not upload any sensitive or confidential screen captures to any website offering OCR services.

  • Views and ORDER BY

    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 like UNION ALL doesn't guarantee ordered results. The truth remains - if you want to order the results of a select statement, add an ORDER BY statement to your select. In this example, I must add that an ORDER BY hidden inside a view definition doesn't do the job of an ORDER BY, although in your tests it might appear that it does.

    If you want to order the results from a view, call the view with an ORDER BY clause instead of forcing an ORDER BY into the view definition.

    There is a fix for SQL Server 2008 to cause the results from a view with TOP (with or without PERCENT) and ORDER BY to have ordered results. Fixes are no substitute for reading the documentation. Here is what the SQL Server documentation tells us:

    "The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself."

    create table table1 (a int);

    -- this won't work and the view won't be created
    create view view1 as
    from table1
    order by a;

    Msg 1033, Level 15, State 1, Procedure view1, Line 4
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

    -- this will create the view
    create view view1 as
    top 100 a
    from table1
    order by a;

    -- results might be in order or might not
    select * from view1;

    -- results will be in order
    select * from view1
    order by a;






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

    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 (

    create table table2 (

    create table table3 (

    insert into table1 (a,b) values (1,2);
    insert into table2 (a,b) values (1,2
    insert into table3 (a,b) values (1,2);

    insert into table1 (a,b) values (11,12);
    insert into table2 (a,b) values (21,22
    insert into table3 (a,b) values (31,32);

    -- Select the following query and then press Ctrl Shift Q to invoke the Query Designer

    select t1.a as t1a, t2.b as t2b, t3.a as t3a, t3.b as t3b
    from table1 t1, table2 t2, table3
    where t1.a = t2.
    and t2.b = t3.b;


    Figure 1. Query Designer window with non-ANSI joins converted to ANSI joins.

    -- Select the following query and then press Ctrl Shift Q to invoke the Query Designer

    select t1.a as t1a, t2.b as t2b, t3.a as t3a, t3.b as t3b
    from table1 t1, table2 t2, table3
    where t1.a = t2.
    and t2.b = t3.
    and t1.a =
    and t2.b =
    and (t3.a = 1 or t3.b = 2);


    Figure 2. Notice that the WHERE clause has more predicates than we started with because of the OR.

    -- Don't forget to clean up your mess!

    drop table table1;
    drop table table2
    drop table table3;

    The Query Designer does a good job converting the first select statement. It doesn't do such a great job on the second select. The OR clause causes the Query Designer to introduce unnecessary complexity into the code.

    I did real world performance testing of code matching the general pattern of the example select statement shown above having the OR clause. The generated code with the OR clause was less efficient than code I manually converted to ANSI inner joins. Original code without an OR in a compound predicate was fine.

    Used with your critical thinking skills, Ctrl Shift Q can help you when refactoring legacy code. It works well most of the time. Hopefully you now have an understanding of when it might be appropriate and when it might not be.

  • 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 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. The where clause filters out the superfluous row leaving you with a single row of output.

    Just because you don't see a Cartesian product doesn't mean there isn't one. With different data, you would see the Cartesian product. When you are maintaining code, look for N-1 joins if you really want to be both thorough and safe.

    create table #table1 (
         a int
        ,b int

    create table #table2 (
         a int
        ,b int

    create table #table3 (
         a int
        ,b int

    insert into #table1 (a,b) values (11,12);
    insert into #table2 (a,b) values (11,22);
    insert into #table3 (a,b) values (11,32);

    insert into #table3 (a,b) values (311,321);

    select *
    from #table1 t1
    inner join #table2 t2
    on t1.a = t2.a;

    select *
    from #table1 t1
    inner join #table2 t2
    on t1.a = t2.a
    , #table3 t3
    where t3.b = 32--comment this out to see the Cartesian product

    select *
    from #table1 t1
    inner join #table2 t2
    on t1.a = t2.a
    inner join #table3 t3
    on t2.a = t3.a;




  • 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 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 more system views, among other things.


    Figure 1. SQL Server 2008 R2 on the left and SQL Server 2012 on the right.

    The SQL Writer Service works with the Volume Shadow Copy Service. Winmgmt works with WMI.

    Database Mirroring is deprecated and is replaced by AlwaysOn Availability Groups. In the side by side comparison shown above, I abbreviated the 14 AlwaysOn system policies and 14 AlwaysOn system conditions into single line entries.

    Notice that Data Transformation Services and SQL Mail, which were previously deprecated, do not appear in SQL Server 2012 at all. Did you know that SQL Server Profiler is deprecated in SQL Server 2012? A list of all deprecated features is found here.  In the future, you will use Extended Events to collect and replay traces.

    There are many changes to SSIS including new Integration Services Catalogs.


    Figure 2. Integration Services Catalogs.

  • 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. 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 a variety of data sources as shown below.

    When you use PowerPivot, you are bringing data from your data source into Excel. You’ll need enough memory and disk space for the data you are working with. If your data changes on the server, you can refresh the data because the Excel file is both a local repository of the data as well as a set of rules on how you’ve decided to present and manipulate your data. You can share the Excel file with others and they can connect to the data source and refresh the workbook with the latest data whenever they want.

    For certain power users and business analysts, it makes far more sense to give them Excel’s PowerPivot to analyze their data than making them learn SQL and use SSMS or SSDT.

    Here are the steps to connect to a data source (SQL Server in this example, but it could another data source) and view the data in Diagram View. Begin by selecting the DATA tab in Excel, then use From Other Sources to select your data source.

    NOTE: Some of the screen captures are annotated in blue because it is a safe color to use with all forms of color blindness.


    Figure 1. Use From Other Sources on the DATA tab to select your data source.

    Make a connection to your data source using whatever credentials are required. In this example, the local SQL Server was used. Notice that a single dot (e.g. period) was used instead of an actual server name because a single dot defaults to the local server.


    Figure 2. Connecting to the local SQL Server using a dot instead of specifying the server name.

    To show the relationships between tables in your PowerPivot diagram, you must check Import relationships between select tables. How many tables you can select depends on the quantity of data and the amount of free memory on your local machine where you are running Excel. If you click the Select Related Tables button, you might bring in more data than your machine can handle.


    Figure 3. How to specify the tables you want in your PowerView diagram. Notice there are 5 steps to follow.

    Remember that you are saving both data and the rules you specified when creating your workbook. You should write a meaningful description so that the purpose of the workbook is clear.


    Figure 4. Enter a meaningful description.

    Specify Table in the Import Data dialog box so that your data source’s tables are instantiated as tables in Excel.


    Figure 5. Select Table in the Import Data dialog box.

    If your machine doesn’t have the capacity to handle all of the data you’ve specified, you’ll receive an error message similar to the following:


    Figure 6. To show you what happens when too much data is required, the Select Related Tables button shown earlier was clicked.

    After the data source’s tables are downloaded into table structures in Excel (notice that each table has its own tab or worksheet in Excel), click the POWERPIVOT tab in Excel and then click Manage to start PowerPivot.


    Figure 7. First click the POWERPIVOT tab and then click Manage.

    A new PowerPivot window appears. Click Diagram View to show the tables and their relationships.


    Figure 8. Click Diagram View on the PowerPivot window.

    Keep in mind that the diagram is of the local copies of the tables in Excel, not the originals on the server. The copies of the tables in Excel have the same referential integrity (relationships) that they have in the original data source.


    Figure 9. Diagram View in PowerPivot.

    Excel has many features to help you manipulate and analyze data. Use the right tool for the job. Sometimes Excel is a far better data analysis tool than SSMS and T-SQL.

    Thanks to SQL Server MVP Robert Cain for his invaluable assistance in writing this post.

  • 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 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.

  • 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 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 desired destination.


    Figure 1. Using the %temp% environment variable in Windows Explorer.

    Windows shell: commands provide similar direct navigation. There is some overlap between environment variables and shell: commands, but most shell: commands offer functionality not provided by Windows environment variables. Notice that you must put a colon between the word shell and the shell command.


    Figure 2. Using the shell:SendTo command in Windows Explorer.

    Where are all of these shell: commands listed? On your machine, assuming you have access to the registry. In corporate environments, you just might be out of luck. Even if you have administrative rights and can view your registry, you are still out of luck because of how the commands are stored in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\FolderDescriptions.


    Figure 3. Shell: commands are under FolderDescriptions in the registry.

    I exported the FolderDescriptions registry key, opened the file with a text editor, sorted it, and extracted the names of all of the shell: commands which I’ve listed below for you. Remember, the syntax you put in the address bar of Windows Explorer is of this format:


    You can also enter the shell: command into your search box as shown in Figure 4. It will start a new instance of Windows Explorer opened to the folder specified in the command.


    Figure 4. Using a shell: command with the search box.

    You may also enter a shell: command in a run dialog box.


    Figure 5. Using a shell: command in a run dialog box.

    List of shell: commands on my Windows 7 machine:

    Administrative Tools
    CD Burning
    Common Administrative Tools
    Common AppData
    Common Desktop
    Common Documents
    Common Programs
    Common Start Menu
    Common Startup
    Common Templates
    Default Gadgets
    Device Metadata Store
    Local AppData
    My Music
    My Pictures
    My Video
    OEM Links
    Original Images
    Quick Launch
    Start Menu
    User Pinned
    Virtual Machines

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement