Voice of the DBA

Category: Blog

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

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

  • Blackout

    It’s anti-SOPA/PIPA day, and a number of sites have shut down for the day. O’Reilly closed their page, and of course, Wikipedia shut down.

    oeillysopa

    Google didn’t do the blackout I was hoping for, but they have hidden their logo:

    blackout

    I like the Wikipedia closure, which appeared seconds after hitting a page. It has a great effect on me.

    wikipedia

    I debated about shutting down this site, but for a WordPress hosted site, I didn’t see an easy way to do it.

    To be clear

    I am not against copyright legislation, nor IP protections. We should ensure that content creators have some recourse and ability to control the way their content is used.

    I think SOPA / PIPA are gross, overreaching ways of doing this that are designed to help a few large companies, and potentially hurt many small ones, and will have no impact on foreign sites. These laws simple cut the US off; they do not affect the operation of the foreign site.

    We can come up with better ways to protect content, while also preserving fair use and personal liberties, and limiting copyright.

    My personal stance is that the original 14 years + 14 year extension for copyright is plenty. If you cannot earn money in those 28 years, let someone else build on your work.

  • SQLServerCentral Webinar #15

    Once again, I’m the moderator for a webinar coming up at the end of the month, on Jan 31, 2012. It starts at 11am EST, and features Brad McGehee (blog | @BradMcGehee and Grant Fritchey (blog | @gfritchey)talking about DBA administrative tasks.

    Come register and save the date if you want a little basic DBA admin knowledge from some experts. It should be an informative hour for you, and this webinar is sponsored by  Red Gate Software.

    I like being the MC rather than the presenter because talking into my computer, in the basement, drives me slightly crazy.