The Basics of Dynamic Data Masking - Step 1 of the Stairway to Dynamic Data Masking (2023-11-08)

SQLServerCentral Newsletter for November 8, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Timescale Launches Dynamic PostgreSQL, the Cost-Effective Alternative to Serverless and Peak-Allocation Pay Models]( - [The Basics of Dynamic Data Masking - Step 1 of the Stairway to Dynamic Data Masking]( - [Something’s Up with Parallelism in Azure SQL DB Serverless]( - [From the SQL Server Central Blogs - Database Concerns – A Poll!]( - [From the SQL Server Central Blogs - My data architecture book now has 15 chapters available!]( - [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Question of the Day - [Changing the PK - I]( The Voice of the DBA  Does Management Care About the Database? Many of the services that businesses use are taken for granted. In an office, management expects electricity, water, and heat just work. If they don't, then often a crisis is declared and funds are spent to rectify the situation. I've been in businesses where any of those systems fail and usually, it's very disruptive. I think these days a computer network is often seen as the same type of utility service where we take it for granted, but if it doesn't work, it's a crisis. Is the database seen as a service or is it more important than that? I think many software applications are different, and often seen as critical to business success, but I sometimes see both developers and management treating the database as a utility service. They just expect it to work, view it as a filing cabinet, and want to ignore it whenever possible. I think this is one reason that developers are often searching out new platforms, either cloud of NoSQL, because they want to keep the storage aspect of data simple and not spend time or effort managing data. Or they want to experiment with something they think is easier to use. Many developers like the idea of a schema-on-read, and sometimes use that to argue against another relational database and instead choose another platform. I think this is short-sighted, however, as the app now has the technical debt of supporting multiple schemas, and many of the databases using this format then suffer from one of two constraints. Either they store duplicate data for performance reasons, essentially de-normalizing their data storage, or they require some sort of ETL to ensure the data can be queried for analytics. Often schema-on-read isn't efficient when querying across documents/keys/nodes/etc. There might be good news for many of us database professionals as [an article]( talks about the boardroom starting to think about the database. To be fair, this is a sponsored article from Redgate, but it is based on a lot of research and feedback from customers that show how management is starting to treat the database as a strategic asset and not a common utility. Many executives are starting to realize that their data is full of value if it can be analyzed, and that the performance of these systems is important. We both need well-designed databases and well-written code, and we need to ensure that database development keeps up with today's application changes. More and more of the customers I work with are finding their executives see more value in ensuring the database gets the resources to ensure it performs well, including better training for developers and better processes. It's good to see more and more companies embracing modern DevOps development in both application software and database software. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Timescale Launches Dynamic PostgreSQL, the Cost-Effective Alternative to Serverless and Peak-Allocation Pay Models]( Press Release from SQLServerCentral New solution allows developers and teams to “buy the base and rent the peak” when provisioning for variable workloads without wastage or performance tradeoffs [Stairway to Dynamic Data Masking]( [The Basics of Dynamic Data Masking - Step 1 of the Stairway to Dynamic Data Masking]( Steve Jones - SSC Editor from SQLServerCentral.com Learn the basics of how Dynamic Data Masking can be used to obfuscate data in SQL Server 2016+. [External Article]( [Something’s Up with Parallelism in Azure SQL DB Serverless]( Additional Articles from Brent Ozar Unlimited Blog Or perhaps another way to phrase that is, Microsoft is up to something with parallelism. I don’t know how long it’s been this way – could even be since the launch of Azure SQL DB Serverless – but I just now noticed it while helping a client with a slow query. [Blog Post]( From the SQL Server Central Blogs - [Database Concerns – A Poll!]( Kevin3NF from Dallas DBAs What are you most concerned about in your database platforms? Please pick from the list below, and expand on your choice in the comments if you like:  Thanks... [Blog Post]( From the SQL Server Central Blogs - [My data architecture book now has 15 chapters available!]( James Serra from James Serra's Blog Only one more chapter to go! As I have mentioned in prior blog posts, I have been writing a data architecture book, which I started last November. The title... [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Site Owners from SQLServerCentral Use the graph table features in Azure SQL that were introduced in SQL Server 2017 and further refined in SQL Server 2019. This book shows you how to create data structures to capture complex connections between items in your data. These connections will help you analyze and draw insights from connections in your data that go beyond classic relationships.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Changing the PK - I I've got this table: CREATE TABLE Invoice ( InvoiceID INT NOT NULL IDENTITY(1,1) CONSTRAINT InvoicePK PRIMARY KEY , InvoiceDate DATE , CustomerID INT); GO I want to change the data type of the PK to a character type. I run this code: BEGIN TRAN DECLARE @e INT = 0 ALTER TABLE dbo.Invoice DROP CONSTRAINT InvoicePK IF @@ERROR<> 0 SELECT @e = 1 ALTER TABLE dbo.Invoice ALTER COLUMN InvoiceID VARCHAR(20) NOT NULL IF @@ERROR<> 0 SELECT @e = 1 ALTER TABLE dbo.Invoice ADD CONSTRAINT InvoicePK PRIMARY KEY (InvoiceID) IF @@ERROR<> 0 SELECT @e = 1 IF @e = 0 COMMIT ELSE ROLLBACK Does this work? 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) The Temporary Synonym What happens when I run this code? CREATE SYNONYM TempSyn FOR tempdb.#mytable Answer: This works Explanation: This works fine. Synonyms don't check for existence of the object. Ref: CREATE SYNONYM - [ [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 - Administration [Execute AS and Impersonate]( - I am trying to write up a stored procedure to allow users to execute jobs in MSDB that are owned by another user. The job-owning user is a service account (set up as non-interactive) has elevated permissions which I do not want the business users to have themselves. The problem is that I can't seem […] SQL Server 2016 - Development and T-SQL [Find the active Group]( - Hello All, I have a requirement where I am trying to get the active group number. A groupnum can have multiple membid's and any of them can be active. I looking to find the groupnum's with only one "Active" memberid. If the memberid is active can be determined by not having any 'Cancelled' transtype. I […] SQL 2012 - General [Can I still get a SQL 2012 eval for my home lab?]( - Just for testing upgrades.  All the links are dead.  I understand it's not in support but like to test the full upgrade properly. SQL Server 2019 - Administration [Strange SQL Login failure issue.]( - I have 2 x servers.  The same 3rd party app on each.  The same config screen for SQL login details. Listener details, windows auth, domain\user, password. All details are correct & the same.  Server 1 - connection into SQL is made, all good. Server 2 - Login failed for user 'domain\user' ... Attempting […] [Moved to Development topic]( - Moved to Development topic SQL Server 2019 - Development [Importing a MySQL database]( - I've used the Import Wizard in SSMS a few times. Mostly to get data from one server to another. Occasionally making some transformations. I may be involved in a new project to migrate an old PHP app to .NET. The app uses an old version of MySQL, version 5.1.41-community. I don't know, yet, if there […] [Query to find latest full backup with all database except tempDB]( - Hi All, I am looking for query to get latest full backup with all database except tempDB. I have got query from internet and trying to make changes looks like some databases are missing from sys.databases. I wanted to do daily check of last full, Differential and Log backup. if I get query for full […] [Replacing last few characters with another value.]( - I'm hoping for a bit of assistance, I know this is probably easy but just having a hard time solving it. What I'm hoping to achieve is the string of '000-0000-0000-0000-000000000000' gets changed to the values of '000-0000-0000-0000-000000000001' '000-0000-0000-0000-000000000099' '000-0000-0000-0000-000000000888' '000-0000-0000-0000-0000000005555' Any help would be appreciated DROP TABLE IF EXISTS #test CREATE TABLE #test ([ValueID] […] [Avoiding Conversion errors]( - Hello All, Couple of questions on conversion errors. First I have to convert since my reference table stores numeric and character data. Is it bad practice to allow implicit conversions? Why does Convert to INT intermittently fail with a conversion error? I can't replicate this with my limited data but it is happening on my […] [Table scan shows too many records]( - Looking at the query plan for a rather complicated select, which draws from views, I see the very beginning of the plan shows a scan of one of the root tables, but it shows that it is returning well over six million records, from a table that contains only around 67,000. It even shows those […] [Need help with the logic]( - I have some date variables which are used in the SP but one of the main table is going away and I am trying to come up with a solution so I can get the correct date. I haven't been successful so I thought I'd ask experts. Below is the original code declare @asofdate datetime […] Reporting Services [Subtotals in a filtered matrix]( - I've been trying to get this to work for months, so I come pleading for help. I have the following matrix. The column and row I will reference are artistically shown below. There is a field which outputs either a 1 or a 0 based on whether it is a "supplementary event" or not. For […] Integration Services [convert to date from datetime]( - I created the following variable to return the date 25 months ago - all fine. DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())) I am having difficulty casting to date datatype, when I add DT_DBDATE (DT_DBDATE) DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())) it throws an error - expression cannot be evaluated. with the message of: […] SQL Server 2022 - Administration [time for stats to get updated when auto update statistics async = true]( - hi, I'm using the following database version of MS SQL SERVER Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) I was experimenting with the Auto Update Statistics Async = TRUE in my test database and noticed that the time it took for my table column stats to get updated took anywhere from immediate to […] SQL Server 2022 - Development [Trigger to track changes]( - I need to track changes when a column is updated on two tables. Is there a way to do it. please send code for that.   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -