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 Analytic Functions

    Last week I demonstrated SQL Server analytic functions at Houston TechFest. Below you will find an expanded set of code samples from my original content. As I explained in the presentation, I also used content from Itzik Ben-Gan’s excellent book from Microsoft Press, which I highly recommend. I also recommend Itzik’s columns at http://sqlmag.com. Joe Celko has written some very informative articles on analytic functions at https://www.simple-talk.com/.

    Run the code samples on a machine running SQL Server 2012 or higher. The code is also available as a file attachment.

    -- create a table that has a column for ordering the data
    CREATE TABLE #numbers (
         nbr    INT
        ,tempus DATE  -- used for ordering the data
    );

    -- insert some sample data
    INSERT INTO #numbers
    (tempus,nbr)
    VALUES
    ('1/1/2018',1)
    ,('2/2/2018',2)
    ,('3/3/2018',3)
    ,('4/4/2018',4)
    ,('5/5/2018',5)
    ,('6/6/2018',6)
    ,('7/7/2018',7)
    ,('8/8/2018',8)
    ,('9/9/2018',9)
    ;

    -- run an ordinary query ordering by the tempus columns
    SELECT nbr
    FROM #numbers
    ORDER BY tempus;

    -- show the nbr value in the current row and in the previous row
    -- the first row retrieved has a NULL for the previous nbr

    SELECT nbr
          ,LAG(nbr, 1) OVER (ORDER BY tempus) AS prevNbr

    FROM #numbers
    ORDER BY tempus;

    -- return the nbr value in the following row
    -- the last row retrieved has a NULL for the following nbr

    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr

    FROM #numbers
    ORDER BY tempus;

    -- return the first value
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr

    FROM #numbers
    ORDER BY tempus;

    -- return the last value
    -- notice how it is really the last value so far

    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- modify code to always return the last value
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- this returns the same results as the previous query
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- notice that the syntax for lastNbr is not what is needed for firstNbr
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    -- fix the previous query to always show the very first value
    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    SELECT nbr
          ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
          ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
          ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS firstNbr
          ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr

    FROM #numbers
    ORDER BY tempus;

    DROP TABLE #numbers;

  • AdventureWorks2014 installation script

    The AdventureWorks2014 sample database is downloadable from here. If you choose to run the script and are unfamiliar with sqlcmd, this post shows you what to do. Be sure to extract the zip file’s contents to a new folder because the instawdb.sql comes with 72 csv files that contain the actual data. If you specify a folder other than the default, you’ll have to change a path in the instawdb.sql script to point to your directory.

    image

    Figure 1. instawdb.sql script file in SSMS. Notice the 3 lines that begin with a colon.

    It’s always prudent to read comments and examine script files before executing them. Notice that you are instructed to change the paths if your actual locations are different from what is in the script. Also notice that FULL TEXT SEARCH should be enabled. The most important instruction in the comments is that the script must be run in SQLCMD Mode, which is available in SSMS but not enabled by default.

    image

    Figure 2. Enable SQLCMD Mode from the Query menu.

    Once SQLCMD is enabled, the SQLCMD statements are highlighted.

    image

    Figure 3. SQLCMD enabled and SQLCMD statements highlighted.

    It took about a minute and a half for the script to execute on my machine, so be patient. You might want to turn off SQLCMD after you’re done installing AdventureWorks2014 to restore SSMS to the state you’re more familiar with.

  • Surface Pro 3 first impressions

    I traded in my Surface 2 (the trade-in program is now over) and bought a Surface Pro 3 with an i7 processor and 8 GB of ram. I greatly prefer the 3 by 2 aspect ratio of the Surface 3. After only one day of ownership, I’ve decided to purchase a docking station. I have a 7 year old desktop with a quad core Q6600 processor overclocked to 3.0 GHz and 8 GB of ram. It has a Plextor 512 MB SSD as the primary drive. It’s a very capable machine, but it does have a little bit, and I do mean only a little bit, of fan noise. I’d like to eliminate even that small amount of noise.

    My subjective initial impression was that my new Surface Pro 3 was significantly outperforming my desktop. Time to use PerformanceTest to get the facts. The Surface Pro 3 outperforms my desktop significantly on every measure except one of the 2D graphics tests. The desktop was slightly better drawing 2D vectors at 18.7k vectors/sec compared to the Surface’s 15.7k vectors/sec.

    PerfRes

    Figure 1. PerformanceTest 8.0 test results.

    I have 6 TB of storage on my desktop. The desktop could be moved to the closet where my router and switch are. With the docking station, the Surface can use my gigabit network to access the files on the desktop. My desktop cannot run the latest version of Hyper-V, which is most inconvenient. Installing Visual Studio 2013 installs Hyper-V. As a reader points out below, this alters the behavior of how Windows runs on the device.

    What I’ve found is that I don’t want to go back to non-touch enabled devices. I use both touch and a Bluetooth mouse with my Surface Pro 3, which is what I also did with my previous Surface 2. Having said that, I use the mouse with SSMS. I’m a little afraid of using touch when doing things to a database. If you’re going to use the Surface Pro 3 for development or doing demos of SQL Server, I recommend that you get 8 GB of ram. Both i7 models have 8 GB of ram. Only the higher version of the i5 Surface that has 256 MB of storage comes with 8 GB of ram.

    UPDATE: After taking the Surface to work today, I concluded that I want a lunch bag with a padded slot to hold the Surface. That way I can carry everything in one convenient bag. To the entrepreneur who creates such a lunch bag, send me one and we’ll call it even, okay?

  • Unsupported way to secure unsupported XP

    The free software tool nLite can be used to customize an XP installation. It isn’t supported by Microsoft, but neither is XP. It is possible to remove Internet Explorer or networking components from an installation. Sometimes there is no need for the XP machine to ever connect to a network. This is particularly true when XP is used to host software used to run a piece of specialized industrial or medical equipment that requires vendor software that runs only on XP. Or maybe your application needs to connect to a network printer but not surf the web.

    I have used nLite and was pleased with the results. When I used it to customize XP builds for restricted use, I would remove the games. This reduces the temptation for users to use the XP machine for anything other than its intended use.

    I recommend that people move off of XP. But I do understand that isn’t feasible for everybody. For those who continue to use XP, nLite might provide a means to reduce the attack surface area of an XP installation. Using an unsupported tool to remove components of an operating system of course introduces new risks of possibly removing something that is needed. Test thoroughly and retest.

  • Free Azure HDInsight ebook from Microsoft

    Microsoft has released a free ebook on Azure HDInsight. Full details are available here. HDInsight is Microsoft’s implementation of Hadoop. Azure HDInsight is Hadoop in the cloud. The ebook gives you a quick overview of what Big Data is and what you can do with it.

  • Windows 8.1 Boot into Desktop Mode

    If you primarily work in desktop mode, you can easily reconfigure Windows 8 to start in desktop mode when booting up. Once in desktop mode, go to the Taskbar, right-click and then select Properties to change the Navigation on your machine.

    image

    Figure 1. Right-click on the Taskbar select Properties.

    On the Navigation tab, check When I sign in or close all apps on a screen, go to the desktop instead of Start to enable booting into desktop mode.

    image

    Figure 2. Check Show my desktop background on Start, Show the Apps view automatically when I go to Start, and List desktop apps first in the Apps view when it’s sorted by Category.

    There are other things you might want to enable. If you check Show the Apps view automatically when I go to Start, you’ll see your applications instead of the tiled view when you switch back to the Modern UI. Since I prefer the category view, I also checked List desktop apps first in the Apps view when it’s sorted by Category. Notice that the desktop background appears because Show my desktop background on Start is enabled.

    image

    Figure 3. The Modern UI with Show my desktop background on Start, Show the Apps view automatically when I go to Start, and List desktop apps first in the Apps view when it’s sorted by Category all checked.

  • Windows Not Sleeping All Night

    Having a computer wake up when you don’t want it to wastes electricity and drains the battery on mobile devices. My desktop had been waking up at night, so I assumed it was some network traffic on my home network. I unchecked Allow this device to wake the computer on my network adapters.

    image

    Figure 1. Network adapter Power Management tab.

    That didn’t solve the problem. I included the screen capture in Figure 1 because it could be part of the solution for someone else.

    To identify the root cause instead of guessing, the System log was examined. As you can see, pressing the sleep button was putting the machine to sleep.

    image

    Figure 2. Timestamp of when the computer was put to sleep.

    To figure out what was waking the machine up, the System log was checked to review all events following the sleep event.

    image

    Figure 3. System log showing what woke the computer up.

    This is what requested the computer to wake up:

    Wake Source: Timer - Windows will execute 'NT TASK\Microsoft\Windows\TaskScheduler\Regular Maintenance' scheduled task that requested waking the computer.

    A Bing search provided more information: http://support.microsoft.com/kb/2799178

    To change this behavior, go to the Action Center and click on Change maintenance settings.

    image

    Figure 4. Automatic Maintenance section in the Action Center.

    There are two options for modifying the Automatic Maintenance schedule. You can change the time or disable the ability for automatic maintenance to wake your device.

    image

    Figure 5. Options for controlling maintenance events waking the computer.

  • Web-based data generator

    One of my coworkers told me about Mockaroo, a web-based data generator. I needed some test data for upcoming blog posts, so I decided to give it a try. It’s pretty good. I had to use Firefox because of problems running Mockaroo on Internet Explorer 11. Using the defaults except for changing the format to SQL, it generated output that looked something like the following. Mockaroo is so good that it generates fake data that could accidentally be real, such as email addresses. Consequently, I edited the values shown below so that they are hopefully actually fake.

    create table MOCK_DATA (
        id INT,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(50),
        country VARCHAR(50),
        ip_address VARCHAR(20)
    );
    insert into MOCK_DATA (id, first_name, last_name, email, country, ip_address) values (1, 'John', 'Deaux', 'jdeaux@fakeDomain.fakeDomain', 'Made', '255.255.255.255');

    It has lots of options, but some edits to the generated output may be necessary for Unicode compatibility in SQL Server. Look at the following output (which my always helpful friend Greg Low says is politically correct):

    create table TEST_DATA (
        chinese VARCHAR(50)
    );
    insert into TEST_DATA (chinese) values ('空调);

    That’s not what is needed for SQL Server to properly handle Unicode characters. The preceding example needs to be changed to the following:

    create table TEST_DATA (
        chinese NVARCHAR(50)
    );
    insert into TEST_DATA (chinese) values (N’空调);

    Mockaroo is extensible because it can use regular expressions to generate data.

  • TechEd 2014 Day 4

    Many people visiting the SQL Server booth wanted to know how to improve performance. With so much attention being given to COLUMNSTORE and in-memory tables and stored procedures, it is easy to overlook how important tempdb is to performance. Speeding up tempdb I/O improves performance. The best way to do this is to not do the I/O in the first place. With SQL Server 2014, tempdb page management is smarter. Pages are more likely to be released before being unnecessarily flushed to disk. Read more about it here.

    Of course, not all tempdb I/O can be eliminated. Performance can be improved by moving database files to flash memory storage. To maximize the performance benefits of flash storage, buffer pool extensions should be enabled.

  • TechEd 2014 Day 3

    There is some confusion about durability of data stored in SQL Server in-memory tables, so some review of the concepts is appropriate. The in-memory option is enabled at the database level. Enabling it at the database level only gives you the option to specify the in-memory feature on a table by table basis. No existing tables or new tables will by default become in-memory tables when you enable the feature at the database level.

    If you choose to make a table an in-memory table, by default it is durable with changes being recorded in the transaction log. You do not have to worry about data loss. However, you have an additional option of making an in-memory table not durable. If you actively choose to do this, you will have data loss if, for example, the server crashes. There are legitimate use cases of choosing to override the default behavior and create in-memory tables that are not durable. If you need staging tables for ETL, non-durable in-memory tables will provide high performance. If you need temporary tables for a particular processing need, non-durable in-memory tables can outperform tables in tempdb.

  • TechEd 2014 Day 2

    Today people asked me about backing up older versions of SQL Server to Azure. Older versions back to SQL Server 2005 can be easily backed up to Azure Storage by installing Microsoft SQL Server Backup to Windows Azure Tool. It installs a service of the same name that applies rules to SQL Server backups. You can tell the tool to backup or encrypt your SQL Server backups. You can have it automatically upload your backups to Azure Storage. Even if you don’t want to upload your backups to Azure, you might want to use the tool just because it can compress or encrypt your backups. Download the tool from here.

    At the Ask the Experts dinner, I heard a joke about DBAs. A group of lions is called a pride or a sault, a group of crows a murder or a parcel, a group of cats a clutter or a nuisance, a group of bison a herd or an obstinancy. What is a group of DBAs called? An obstinancy.

  • TechEd 2014 Day 1

    Today at TechEd 2014, many people had questions about the in-memory database features in SQL Server 2014. A common question is how an in-memory database is different from having a database on a SQL Server with an amount of ram far greater than the size of the database. In-memory or memory optimized tables have different data structures and are accessed differently using a latch free and lock free approach that greatly improves performance. This provides part of the performance improvement.

    The rest of the performance improvement comes from natively compiled stored procedures that can only access memory optimized tables. Conventional stored procedures can access either conventional or memory optimized tables. While it is true that conventional stored procedures are compiled, they do not compile all of the way down to native machine code. Natively compiled stored procedures are faster than conventional stored procedures.

    For obtaining an in-depth understanding of in-memory database features, I recommend that you read the excellent whitepaper written by my friend Kalen Delaney which can be downloaded from Microsoft here.

  • TechEd 2014 Day 0

    Microsoft’s TechEd 2014 conference opened today. It doesn’t fully open until tomorrow, but was open for doing hands on labs and other side activities. The labs provide you with a convenient, guided tour of new features. To make the best use of your time, I recommend reading the instructions carefully and thoughtfully. Try to understand why and what you are doing instead of just following the instructions. I did three SQL Server 2014 labs today and found them to provide a good introduction to new features. Working with new features provides a level of understanding that you can’t obtain from just reading about them.

    Not at TechEd? No problem. You can find virtual labs online here. SQL Server 2014 labs from TechEd will be added to the online library labs sometime after TechEd. Running the labs on your own machine requires that you install an application for which local administrator rights and a reboot are required.

  • Links and resources for understanding windowing functions

    Today at SQL Saturday in Houston I gave a presentation on SQL Server 2012/2014 windowing functions. The focus was on analytic functions. I used several different resources that I want to share with you.

    First, purchasing Itzik Ben Gan’s excellent book on windowing functions is a must. I used some of his examples because they are clear and useful. You should be able to refactor his queries to solve real world problems.

    Red Gate’s Simple Talk website has several good articles on windowing functions. I used this one. It’s worth a read and has good sample code.

    Itzik has some very good articles on windowing functions you can find on the SQL Server Pro website, such as this one. He wrote a quite advanced article about a problem I encountered as a graduate nursing student at the Veterans Administration Hospital. The problem is calculating when to reorder drugs for patients. He came up with an elegant solution he wrote about here.

  • Changes to Azure SQL service tiers and pricing

    Today Microsoft announced changes to Azure SQL Database service tiers. I’ll simplify it for you: You’ll get larger databases for less money and a better uptime SLA. The Web and Business editions of Azure SQL Database are going away with retirement in 12 months. New service tiers of Basic and Standard are now available.

    Full details on the new tiers are available here and pricing details are here.

More Posts Next page »

This Blog

Syndication

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