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

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

     

    image

    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:

    Management
         Policy Management
              Policies
                   System Policies
                        SmartAdminSystemHealthPolicy
                        SmartAdminUserActionsHealthPolicy
              Conditions
                   System Conditions
                        SmartAdminSystemHealthCondition
                        SmartAdminUserActionsHealthCondition
              Facets
                   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:\

    ...snip...

    @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"

    C:\Windows\system32>

     

    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"

    C:\Windows\system32>

     

    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 http://services.odata.org/AdventureWorksV3/AdventureWorks.svc

    image

    Figure 1. AdventureWorks subset published at OData.org

    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.

    image

    Figure 2. Selecting From OData Feed.

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

    image

    Figure 3. Specifying an OData source.

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

    image

    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.

    image

    Figure 5. Query Editor

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

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

    Figure 5. Editing the imported table.

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

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

    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.

    image

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

  • 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 who has done a great job detecting fraud. 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. 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 their fault that my credit card was stolen, but they made me do extra work to fully change payment methods. My internet provider 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 the cable provider 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 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.

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

This Blog

Syndication

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