Stairways to ScriptDOM Level 4 – Finding Nuanced Patterns (2022-12-07)

SQLServerCentral Newsletter for December 7, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairways to ScriptDOM Level 4 – Finding Nuanced Patterns]( - [How to Use Any SQL Database in GO with GORM]( - [Data Masking in Principle]( - [From the SQL Server Central Blogs - Creating a Unity Catalog in Azure Databricks]( - [From the SQL Server Central Blogs - Granting read access to SQL Server services with Just Enough Administration]( Question of the Day - [The Availability Group Secondary Connection]( The Voice of the DBA  Daily Coping Tip Stop for a minute when you walk outside and just enjoy nature. 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. We are All Data Companies This editorial was was originally published on Mar 21, 2017. It is being republished as Steve is on holiday today. I have seen more than a few people note that every company is a software company. Or that every company should be a software company because software is becoming so important. I'm not sure that's true. My mechanic certainly uses software, and he competes well with other businesses by tracking my car, reminding me of issues, and keeping in contact through integration between his point of sale system and some sort of email management software. However, this is a small shop, probably less than 20 employees, and I'm not sure they're ever going to be in the business of software. They are consumers, and they need support, but they aren't in the software business. My wife's company is similar. She needs a website, needs to schedule events and take payments, but she's not going to develop software to help her. Nor am I going to write code, because that might be a never-ending job. It's easy to get out of touch and think that many larger companies need to be software companies. That might be true for some businesses, especially when they need to manage some infrastructure, but for many smaller businesses, I'm not sure that's the case. Software is critical to small medical practices and retail stores, and service people, but only as a tool. Not as something they'd want to maintain or even customize beyond a simple look and feel. Is that true for data? If many companies aren't software companies, are they data companies? I think so. Data is important, and having some way to look at data and manage it becomes important for them to run their business well. My wife struggles with data, but realizes that managing her mailing list and tracking the contacts is valuable. It's hard, and her bits are stored in disparate applications, but she recognize the value of data. The same for my mechanic. They've seen plenty of value in treating a string of disparate transactions from me across multiple cars as those from a single customer. They also have learned that finding software to understand me as a customer, as well as string together recommendations and past work on single vehicles allows them to provide better service. Many companies want to track us through email, phone, or some other identification data because they realize that many people have come to expect a business to know something about us. We might not like them sharing or selling that data, but we do want to them to provide a better experience for us. I'm still surprised when there's good integration between disparate systems, or a customer service call allows the company to recognize something about me and provide more value. I believe my kids and future generations will expect this to always be the case, shunning companies those that don't provide customization. I worry about data security, data privacy, data integrity, and more, but I do also think that as more companies realize that they are data companies, and data drives many of their interactions, they will see data as more valuable. What they do with that, I'm not sure, but I can only hope they start to treat our data as a valuable resource worth protecting. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Technical Article]( [Stairways to ScriptDOM Level 4 – Finding Nuanced Patterns]( Diligentdba 46159 from SQLServerCentral This level of the Stairway to ScriptDOM looks at finding more complex instance of anti-patterns in your code. [External Article]( [How to Use Any SQL Database in GO with GORM]( Additional Articles from SimpleTalk ORMs (Object Relational Mappers) are tools (libraries, modules, packages) that provide the functionality for interacting with SQL databases. This article will look at how to use any SQL Database in GO with GORM. [External Article]( [Data Masking in Principle]( Additional Articles from Redgate This is the first of two articles to describe the principles and practicalities of masking data in databases. It explains why an organization sometimes needs masked data, the various forms of masked data we can use, the sort of data that needs to be masked, and the potential pitfalls. [Blog Post]( From the SQL Server Central Blogs - [Creating a Unity Catalog in Azure Databricks]( Meagan Longoria from Data Savvy Unity Catalog in Databricks provides a single place to create and manage data access policies that apply across all workspaces and users in an organization. It also provides a... [Blog Post]( From the SQL Server Central Blogs - [Granting read access to SQL Server services with Just Enough Administration]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold We’ve all been there as DBAs…people requesting access to the servers that we look after to be able to view certain things. I’ve always got, well, twitchy with giving...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Availability Group Secondary Connection In a SQL Server Availability Group, I want to control access to the secondary node(s). What types of connections are allowed for clients? 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 DAX Text Contatenation I have two columns in a Power BI data model. They are: Assists[Event] and Assists[Opponent]. These are text values and I want to put them together with a hyphen. Which DAX formula will work? Answer: EventDesc = Assists[Event] & " - " & Assists[Opponent] Explanation: The concatenation operator is an ampersand (&) in DAX. Ref: DAX Operators - [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 2016 - Administration [Copy backup to not destroy backupchain]( - Hi, A bit unsure about how to make a copy only backup and the right way och doing this. I dont want to tamper with the backupchain and do something wrong. In the general tab\Destination\Back up to: , where i can choose destination i alredy have a path where the backups are stored. Can i […] SQL Server 2016 - Development and T-SQL [Null value when checking unused indexes]( - I'm using this query to check if I have any unused indexes.. One of the tables it display's has lots of user_updates but the indexname field is NULL... There is a non-clustered index on the table No fill factor and page fullness about 50% and 98% fragmented. Min row size 42 and max 74 avg […] [Degraded performance with extended event session]( - We have noticed that our nightly jobs take 2-3 times longer when the following extended event session is running. There is a clear deterioration in server performance across two separate servers running the same XE, even though there is not much data being written to the target files by the sessions (only a few MB). […] SQL Server 2012 - T-SQL [mdf space balance]( - I have a DB that is running out of space on a drive... If I add another data file will the drive that is running low run out of space, or will it use the new data file just created. How does it balance data consumption. Thx. [Using Case with DATEDIFF for Aging Report]( - I need help to create a Aging report, I am not retrieving the correct amounts. I am wondering if I need add debit and credit first and then the dateiff SQL Server 2019 - Administration [Can someone explain where non - clustered index is stored?]( - I am learning non-clustered and clustered indexing. When reading this article ~:text=Clustered%20indexes%20only%20sort%20tables,involve%20any%20extra%20lookup%20step. came across this line by the author Non-clustered indexes are stored in a separate place from the actual table claiming more storage space. Question is, where is this table? I am looking at my Azure Data Studio and am scratching my head looking into […] SQL Server 2019 - Development [Multiply time by hourly rate]( - Hello all, I have this table with time for each row. It shows hh:mm. I have rows with all kind of time, 6:30, 5:10, 5:00, 6:42 etc, and I have to multiply it by the hourly rate. Could you please help me? I think I have to, first convert this time column into a number […] [Compare 2 tables and highlight where the column values has been updated]( - Hi All, I have a requirement to compare data from same table with different snapshot dates and highlight what column values changed from to TO Can any one help me with above requirement please?  Regards, R [Best primarykey and index]( - Hi all, i've to refactor a database with several tables with around 100.000.000 records each one. Each table has the following relevant columns: - ID (bigint) - Tenant (actually i have 10k tenants) - Year (all info has Always a filter search by year) - other 10/20 simple columns I was thinking to have: 1) […] [stored procedure not running]( - Created a stored procedure and logged in SSMS as Administrator and this user is exists in the Security > Logins I'm able to successfully execute the sp using T-SQL. e.g, declare @output_execution_id bigint exec dbo.int_Bill_Run @output_execution_id output Now, when I add it SQL server agent as a job I get errors - Executed as user: […] Azure Data Factory [API Configuration help ADF]( - Hi I am trying to connect to a HR system to simply pull down some data and copy in ASDB I have managed to get it to work in excel using the following code below in the advanced editor but dont even know where to start in terms of where i put this information in […] Reporting Services [Empty Field is causing entire Page to be blank]( - I'm a little new to SSRS and am running into a strange issue. I have a Table tied to a data source. Essentially, it is just a letter with text and two fields from the dataset ([Salutation] and [FullName]). The [Fullname] is a field that defines the Associate for the record and it may be […] [Empty Field is causing entire Page to be blank]( - I'm a little new to SSRS and am running into a strange issue. I have a Table tied to a data source. Essentially, it is just a letter with text and two fields from the dataset ([Salutation] and [FullName]). The [Fullname] is a field that defines the Associate for the record and it may be […] General [Error when running batch file]( -  I have a batch file, with the command to drop the tables from the server database. The command works in the database but when I ran the file it gives me an error executing my T-SQL Table dropping script: Sqlcmd: 'ds.user': Unexpected argument. Enter '-?' for help. Any idea what might be the problem? […] Powershell [Retrieve Password from CyberArk using powershell]( - Hi, I am new to Powershell script and i have to create a power shell script to get password for SQL service account from CyberArk.. Could you please suggest how to do that ?   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -