Voice of the DBA

Author: way0utwest

  • Adding a Computed Column–#SQLNewBlogger

    Recently I needed to add a computed column to a table and realized that I didn’t remember the syntax. This short post show how to do this.

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

    Adding the Column

    I had a table, OrderHeader, and wanted to add a new column, OrderedByDate. I can do this with a simple ALTER statement and an ADD. The inserting part is the computation. You use as AS clause with the computation instead of a datatype. For example, my code was:

    ALTER TABLE dbo.OrderHeader 
   ADD OrderedBy AS OrderDate;
GO

    This added a copy of my OrderDate column with a new name. This is useful for zero downtime deployments in some cases, and in this case, I wanted to just have a copy. However, if I wanted some calculation, I could easily do that by specifying this as I would in a SELECT statement. For example, if I wanted the computed column to be a week later, I could do this:

    ALTER TABLE dbo.OrderHeader 
   ADD OrderedBy AS DATEADDD(DAY, 7, OrderDate);
GO

    This would add a week to the original value and return that. I can likewise do any sort of string or numeric manipulation I want. A common one is adding or multiplying two columns together for a new value. For example, adding various charges for a total in an order.

    When you do this as a computed column that is not persisted, no space is taken in the actual table rows. If you persist this, then space is used.

    More information on Microsoft Learn.

    SQL New Blogger

    I realized that I needed to double check the syntax in the docs, and when I did, I took this as an opportunity to write a short blog post. I grabbed a link, wrote some code, and then spent 10 minues knocking out this post.

    If some employer does this a lot, they might search your blog to see if you can do this. A nice few posts on how to do this, what persisted does, how you index this, etc. Take a few minutes and start blogging on topics like this throughout your week.

  • Stale Data Causes Security Issues

    Security has become better and better in many organizations. At the same time, hackers and malicious actors are doing a better and better job of finding new ways to attack systems. Some work to target specific individuals, often because of government or industrial espionage. Most of us aren’t likely to deal with those issues, unless we work with (or are) someone that is very important in a particular situation.

    Instead, many of us deal with wider spread attacks that look to exploit vulnerabilities in technology or humans at scale, hoping to find the weak links. Lots of people I know have dealt with viruses in the past that shut down systems, and more recently, had to rebuild systems crippled by ransomware.  Despite their best efforts, this often means lots of extra unexpected work, combined with the stress of falling behind on our commitments. We have plenty of other work to do.

    Windows has been vulnerable throughout its history, and it appears, lately through a data problem. There is a class of attacks that look to use approved, though old and unpatched, drivers as a vehicle for gaining a foothold inside a network. This was a problem (called the BYOVD issue) and Microsoft addressed this with a block list that was used to prevent the loading of vulnerable drivers. They updated this list through Windows Update.

    Except they didn’t. In database terms, we had an eventually consistent set of data, which was being updated at Microsoft, but not being sent to client workstations. There were over 3 years of the list not being updated, despite assurances from Microsoft that Windows 10 PCs were protected. There are instructions for manually updating your machine.

    I don’t envy this being a process I’d want to build. Getting data from security researchers or elsewhere, putting it in a database (I hope), then exporting this into a text format and getting that loaded into the Windows Update process, all while trying to ensure the process is secure along the way. That can’t be easy inside a large company like Microsoft. At the same time, not noticing this wasn’t working isn’t excusable. Likely there were issues, but my guess is someone didn’t want to admit a failure and get a bad annual review.

    Data sync issues are nothing new, and many of us struggle with these on a weekly basis. However, these are important issues. Replication can fill log files (and disks), broken ETL processes can cause execs to make poor decisions, and in the security space, not updating drivers and block lists leave us vulnerable.

    This situation isn’t excusable and Microsoft ought to be ashamed. Some of these execs ought to lose bonuses, at the very least. It’s also not excusable in our orgs. We ought to be sure we’re patching on a regular basis and minimizing the attack surface area we present. It’s the least we can do as IT professionals.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Daily Coping 1 Mar 2023

    Today’s coping tip is to call a friend to catch up and really listen to them

    Actually a friend pinged me to ask about a call recently. I made some time and set up a call.

    I’m glad I did. A friend is having changes in life and wanted to talk. I feel similarly, and in this case, we had some similar challenges that we could talk about, support each other, while enjoying catching up.

    Worth the 15 minutes of my life to do this.

    I started to add a daily coping tip to the SQL Server Central newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

  • Daily Coping 28 Feb 2023

    Today’s coping tip is to thank three people you feel grateful to and tell them why.

    This is something I tend to do privately, thanking people who’ve impacted my life. I’ll do that, but in general, here are the people I want to thank.

    Friend 1 is someone I’ve known for many years in Denver. We get together periodically for lunch or dinner, but I’m always happy to have an ear to vent about life, kids, marriage, work, etc.

    Friend 2 is someone I’ve known from my work and community experience who has been a friend and support system in many ways over the years. We’ve had the chance to share some tough times and help each other, which I appreciate.

    Friend 3 is a friend at work who’s been someone that has supported and helped me grow myself as an advocate.

    I started to add a daily coping tip to the SQL Server Central newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.