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

  • 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
    ,Tempus
    ,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
    ,Tempus
    ,PreviousQuantity
    ,CurrentQuantity
    ,(CurrentQuantity - PreviousQuantity) / PreviousQuantity * 100.0 AS PercentageChange
    FROM
    (SELECT Employee
    ,Tempus
    ,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.

    CREATE TABLE #Sales
    (
         Employee NVARCHAR(30)
        ,Tempus DECIMAL
        ,Quantity DECIMAL
    );

    INSERT INTO #Sales
    (Employee, Tempus, Quantity)
    VALUES
    (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
    ,Tempus
    ,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
    ,Tempus
    ,PreviousQuantity
    ,CurrentQuantity
    ,(CurrentQuantity - PreviousQuantity) / PreviousQuantity * 100.0 AS PercentageChange
    FROM
    (SELECT Employee
    ,Tempus
    ,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.

    image

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

    Here are the properties for my modern UI Skype shortcut:

    image

    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.

    image

    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.

    image

    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.

    image

    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:

    Microsoft.SkypeApp_2.0.0.5011_neutral_split.scale-140_kzf8qxf38zg5c
    Microsoft.SkypeApp_2.3.0.1008_neutral_split.scale-140_kzf8qxf38zg5c
    Microsoft.SkypeApp_2.3.0.1008_x86__kzf8qxf38zg5c
    Microsoft.SkypeApp_2013.1112.1630.458_neutral_~_kzf8qxf38zg5c

    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.

    image

    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.

    image

    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.

    image

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

    image

    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.

    image

    Figure 10. Click the Change Icon button.

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

    image

    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.

    image

    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.

    image

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

    image

    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.

    image

    Figure 15. Pinning the shortcut to the taskbar.

    image

    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.

    image

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

    image

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

    image

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

    image

    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.

    image

    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.

    image

    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

    image

    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.

    image

    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.

    image

    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.

    image

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

    image

    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.

    image

    Figure 2. Select Settings on the charms bar.

    image

    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.

    image

    Figure 4. Tiles options.

    image

    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.

    image

    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.

    image

    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.

    image

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

    image

    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.

    image

    Figure 10. Selecting the Properties dialog box for Documents.

    image

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

    image

    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.

    image

    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}


    image

    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.

    image

    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.

    image

    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.

    image

    Figure 17. Screenshots folder.

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

    image

    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.

    image

    Figure 19. Selecting only Windows Live Writer.

    image

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

  • Target Database Breach and Customer Service

    This week’s massive security breach of Target’s database caused me to pay extra attention to my credit card purchases. I found fraudulent charges, but I don’t know if the root cause is the Target breach or something else. If your company accepts credit card payments online, there is a lesson for you in how you should treat your customers. If you accept credit cards for automatic monthly payments, what happens when a card is denied? Do you help the customer or punish the customer?

    I have been inconvenienced in two ways today. My time has been wasted. First, I had to spend time with American Express getting charges removed from my bill. Every Saturday I use Quicken to check all of my balances, which were $1,000 over my expectations. I had fraudulent charges of $499.95 from steampowered.com and $498.98 from steamgames.com, which appear to be the same company. I have no evidence good or bad about that company’s antifraud procedures and they are not the subject of this post. There were also three temporary authorizations of $1.06 each to McAfee in Plano, TX which I reported as fraud.

    The real inconvenience began after reporting the fraudulent charges to American Express. I have seven preauthorized payments linked to my American Express card. That’s where your company comes in. Credit card fraud is a fact of life. So are preauthorized automatic monthly payments made by credit card. Sooner or later, your customers are going to have to change the credit card number because of fraud. Think through this carefully. Your company isn’t in any way responsible for the credit card fraud. The customer chose to use a particular card number to pay you. If the customer ends up having to change the payment method through absolutely no fault whatsoever of your company, is it your problem? It is because whenever your customer has a problem, so do you.

    Let’s begin with my insurance company, Amica. I have three insurance policies with them, all billed to my credit card. I wasn’t able to change the payment method once. I had to change the payment method for each policy. Sure, it’s not Amica’s fault that my credit card was stolen, but they made me do extra work to fully change payment methods. Comcast accepted my change of credit card, but informed me that it would apply to the next bill and that I needed to pay the current bill (due in about 2 weeks) if I wanted a different payment method. So, I had to enter the new credit card number twice. AT&T’s bill is due about the same time, but the change I made at AT&T’s website went into effect immediately. I didn’t have to do the extra work that Comcast requires. But AT&T, although deserving of an honorable mention, isn’t the customer service winner. The clear winner is the Harris County Toll Road Authority. Although I did login to my account and change the method of payment, I didn’t actually have to. I could have done nothing and been fine, which is why they are the clear winner head and shoulders above the rest. If I am on vacation, I’d like to have the luxury of time to deal with the problem upon my return home.

    The reason I could have done nothing is because the Harris County Toll Road Authority provides a means to enter a second payment method. If I had done nothing, the next month’s charge would have been denied on the AMEX card, which would then cause the system to try the second card on file for the payment. Why is this so important? Because we’re talking about charges levied by insurance companies, utilities, and government agencies. If your card is denied and you are on vacation, in the hospital, deployed in the military fighting terrorists, or your spam filter is a little too aggressive, you might miss the notice that your payment was denied. Credit card fraud can begin a cascade of steps resulting in you paying late fees or in a worst case scenario, having your utilities cut off, your insurance terminated, or your car impounded. When I shop for an electric service provider, my current provider, Reliant, goes to the bottom of the list because they don’t offer a backup payment method. They aren’t obligated to do so, they are meeting levels of service that are the norm today and they are within their rights not to provide a backup payment mechanism. But I want to minimize the risk of my electric service being terminated after my credit card is cancelled because of fraud. Depending on the nature of the fraud, the credit card company may terminate the card on their initiative and you might not know this immediately. That’s why backup payment plans matter.

    If you manage databases for your company, good security practices can help minimize risks. If you are storing credit card data in SQL Server, you need to implement strong security procedures and policies. Use BitLocker for drive level encryption. Use Transparent Data Encryption for database level protection. Configure your SQL Server 2008 or SQL Server 2012 for FIPS 140-2 certification. Run the SQL Server Best Practice Analyzer for 2012 or 2008 R2.

    If you are in the United States and use credit cards, you can get a free credit report once a year. Visit the Federal Trade Commission website for the facts http://www.consumer.ftc.gov/articles/0155-free-credit-reports. You can get one free credit report per vendor per year and there are three vendors. I recommend getting a free report from one vendor, wait 4 months and get a free report from another vendor, wait 4 months and get a free report from the remaining vendor. By staggering your requests in this manner, you can end up with a free credit report every 4 months. It seems like a better idea than requesting from all three vendors at the same time. Married couples can alternate between spouses if credit is being reported jointly. In that case, married couples can get a free credit report every 2 months.

  • Microsoft Mathematics for Work and School

    Quite by accident, I discovered a really nice free software product from Microsoft called Microsoft Mathematics. You can download the standalone version from here. There is also a Microsoft Mathematics add-in for Microsoft Word and OneNote with the 2010 version found here and the 2013 version found here.

    image

    Figure 1. Standalone version of Microsoft Math showing the Formula and Equations dropdown menu selections.

    image

    Figure 2. Gravitational formula with Plot this equation menu option selected.

    image

    Figure 3. Graph clearly showing that the force of gravity declines with increasing distance.

    The add-in for Word and OneNote adds the Mathematics tab to the ribbon, which builds upon existing features for entering equations. The add-in provides computational and graphing features.

    image

    Figure 4. Mathematics tab selected with the mouse over the Equation menu.

    After selecting an equation, the DESIGN tab is added to the ribbon.

    image

    Figure 5. Microsoft Mathematics DESIGN tab in Word.

    image

    Figure 6. 3D graph showing how the area is a function of the square of the radius.

    The add-in provides an option to insert a graph into Word or OneNote.

    image

    Figure 7. Graph embedded into Word document.

    I found Microsoft Mathematics to be easy to use.

  • Hour Of Code

    Learn to program. It only takes an hour and it’s free. You can learn how to code at an Apple store on December 11, 2014 or the entire week at a Microsoft retail store. Learn more about this project here.

    Do you already know how to code? Teach someone who doesn’t. You can spare an hour to empower someone else.

  • Windows 8.1 Installation Notes

    If your machine doesn’t have a DVD drive, you’ll either need to do a network boot or boot from USB. Fortunately making bootable USB media from an iso file is much easier. Microsoft has a free utility shown below that does it painlessly.

    When installing Windows 8.1 or any operating system, you want to be thorough and get as much right the first time as you can. Since I wanted a clean, fresh install, I knew I’d be reinstalling all of my applications from scratch. This was a great opportunity to install an SSD. Whenever you get a new motherboard, you want the latest BIOS and whenever you get a new SSD, you want the latest firmware. That means you need to know what firmware you have. Update your firmware and BIOS before installing the operating system.

    There’s a lot of bad advice on the internet for finding the firmware version of your SSD. You can reboot your machine and watch the POST process and hopefully see the firmware version in the instant before it disappears. There’s a more relaxed way of finding the firmware version. Some people advise you to install a program that will show you the firmware version, but that’s not necessary. You should be able to get the information you need from Device Manager.

    image

    Figure 1. Open Disk drive in the Device Manager, select your SSD, right-click, select Properties.

    image

    Figure 2. Select the Details tab and then select Hardware Ids.

    image

    Figure 3. The firmware version of this SSD is 1.04

    I updated the firmware of my SSD to 1.05. I was very disappointed in Plextor’s firmware update utility. It displayed three identical messages about failing to “re-IDENTIFY”, whatever that is. I assumed that the upgrade didn’t work, so I ran the update utility again. It said I was at 1.05. Plextor should write a better utility.

    Since I have an iso file instead of media, I wanted a way to copy the iso to a USB device and boot from it. Fortunately Microsoft has a free utility for doing this here. Don’t worry about it saying Windows 7.

    image

    Figure 4. Microsoft ISO to bootable USB utility.

    image

    Figure 5. Pick your USB device.

    image

    Figure 6. Your USB device will be wiped clean.

    image

    Figure 7. Do you really have a choice if you want to do this?

    image

    Figure 8. How long this takes depends on the quality of the USB device.

    image

    Figure 9. All done!

    I did notice a significant performance difference between an old USB device and a new, high quality USB device in terms of I/O performance.

    Since Windows 8.1 is going onto a new drive, I decided to keep my Windows 7 installation. I’ll probably dual boot with a default for Windows 7. One OS will have SQL Server 2012, the other SQL Server 2014. Storage is affordable and I feel better being able to revert back to the Windows 7 system if necessary.

  • USMT for upgrade to Windows 8.1 and how it works with SQL Server

    I ordered a 512 GB SSD so that I can get great performance running Windows 8.1. I’ll have all of my spinning drives intact, so I’m not worried about actual data loss. The concern is in preserving things like IE favorites for every family member. The User State Migration Tool (USMT) has ScanState.exe to capture what you want to preserve and LoadState.exe to migrate the saved state to the new installation.

    Before forging ahead with USMT, it’s advisable to first check your PC for any potential issues. Download and run the Upgrade Assistant to find out if you have any compatibility issues. Go to this page to both obtain it and find out more about it.

    image

    Figure 1. Upgrade Assistant.

    I didn’t have any hardware or driver issues. The only issues were with programs I don’t want on the new Windows 8.1 installation anyway.

    USMT is found in the Windows Assessment and Deployment Kit (ADK) for Windows 8, which you download from here. If the installation defaults are used, you will NOT obtain the USMT as the screen capture shows.

    image

    Figure 2. ADK installation defaults. Notice that USMT is not selected.

    If you have particular applications that you are interested in migrating, you might want to also select the Application Compatibility Toolkit. I didn’t actually use it, but just wanted to make you aware of its existence.

    image

    Figure 3. ADK with USMT selected.

    Be sure to use the right executables for your machine’s architecture. Look in the appropriate folder for your processor.

    32-bit C:\Program Files (x86)\Windows Kits\8.0\Assessment and Deployment Kit\User State Migration Tool\x86
    64-bit C:\Program Files (x86)\Windows Kits\8.0\Assessment and Deployment Kit\User State Migration Tool\amd64

    I copied all of the files in the …\amd64 folder to C:\USMT on my Windows 7 machine. If you read the USMT documentation (which is advisable), it recommends that you take this approach. Open an administrative command prompt and navigate to that folder. Specify an output XML file. Here is my session:

    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

    C:\Windows\system32>cd ..\..

    C:\>cd USMT

    C:\USMT>scanstate /genmigxml:C:\USMT\Results\genMig.xml

    SCANSTATE.EXE Version 6.2.9200.16384
    (C) 2012 Microsoft Corporation. All rights reserved.

    Log messages are being sent to 'C:\USMT\scanstate.log'

    Starting the migration process

    I examined the XML file and realized that I had the following users on my machine that I’d completely forgotten about:

    MsDtsServer110
    MSSQLFDLauncher
    MSSQLSERVER
    MSSQLServerOLAPService
    SQLSERVERAGENT

    Taking the defaults when running LoadState would migrate these users. There’s no point in doing that because I’m going to be reinstalling SQL Server after installing Windows 8.1 from scratch.

    You can use the /ui switch to include only the specified users when running either ScanState or LoadState. There is also a /ue switch to exclude the specified users. Read more about the switches here. It’s a tradeoff between space and flexibility. If you take the defaults with ScanState, you grab everything. It takes more space, but you can migrate any user later. If you restrict what you capture with ScanState, you will use less space but can’t later LoadState for a user you didn’t capture with LoadState in the first place. My recommendation is to exclude the SQL Server users shown above from the ScanState. I definitely won’t be migrating them because my objective is to make a full backup of Windows 8.1 before installing any applications. I don’t want any clutter with superfluous user accounts in that initial system image.

  • Searching for database objects

    It’s easy to find what calls a stored procedure in SQL Server. Finding where it is called outside of SQL Server is an entirely different matter. I use great free utilities for either use case.

    Of course, you can use built-in features in SSMS to find out what calls a stored procedure, for example. Select an object in the Object Explorer, right-click, select View Dependencies.

    image

    Figure 1. View Dependencies in SSMS

    You can also script a solution, either in T-SQL or PowerShell. Dependencies are found in sys.sysdepends in SQL Server 2005 and above or sysdepends in SQL Server 2000. What I really like to use is the free SQL Search utility from Red Gate. It’s powerful, intuitive, works well, and free (just in case you missed that very important point).

    Things get more challenging when you need to know what items outside of SQL Server are referencing SQL Server objects. If you have .NET code, you can search all of your code using Visual Studio. But you might not have Visual Studio and even if you do, you could have a large collection of batch jobs (could be non-Microsoft) scattered around your filesystem as I experienced today.

    My favorite free tool for searching the filesystem is FileLocator Lite. I’ve been using it for many years, but didn’t blog about it when I first discovered it because it had an awful name – Agent Ransack. Try telling your software compliance people that you want some freeware named Agent Ransack – that’s just not going to happen. Fortunately, this great tool was rebranded as FileLocator Lite.

    I had to search all of the many files on a large network share for all of the stored procedures in a particular database. First I got a list of the names of all of the stored procedures.

    select name
    from sys.sysobjects
    where type = 'P';

    The list looked something like this:

    uspA
    uspB
    uspC
    uspD
    uspE
    uspF

    FileLocator Lite can search for multiple strings at once, provided they are separated by the correct Boolean operator, which is an OR in my case. It appeared to me that Boolean operators need to be in all uppercase to work properly. I used regular expressions with SSMS’s Search and Replace option to quickly edit my long list in a single pass.

    image

    Figure 2. Global search and replace using regular expressions.

    I substituted OR \n for \n and ended up with something like this which I pasted into FileLocator Lite:

    uspA
    OR uspB
    OR uspC
    OR uspD
    OR uspE
    OR uspF

    image

    Figure 3. FileLocator Lite search results.

    FileLocator Lite will search subfolders if you check the box to do so. It also supports regular expressions. What I find particularly useful is the context sensitive pane on the right where you can preview and see if the found string really is what you want. You can save your search criteria or your results to a file.

  • Accessibility, Section 508, and the Bond rule for character size

    When you are dealing with the United States federal government, accessibility isn’t a nice to have, it is the law. As I was researching the law, I came across a related item, the Bond (as in 007) rule, which was proposed by Sidney Smith in the journal Human Factors in 1977.

    The federal government has an entire website section508.gov describing the 1998 law that requires federal agencies to make their applications and websites accessible. I encourage you to go to the site and learn more. Of particular interest to application developers is the Technology Tools page. It contains a lot of great links. You’ll find references to many things things including some I’ve blogged about before – color blindness and epilepsy.

    Smith discovered that to be readable by the masses, a character needs to be at least .007 radians. That’s why some people refer to this as the Bond Rule. Using radians to specify character height provides a measurement independent of the viewing distance. To know the exact character height needed to be visible at a specific distance, multiply the viewing distance by 0.007 to find the character height in the same units as the viewing distance. If you have 20/40 vision and are reading text that is 100 cm away, the character size should be at least 0.7 cm (7 mm). 20/40 is used instead of 20/20 because 20/40 is more representative of the real world than the idealized 20/20.

  • Processing nonstructured data using FILESTREAM and FileTable

    SQL Server 2012 simplifies the processing of unstructured data found in files. The FileTable enhancements to FILESTREAM allow documents to be known to SQL Server full-text search by simply copying them to a FileTable network share. This is much more convenient than it was in SQL Server 2008 R2 where the files had to be explicitly loaded into SQL Server as blobs before they could be processed by full-text search.

    Although I’m a full-time IT consultant, I spend most of my spare time pursuing graduate studies in nursing. I’m always looking for ways to use technology to improve my efficiency as a student and also improve my effectiveness as a consultant. Some of the technical tips and tricks I’ve figured out for school have been useful to my clients in corporate environments. In this blog post, I’ve taken PowerPoint slides from my pharmacology course to evaluate the usefulness of using FileTable to process unstructured data found in the same kinds of files used in corporate environments.

    image

    Step 1. Access your SQL Server instance’s properties to enable FILESTREAM.

    The first checkbox is to enable FILESTREAM in SQL Server. The second checkbox is to allow Windows to read and write FILESTREAM data – in other words, make your files accessible to Windows. The third checkbox is to allow remote users to access your FILESTREAM data. This is the option for real world usage. Typically a SQL Server is on a remote machine, not locally installed.

    image

    Figure 2. Check everything if you want maximum functionality.

    Changing FILESTREAM settings requires a restart of the SQL Server service.

    You must create a FILESTREAM enabled database or add FILESTREAM to an existing database. For this blog post, I chose to create a new database.

    CREATE DATABASE School
    ON
    PRIMARY (NAME = School, FILENAME = 'd:\school\dbFiles\school.mdf')
    ,FILEGROUP FileStreamGroup
     CONTAINS FILESTREAM(NAME = schoolFilestream, FILENAME = 'd:\school\schoolFilestream')
     LOG ON (NAME = SchoolLog, FILENAME = 'd:\school\dbFiles\school.ldf');
    GO

    The folder for the data and log files must exist. The subfolder for the FILESTREAM must not exist and will be created when the database is created.

    image

    Figure 3. Folder structure showing that D:\school\dbFiles exists and D:\school\schoolFilestream does not exist.

    After creating the database with the above T-SQL statement, the D:\school\schoolFilestream folder is created as shown below. As the dialog box shows, it is considered a system folder by Windows.

    image

    Figure 4. Click Continue to access the new folder and its contents.

    image

    Figure 5. The new D:\school\schoolFilestream folder and its contents.

     

    image

    Figure 6. FILESTREAM Directory Name (which must NOT be a full path) and Non-Transacted Access options.

    The options specified created a folder fileContainer under a new mssqlserver share as shown below.

    image

    Figure 7. Network showing the fileContainer FILESTREAM directory name under the default share name of mssqlserver.

    At this point the fileContainer folder is empty. Time to create a FileTable. Notice that the CREATE TABLE statement does not specify any columns. The default is to create a folder with the same name as the FileTable, but the example shows how to use FILETABLE_DIRECTORY to override the default and use pharmFiles for the folder name. Notice that the WITH clause is also used to create a unique constraint.

    CREATE TABLE pharm AS FileTable
    WITH (FILETABLE_DIRECTORY = N'pharmFiles'
             ,FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = ui_file_stream);

     

    image

    Figure 8. The CREATE TABLE statement created a new folder pharmFiles.

    Within SSMS, you can see that a FileTable has predefined columns.

    image

    Figure 9. FileTable columns are predefined.

    Now it is time to copy all of the pharmacology PowerPoint files to the pharmFiles folder on the network share.

    image

    Figure 10. PowerPoint files added to the previously empty pharmFiles folder.

    Let’s examine the contents of the pharm FileTable after the file copy.

    image

    Figure 11. Notice that SQL Server recognizes all of the files that were copied to the pharmFiles folder on the mssqlserver share.

    Notice that the files are of type pptx. SQL Server 2012 by default does not index pptx files. Use the following query to find out what file types are indexed.

    SELECT * FROM sys.fulltext_document_types
    ORDER BY document_type;

    It is necessary to add an IFilter, which you will probably find is more than a one step process. As of the date of publication, the latest and greatest IFilter pack is the Microsoft Office 2010 Filter Pack (a.k.a. Filter Pack 2.0). It’s possible that when you read this, a newer version may be available, so do your due diligence and check. After installing the Filter Pack, you may need to install a service pack. There’s more than one service pack that has been released for Filter Pack 2.0. I will not provide any links because I don’t want to provide you with outdated information. Microsoft recommends that you obtain service packs by running Windows Update, which is what I did. As a best practice, after installing something from Windows Update, you should check for additional updates that could be required after what you just finished installing. I had three consecutive updates to do. You could have more or less than that.

    The Office Filter Pack does not have a dependency on Microsoft Office. You don’t need to have Office installed on your SQL Server machine for the Filter Pack to work. It’s not a good idea to have Office installed on a server.

    After installing and updating the Filter Pack, run the following command to load the new filters.

    exec sp_fulltext_service 'load_os_resources', 1;

    Update the system metadata about what filters are installed.

    exec sp_fulltext_service 'update_languages';

    Restart the filter daemon.

    exec sp_fulltext_service 'restart_all_fdhosts’;

    Confirm that the new filters were installed.

    SELECT * FROM sys.fulltext_document_types
    ORDER BY document_type;

    Create a fulltext catalog and a fulltext index.

    CREATE FULLTEXT CATALOG pharmCat AS DEFAULT;

    CREATE FULLTEXT INDEX ON pharm (file_stream TYPE COLUMN file_type) KEY INDEX ui_file_stream;

    Here’s a query to find out what files in the pharm folder contain the drug name Vancomycin.

    SELECT *
    FROM dbo.pharm
    WHERE CONTAINS (file_stream, 'Vancomycin');

    A single row is returned because only one file, Anti-infective.pptx mentions that drug. A future post will address using FileTable to make sense of unstructured data.

  • [OT] Project Tuva

    While researching an upcoming blog post on using SQL Server 2012 FileTable, I stumbled across Project Tuva. Bill Gates bought the rights to some of Nobel Laureate Richard Feynman’s physics lectures and made them available for free at http://research.microsoft.com/apps/tools/tuva/.

    Speaking of recorded lectures, I highly recommend that students listen to podcasts of lectures during their commutes. The main reason I made it through pharmacology and obstetrics was because I listened to the podcasts of my lectures several times during my normal commuting. The repetition really reinforced the topics. From repetition came familiarity with the material and from the familiarity came understanding.

  • Why ICD codes matter to data professionals

    The International Classifications of Diseases (ICD) is a global standard administered and copyrighted by the World Health Organization (WHO). The 10th revision, ICD-10 is the current revision. Some countries adapt the WHO standard. In the United States, the National Center for Health Statistics (NCHS) has made two modifications to ICD-10 known as ICD-10-CM (Coordination and Maintenance) and ICD-10-PCS (Procedure Coding System). ICD-10 was originally scheduled to replace ICD-9 as the U.S. standard on October 1, 2013, but the implementation date was changed to October 1, 2014.

    Systems must be modified to accommodate this new standard. Schemas must be changed, mappings between old and new must take place. Much work is yet to be done, which is why implementation was delayed by a full year.

    The granularity and specificity of ICD-10 codes has been ridiculed by the press. It is helpful to understand ICD-10 instead of laugh at it if you want to win ICD conversion business. Code W22.02XA is the code for “walked into a lamppost, initial encounter” and code W22.02XD is the code for “walked into a lamppost, subsequent encounter”. In clinical parlance, an initial encounter is the first time the patient seeks treatment. Followup visits about the same condition are known as subsequent encounters. The term “subsequent encounter” has nothing to do with how many times a patient has walked into a lamppost, which some people have misinterpreted.

    The number of codes increased by an order of magnitude when going from ICD-9 to ICD-10. Data professionals should be able to understand and appreciate the intention behind having more specificity to diagnosis codes. If only W22 (striking against a stationary object) was used, the data would be too vague to help with prevention efforts. Just for sake of discussion, let’s consider what if the data showed that most people who walk into something actually walk into lampposts instead of walls (W22.01XA) and furniture (W22.03XA). In this contrived example, it might be in the public interest to see if it would be cost effective to develop a lamppost injury prevention program (just kidding trying to make a point about data mining).

    Fine grained data lends itself to analysis. Federal payment programs (Medicaid and Medicare) and private insurance companies are already scrutinizing diagnosis codes and withholding payments in some cases. If a patient without an infection is admitted to a hospital and contracts an infection while being treated in the hospital (this is called a nosocomial infection), payment for treating the infection is likely to be denied. This gives hospitals an incentive to be more diligent in preventing infections.

    Having more detail gives hospitals and practitioners an improved ability to indicate the extra complexity of a case and get reimbursed at a higher rate. This could give providers an incentive to convert to ICD-10 perhaps even ahead of the October 1, 2014 deadline. More codes means more data, which can lead to projects to expand and optimize systems to accommodate the impending data explosion.

This Blog

Syndication

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