Voice of the DBA

Category: Blog

  • Advice for 2023 PASS Data Community Summit Attendees

    Edwin Sarmiento has a great First Timer Guide for the PASS Data Community Summit, which is updated for 2023. He’s been compiling this since 2016, and it’s worth reading.

    I (along with Grant and Ryan) got asked to record some video tips, which we did. I had fun one afternoon while handling some ranch chores. Hopefully you smile when you watch mine in this playlist.

    I decided to put a few things down in a post, to help those getting ready, based on my past experiences. Seattle has changed a lot, but it can be a fun place to visit. I’ve added a few sections below for:

    Travel

    I assume most of you can make your own travel arrangements, and Edwin has good advice in his PDF. I’ll just say that traffic in the area can be really, really bad. The train isn’t too bad, but from SEA –> downtown, it can be slow. Quite a few stops. It’s cheap, and the time is consistent. It lets you out below the Nordstrom’s, a few blocks from the convention center, but in the middle of lots of hotels.

    An Uber/Lyft works well, but can be pricey. I’ve seen surge pricing more than double the cost. I think I’ve paid $25 and I’ve had $70, so I would suggest that you try to share rides with others. Lots of people coming into town, so reach out to friends, colleagues, anyone on socials with your arrival time and maybe go in a group.

    Plus, the ride is a good time to network.

    DO NOT RENT A CAR. Parking it cra-say-zy expensive at Seattle hotels. Plus, this is a walkable city. You’ll be able to walk easily to the Space Needle or waterfront if you want.

    Seattle could have mild weather or it could be cold and rainy. It’s November, so plan for rain and 40F/8C. It it’s nicer, then great.

    Food

    I am not a big foodie, so take this with a grain of salt. Quite a few places have closed with the pandemic, but there are still some places I’ve enjoyed.

    Hotel food is pricey. If you’re on a budget or per diem, watch out. The Cheesecake Factory is generic American chain, but it’s good and it’s across the street from the convention center. It’s been a place I’ve sometimes had a quick meal, and they don’t seem to get too crazy busy.

    If you want to spend too much money on Whiskey, the Hyatt has a whiskey bar. It is $$$$, but friends who like whiskey like it.

    Purple Cafe is cool. A wine bar with some interesting food, but it gets busy. Best bet is go off hours or pray for a bar seat.

    Lowell’s in the Pike Market is nice for a diner type place and I’ve had a nice meal with a view. There are some nice little coffee/bakery places in the market. I also had a great Filipino breakfast at Ludi’s. I know a lot of people enjoy Top Pot Doughnuts or Biscuit Bitch.

    Keep in mind that there will be a lot of people downtown between Ignite and the Summit, so go early to places or don’t be in a hurry. Or go with just 1-2 people. Groups that are bigger get harder to seat.

    Conference Tips

    Power will be hard to come by, so charge when you can, carry a battery, and watch your cell usage. You might go airplane mode during the day and use wifi for notifications from work.

    The Community Zone is a great place where many speakers, SQL Saturday organizers (and other events), and others (esp Microsoft people) hang out. It’s worth stopping by there periodically and meeting people. Listen in on conversations, introduce yourself and say hi. Take pictures, and learn to get more involved in the data community (and #sqlfamily). Even if you’re introverted, it’s a good place to practice your networking.

    The expo hall is good. The vendors certainly want to sell you services or software, they give away prizes and swag. That’s a part of conferences, so enjoy it. Take something home to a coworker or family member, ask questions of vendors. They might have a solution that helps you, but many of them are tech people as well and might suggest a solution. I’ll be at the Redgate booth at random times, and I certainly am happy to solve problems not related to Redgate tools.

    Take advantage of Microsoft people at the event. Either in sessions or in the expo hall. Ask about issues, complain about problems, learn from what others ask. You never know when you’ll learn something.

    If a session is interesting, or you have a question, ask. If you’re shy, hang out after and ask the speaker at the end by the stage. Or listen to what others ask. I’ve often just lurked near the speaker of a good session to see what others ask and if there is something I haven’t thought of.

    Take care of yourself. These are long days. Drink water, don’t over eat, and don’t over drink. As Edwin notes, RND (RSVP for stuff, network, and drink in moderation). Enjoy the week, and expect long days.

    Don’t carry too much stuff. If you don’t need a laptop, get by with paper and pencil. I think it helps you remember more, and I don’t need to charge a notebook. This year most stuff is in the main convention center, but still, it’s a lot of walking.

    Tourist Things

    The Space Needle is an icon. If you haven’t been in that area, it’s neat. The Music Experience Project and Sci Fi Museum are there as well, and I’ve been multiple times. In fact, I was at the MEP in July with my family and loved it.

    The Underground Tour is neat. I’ve done these in a few cities, but Seattle is one of the best. The late night one is the best (I’ve done the early and late ones).

    I took my kids to the Museum of Flight, but I loved it as well. There is a great Children’s museum as well. The Boeing factory tour in Everett is far, but I loved it. I did go pre-pandemic and saw 5 or 6 747s in one building. That was incredible.

    The Pike Market is an icon. On the weekends, if you’re there, lots of people are selling wares in small market shops. Watching them throw fish is cool. At least for 5 minutes. The waterfront has a few nice museums and it’s a nice place to hang out. The original Starbucks is there. Lots of people line up to go in and take pictures, but I wouldn’t stand in line. Nothing amazing about it.

    There is a Game Night on Wednesday night, but there are some Game bars in Seattle. It’s a vibrant, albeit expensive, town, so take advantage of that. Make friends, go out and enjoy yourself.

  • Running a Command Line SQL Compare Comparison

    Recently a customer was looking to automate some of their SQL Compare checks, but they wanted to do this in a dynamic way, since they needed to do this at scale. Their idea was to not have a SQL Compare project, but build a mapping at the command line.

    This post addresses a part of their issue. This will look at how to set up a basic SQL Compare command line.

    This is a part of a series of posts on SQL Compare on my blog. You can read other posts I’ve written by clicking the link.

    Adding Parameters to SQL Compare.

    To build up a comparison from the CLI, let’s start with adding parameters slowly. First, let’s just connect. To do that, I’ll run SQLCompare.exe with a couple parameters. We need server and database parameters.

    The command line I’ll use will connect to my local machine (Aristotle) and then two databases: dlm_1_dev and dlm_2_integration. The code I’ll run is:

    sqlcompare /Server1:"Aristotle" /db1:dlm_1_dev /Server2:"Aristotle" /db2:dlm_2_integration

    This uses the /Server1 and /Server2 parameters, which can be shortcutted to s1 and s2. The structure is a colon and the value. For the database, we can use database1 or db1, and database2 or db2. I’ve shown both the long and short items above.

    When I run this, I see results, which scroll off the screen for these two items:

    2023-10-19 13_39_43-cmd

    If I look at a couple named instances, then I need to add in the instance name inside the quotes with a \ that will designate the appropriate instance. This is my command for named instances:

    sqlcompare /Server1:"Aristotle\SQL2022" /db1:compare1 /Server2:"Aristotle\SQL2017" /db2:compare2

    As you can see below, this gives me a few differences, with the indicators on the right about in which databases the changes are located.

    2023-10-19 14_23_15-cmd

    This post shows the basics of working with the command line. I’ll look at more advanced options in future posts.

    SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an evaluation today.

  • T-SQL Tuesday #168 – Mature Window Functions

    tsqltuesdayIt’s time for T-SQL Tuesday and I’m hosting this month. I usually do one a year, just because I can and being responsible for a month keeps me engaged in the party.

    This month my invitation is on Window functions and is described below.

    If you’d like to host T-SQL Tuesday, let me know. I have lots of openings in 2024 and I’m looking for someone with a blog, some creativity, and an idea for a technical topic that you’d like to see other people write about.

    Mature Window Functions

    We’ve had window functions in SQL Server for a decade now, since SQL Server 2012.

    This month I’m asking you to write on how window functions have made your life easier. A few ideas for you:

    • What problems have you solved with a window function? Bonus points for lead/lag/first_value/last_value
    • Have you used the SQL Server 2022 enhancements in any queries?
    • How has performance improved for you with a window function
    • Draw a picture of a window with a spatial function – more extra points

    Give us some specifics, with real world problems. Obfuscate the data, at least if you have my name in your dev system, but help others understand how they might solve a complex aggregate using a Window function. The more specific examples, the more others might get help from one of the posts.

    The Rules

    Not many rules, but a few of them.

    • Post between 00:00:00 and 23:59:59 on 2023-11-14
    • Include the logo above in your post
    • Link that logo to this post
    • Leave me a trackback or comment on this post (double check if you have this automated)
    • Post you URL on Twitter, LinkedIn, etc. with the hashtag #tsql2sday
    • Have fun

  • Creating a Self Referencing FK in a CREATE Statement–#SQLNewBlogger

    I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.

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

    Creating the FK

    The last post showed how to create the FK, but this works within a table as well. Let’s say I want to have an Employee table that links back one employee to another, who is their manager. That type of structure looks like this:

    CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
)
) ON [PRIMARY]
GO

    I can add a link that makes MgrID a FK reference by altering the code like this:

    CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
),
CONSTRAINT FK_MgrID_EmpID FOREIGN KEY (MgrID) REFERENCES dbo.Employee (EmpID)
) 
GO

    Easy.

    SQL New Blogger

    This is a post that took me less than 10 minutes to write. I changed the code from the previous post and wrote this right after the other one. The search and replace was the longest code part, and then the writing was quick, 5 minutes.

    This is a core skill for a DBA or developer. Write your own post to show how and why to build a self referencing FK for some scenario that you work with in your job, or in a project.