SQL Server 2016 - Always Encrypted (2021-05-21)

SQLServerCentral Newsletter for May 21, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Server 2016 - Always Encrypted]( - [SQL Server plan cache mining – Plan attributes]( - [Creating Idempotent DDL Scripts for Database Migrations]( - [From the SQL Server Central Blogs - Encryption in SQL Server #1 – Column Level Encryption]( - [From the SQL Server Central Blogs - Career: Talk To References]( Question of the Day - [A PowerShell Alias]( The Voice of the DBA  Daily Coping Tip Watch the sunset and reflect on the world 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. Should You Learn R or Python? I've been working on various skills over the last few years, trying to solve some simple problems in Python and PowerShell, in addition to T-SQL, to continue to improve my skills. It's an interesting challenge at times, especially when I need to use new features or functions to which I haven't had exposure in the past. I also work with R lightly, as I need to build some Questions of the Day for SQLServerCentral and I try to alternate Python and R questions every week. This has caused me to dig in and try to learn more about the language and how to manipulate data. Recently I was [reading an essay]( from a consultant that works with clients using both R and Python. The piece talks about the differences and how these work to solve business problems. If you don't want to read the entire thing, the comparison starts with the simple "you need both", though there is more to the story. The most interesting part of this for me was that the author notes that while these are good languages in different ways for data analysis, they aren't great for data preparation and SQL is still required. Either a database like SQL Server or a platform like Apache Spark. Part of the reason is that R and Python aren't very efficient, and as we work with more data and larger workloads, efficiency matters. The other part of the piece I liked was the note that we need to collaborate and our work needs to be reproducible for others. I love having git for moving code around and keeping configuration files in a repository of some sort. It has certainly helped me take advantage of bits that others have written and easily reproduce their work on my system. While some of us work with just SQL, I expect that we will get involved with other parts of projects and may need to help troubleshoot or improve code. I find both of these languages interesting and a nice complement to each other. I've also learned there are places where I much prefer one over the other, especially with some of the [Advent of Code]( problems. Some are simple in SQL, but others are much more suited to Python. I haven't tried them in R, but I bet some of them would be well suited to that environment. If you have tried either, or have a preference, let us know. What are the advantages or disadvantages of each when you are working in a business? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQL Server 2016 - Always Encrypted]( Anil Kumar from SQLServerCentral Always Encrypted is a new security feature which was introduced in SQL Server 2016. Always Encrypted is a technology to ensure the data stored in a database remains encrypted at all times during SQL Server query processing. Always Encrypted allows clients to encrypt sensitive data, such as credit card numbers and national identification numbers, inside the […] [SQL Server plan cache mining – Plan attributes]( Additional Articles from SimpleTalk Edward Pollack demonstrates how SQL Server plan cache mining can uncover a wealth of information to help with troubleshooting performance issues. [Creating Idempotent DDL Scripts for Database Migrations]( Additional Articles from Redgate How to write idempotent DDL scripts that Flyway can run several times on a database, such as after a hotfix has already been applied directly to the production database, without causing errors. From the SQL Server Central Blogs - [Encryption in SQL Server #1 – Column Level Encryption]( david.fowler 42596 from SQL Undercover For one reason or another data security and encryption has been coming up quite a bit in my day to day work recently and I’ve started to realise that... From the SQL Server Central Blogs - [Career: Talk To References]( Steve Jones - SSC Editor from The Voice of the DBA As we try to get through these strange times in the world, I am starting to see a bit more career movement in 2021 than I saw for much...   Question of the Day Today's question (by Steve Jones - SSC Editor):  A PowerShell Alias What does the gwmi command do in Powershell? 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) Multiplying Vectors in R If I run this code in R, what is the result? > i <- c(1,2,3) > j <- (4,5) If I do this, what is the value of k? > k <- i * j Answer: 4 10 12 Explanation: If we multiply two vectors of unequal lengths, the shorter one is recycled, with values being repeated. We end up with 4 * 1, 2 * 5, and then 4 * 3. Ref: Vector Arithmetic - [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 [Availability Group with Replication]( - I have configured availability group for automatic failover with synchronous mode. So that way we can run the business for 24hrs. However, I am planning to configure replication. So when the role changes I want replication to handle itself without any manual intervention. Would that be possible? Please advise? [Why doesn't the Secondary failover?]( - Hi, I hope someone can help. I will try to summarise as best as I can. We have four servers in a cluster with an AG set with Database Health Detection. Three servers are on site1 with the fourth on a separate DR site2. We are testing DR by cutting the link between the two […] SQL Server 2016 - Administration [Query store index rebuilds]( - Recently we experienced a sudden surge in disk I/O every twenty minutes or so. We backup logs every five minutes, and every third or fourth backup would increase dramatically (from 100 MB to 3 GB in size). It happened at 2 AM without any changes being done to the server. I dumped the contents of […] SQL Server 2016 - Development and T-SQL [Query Recommendations]( - Hi,  There is a select query which is running very slow. Select customerAcc_ID, Balance, Additiondate, FROM dbo.fnGet_NAME] (@processDate) -- This function is hitting a very big table and I do see some missing IDX hints, however, apart from IDX's I wanted to find out more on this.  Here is my questions, is using […] [SSIS Import only new values from a CSV without using a staging table]( - Hello, How can I import a CSV file in SSIS (2016) at the same comparing a value in the destination table if the record is new? The CSV file is a continuous record-appending file. I want to avoid a staging table if possible. Thank you, Vinay [loading data from staging table to final with validations to data specific]( - Good Morning, Have a Question, we have a FACETS feed it has about 50 columns in it. it is daily feed it get loads with all fields (Dates, Money, Strings) as varchar(500) to the table called STAGING_FACETS in sql server. so here I need to cleanse the data and loads to final table (PR_FACETS) . in […] Administration - SQL Server 2014 [How to find a user and a spid that holds database in single_user mode?]( - I am recreating a scenario when somebody or a process set database to single_user and I need in my script to set it to multi_user. So far I tried to: alter database test1 set multi_user with rollback immediate as well as with no_wait, but nothing helped, I get an error: "Changes to the state or […] SQL 2012 - General [How to rewrite sql query after where condition with another query ?]( - I work on sql server I need to rewrite statement after where statement so how to rewrite I don't understand what is writing after where so can you help me how to write it alternative select top 1 * FROM dbo.GlobalPartNumberPortions Po WITH(NOLOCK) INNER JOIN dbo.GlobalPartNumber GOl WITH(NOLOCK) ON GOl.GlobalPnId = Po.GlobalPnId AND Po.GroupId = […] SQL Server 2012 - T-SQL [How to rewrite sql query after where condition with another query more arranged]( - I work on sql server I need to rewrite statement after where statement so how to rewrite I don't understand what is writing after where so can you help me how to write it alternative SELECT top 1 * FROM extractreports.dbo.TblTemp T with(nolock) JOIN extractreports.dbo.Tbl_Temp TT with(nolock) ON T.GlobalPnID=TT.GlobalPnID join (Select Max(GPNP.GroupID) GroupID,GPNP.GlobalPnID from dbo.GlobalPartNumberPortions […] SQL Server 2019 - Administration [Joining 3 tables]( - Hello All, I am new to the SQL world and with work I use SQL studio. I have a question, I want to inner join 3 tables. the table names are Integration.Encounter, Integration.Schedule and Integration.Person. I have selected the specific columns I want and I coded it like this. select top 5* ,e.[Person ID] ,e.[Facility] […] [How can I tell where my Database encryption keys are kept]( - Hello, can someone please help me with the following question, thanks in advance I am not a DBA, but am learning Azure One of the exercises was the use Azure Key Vault to store the encryption key (key encryption key) to encrypt the column encryption key, and store this KEK in Azure Key Vault (rather […] SQL Server 2019 - Development [Geocoding in SQL Server - do I need C# for it?]( - I've been digging around playing with spatial maps in SQL Server. I've read a few articles on geocoding etc, and it looks like it's done under the covers in Excel 2016+ using Bing under the covers. If I want to do it in SQL Server, do I have options besides creating an account on Bing/Google […] [UDF could not be bound]( - Hi everyone, I have created a sum UDF and I cannot call it. The variable being called cannot be bound. CREATE FUNCTION UDF_datasummary --Define input variables ( @HS_code VARCHAR(20) ) --Define output table RETURNS @sums TABLE ( Month FLOAT, HS_code VARCHAR(20), HS_Desc VARCHAR(255), [Country] VARCHAR(255), Total_qantity INT, Total_amount DECIMAL(18,2) ) AS BEGIN INSERT INTO @sums […] [Incorrect syntax near the keyword 'AS']( - Hi Everyone. I'm hoping someone can take a look at my code here to see what I am missing. I'm trying to create a sub-query and name its alias, and then include that alias/column in the outer select query. But when I do that I get the error message Msg 156, Level 15, State 1, […] In The Enterprise [Group Managed Service Accounts for SQL Server 2017/2019]( - We use group managed service accounts for running the SQL Server 2017/2019 sqlserver.exe processes on Windows Server 2016 in an AD domain. As far as I know this is recommended by Microsoft. This worked well for at least 1 year, but since 4-5 months we have the problem that some of these group managed service […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -