Voice of the DBA

Author: way0utwest

  • Insert multiple rows from one INSERT statement

    One of the things that has been annoying for a long time in SQL Server is adding lots of data for testing. Personally I use Data Generator, and I recommend that, but for a quick few rows of data, do you want to do this:

    CREATE TABLE SalesOrders
( OrderID INT IDENTITY(1,1)
, OrderDate DATETIME
, CustomerID INT
, OrderAmount NUMERIC(10, 4)
)
GO
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

    There’s a lot of INSERT typing, even with copy/paste functionality and editing the various rows gets to be cumbersome of you actually get some sample data like this that you want to convert (say from someone’s blog post of results:

    1, 12/1/2011, 3, 123

    2, 12/2/2011, 4, 2

    3, 12/1/2011, 3, 123

    You could do the UNION thing, like this:

    INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
UNION ALL
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
UNION ALL
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
UNION ALL
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
UNION ALL
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

    That works, but it’s still a little cumbersome.

    In SQL Server 2008, there’s a better way. You can now include multiple sets of data in your insert, like this:

    INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
VALUES 
(           '1982-05-19 06:31:48.950', 1,           579040.5070),
(           '1994-11-27 17:14:41.790', 2,           348808.5860),
(           '1972-11-08 17:40:01.170', 3,           758992.3650),
(           '1972-05-31 01:19:05.530', 4,           779853.1990),
(           '1994-12-22 10:40:57.410', 5,           666173.8040)

    Just put brackets around each set of data, and you can easily insert multiple rows.

  • Jiggly Code

     

    jiggly, jiggly, jiggly

    This discussion is a little funny, an exchange on a Linux board in which Linus Torvalds writes: “This kind of “I broke things, so now I will jiggle things randomly until they unbreak” is not acceptable.”

    I’d hope that most people would not attempt to write code in this method, but I’ve seen many developers do just that. They start trying other solutions until one works, often implementing the solution without understanding how it works. The fact that it works, even in a limited way, is often good enough for most people.

    I like that the people in the discussion do not get too distracted and instead dive further into understanding the problem. Not enough people dig into the root cause analysis of issues to fully understand the issues. I know time is tight, and I’ve often been too busy to spend much time uncovering every issue. I also think that often subsequent issues, or chronic problems, are often the result of the lack of understanding of the root causes.

    The software we write gets more and more complex. We incorporate new technologies like virtualization, and some of us will even work with distributed applications that might make use of calls to other systems through messaging or even with communications with cloud databases. Learning to trace the data flows, troubleshoot communications,and thoroughly understand the mechanics of how our applications interact becomes more and more important. Especially as our applications seem to reach wider and wider client bases, with high expectations that things “just work”.

    Many people in our SQL Server community preach a continuous improvement of one’s skills and the goal of writing better and better code over time. It takes some effort, but we all ought to be putting forth that effort to improve regularly, at whatever rate we can sustain in our lives. As long as it’s some effort to get better each month.

    Steve Jones

    The Voice of the DBA Podcasts

  • Better Than Batman

    I’ve always been a fan of Bill Gates. He changed the way we look at personal computing and provided an environment in which software development was widely encouraged. He didn’t create a perfect environment, and Microsoft has made a few mis-steps at times, but they certainly changed the world. Mr. Gates is also a great speaker, and I had the chance to see him live twice, where he delivered a fascinating keynote that entertained and inspired me.

    He left Microsoft as a full time employee in 2008, and has moved on to working with the Gates Foundation. His missions there, along with his wife, Melinda, is to improve people’s lives. The foundation has primarily focused on ending disease and hunger in developing countries, and to date has done some very good work for millions around the globe. I ran across an infographic recently that talks about philanthropy and have reprinted a portion here:

    The Gates Foundation has made an impact on the world.

    The title of the graphic is “Bill Gates is Better Than Batman” and it shows a impact of the charitable donations that Bill Gates, and many other billionaires, are making or pledging. It’s mind-boggling to look at the sums involved, and at the amounts that will be available in the future. There are a number of wealthy individuals that are trying to make a difference in the world, and it’s inspiring. Especially after fiascos like Enron, Worldcom, and other scandals where it seems greed has been the goal.

    As I get older, I view the world differently. I find myself with more understanding, more compassion, and more of a desire to help others. I suspect as many of these billionaires start to understand their own mortality, they feel the same way. It should be easy to give away money when you have more than you’ll ever spend, but it doesn’t always seem to be something people do. I am proud that so many rich tech people are looking beyond themselves, trying to make the world a better place.

    One more person I applaud is Alexis Ohanian, founder of Reddit, fellow UVA alum, and founder of Breadpig. He’s not a billionaire, but he’s trying to make a difference. He gave a great talk at the 2011 Business of Software conference, asking for more of us to do the same, even if we’re not billionaires

    Steve Jones

    The Voice of the DBA Podcasts

    .

     

  • RECONFIGURE can flush the procedure cache

    I ran across this KB article the other day, which lists a few ways in which performance is affected by various maintenance or administrative type operations.

    In KB article 917818, it notes that some operations cause a performance issue. Some of the operations make sense (offline/online, restores, etc), but there were a few that surprised me. For example, did you know that Autoclose flushes the cache? Might not be a big deal, but it also might mean that your apps based on Express might end up running slowly each time the user accesses the databases.

    There are also a number of items which are implemented by a RECONFIGURE that will flush the cache. These are listed in the KB and are:

    • cross db ownership chaining
    • index create memory (KB)
    • remote query timeout (s)
    • user options
    • max text repl size (B)
    • cost threshold for parallelism
    • max degree of parallelism
    • min memory per query (KB)
    • query wait (s)
    • min server memory (MB)
    • max server memory (MB)
    • query governor cost limit

    Also, changing a filegroup to read-only will flush the cache.

    This is by design, and I wouldn’t expect it to change anytime soon. Since these options can affect query plans, it might make sense to flush the cache, but if you don’t agree, file a CONNECT item and stump for votes.