C# Corner is holding a virtual SQL Server conference on Jan 29-30, 2021. This is a charitable event, aimed at raising money for children affected by COVID-19.
I’ll be speaking on databases and DevOps, so if you want to kick off some learning, join me for a session or ten at this event.
You can register, and donate to help raise money for children.
I was listening to someone talk about data privacy recently, and the ways that you can protect the sensitive information in your databases. They had a great quote about something you might consider. They said, “The best way to protect data is not hang onto the raw data at all.”
If we don’t have sensitive data, then a loss of data can’t occur. Hacks won’t cause issues, we can’t accidentally send out data or leave it lying around. There’s a good case to be made that keeping less sensitive data around is a good idea.
For some applications, we can’t avoid keeping sensitive data. Medical databases keep private health information. E-commerce systems likely need financial information. Many of us will definitely have to deal with some sensitive data, and protect it, but we can minimize our struggles.
We often don’t have a good reason for keeping lots of data around. Lots of queries run by users end up looking at only a small portion of data. Often recent data is needed, and some aggregates for older data, but we don’t actually look at the details of old data often. We may even have older data around that we’ve forgotten about, and our users don’t even know is available.
We certainly don’t often need sensitive data in non-production environments. Plenty of people use scripts or tooling to obfuscate, anonymize, generate, or otherwise ensure sensitive data isn’t in unprotected environments. We can archive, or even change, old data to ensure it isn’t a liability. We can even do this in production, preserving metrics, but delinking data from any individual.
I’ve always been someone that kept more data than necessary, just in case I needed it. However, over time, I find that the costs, and the potential risks, just aren’t worth it. Moving forward, archival, anonymization, and other strategies need to be a part of any system I manage.
Steve Jones
Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.
I started to add a daily coping tip to the SQLServerCentral 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.
Today’s tip is to let someone know how much you appreciate them and why.
This is probably easiest with family, but I wanted to step outside my comfort zone here. I decided to take a minute and chat with someone that has taught me a lot and enriched my life the last few years.
I coached my kids when they were young, but I stopped at some point. As my daughter became a competitive volleyball player, I enjoyed watching and supporting her. It brought back memories of all the times I’d enjoyed the game as a young man. At some point she become more independent, and I was less involved in her activities. I decided to start coaching at a competitive level.
The director of the club where she spent her last 4 years is someone whose company I’ve enjoyed over the years with lots of conversations and shared memories. I spent a year apprenticing with him and then assisted a few others before getting my own team.
In the time I’ve know him, he’s taught me a lot about a game that changed from my youth. He helped me learn how to coach, with all the tricks to push, inspire, motivate, comfort, and more, outside of teaching skills. He’s challenged my thinking and helped me to grow as a more well rounded person that has to work with and influence others.
I’ve really appreciated my time with him, and hopefully will get more as the world returns to a more normal pace in the future.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Recently I ran across a question posted by a beginner on the Internet and thought this would be a good, basic topic to cover. The question was: how can I replace a value in a comma separated string in a table?
This post covers the basics of this task.
Suppose you have some strings in a table, and they contain multiple values. I see this often when application developers serialize some data. For example, I might create a table like this:
CREATE TABLE mytable ( mykey INT NOT NULL CONSTRAINT mytablepk PRIMARY KEY , myval VARCHAR(100)); GO INSERT dbo.mytable (mykey, myval) VALUES (1, 'apple,pear,banana') , (2, 'pear,peach,melon'); GO SELECT * FROM dbo.mytable AS m;
This has a few rows of multiple values in a field.
Imagine now I need to change pear to grape in all rows. I want a simple solution to do this.
I have seen some people try to use complex substring calls paired with other functions to do this, but T-SQL gives you a really simple solution. We have a REPLACE() function that allows us to change a string without parsing it.
The simple way to do this is like this:
SELECT m.mykey , m.myval , REPLACE(m.myval, 'pear', 'grape') AS newstring FROM dbo.mytable AS m;
Always run a SELECT before an UPDATE, but in this case, I can see that pear has been removed and grape is in its place.
REPLACE() works by passing in a string as the first parameter, then a second string to search for, pear in this case, and finally a replacement. I could then put together an UPDATE statement to change my table.
UPDATE dbo.mytable SET myval = REPLACE(myval, 'pear', 'grape') FROM dbo.mytable AS m;
If I run this, the results shown above for newstring will replace the myval string for all rows.
This is an example of a basic type of T-SQL solution that is simple, with a quick explanation. I answered this for someone and then spent 10 minutes writing this up.
A good story to have ready for an interview.
Voice of the DBA 