Voice of the DBA

Tag: SQLNewBlogger

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

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

  • Disabling SQL Server Network Protocols

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

    I ran across a question on network protocols recently, which is something I rarely deal with. Often the default setup for SQL Server is fine, but there are certainly times you should add or remove network connectivity according to your environment.

    Here’s a short post on turning off (or on) a network protocol for SQL Sever.

    What’s Enabled?

    The easiest way to verify what’s enabled is to use the SQL Server Configuration Manager. You’ll need administrative permissions on the host to run this, but it’s easy to find.

    2016-01-13 14_59_40-Start

    Once you open it, typically you’ll have a list of the items that can be configured.

    2016-01-13 15_02_09-Photos

    We want the SQL Server Network Configuration, which is the server level configuration for this host. The Client configurations are for the host being used a client to connect to a SQL Server.

    2016-01-13 15_02_31-Photos

    As you can see here, I have Shared Memory and TCP/IP enabled for this instance, but Named Pipes disabled.

    Disabling a Protocol

    As you might expect, this is easy. I right click on a protocol, and I can change the status. In this case, I’ll disable Shared Memory

    2016-01-13 15_03_50-Photos

    Once I do that, the protocol is disabled. However not on the instance. I’ll get this message.

    2016-01-13 15_04_56-Photos

    I need to restart the server. Once that’s done, no one will be able to use Shared Memory on the host.

    I can fix this

    2016-01-13 15_04_49-Photos

    Of course, I need to restart my instance again.

    Checking the Log

    When SQL Server starts, quite a bit of configuration information is written into the log. This is useful for troubleshooting in many cases. One of the things you’ll find is the network listeners, as shown here.

    2016-01-13 15_08_14-Log File Viewer - JollyGreenGiant_SQL2016

    This is usually after the database startup information, so if you look, you can see I have some local pipes and some TCP/IP settings here.

    SQLNewBlogger

    After reading a question, this was less than 10 minutes to write, with making screenshots. However I’ve done this before. If this was your first time, then it might take you longer to research and write, but I bet most DBAs could do this in 30-45 minutes.

  • Multiple CTEs – #SQLNewBlogger

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

    One of the techniques that I find very handy in solving SQL problems is the CTE. This is much easier to read, for me, than embedding code into a derived table. For example, which of these is easier to decode, or perhaps more importantly, debug?

    WITH calendarquarters (qtr, yr, dt)
    AS
    ( SELECT qty = ‘Quarter ‘ + CAST(c.QtrNum AS VARCHAR(3))
           , yr = ‘Our year ‘ + CAST(YEAR(c.CalDate) AS VARCHAR(4))
           , c.CalDate
       FROM dbo.Calendar AS c
    )
    SELECT *
    FROM calendarquarters cq
    INNER JOIN dbo.CustomerSales AS s
      ON cq.dt = s.LastSale
    Or this:

    SELECT *
    FROM (  SELECT qty = ‘Quarter ‘ + CAST(c.QtrNum AS VARCHAR(3))
           , yr = ‘Our year ‘ + CAST(YEAR(c.CalDate) AS VARCHAR(4))
           , dt = c.CalDate
       FROM dbo.Calendar AS c
    ) cq
    INNER JOIN dbo.CustomerSales AS s
      ON cq.dt = s.LastSale

    I’d argue the first becomes easier, especially when I have multiple tables in the join. In this way I can more easily see in the first example I’m joining two tables/views/CTEs together. If I want to know more about the details of one of those items, I can easily look up and see the CTE at the beginning.

    However when I want multiple CTEs, how does this work?

    That’s pretty easy. It’s actually just listing each CTE, separated by commas. In my case, I wanted to do this:

    with upcte

    as

    (select uplen = len(replace(floorstring,’)’,’’)

    from Day1

    )

    , downcte

    as

    (select downlen = len(replace(floorstring,’(’,’’)

    from Day1

    )

    select uplen – downlen

    from upcte, downcte

    That’s it. I use the WITH once, and then each CTE stands along. I could add the column names if I wanted, but here I can easily see I’m querying two numbers from two “tables”, CTEs in this case, and performing subtraction. If I want the details, I get that from the previous definitions.

    SQLNewBlogger

    A quick post as I used this technique in solving Day 1 of the Advent of Code. This took about 5 minutes to write, and I got to add my own twist to the concept.

    References

    A quick one from my first Google result. While I knew how to do this, I double checked myself with a search.

    Multiple CTE in One Select Statement Query – http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/