Searching an Encrypted Column (2023-12-15)

SQLServerCentral Newsletter for December 15, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Searching an Encrypted Column]( - [When an update doesn’t update]( - [Running Azure CosmosDB queries from SQL Server using ODBC driver]( - [From the SQL Server Central Blogs - SQL Server Quickie #46 – SQL Server on Linux]( - [From the SQL Server Central Blogs - Comparing Two Scripts with kdiff3]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [Finding Database File Growths]( The Voice of the DBA  Try, Try Again, Until It's Right One of the challenges with making changes in a database environment is that undoing those changes can be hard. What's often preferred is rolling forward with a new change to correct the issue, but that's often done with limited analysis and thought. Instead, we hope our staff makes a quick patch and a better decision under pressure than they did with more time to examine the problem. That works if it's a simple mistake that was made in implementation but not if we haven't designed our solution well at the start. I ran across [an article on DoorDash]( that I thought was interesting. During the pandemic, their business exploded and they outgrew the Aurora PostgreSQL database. They migrated to [Cockroach]( a cloud version of PostgreSQL that's distributed and can (theoretically) scale much higher. The thing I found interesting is that the engineers at DoorDash were trying to break apart their monolith and get better scalability, primarily from certain tables, by extracting their tables to get single writers in a cluster, which should help them handle a larger workload. They wanted to use their main identity table as a test, which I assume is the table that tracks each user in the system. They tried to migrate this and cutover to a new cluster 4 times before a fifth attempt worked. I think any large migration is fraught with issues, but I appreciated the design here that allowed them to rollback their change and revert to the previous version of the database. That's something I don't see many teams think about or build into their database change process. I think having a clear, known, tested way to undo changes is important, at least for some of your tables. There are two pieces of advice they give that I often give to customers as well. First, learn to spread out changes across batches. When I work with [Flyway]( customers, I always let them know they need to think of a migration script as a unit of deployment and break those apart as best you can. Those often also become units of rollback, so keep them small. Not necessarily every change in its own script, but don't bundle too many things together. Second, keep things simple. Too often I find engineers build clever solutions that make sense to them, but no one else. You never know the quality of your next hire, so don't overcomplicate things without a really good reason. Did their process work? They've grown to about 1.9PB of data. That's a lot of food orders. They've also had other metrics of success, and seem to be saving time for their tech team, which is often one of the main reasons to build a better process and use it consistently. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Searching an Encrypted Column]( dave-L from SQLServerCentral This article examines the challenges with searches on encrypted data and presents a possible solution that you might use to speed up those queries. [External Article]( [When an update doesn’t update]( Additional Articles from SimpleTalk Tell me if you’ve heard this one before: I changed data from my application, but when I checked the database, I couldn’t see the change! I’ve seen this. Loads. It can be quite perplexing for folks because they expect to see an error message if the insert, update, or delete failed. [External Article]( [Running Azure CosmosDB queries from SQL Server using ODBC driver]( Additional Articles from Microsoft MSDN Azure CosmosDB provides ODBC driver that enables you to query CosmosDB collections like classic databases. In this post you will see how to query CosmosDB collections from Sql Server using Transact-Sql. [Blog Post]( From the SQL Server Central Blogs - [SQL Server Quickie #46 – SQL Server on Linux]( Klaus Aschenbrenner from Klaus Aschenbrenner Today I have uploaded SQL Server Quickie #46 to YouTube. This time I’m talking about SQL Server on Linux. [Blog Post]( From the SQL Server Central Blogs - [Comparing Two Scripts with kdiff3]( Steve Jones - SSC Editor from The Voice of the DBA I had a customer recently ask if SQL Compare could show them the differences in two scripts they’ve written. They weren’t using version control (tsk, tsk, shame), but saw... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Finding Database File Growths I have a SQL Server 2022 instance that I set up with all the defaults for a US installation. I am wondering when the data file for one of my databases grew. Where can I find this information? 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) Getting a List of Names There are 500 names in the Person.Person table in my database. I run this query: SELECT STRING_AGG (CONVERT (NVARCHAR(MAX), p.FirstName), ',') FROM person.Person AS p; How many rows are returned? Answer: 1 Explanation: This returns one row, with a long string. Ref: STRING_AGG - [ [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 [Query]( - ServerName DatabaseName DatabaseSize(MB) Date Server DBName Size Date A DB 110 6/1/2023 A DB 113 7/1/2023 A DB 118 8/1/2023 A DB 130 9/1/2023 A DB 120 10/1/2023 A DB 140 11/1/2023 A DB 143 12/1/2023 A DB2 1110 6/1/2023 A DB2 1113 7/1/2023 A DB2 1118 8/1/2023 A DB2 1130 9/1/2023 A DB2 1120 […] SQL Server 2016 - Development and T-SQL [send csv file or txt file to sftp server]( - Hello Can someone help me with code to send file to sftp server please: Here is my code: # SQL Server query $Query = "SELECT name from tblName" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=CWR-PP02;Database=testdb;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $Query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = […] SQL Server 2019 - Development [Improving Code Readability]( - Below is a code i wanted to improve some readability and functionality: GO /****** Object: View [Prod]. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [Prod]. AS --------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT MAN_ADJ_ALL.[FY] ,MAN_ADJ_ALL.[Period] ,MAN_ADJ_ALL.[Group] --ADJUST THIRD PARTY TO ALLOCATED PLANT ,CASE WHEN MAN_ADJ_ALL.[Cost Element] LIKE 'Variable 3rdP Cycle1' THEN MAN_ADJ_3RDPARTY.[Plant] ELSE MAN_ADJ_ALL.[Plant] END AS [Plant] […] [Selecting from a view, base table has a DENY on a column]( - This is something I've never seen before and I can't think of the right way to search properly for this, so I'd like to throw it out to this group. We have an audit table that stores the old password value when someone changes it. There is a DENY on this column for all but […] [JSON data with Pivoted]( - Hi All, I need some assistance and not sure how to achieve the expected output. I have a JSON input parameter which is a nvarchar(max) passed from my application as per my sample code below. I've started to break up the JSON into a table output and am a bit stuck on getting how I […] SQL Server 2008 - General [Merge Replication SQL 2008 (10.0.6556) - downloads slow and behind]( - Merge Replication SQL 2008 (10.0.6556) - downloads slow and behind I am working on newly virtualized sql cluster with sql 2008 and merge replication with replication of many tables on a large database (multiple TB data file). Recently, the merge replication of one of two pubs for it started to appear to only perform uploads […] Azure Data Lake [Lake Database Performance Optimization]( - I recently created a synapse link for Dataverse. The resultant Lake Database in Synapse is powering a number of Power BI reports, however the performance of the lake database is a slower than we had expected. What steps can I implement to improve the performance of the lake database? Analysis Services [Calculated Time Periods Hierarchy]( - Please help me understand why I am getting the following error: "A set has been detected that can't contain calculated members." I have Attributes "Dates" and Hierarchies "Calendar" (YearID/QuarterId/MonthId/dtDate). I've created  Calculated Member a "Current Month" for the parent hierarchy «Dates.Calendar.[all]». CREATE MEMBER CURRENTCUBE.[Dates].[Calendar].[All].[Current Month] AS StrToMember("[Dates].[Calendar].[Month Id].&[" + Format(now(), "yyyyMM") + "]"), VISIBLE = […] [Calculated Time Periods Hierarchy]( - please help me figure out why I'm getting the following error: "A set has been encountered that cannot contain calculated members."  I have Attributes "Dates" and Hierarchies "Calendar" (YearID/QuarterId/MonthId/dtDate) I created Calculater Member "Current Month" for Parent hierarchy "Dates.Calendar.[all]" CREATE MEMBER CURRENTCUBE.[Dates].[Calendar].[All].[Current Month] AS StrToMember("[Dates].[Calendar].[Month Id].&[" + Format(now(), "yyyyMM") + "]"), VISIBLE = 1 […] [Relationship between dimension and measures]( - There is a Fact Table (.....,MenuID, MOption, CallTime) There is also a Dimension "Menu" Table (MenuId,OptionID) Relationship between these tables (MenuID and MOption) In the Dimension table, I created another field - keyField (string(MenuID+MOption) as OptionId2) when creating Dimension, I use the new key (OptionId2) and build the hierarchy accordingly -MenuId -OptionId2 (ключ)  but […] SQL Server 2022 - Administration [Moving database servers - IP address change - listeners]( - Hi, We will be moving our physical database servers to a new location. Prior to the move, new IP address for all the SQL servers will be changed/updated.   Are the IP address listed above for the cluster server? After the servers are moved will I need to change the IP address for the […] [how to install smo in an offline machine]( - hi,  how do I install smo in a offline machine Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 170.18.0  PS C:\Users\Administrator> Find-Module -Repository smo WARNING: The file extension 'C:\Packages\nuget.exe' is not valid. The required file extension is '.nupkg'. Version Name Repository Description ------- ---- ---------- ----------- 21.0.17224 SqlServer SMO This module allows SQL Server developers, admin  […] SQL Server 2022 - Development [Retrieving First Word, First + Second Word, First + Second + Third Word, First]( - Hi All, I have the company names in one column. Need a sql server query to fetch in separate column like Example: ABC private limited company First Word ABC First + Second Word ABC private First + Second + Third Word ABC private limited First + Second + Third + Fourth Word ABC private limited […] [Why don't these two queries return the same data?]( - I have a table-value function that returns data for a report. However, it's not returning the correct data, so I've reworked it and it's now returning what I'd expect ... the thing is, to my obviously unseeing eyes the two queries should be functionally identical. Please can someone help? This query doesn't work: SELECT Grades.[Name] […] [SQL Challenge: Employee Salary Analytics]( - onsider an employees table: employee_id (int) employee_name (varchar) department (varchar) joining_date (date) salary (decimal) Highest Earner by Department: Find the employee with the highest salary in each department. Average Salary by Department and Year: Calculate the average salary for each department per year. Longest Serving Employee: Identify the employee with the longest tenure. Please provide […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -