THE SQL Server Blog Spot on the Web

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

John Paul Cook

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

    image

    Figure 1. Check Enable Advanced Query Editing.

    Step 4. Open a workbook.

    Step 5. Click DATA EXPLORER.

    image

    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.

    image

    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.

    image

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

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

    image

    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.

    image

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

    Locate the Include Descriptive Headers and set it to False.

    image

    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 (
        a DATETIME DEFAULT CURRENT_TIMESTAMP
    NULL
    );

    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
    )
    ON [PRIMARY]
    GO

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

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

     

  • DISTINCT and ORDER BY

    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.

    create

    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;

    ExecutionPlanOrderBy

    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.

    screenCapture

    Figure 1. Screen capture of text.

    After pasting the image into OneNote, right-click the image and select Copy Text from Picture.

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

    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 http://www.free-ocr.com/ 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);
    go

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

    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
    select
    top 100 a
    from table1
    order by a;
    go

    -- 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 (
        a
    int
       ,b
    int
    );

    create table table2 (
       a
    int
      ,b
    int
    );

    create table table3 (
        a
    int
       ,b
    int
    );

    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
    t3
    where t1.a = t2.
    a
    and t2.b = t3.b;

    image

    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
    t3
    where t1.a = t2.
    a
    and t2.b = t3.
    b
    and t1.a =
    1
    and t2.b =
    2
    and (t3.a = 1 or t3.b = 2);

    image

    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.

    SSMS

    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.

    Catalog

    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.

    DataSource2

    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.

    connect

    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.

    SelectTables

    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.

    SaveODC

    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.

    table 

    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:

    boundsError

    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.

    Manage

    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.

    PowerPivot

    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.

    DiagramView

    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.

    image

    Figure 1. Extract the stencils to your My Shapes folder.

    image

    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.

    image

    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.


    image

    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.

    image

    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:

    shell:nameOfShellCommand

    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.

    image

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

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

    image

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

    List of shell: commands on my Windows 7 machine:

    AddNewProgramsFolder
    Administrative Tools
    AppData
    AppUpdatesFolder
    Cache
    CD Burning
    ChangeRemoveProgramsFolder
    Common Administrative Tools
    Common AppData
    Common Desktop
    Common Documents
    Common Programs
    Common Start Menu
    Common Startup
    Common Templates
    CommonDownloads
    CommonMusic
    CommonPictures
    CommonRingtones
    CommonVideo
    ConflictFolder
    ConnectionsFolder
    Contacts
    ControlPanelFolder
    Cookies
    CredentialManager
    CryptoKeys
    CSCFolder
    Default Gadgets
    Desktop
    Device Metadata Store
    DocumentsLibrary
    Downloads
    DpapiKeys
    Favorites
    Fonts
    Gadgets
    Games
    GameTasks
    History
    HomeGroupFolder
    ImplicitAppShortcuts
    InternetFolder
    Libraries
    Links
    Local AppData
    LocalAppDataLow
    LocalizedResourcesDir
    MAPIFolder
    MusicLibrary
    My Music
    My Pictures
    My Video
    MyComputerFolder
    NetHood
    NetworkPlacesFolder
    OEM Links
    Original Images
    Personal
    PhotoAlbums
    PicturesLibrary
    Playlists
    PrintersFolder
    PrintHood
    Profile
    ProgramFiles
    ProgramFilesCommon
    ProgramFilesCommonX64
    ProgramFilesCommonX86
    ProgramFilesX64
    ProgramFilesX86
    Programs
    Public
    PublicGameTasks
    PublicLibraries
    Quick Launch
    Recent
    RecordedTVLibrary
    RecycleBinFolder
    ResourceDir
    Ringtones
    SampleMusic
    SamplePictures
    SamplePlaylists
    SampleVideos
    SavedGames
    Searches
    SearchHomeFolder
    SendTo
    Start Menu
    Startup
    SyncCenterFolder
    SyncResultsFolder
    SyncSetupFolder
    System
    SystemCertificates
    SystemX86
    Templates
    User Pinned
    UserProfiles
    UserProgramFiles
    UserProgramFilesCommon
    UsersFilesFolder
    UsersLibrariesFolder
    VideosLibrary
    Virtual Machines
    Windows

  • 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 as add the Shift key to the mix after selecting the file of interest.

    image

    Figure 1. Normal right-click menu option.

    image

    Figure 2. Shift plus right-click menu option. Notice the addition of Pin to Start Menu and Copy as path.

    Here is what Copy as path put in my paste buffer:

    "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf"

    Considering that this particular path has embedded spaces, the double quotes surrounding the fully qualified file name are good to have. When there aren’t any spaces in the path or file name, the double quotes may be annoying. But the annoyance of double quotes is nothing compared to not having this functionality at all. Even on my own machine at home, I prefer Copy as path over other methods because I don’t want to install a send to clipboard program on my machine. The fewer programs that are installed, the better things are.

  • 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 whether you use UNION or UNION ALL. If you have duplicates, UNION will return fewer rows than UNION ALL.

    No matter who your database vendor is, a UNION statement follows the rules of set theory. Set theory tells us that a set only contains unique elements. In other words, there are no duplicates in a set. If you use a UNION statement, you will not see any duplicate values in your result set. To toss out the duplicates, the database engine has to do some type of sort or merge operation to identify the duplicates. The side effect of this is that your result set tends to be ordered. Notice I said tends. This is a big misconception about UNION statements. You may have always seen the results of UNION statements to be perfectly ordered. Just because you observe a query returning sorted results without an ORDER BY statement doesn't mean you can always count on sorted results. If you require the results to be ordered, you must use an ORDER BY clause. Without it, you're gambling.

    I've provided some sample code that will work anywhere for you to play with and learn. Select the first three lines and execute them by themselves to see in what order the results appear. Then execute everything. Examine the output and remember what you learned for the next time when it is a real coding assignment.

    select-- Select only the first three lines
    UNION     -- and execute them by themselves.
    select-- See what row is returned first.
    UNION
    select
    3
    UNION
    select
    2

     

    select 7
    UNION ALL
    select 2
    UNION ALL
    select 3
    UNION ALL
    select 2

     

This Blog

Syndication

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