Voice of the DBA

Category: Blog

  • Loading a Text File from T-SQL

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    One of the interesting things I’ve had to work on with the Advent of Code puzzles is loading files into SQL Server. Some of the inputs are large strings, but many are files with lines of code that need to be loaded into SQL Server.

    I thought this might be a nice, simple SQLNewBlogger post. Do you know how to load a text file? Certainly the Import/Export wizard can work, but can you quickly load a file from T-SQL itself?

    If you can’t, go work that out. If you get stuck, come back or search for help.

    Loading a Text File

    Obviously you need a place to load the file. I created a table for each puzzle, and here is the table for Day 2.

    create table Day2_WrappingPresents
    ( dimensions varchar(12)
    )
    go

    Now ordering doesn’t matter for this puzzle, so I have a very simple table. If ordering mattered, I’d have to do this differently.

    To load this file, I’ll use the BULK INSERT command. This takes a table as a target, and optionally has a number of parameters.  Since this is a simple load of a simple file with one column of data to a table with one column of data, I can use the defaults.

    bulk insert Day2_WrappingPresents
    from ‘C:\Users\Steve\Documents\GitHub\AdventofCode\Day 2 – Wrapping\input.txt’

    In this case, the insert will load all 1000 rows into the table. A simple query shows this works:

     

    Now I can get on with the rest of my puzzle solution.

    SQLNewBlogger

    This is a great example of a simple thing that we might not need to do often, but we may need to do at times. Knowing how to do this, a simple operation, showcases that you are improving your SQL Server skills. This post took me about 5 minutes to write.

  • Laptop Build Quality

    I’ve been looking around at various laptops, in preparation for getting a new one. I wrote about considering the Surface Book, which is still on the list, but has dropped a bit. The hardware quality is great, but when I was in the UK last week, a few people had them and complained about some driver bugs. In particular, I was messing with one person’s touch keyboard, and they warned me not to pull if off.  If I did, the machine might crash.

    Ugh. At $2k and lots of hype, I wouldn’t expect any issues like that.

    In any case, this post is about build quality, not software.

    I was laying in bed this week, working on some editorials when my daughter came in. She wanted me to look over a piece she was writing for school and handed me her laptop. She has a Macbook Air, and as soon as I put my Toshiba z30 down, I was impressed with the Air’s build. It’s solid, it’s light, but it feels strong. I remember loving my Macbook Air, and holding it as I reviewed her work, I was reminded of that.

    My z30 flexes, to the point that across a year, my touch point is unusable with the twisting of the frame. The trackpad was also far, far superior on the Air. I thought the Macbook Pro was like that, so I swung by a Best Buy to check. I walked in and went to the Apple section, picking up a Macbook and it feel solid. It’s just a better device than my Toshiba.

    However I was curious about others. I did walk over and look at a Surface Book. It’s a solid machine, about the size and weight of the MBP. However it has the touch screen, which is interesting. The trackpad works differently, but it’s a nice machine. Detaching the screen, it’s a tablet, which is nice. I still don’t know how much I’d use the tablet factor, but it’s tempting. However the weight distribution is strange. The screen is heavier than the keyboard, the opposite of most laptops.

    I also walked over to look at a Yoga 900, which I was curious about after reading Tim Mitchell’s review. I’m actually anxious to see how Tim’s machine looks next month in NM, but for now I contened myself with the display model. The hinge is neat, but this is a light laptop. At first glance, it also was solid. The flex I have on my Toshiba was not there. Despite a few reviewers noting this felt plastic and cheap, I didn’t get that feeling. It’s no Macbook, but it’s better than my Toshiba.

    This will be an interesting decision for me, but since I’m going to wait for Apple’s announcement in March and see what they might do. I doubt they’ll go touch screen, but you never know. I have gotten used to touching my screen for some reading, and I think I might miss that with a MBP.

  • Creating a Database Snapshot

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.

    However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick "create database xx" syntax I often use, with a snapshot I need to be more specific.

    The big item that I must specify is the location of the snapshot file. This is the file that gets written to with the copy-on-write process that ensures the original state of the pages when the snapshot is created are still available.

    You do need to give the database a logical name as well, which can be anything, but the reference below has suggestions. I’d say that this is bad:

    CREATE DATABASE sandbox_snapshot
    ON (NAME = MySnap
    , FILENAME = ‘h:\SQL Server\MySnap.ss’
    )
    AS SNAPSHOT OF Sandbox

    But this is better

    CREATE DATABASE sandbox_snapshot_20150122_1345
    ON (NAME = SandBox_Snap_20150122
    , FILENAME = ‘h:\SQL Server\MySnap.ss’
    )
    AS SNAPSHOT OF Sandbox

    Because the snapshot is based on an existing database, at a particular point in time, it’s useful to specify the time when the snapshot was created, if possible. While you can get this from metadata, if you have people that look to multiple snapshots for information, it can be handy to know when each is from.

    However if you are providing this on a schedule, like daily, for people to report from, you might need to have the same name every day. Think about this, as you cannot rename a snapshot once it’s created.

    SQLNewBlogger

    When I realized I had to lookup the syntax, I took a few notes and captured code, which meant I combined this writing (< 10 minutes) with other work I was doing.

    You should do the same. When you tackle something new, take screenshots, save code, and drop it in a OneNote/EverNote/etc notebook for your weekly blog writing.

    Reference

    The references I used:

  • Push Button Deployments

    I worked in an Agile team in 2000 and we deployed changes to our web platform every Wednesday for over 18 months. That was about the length of time I was involved before the small startup failed, but not for lack of technology. In fact, I managed a few weeks extra pay by helping sell and deploy our IP to another company.

    Deploying regularly and often, to meet business needs and get features into the hands of customers is important. For most businesses, this is something the CTO and CEO want. It can work, and there’s a case study with Nmbrs, a Redgate customer, that has used DLM with this quote:

    “The productivity of the team has improved dramatically. We were doing one database update a week, which took almost a full day. Now it takes ten minutes. We can spend the extra time we’ve gained developing new features.”

    That’s been my experience with tooling and automation. If you get things setup, as I did with my startup, then you gain confidence in deployments, are willing to make them more often, and get these small chunks of software to customers on a regular basis. It’s not just customers, as we found we could make some fundamental changes in databases and applications that helped smooth development later without affecting customers.

    I know I’m paid by Redgate to talk about DLM, but the concept, the idea, the processes really do work well. They’ll help you get features to customers quicker (and feedback to developers faster) as well as give you confidence that you can release, if you find an issue with your application.