Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Let someone know how much you appreciate them and why

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

The Best Way to Protect Sensitive Data

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

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.

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 and other strategies need to be a part of any system I need to manage.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents

PostgreSQL DML Statements

Shivayan Mukherjee from SQLServerCentral

DML, or Data Manipulation Language, statements are used to manipulate the data present in a database. The most important DML statements are INSERT, UPDATE & DELETE. This tutorial covers the various PostgreSQL DML statements and how we can use them with SQL shell as well as pgAdmin. Assuming you are familiar with table creation in PostgreSQL, […]

What is a Relational Database Management System

Additional Articles from MSSQLTips.com

This article covers the relational database system evolution and covers terms used in relational models such as relation, tuple, attribute, domain, schemas, and key.

From the SQL Server Central Blogs - Game changer settings # MAXDOP and Cost threshold for parallelism

Brahmanand Shukla from SQL Server Carpenter

I got a performance tuning assignment for an esteemed customer in the Financial Services domain. I went through the wait stats of SQL Server using the built-in SQL Server...

From the SQL Server Central Blogs - Experiments With Go Arrays and Slices

Sheldon Hull from Sheldon Hull

Simplicity Over Syntactic Sugar
As I’ve been learning Go, I’ve grown to learn that many decisions to simplify the language have removed many features that provide more succinct expressions in...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Masking with BCP

I have a normal user that wants to export some product data. When this user queries the table in SSMS, they see masked data, such as this: 
10000023      BaskeXXXXXX     460      0.8027
10000024      InnerXXXXXX     199      0.3597
10000025      XXXXXX          390      0.8232
10000026      TableXXXXXX     2238     0.4413
This user wants to bcp out using this code: 
bcp "ProductDetails" out productexport.txt -S SQL01 -c
When they get the export, what will the file look like?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Merging Dictionaries

I am using Python 3.7 and I have this code:

a = {'DEN' : 'Denver Broncos', 'KC': 'Kansas City Chiefs'}
b = {'LV' : 'Las Vegas Raiders', 'LAC': 'Los Angeles Chargers'}

I want to merge these two dictionaries together. Which code does this?

Answer: AFC_West = { **a, **b}

Explanation: You can use a dictionary syntax, but use the ** with each variable to merge these. Ref: EP 448 Review - https://mail.python.org/pipermail/python-dev/2015-February/138564.html

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Development
Creating and joining multiple queries (more than 2) - Hi, I know how to create and join 2 queries but on occasion Ive needed to join more than 2 and Im unsure how to achieve this? The method I use for joining to is as per the example below - can you just extend this as required? Select * from ( SELECT Stuff.Example1 Stuff.Example2 […]
SQL Server 2016 - Administration
How to upgrade to 2016 AlwaysON from SQL Server 2012 ?. - Hello, I'm looking for the best way to go from existing 2012 Enterprise AlwaysON Cluster to SQL 2016 Enterprise AlwaysON . Existing setup: 2 nodes with OS 2012R2 and SQL Server 2012 Enterprise on latest service pack. Goal for new setup: Purchased 2 new servers that I want to be the final 2 new servers. […]
Help with Actual execution Plan - Please advise on the attached execution plan. If they open as XML then please rename them as .sqlplan. I did save them as .sqlplan for now. You might need winzip to extract since I was unable to attach direct files. Thank you!  
SQL Server 2016 - Development and T-SQL
Query Code returning incorrect results - Hello Community, I have the following logic: Find customers who didn't place orders from 2019-02-01 to 2019-03-01. Output customer's first name. I am working with the following dataset: CREATE TABLE #tmpTable ( id int, first_name varchar(50), last_name varchar(50), city varchar(50), address varchar(50), phone_number varchar(50), id int, cust_id int, order_date date, order_quantity int, order_details varchar(50), order_cost […]
ORDER BY using successful thousand comma separator - This query requires ActualTotal to be ordered with largest value top however as you can see from the screengrab, this hasn't worked completely. Is there a way to achieve this? Here is my current code: SELECT m.name, FORMAT(x.ComTotal,'N0') AS Total_2020, FORMAT(y.Totals,'N0') AS Some_2020, FORMAT(x.ComToTal + y.Totals,'N0') AS ActualTotal FROM Mees m LEFT JOIN View_2020_Listx_Jan2020 x […]
Odd query question i did some basic digging but didn't find anything relevant. - SQL Server 2016 cursor and query: select * from table1 where col1 = '1' and col2 = '00001' and col3='I' and col4 = 'JULY--2015' this is a dynamically created query and for the most part the col1, col2 and col3 stay the same only the col4 changes in this test case. while all the other […]
SQL query for First swipe in and Last Swipe Out for each day for each pay period - Hi, "EMPLOYEE" table has EmpName,EMPID . "SWIPE" table has SwipeTime_UTC,Swipe_location,EmpID  . Note:Swipein and swipeout locations are different. SwipeTime_UTC is UTC time format. Looking for SQL query to generate  the first swipe in and last swipe out for each employee on each day for given pay period.(Display time should be in EST). Any suggestions pls. Thanks.
Administration - SQL Server 2014
Slow database performance - Hi All, We are seeing serious I/O contentions issues on one of our database called "MarketingDB". Looking for some suggestions on how we can improve I/O demands for this database. Enabled Instant file initialization for data files to get some performance gain. All data files are in one drive and log file is in a […]
Development - SQL Server 2014
Need to insert differences between Staging and Working tables into the Working T - Hi, Still kind of a newbie to SQL Server. Anyway I have two tables: tblStaging and tblWorking. The staging table is updated every 15 minutes and I need to look at what's different in the Staging table and insert the differences into the Working table. I have a combination of two fields that guarantee uniqueness. […]
SQL 2012 - General
How to get duplicate on PartId and Company Id both and have different Status ? - How to get duplicate on Part Id and Company Id both and have different Status ? I work on SQL server 2012 I have issue I can't get duplicate on company id and part id and on same time have different status as partid 1211 and companyid 3030 repeated two times meaning two rows but […]
There is insufficient system memory in resource pool - Not a DBA here. Now that that is out of the way... I am troubleshooting an issue where our SQL service is stopping. We are on SQL 2012 Express x64 11.0.2100.60 running on Windows Server 2008 R2 Standard x64. The Windows application event logs show: -Event ID 701: There is insufficient system memory in resource […]
SQL Server 2012 - T-SQL
Last Column value at the maximum value of Cond Column - Good morning,   I'm trying to get the Temp value at the maximum value of the Cond column. The red line in the first query is what I'm expecting, the second one its the result I'm getting at the moment.   Current query: SELECT TOP (1) DateTime, CASE WHEN TagName = 'WFI_LP01_Cond.PV' THEN value END […]
SQL Server 2019 - Development
CONCAT and CASE WHEN combined - Hi everyone, I'm trying to create a query that concatenates the directions field that were involved in each transaction_id. I've tried to do it by creating a flag table with multiple case whens, and then querying on it, but as you can see in the image below, I'm having trouble to get a multiple 1's […]
Reporting Services
Execute Stored Procedure (with no Fields) - Hi Guys, I'm searching for a while now, but can't find a solution. We have a ssrs project where we are using Reports for Dashboarding. To do Right Management, we have a function for each table, these functions has a so called "Token" wich represents a specific user. As these Tokens have a timeout, there […]
General
How often have you needed to start a sequence of numbers at other than 1 or 0? - Itzik Ben-Gan (definitely one very smart guy in the world of SQL Server and T-SQL) published an interesting challenge back on the 9th of December, 2020.  You can find that challenge at the following link... https://sqlperformance.com/2020/12/t-sql-queries/number-series-challenge To summarize... Itzik posted the basis of his world-famous "GetNums" function, which generates a sequence of numbers.  The version […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -