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

  • Windows 8.1 Start Menu partial workaround

    I want a Windows 7 Start Menu when I’m in desktop mode on Windows 8.1 without installing third party applications or going to a lot of trouble. I implemented a simple workaround that provides some of the functionality I’m seeking. It’s limited to operating system specific features. I’ll call it a half-full glass.

    What I want is the box on the Windows 7 start menu where I can type what I’m looking for and quickly find it. Today I needed to scan something. I use the scan feature that comes with Windows. Because I have a settings folder named settings.{ED7BA470-8E54-465E-825C-99712043E01C} on my desktop, I knew I could go to my desktop, double-click the settings folder and bring up a settings window with a search box.


    Figure 1. Settings box showing the search feature.

    But I wanted a simpler, more direct way to bring up the settings folder. I wanted to simply click on my taskbar and have magic happen. I tried to pin the settings folder to the taskbar, but that was not allowed. I created a shortcut to the settings folder and tried to add the shortcut to the taskbar. That didn’t work, either. I renamed the shortcut file to settings.exe so that the taskbar would treat it just like any other exe file. I was able to drag the shortcut named settings.exe to the taskbar and then pin it to the taskbar as shown below. Now a single click of the taskbar invokes the settings window.


    Figure 2. Settings folder on desktop. Shortcut named settings.exe in Windows Explorer and also pinned to the taskbar.

  • SQL Server 2014 Backup to the Cloud

    Backing up SQL Server to the cloud makes a lot of sense, particularly for small businesses. Managing the physical media takes up too much time and effort for a small shop. Azure storage simplifies the backup process allowing a small business to allocate its limited resources more effectively.

    As I was preparing this post and editing the screen captures showing how to back up SQL Server 2014 to Windows Azure blob storage, I discovered that earlier today Microsoft released a whitepaper covering all of this and more in depth. Better to have discovered that early in the writing process instead of later! You can download the SQLServer 2014 and Windows Azure Blob Storage Service: Better Together whitepaper to get the whole story.

  • Windows 8.1 Usability Enhancements

    If your copy of Windows 8.1 is set to automatically install updates, you may have missed that you now have new usability enhancements to improve the user experience for both using a mouse and working in desktop mode. You can easily pin modern apps to your taskbar by right clicking a live tile.


    Figure 1. After right-clicking the Skype live tile, a menu appears. Select Pin to taskbar to pin the application to the taskbar.

    Not only does the shortcut appear on the taskbar in desktop mode, the taskbar is now available in the Modern UI. Drag your mouse cursor below the bottom edge of the screen in Modern UI mode and the taskbar appears.


    Figure 2. Taskbar showing in Modern UI with Skype icon added.

    The desktop taskbar allows Modern UI apps to be closed from the app thumbnail by offering the familiar red X to close.


    Figure 3. Desktop taskbar with Skype icon added. Notice that Modern UI apps can be closed from the desktop taskbar just like desktop applications can be.

    As a Modern UI app, Skype still takes over the entire window but now Modern UI apps have a title bar from which the application can be closed. Drag your mouse cursor above the top edge of the screen to make the modern app’s title bar appear.


    Figure 4. Modern apps now have a title bar from which they can be closed.

    The taskbar now has icons for Modern UI apps that are running, which provides you with a more complete view of what’s actually happening on your system.


    Figure 5. Icons for modern apps (OneDrive and Weather) on the taskbar.

    When invoking media files while in desktop mode, they no longer take over the entire screen. Instead, they open in a window.


    Figure 6. Media files open in a window instead of taking over the desktop.

    When you are in the Modern UI and want to shut down or restart, the addition of a power button makes things easier. Notice there is also a search button.


    Figure 7. A power button and a search button have been added to the Modern UI.

    Unfortunately, the Microsoft Reader app still takes over the entire desktop when opening a pdf file. It would be nice if it opened in a window like media files do now. My workaround for opening pdf files in a window is Foxit Reader. It has more features than Adobe Acrobat and is my preferred tool for reading pdfs. It allows me to annotate pdfs and insert a digitized copy of my signature.

  • SQL Server 2012 and 2014 Management Differences

    Whenever a new version of SQL Server is released, I compare what is in SQL Server Management Studio to get an overall, visual representation of the differences. There are of course differences that aren’t readily apparent by comparing what’s in SSMS. The scope of this post is limited to the differences that are apparent in putting the different versions of SSMS side by side. In the comparison of 2012 to 2014, the SSMS differences appear under the Management node, which is why other SSMS nodes are not shown.



    Figure 1. Composite screen capture of SSMS with SQL Server 2012 Enterprise on the left and SQL Server 2014 Enterprise on the right.

    Notice that all of the differences are additions to SSMS in SQL Server 2014. Whatever appears in SQL Server 2012 SSMS also appears in SQL Server 2014 SSMS. The additions are in the list below:

         Policy Management
                   System Policies
                   System Conditions
                   Smart Admin
                   SmartAdmin State
         Managed Backup

    Smart Admin provides an automated and simplified approach to SQL Server administration. It also supports SQL Server Managed Backup to Windows Azure. Backing up SQL Server to the cloud is a great way to keep backups safe and secure while minimizing the cost of personnel and infrastructure.

  • Security in the Cloud including HIPAA

    I’m increasingly recommending cloud based strategies to both drive down costs and simplify things. Cloud technology is now at a point there there are very clear guidelines and frameworks for addressing security concerns. Take a look at the Microsoft Azure Trust Center for a list of all of the security certifications Microsoft has earned.

    Notice that Microsoft has a P-ATO (Provisional Authority to Operate) from FedRAMP, the United States federal government cloud computing watchdog agency. FedRAMP addresses IaaS, PaaS, and SaaS. Of particular interest to those in healthcare is Microsoft is the HIPAA Business Associate Agreement or BAA. The Microsoft HIPAA/HITECH Act Implementation Guidance whitepaper is found here. A list of FedRAMP compliant cloud providers is found here.

    Small businesses can in many cases be better off by eliminating their server rooms and moving them off premises to the cloud. I did some consulting for a law firm a few years ago after a catastrophic failure of a SQL Server. SQL Azure wasn’t available back then, but it would be my recommendation to that firm today.

  • Performance and Security Implications of 8.3 File Names

    While testing exports of SQL Server tables to files, one thing led to another and I started investigating the file server where the files are saved. Finding files on the server seemed slow. Further investigation revealed several MSDN and TechNet posts on how 8.3 file names adversely affect both file enumeration and creation performance. This post provides a detailed description of how slow file server performance was found to be caused by having 8.3 files names enabled. This post explains how 8.3 file names can be exploited to sneak a malicious exe onto your server by hiding it in safe file name such as a txt file.

    This post explains how to check for and disable 8.3 file names. Pay particular attention to how you also need to do 8.3 name stripping to realize the maximum benefit of disabling 8.3 file names. I’m going to stop short of recommending that you do 8.3 name stripping. Here’s some edited output from my desktop that might make you want to proceed with extreme caution:

    C:\Windows\system32>fsutil 8dot3name set C: 1
    Successfully disabled 8dot3name generation on C:

    C:\Windows\system32>fsutil 8dot3name strip /s /v C:\


    @C:\PROGRA~2\WIC4A1~1\Writer\WI68BE~1.DLL,-1001        HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E

    Total affected registry keys:                2148

    The operation failed because registry entries refer to 8dot3 names in the
    specified path.
    For details on the affected registry keys please see the log:
      "C:\Users\John\AppData\Local\Temp\8dot3_removal_log @(GMT 2014-04-05 02-42-36).log"



    Let’s take a look at the log file.


    Registry Data                                                                     Registry Key Path
    -------------------------------------------------------------------------------   ------------------------------------------
    C:\PROGRA~1\MICROS~2\Office15\1033\ACCESS12.ACC                                   HKCR\.accdb\Access.Application.15\ShellNew
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE,0                                      HKCR\Access\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\PROTOC~1.EXE "%1"                                   HKCR\Access\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP "%1"                        HKCR\Access.ACCDAExtension.15\shell\open\command
    "C:\PROGRA~1\MICROS~2\Office15\MSOHTMED.EXE" "%1"                                 HKCR\Access.Application.15\HTML Handler
    "C:\PROGRA~1\MICROS~2\Office15\MSOHTMED.EXE" /o3 "%1"                             HKCR\Access.Application.15\HTML Handler\shell\edit\command
    "C:\PROGRA~1\MICROS~2\Office15\EXCEL.EXE" /dde                                    HKCR\Access.Application.15\search\AnalyzeInExcel\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP "%1"                        HKCR\Access.Extension.15\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE,42                                     HKCR\Access.LockFile.15\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Form.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Form.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [OpenQuery "%1"]  HKCR\Access.Shortcut.Query.1\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Query.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Query.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Report.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Report.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [OpenTable "%1"]  HKCR\Access.Shortcut.Table.1\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1"][ShellQuit]  HKCR\Access.Shortcut.Table.1\shell\print\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE /NOSTARTUP /SHELLSYSTEM [PrintTo "%1","%2","%3","%4"][ShellQuit]  HKCR\Access.Shortcut.Table.1\shell\printto\command
    C:\PROGRA~1\MICROS~2\Office15\MSACCESS.EXE,0                                      HKCR\Access.UriLink.15\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\PROTOC~1.EXE "%1"                                   HKCR\Access.UriLink.15\shell\open\command
    C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE15\MSOICONS.EXE,6                             HKCR\ACLFile\DefaultIcon
    C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE15\MSOICONS.EXE,6                             HKCR\AWFile\DefaultIcon
    C:\PROGRA~1\MICROS~2\Office15\Lync.exe,0                                          HKCR\callto\DefaultIcon
    "C:\PROGRA~1\MICROS~2\Office15\Lync.exe" "%1"                                     HKCR\callto\shell\open\command
    C:\PROGRA~1\MICROS~2\Office15\OUTLRPC.DLL                                         HKCR\CLSID\{0002034C-0000-0000-C000-000000000046}\InprocServer32

    C:\Program Files\WindowsApps\Microsoft.BingFinance_2014.221.1803.4346_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingFinance_2014.221.1803.4346_neutral_~_8wekyb3d8bbwe
    C:\Program Files\WindowsApps\Microsoft.BingFoodAndDrink_2014.228.447.1992_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingFoodAndDrink_2014.228.447.1992_neutral_~_8wekyb3d8bbwe
    C:\Program Files\WindowsApps\Microsoft.BingHealthAndFitness_2014.221.713.446_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingHealthAndFitness_2014.221.713.446_neutral_~_8wekyb3d8bbwe
    C:\Program Files\WindowsApps\Microsoft.BingMaps_2014.130.2132.1189_neutral_~_8wekyb3d8bbwe\AppxMetadata\AppxBundleManifest.xml  HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Applications\Microsoft.BingMaps_2014.130.2132.1189_neutral_~_8wekyb3d8bbwe

    @C:\PROGRA~2\MICROS~3.0\VC\bin\vcmui.dll,-101                                     HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E
    @C:\PROGRA~2\WI3CF2~1\8.1\WINDOW~1\WINDOW~1.DLL,-101                              HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E
    @C:\PROGRA~2\WIC4A1~1\Writer\WI68BE~1.DLL,-1001                                   HKU\S-1-5-18\Software\Classes\Local Settings\MuiCache\452\52C64B7E

    Total affected registry keys:                2148


    Things were much simpler on my E drive and didn’t cause me to worry or wonder if there were unintended consequences.


    C:\Windows\system32>fsutil 8dot3name strip /s /v E:\
    Scanning registry...
    Registry Data                                            Registry Key Path
    ------------------------------------------------------   ------------------------------------------

    Total affected registry keys:                   0

    Stripping 8dot3 names...

    8dot3 Name      FileId                Full Path
    -------------   -------------------   -------------------------------------------------------------

    Total files and directories scanned:         4936
    Total 8dot3 names found:                        0
    Total 8dot3 names stripped:                     0

    For details on the operations performed please see the log:
      "C:\Users\John\AppData\Local\Temp\8dot3_removal_log @(GMT 2014-04-05 02-58-52).log"



    My recommendations:

    1. On installation of a new volume, disable 8.3 file name creation before putting any files on it.

    2. On a new server build, disable 8.3 file name creation in the registry.

    3. Don’t trust my recommendations. Educate yourself and test thoroughly. Then test some more.

    If you think this might only be a file server issue, think again. I’m preparing a SQL Server for FILESTREAM access. I can see a FILESTREAM enabled SQL Server having enough files that 8.3 file names might affect performance.

  • BI Beginner: Power Query and OData

    I’ve observed that not very many data professionals are familiar with OData. It’s an open data access protocol built on AtomPub and JSON. It provides a RESTful means of retrieving data, which is what this post is about.

    One of the things that makes OData important is that it is recommended by the Open Government Data Initiative. Although this post uses the familiar SQL Server AdventureWorks database, remember, OData is open. It’s not limited to SQL Server or even SQL databases in general.

    A small subset of AdventureWorks is published at


    Figure 1. AdventureWorks subset published at

    Open Excel that has the Power Query add-in installed and select the POWER QUERY tab. Select From Other Sources and then choose From OData Feed.


    Figure 2. Selecting From OData Feed.

    Enter the URL for the AdventureWorks OData source and click OK.


    Figure 3. Specifying an OData source.

    Notice that the Power Query Navigator has a popup for peeking at the data.


    Figure 4. Peeking at the data.

    Double-click CompanySales to open the Query Editor. You can rename and delete columns as well as several other changes you might want to check out.


    Figure 5. Query Editor

    After clicking Apply & Close, the data was loaded into the spreadsheet.


    Figure 6. CompanySales from OData loaded into Excel.

    Notice how the query indicates when it was last updated. It’s important to understand this isn’t just a static one time download. This is a data model in Excel that is connected to a live data source. Go to the DATA tab and select Refresh All.


    Figure 7. Notice that the last updated time has changed to reflect the update.

    By taking advantage of new data management features in Excel, you’re no longer stuck with static, stale spreadsheets.

  • BI Beginner: Use Power Query To Get Data From Web Pages

    We’ve all seen data on a web page and wished we had it in Excel. Copying a table from a web page and pasting it into Excel often leads to disappointing results. Power Query makes it easy to import data from a web page into Excel. As a nurse, I’m particularly interested in analyzing public health data for research purposes. I found some good data on tuberculosis treatment success rates at the World Bank’s website. You can find the data here. The screen captures in this post were made on a Windows 8.1 desktop with Office 2013. Power Query was downloaded from here.


    Figure 1. World Bank tuberculosis data.

    On the POWER QUERY tab in Excel, click From Web to bring up a dialog box for entering the web page’s URL. Enter the URL for your web page and click OK.


    Figure 2. Enter the web page URL into the Power Query dialog box and click OK.

    After a few seconds, Excel presents the DOM in the Power Query Navigator. Use the Peek feature to examines the contents of the items in the Navigator list.


    Figure 3. Mouseover on Table 0 invokes the Peek feature to show a preview of the table on the World Bank’s web page.

    Double-clicking Table 0 in the Navigator brings up the Query Editor.


    Figure 4. Table imported from web page ready for editing.

    Edit the data as needed before completing the import process. I deleted the two rightmost columns and named the table TB Treatment Success Rates.


    Figure 5. Editing the imported table.

    Click Apply & Close to close the Query Editor and complete the import process.


    Figure 6. Data imported from World Bank web page table into Excel.

    Power Query is a very useful addition to Excel that makes it easy to conveniently and quickly bring data from just about any data source into Excel. I find it a valuable tool to use both at work and at school.

  • Comparison of SSMS and Visual Studio for Development

    Either SQL Server Management Studio (SSMS) or Visual Studio can be used for database development. I put them side by side into a single composite screen capture where you literally see the gaps between the products. SQL Server 2012 was installed with all features selected and default settings. The only modifications to the SQL Server installation were the additions of the AdventureWorks2012 and AdventureWorks2012DW databases.

    Using Visual Studio instead of SSMS for database application development gives you the advantage of integration with Team Foundation Server for source code control. Additionally, localdb gives you a way to debug stored procedures locally in case your DBA won’t grant you sufficient rights to debug on the database server. You can copy your production schema to your localdb and debug from within Visual Studio.


    Figure 1. SQL Server 2012 Management Studio on the left, Visual Studio 2013 on the right. Screen captures made on Windows 8.1.

  • Removing trailing spaces in SSMS or Visual Studio

    Regular expressions can easily and quickly remove trailing spaces from every line in your query window as well as a few other tasks I’ll explain. To get started, you need to know how to specify the end of a line. There are two ways to specify the end of a line. You can use the  \n escape sequence or the end of line metacharacter $Figure 1 shows how to remove a single trailing space from however many lines end that way. A more robust regular expression is shown in Figure 2. And a reader offers a keyboard shortcut to do the same thing without using regular expressions – see the comments section at the bottom. Removing extra blank lines is also described at the end of the post.


    Figure 1. Replace space newline “ \n” with newline “\n” to remove one trailing space.

    The problem with the syntax shown in Figure 1 is that you’ll have to iteratively click Replace All multiple times if you have more than one trailing space on some lines.

    There is an easy fix that will remove all trailing spaces from all lines with a single Replace All. Add a + sign after the space and before the \n escape sequence. In other words, you want to use +\n as your search string.


    Figure 2. Replace space plus newline “ +\n” with newline “\n” to remove all trailing spaces.

    Alternatively, you can use the end of line metacharacter expression +$ as your search string and have no replacement string.


    Figure 3. Replace space plus dollar “ +$” with nothing to remove all trailing spaces.

    Notice that I used and to delimit my find and replace strings. As the screen captures show, there aren’t any quotes around the find and replace strings.

    Once you understand the pattern of using the + metacharacter, you can extend it to other edits such as normalizing inline comments to be exactly two consecutive dashes. Sometime you will see inline comments with more than two consecutive dashes. Use --+ as your search string and -- as your replacement string. Don’t use -+ as your search string because it would change a single dash to two consecutive dashes. That’s why you should use use --+ to find all occurrences of two or more dashes.


    Figure 4. Replace dash dash plus “--+” with dash dash “--” to normalize inline comments at two dashes.

    Once you understand the \n escape sequence for the end of a line, you can use this knowledge to remove superfluous blank lines. Sometimes when copy and pasting from a web page or email, you end up with a blank line inserted after each line in the source. This is easy to fix using regular expressions. When a blank line follows a line of text, you have two consecutive newline characters. Use \n\n as your search string and \n as your replacement string to remove the superfluous blank lines as shown in Figure 5.


    Figure 4. Regular expression to remove superfluous lines when every other line is blank.

  • SQL Server 2012 Windowing Functions: LAG

    SQL Server 2012 introduces new windowing functions beyond the basic ranking functions of RANK, DENSE_RANK, NTILE, and ROW_NUMBER. The LAG function is particularly useful when comparing data in the current time period to the previous period. Period to period changes in sales, nosocomial infection rates, and manufacturing defects are just some examples of where a LAG function can simplify your code. The purpose of this post is to provide a basic introduction. Subsequent posts will discuss more advanced queries.

    The traditional approach to comparing a previous period to a current period is to use a self-join, which is illustrated in the first SELECT statement shown below. Notice that the condition on the Tempus column reduces the 12 months (rows) of data to 11 rows. This is expected for the data and logic in the complete example shown further down the page. Also notice that the logic for matching data in the Tempus column with the previous time period works only within the same year when using dates formatted as shown. For example, 201212 + 1 won’t match with 201301. This could be fixed by using date formatting functions including DATEADD. The point here is that dates typically require additional work if you want a robust, generic solution.

    SELECT c.Employee
    , c.Tempus
    , p.Quantity AS PreviousQuantity
    , c.Quantity AS CurrentQuantity
    FROM #Sales p
    INNER JOIN #Sales c
    ON c.Employee = p.Employee
    WHERE c.Tempus = p.Tempus + 1;

    Employee Tempus PreviousQuantity CurrentQuantity
    -------- ------ ---------------- ---------------
    1        201302 100              200
    1        201303 200              150
    1        201304 150              175
    1        201305 175              200
    1        201306 200              250
    1        201307 250              275
    1        201308 275              300
    1        201309 300              350
    1        201310 350              400
    1        201311 400              200
    1        201312 200              300

    (11 row(s) affected)

    Now look at the output from using the LAG function. Notice that 12 rows are returned. This is important when you want to add aggregations to your result set, but this is a topic for a future post on more advanced techniques. The logic for processing the Tempus column is intrinsically more robust and simpler when using the LAG function.

    SELECT Employee
    ,LAG(Quantity) OVER (PARTITION BY Employee ORDER BY Tempus ASC) AS PreviousQuantity
    ,Quantity AS CurrentQuantity
    FROM #Sales;

    Employee Tempus PreviousQuantity CurrentQuantity
    -------- ------ ---------------- ----------------
    1        201301 NULL             100
    1        201302 100              200
    1        201303 200              150
    1        201304 150              175
    1        201305 175              200
    1        201306 200              250
    1        201307 250              275
    1        201308 275              300
    1        201309 300              350
    1        201310 350              400
    1        201311 400              200
    1        201312 200              300

    (12 row(s) affected)

    Now that you can see the quantities for each time period and its immediate predecessor, you’re probably going to want to compute percentage changes from one time period to another.

    SELECT Employee
    ,(CurrentQuantity - PreviousQuantity) / PreviousQuantity * 100.0 AS PercentageChange
    (SELECT Employee
    ,LAG(Quantity) OVER (PARTITION BY Employee ORDER BY Tempus ASC) AS PreviousQuantity
    ,Quantity AS CurrentQuantity
    FROM #Sales) AS Sales;

    Employee Tempus PreviousQuantity CurrentQuantity PercentageChange
    -------- ------ ---------------- --------------- -------------------
    1        201301 NULL             100             NULL
    1        201302 100              200             100.000000000000000
    1        201303 200              150             -25.000000000000000
    1        201304 150              175             16.666666666666667
    1        201305 175              200             14.285714285714286
    1        201306 200              250             25.000000000000000
    1        201307 250              275             10.000000000000000
    1        201308 275              300             9.090909090909091
    1        201309 300              350             16.666666666666667
    1        201310 350              400             14.285714285714286
    1        201311 400              200             -50.000000000000000
    1        201312 200              300             50.000000000000000

    (12 row(s) affected)

    I highly recommend that you purchase Itzik Ben Gan’s book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions to acquire an in-depth knowledge of windowing functions. Itzik reviewed my code and suggested the addition of the index commented out in the code sample shown below. I recommend running the queries with and without the index to see how the index causes the SORT operator to drop out of the query plan.

         Employee NVARCHAR(30)
        ,Tempus DECIMAL
        ,Quantity DECIMAL

    INSERT INTO #Sales
    (Employee, Tempus, Quantity)
    (1, 201301, 100)
    ,(1, 201302, 200)
    ,(1, 201303, 150)
    ,(1, 201304, 175)
    ,(1, 201305, 200)
    ,(1, 201306, 250)
    ,(1, 201307, 275)
    ,(1, 201308, 300)
    ,(1, 201309, 350)
    ,(1, 201310, 400)
    ,(1, 201311, 200)
    ,(1, 201312, 300)

    --Suggestion from Itzik Ben-Gan to eliminate the sort operation shown in the query plan.
    --CREATE INDEX IDX_Sales_Employee_tempus ON #Sales(Employee, Tempus) INCLUDE (Quantity);

    -- traditional solution using a self-join
    -- notice a different approach is required if Tempus data included other years
    SELECT c.Employee
    , c.Tempus
    , p.Quantity AS PreviousQuantity
    , c.Quantity AS CurrentQuantity
    FROM #Sales p
    INNER JOIN #Sales c
    ON c.Employee = p.Employee
    WHERE c.Tempus = p.Tempus + 1;

    -- LAG function used to get date from the previous period
    -- easier and more robust logic for handling the Tempus column
    SELECT Employee
    ,LAG(Quantity) OVER (PARTITION BY Employee ORDER BY Tempus ASC) AS PreviousQuantity
    ,Quantity AS CurrentQuantity
    FROM #Sales;

    -- Create an alias for the previous query and computer percentage change
    SELECT Employee
    ,(CurrentQuantity - PreviousQuantity) / PreviousQuantity * 100.0 AS PercentageChange
    (SELECT Employee
    ,LAG(Quantity) OVER (PARTITION BY Employee ORDER BY Tempus ASC) AS PreviousQuantity
    ,Quantity AS CurrentQuantity
    FROM #Sales) AS Sales;

    DROP TABLE #Sales;

    Windowing functions provide a layer of abstraction that can simplify many coding tasks. Complicated tasks become simple tasks, often with a significant performance improvement.

  • Customizing Windows 8.1 Menus and Creating Shortcuts for Modern Apps

    It’s not possible to directly make a shortcut for a modern UI app like you do for a conventional application, but there is a workaround. You can add the shortcut to the desktop, the taskbar, or a custom toolbar menu. After my previous post on adding a toolbar menu for SQL Server, I have additional suggestions on making custom toolbar menus.

    Skype on Windows 8.1 is a modern UI app that I wanted on my taskbar. That requires creating a shortcut, which isn’t as simple as right-clicking the exe and selecting Create shortcut.


    Figure 1. This approach doesn’t work for modern UI apps.

    Here are the properties for my modern UI Skype shortcut:


    Figure 2. Notice the shortcut uses explorer.exe to invoke Skype.

    Creating the shortcut isn’t difficult, provided you know what string to use for the modern UI application. Use Control Panel to begin the process of finding the application’s URL. Within Control Panel, use associate as your search string.


    Figure 3. Search using associate as the search string and then select Change the file type associated with a file extension.

    You won’t be changing any file extensions. It is necessary to scroll down past the file extensions until you see Protocols. Within the Protocols section, scroll down to find the URL for the Skype application.


    Figure 4. The URL for Skype is URL:skype

    Once you find the URL for a modern UI app, you’ll use just the string value to create a target for your shortcut that follows the pattern shown in the this example:

    C:\Windows\explorer.exe skype://

    At this point, you have a working shortcut that will invoke your modern UI app. If you want a transparent icon to go with your shortcut, there’s more work to do. Most of the modern UI apps that come with Windows 8 are found in C:\Program Files\WindowsApps, which you don’t have access to by default even if you are an administrator.


    Figure 5. Default permissions for C:\Program Files\WindowsApps

    You can either give yourself permissions if you are an administrator or use a command window. I decided to avoid changing security settings and used a command window and DOS commands instead.

    C:\Program Files\WindowsApps>dir
    Volume in drive C has no label.
    Volume Serial Number is 5614-4F17

    Directory of C:\Program Files\WindowsApps

    01/03/2014 05:50 PM <DIR> 4DF9E0F8.Netflix_2.2.0.11_x64__mcm4njqhnhss8
    01/04/2014 11:52 AM <DIR> Deleted
    12/15/2013 09:56 PM <DIR> Microsoft.BingFinance_2013.1119.716.4094_neutral_~_8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingFinance_3.0.1.174_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingFinance_3.0.1.299_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingFinance_3.0.1.299_x64__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingFoodAndDrink_2013.1009.24.3213_neutral_~_8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingFoodAndDrink_3.0.1.177_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingFoodAndDrink_3.0.1.201_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingFoodAndDrink_3.0.1.201_x64__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingHealthAndFitness_2013.1202.919.1616_neutral_~_8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingHealthAndFitness_3.0.1.176_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingHealthAndFitness_3.0.1.335_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingHealthAndFitness_3.0.1.335_x64__8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingMaps_2.0.2009.2356_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingMaps_2.0.2210.2401_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingMaps_2.0.2210.2401_x64__8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingMaps_2013.1010.2301.1978_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingNews_2013.1123.1312.1382_neutral_~_8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingNews_3.0.1.174_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingNews_3.0.1.321_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingNews_3.0.1.321_x64__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingSports_2013.1010.2028.5997_neutral_~_8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingSports_3.0.1.174_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingSports_3.0.1.203_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.BingSports_3.0.1.203_x64__8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingTravel_2013.1010.223.1706_neutral_~_8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingTravel_3.0.1.174_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingTravel_3.0.1.202_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingTravel_3.0.1.202_x64__8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingWeather_2013.1010.2033.469_neutral_~_8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.BingWeather_3.0.1.174_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingWeather_3.0.1.203_neutral_split.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.BingWeather_3.0.1.203_x64__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.HelpAndTips_2013.1004.255.5080_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.HelpAndTips_6.3.9600.20274_neutral_HelpAndTips.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.HelpAndTips_6.3.9600.20274_x64__8wekyb3d8bbwe
    12/15/2013 09:36 PM <DIR> Microsoft.Internal.Media.PlayReadyClient_2.3.1678.1_x86__8wekyb3d8bbwe
    09/29/2013 10:08 PM <DIR> Microsoft.Media.PlayReadyClient.2_2.8.1947.0_x64__8wekyb3d8bbwe
    09/29/2013 10:08 PM <DIR> Microsoft.Media.PlayReadyClient.2_2.8.1947.0_x86__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.Reader_2013.1007.1759.4672_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.Reader_6.3.9600.16422_neutral_glcnd.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.Reader_6.3.9600.16422_x64__8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.SkypeApp_2.0.0.5011_neutral_split.scale-140_kzf8qxf38zg5c
    12/15/2013 09:56 PM <DIR> Microsoft.SkypeApp_2.3.0.1008_neutral_split.scale-140_kzf8qxf38zg5c
    12/15/2013 09:56 PM <DIR> Microsoft.SkypeApp_2.3.0.1008_x86__kzf8qxf38zg5c
    12/15/2013 09:56 PM <DIR> Microsoft.SkypeApp_2013.1112.1630.458_neutral_~_kzf8qxf38zg5c
    12/15/2013 09:36 PM <DIR> Microsoft.VCLibs.120.00.Preview.Internal_12.0.20222.2_x64__8wekyb3d8bbwe
    12/15/2013 09:36 PM <DIR> Microsoft.VCLibs.120.00.Preview.Internal_12.0.20222.2_x86__8wekyb3d8bbwe
    12/15/2013 09:36 PM <DIR> Microsoft.VCLibs.120.00_12.0.21005.1_x64__8wekyb3d8bbwe
    12/15/2013 09:36 PM <DIR> Microsoft.VCLibs.120.00_12.0.21005.1_x86__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsAlarms_2013.1204.852.3011_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsAlarms_6.3.9654.20335_neutral_Time.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsAlarms_6.3.9654.20335_x64__8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.WindowsCalculator_2013.1007.1950.2960_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.WindowsCalculator_6.3.9600.20278_neutral_Numbers.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.WindowsCalculator_6.3.9600.20278_x64__8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> microsoft.windowscommunicationsapps_17.5.9600.20315_neutral_comm.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> microsoft.windowscommunicationsapps_17.5.9600.20315_x64__8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> microsoft.windowscommunicationsapps_2013.1113.2124.1473_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsReadingList_2013.1119.2112.818_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsReadingList_6.3.9654.20321_neutral_Stash.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsReadingList_6.3.9654.20321_x64__8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.WindowsScan_2013.1007.2015.3834_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.WindowsScan_6.3.9600.16422_neutral_ScanApp.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.WindowsScan_6.3.9600.16422_x64__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsSoundRecorder_2013.1010.500.2928_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsSoundRecorder_6.3.9600.20280_neutral_SoundRecorder.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.WindowsSoundRecorder_6.3.9600.20280_x64__8wekyb3d8bbwe
    12/15/2013 09:36 PM <DIR> Microsoft.WinJS.2.0.Preview.Internal_1.0.9385.3_neutral__8wekyb3d8bbwe
    12/15/2013 09:36 PM <DIR> Microsoft.WinJS.2.0.Preview_1.0.9431.0_neutral__8wekyb3d8bbwe
    12/15/2013 09:35 PM <DIR> Microsoft.WinJS.2.0_1.0.9600.16408_neutral__8wekyb3d8bbwe
    12/15/2013 09:34 PM <DIR> Microsoft.WinJS.Preview.1_1.0.9345.0_neutral__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.XboxLIVEGames_2.0.139.0_x64__8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.XboxLIVEGames_2013.1011.10.5965_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.ZuneMusic_2.2.299.0_neutral_resources.scale-140_8wekyb3d8bbwe
    12/15/2013 09:56 PM <DIR> Microsoft.ZuneMusic_2.2.299.0_x64__8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.ZuneMusic_2.2.41.0_neutral_resources.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.ZuneMusic_2013.1116.205.2878_neutral_~_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.ZuneVideo_2.2.299.0_neutral_resources.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.ZuneVideo_2.2.299.0_x64__8wekyb3d8bbwe
    12/15/2013 07:59 PM <DIR> Microsoft.ZuneVideo_2.2.41.0_neutral_resources.scale-140_8wekyb3d8bbwe
    12/15/2013 09:55 PM <DIR> Microsoft.ZuneVideo_2013.1116.205.5085_neutral_~_8wekyb3d8bbwe
                   0 File(s) 0 bytes
                  83 Dir(s) 437,786,386,432 bytes free

    Notice there are four directories for Skype:


    The full path to the third directory is C:\Program Files\WindowsApps\Microsoft.SkypeApp_2.3.0.1008_x86__kzf8qxf38zg5c

    You can navigate directly to that folder in Windows Explorer even though you can’t navigate to the parent folder as shown previously.


    Figure 6. The color icons for Skype are in subfolders of the images folder.

    I decided to use skypeSmall.png in C:\Program Files\WindowsApps\Microsoft.SkypeApp_2.3.0.1008_x86__kzf8qxf38zg5c\images\targetsize-32 for creating my transparent icon.


    Figure 7. skypeSmall.png must be converted into a transparent icon.

    Although Paint can be used to save a graphics file as an icon file, it won’t have any transparency. An icon editor is needed to perform the task efficiently. I used a portable version of IcoFx because it is free and I didn’t want to install anything extra on my machine. Open the png in IcoFx to create an ico file with transparency.


    Figure 8. Use True Color + Alpha Channel (32 bits) to create a transparent icon in IcoFx.


    Figure 9. Click File and select Save As to complete the process of creating your ico file.

    After creating your icon file, use the Change Icon button on the Properties pages for your shortcut.


    Figure 10. Click the Change Icon button.

    Select your new icon and click OK and OK to update your shortcut.


    Figure 11. Select your new icon file.

    I copied the icon file to C:\ProgramData\Microsoft\Windows\Start Menu\Programs and made a custom toolbar which worked, but not quite as intended as I later discovered. What I should have done is create a copy of the C:\ProgramData\Microsoft\Windows\Start Menu\Programs folder, modify the copy, and make a custom toolbar pointing to the copy. That’s what I eventually did.


    Figure 12. Creating a custom toolbar.

    The problem with adding the new Skype shortcut to C:\ProgramData\Microsoft\Windows\Start Menu\Programs is that it will also appear under Apps in the modern UI – not what is desired. It’s a good idea not to make any changes to that folder.

    Here is what I recommend. Copy the entire C:\ProgramData\Microsoft\Windows\Start Menu\Programs folder to another location. I choose the my Documents folder to store the copy. Add the Skype shortcut to your copy. Make any further customizations to the copy. Create a custom toolbar pointing to the copy.


    Figure 13. Point your custom toolbar to a customized copy of C:\ProgramData\Microsoft\Windows\Start Menu\Programs


    Figure 14. Custom toolbar showing cascading menus. Notice that the shortcut for Skype was added.

    If you want to add your new Skype shortcut to your taskbar, go to the copy of the Programs folder and right-click the shortcut and select Pin to Taskbar.


    Figure 15. Pinning the shortcut to the taskbar.


    Figure 16. Shortcut pinned to taskbar.

  • Windows 8.1 Installation Notes Part 3

    Windows 8.1 has a very capable modern UI mail program simply called Mail. Installing Outlook 2013 does not fully replace it because one default remains with Mail, the mailto association. To change the association to Outlook, go to Control Panel, select Default Programs, and then select Set your default programs.


    Figure 1. Default Programs. Select Set your default program.


    Figure 2. Notice that not all of the defaults are associated with Outlook 2013.


    Figure 3. Notice that mailto is not associated with Outlook 2013.


    Figure 4. Select Set this program as default to make Outlook 2013 the default for all mail related actions.

  • Add SQL Server Menus to Windows 8.1 Desktop

    If you do most of your work with SQL Server, you’re going to be working primarily in the desktop experience. You can easily take the modern UI menu and expose it on the desktop as a series of Windows 7 style cascading menus.


    Figure 1. Modern UI menu for SQL Server.

    The first thing you need to know is find out where the SQL Server menu items are stored. Go to the Apps page and right click on SQL Server Management Studio.


    Figure 2. Right click on SQL Server Management Studio and then click or touch Open file location.

    Windows Explorer will open in the desktop with the selected shortcut highlighted. Go to the address bar and select the path. Copy to your paste buffer. In this example, since the installation defaults were chosen, the path is C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2012


    Figure 3. Select the path in the address bar and copy it to your paste buffer.

    The next step is to right click the taskbar and create a new toolbar.


    Figure 4. Right click the taskbar, select Toolbars, then select New toolbar.

    Paste the previously saved path into the New Toolbar – Choose a folder dialog box or you can navigate to the location.


    Figure 5. Paste the path into the Folder textbox and click Select Folder.

    You now have a new toolbar that looks like a standard Windows 7 cascading menu.


    Figure 6. SQL Server cascading menu added to the taskbar.

  • Windows 8.1 Installation Notes Part 2

    After installing Windows 8.1, there are several tricks and customizations worth considering if you develop software or want a user experience more like Windows 7. One neat feature is the popup menu shown below that appears after pressing the X key while holding down the Windows key (thank you Matthew Roche for that tip).


    Figure 1. When in desktop mode, Windows key plus the X key displays this helpful menu.

    Enabling Administrative Tools on the Apps part of the modern UI is easy to do. Bring up the charms bar and select Settings and then select Tiles.


    Figure 2. Select Settings on the charms bar.


    Figure 3. Select Tiles to see the option for displaying Administrative Tools.

    After selecting Yes under Show administrative tools, you’ll be able to see a new Administrative Tools section on the Apps part of your modern UI. If you have a particularly large screen (mine is a 2560x1600 30” monitor), you might want to also select Yes underneath Show more tiles.


    Figure 4. Tiles options.


    Figure 5. Administrative Tools appearing on Apps menu.

    Windows 8.1 has essentially two Internet Explorers, one for the modern UI and one for the desktop that works like IE in Windows 7. This creates an inconsistency in the browsing experience for people who use IE both from the modern UI and also from the desktop. By changing the settings in the desktop version of IE, you can have it appear when browsing from the modern UI. By doing this, you’ll have the same open windows regardless of where you start giving you a consistent, uniform browsing experience throughout Windows 8.1.

    From the desktop experience, open Internet Explorer. Left mouse click the Tools menu, which is the icon that looks like a gear. Select Internet options.


    Figure 6. Desktop version of IE, Tools menu.

    On the Internet Options dialog box, select the Programs tab. Under Choose how you open links, select Always in Internet Explorer on the desktop and check Open Internet Explorer tiles on the desktop.


    Figure 7. Internet Options, Program tab.

    When you go back to the modern UI, web pages will now open in the desktop experience Internet Explorer.

    I installed Windows 8.1 on a solid state drive (SSD) on my desktop. Whether you have a desktop, laptop, or tablet, SSDs provide excellent performance but tend to be lacking on capacity because of the cost. My desktop has a 512 MB SSD and multiple spinning hard drives with larger capacities. My Surface 2 tablet has a 32 GB SSD and a 64 GB micro SDXC card. I want to save the SSD for programs and offload large files (e.g., installation media, video) and Office files elsewhere. By changing properties of key folders, I can redirect the storage of files from one device to another.

    My desktop presents an interesting case. It was a Windows 7 machine with all kinds of files large and small stored on the C drive, a spinning disk. Windows 8.1 was installed on a new SSD as the C drive. On Windows 8.1, that spinning drive containing Windows 7 is mapped to drive letter X. By repointing the Documents folder from C to X on Windows 8.1, I’ll keep the SSD from getting cluttered with my files and I’ll automatically be pointing to the existing files I was using under Windows 7. If I edit the files under Windows 8.1, the latest versions will be available on Windows 7 when I choose to boot into 7 instead of 8.1.

    By default, your profile is protected from access by others. Although I have a profile named John on both Windows 7 and Windows 8.1, John on Windows 7 is protected from access by John on Windows 8.1. That’s why I used Windows Explorer to navigate to the Users folder on the hard drive (drive letter X on my machine) containing the Windows 7 installation. Then I selected the folder named John. A dialog box appeared telling me that I didn’t have permission. I clicked Continue to get access to the folder. There are other approaches I could have taken, but I took this specific approach because it provided a nice visual representation of the progress, which was quite slow, taking about half an hour.


    Figure 8. No permission to folder on the Windows 7 hard drive.


    Figure 9. Green bar showing progress in obtaining permission to access files in the selected folder.

    To move the Documents folder to a new location, go to Windows Explorer and select Documents. Select Properties. Select the Location tab. Specify the new location and click OK.


    Figure 10. Selecting the Properties dialog box for Documents.


    Figure 11. Moving Windows 8.1 Documents to a new location. Specify a new location and click OK.


    Figure 12. Click Yes to complete the move.

    If you have files in the old location that match file names in the new location, you’ll see a warning dialog.


    Figure 13. Warning dialog to prevent files from being overwritten.

    After these steps, files under the Documents folder under my profile are no longer on my SSD and instead are on the much larger hard disk where Windows 7 is installed. On my Surface 2 tablet, the secure micro SDXC card is drive letter D. A D:\Users\John\Documents folder hierarchy was created and the Documents folder was relocated to that location.

    Another handy trick is something that comes from Windows 7. You can create a folder on your desktop to access all of the settings on your machine. After creating the folder, rename it to something of this format:  anyNameYouWant.{ED7BA470-8E54-465E-825C-99712043E01C}


    Figure 14. Renaming a desktop folder to settings.{ED7BA470-8E54-465E-825C-99712043E01C}

    After renaming the folder, it will have a different icon signifying its special purpose.


    Figure 15. settings folder icon

    Double-click the settings folder on your desktop to see a list of all of the administrative applets on your computer.


    Figure 16. Windows 8.1 settings all in one convenient place.

    Windows 8.1 has a screen capture keyboard shortcut that was used to make some of the screen captures in this blog post. If you press the print screen key while holding down the Windows key, your entire screen or screens will be automatically saved into the Screenshots folder found in your Pictures folder. If you’re using a tablet, you won’t have a print screen key, so you should hold down the Windows key and press the volume down button to capture the screen into your Screenshots folder.


    Figure 17. Screenshots folder.

    This blog post was written using Windows Live Writer, which is part of Windows Essentials.


    Figure 18. Windows Essentials download page.

    I recommend only installing the Writer program because Windows 8.1 comes with newer or alternate versions of the other programs.


    Figure 19. Selecting only Windows Live Writer.


    Figure 20. Windows Live Writer has a dependency on earlier versions of the .NET Framework.

This Blog


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