Parsing a Pairs of Values in a String While Preserving Order (2023-10-11)

SQLServerCentral Newsletter for October 11, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Parsing a Pairs of Values in a String While Preserving Order]( - [Stairway to Columnstore Indexes Level 12: Clustered or Nonclustered?]( - [Decision Tree - Credit Card Fraud Detection]( - [From the SQL Server Central Blogs - A NEW’er Solution For An Old Problem: How To Gain Access To SQL Server When No DBA Around]( - [From the SQL Server Central Blogs - Data Platform SLAs]( - [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Question of the Day - [The Low Deadlock Priority]( The Voice of the DBA  Modern Development I was browsing online the other day during a break and stumbled upon an article on [the modern architecture stack]( for a startup. It's not a bad read, and as someone who's worked in startups, it's interesting to see what others think. As with many of these articles, it has a lot of practical advice, and it's also not relevant for many of us because we don't often work in greenfield development. However, we could view adding something new to our application as greenfield. It's not completely the same, but there are similarities when we start a new feature that doesn't exist anywhere. Some of their advice, like docker-izing everything, won't apply, but there is one thing that does apply: data. There is a quote, which I really like: "...what is the point of an API running on top of an empty DB? Manually entering necessary data shortly leads to depression (and the risk of increasing the duration of development cycles). Hence, we prepared a curated dataset that was inserted into the local DB to be able to play with." That's similar to the advice I give when speaking on DevOps. Invest in a curated data set for developers. As this group learned, you can start to use this in testing, CI, etc. and it makes life better for developers. Heck, if you're using version control and you create a data set for yourself, save the insert statements, put them in a folder, name the script for the table, and share it with others. It's a small change, but it's one that pays big dividends over time. If others use your INSERTS, and you use theirs, all of a sudden you have a good curated set. If you maintain this as you find bugs or strange things customers enter into production, you don't need a restore of production; you can add more data and run just those statements. Heck, if you add columns, add some data. You'll do that anyway to test your new column so maintain this as a script. Or update your data and use [SQL Prompt]( to create INSERT statements for all the data and replace the entirety of your data script. A big part of development is maintaining data. While there are virtualization solutions, like [Redgate Clone]( those can be cumbersome and expensive. They do solve problems, and they might be a good fit for your situation. However, I really like smaller sets of data that duplicate our problem domain. For those of you dealing with time-based problems, include scripts that "update" dates and times to simulate problems from today or yesterday rather than last week. The limit is the creativity of your team, and as you maintain this dataset, everyone benefits from small changes made over time. Version control has been a boon to sharing software projects between developers, but underutilized as a way of sharing data as well. Add some data scripts to your project and you might be surprised how much easier it is to work as a team. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Parsing a Pairs of Values in a String While Preserving Order]( wfw311 from SQLServerCentral Learn how you can parse a series of parameters that are passed in a string of tuples, meaning pairs, of values and preserve order without using STRING_SPLIT with the ordinal parameter. [Stairway to Columnstore Indexes]( [Stairway to Columnstore Indexes Level 12: Clustered or Nonclustered?]( Hugo Kornelis from SQLServerCentral.com The previous levels of this stairway describe details, features, and limitations of columnstore indexes in SQL Server. But they do not answer what should be the first question for every database professional: should columnstore indexes be used in my databases; on what tables should they be used; and should they be clustered or nonclustered columnstore indexes? [Technical Article]( [Decision Tree - Credit Card Fraud Detection]( Additional Articles from CodeProject.com Credit card fraud detection is an important application of machine learning techniques, including Decision Trees. The goal is to identify and detect fraudulent transactions and separate them from valid transactions to prevent financial loss and protect user accounts. [Blog Post]( From the SQL Server Central Blogs - [A NEW’er Solution For An Old Problem: How To Gain Access To SQL Server When No DBA Around]( SQLPals from Mission: SQL Homeostasis How To Gain Access To SQL Server When No DBA When I showed a draft of this article to a friend of mine, to... [Blog Post]( From the SQL Server Central Blogs - [Data Platform SLAs]( kleegeek from Technobabble by Klee from @kleegeek Database professionals of the world – I have a question. Has your organization defined service level agreements (SLAs) for your data estate? I’m talking specifically the Recovery Point Objective... [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Steve Jones - SSC Editor 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):  The Low Deadlock Priority Deadlock priorities range from -10 to 10. If I run this, what is my priority? SET DEADLOCK_PRIORITY LOW; GO 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) Optimized Locking In Azure SQL Database, what are the two main components of optimized locking? Answer: a transaction ID (TID), lock after qualification Explanation: The two components are a transaction ID and lock after qualification. These are used to improve concurrency in Azure SQL database. Ref: Optimized Locking - [ [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 [Central Management Server (CMS) on AlwaysOn for Failover Process]( - Good afternoon! Is there any type of documentation or process for failing over a CMS server? Do I have to remove them from Primary and add them to secondary (new primary)? This is cumbersome work. Let's assume it's HA and failed over every 2 weeks. Or assume a DR failed over for over a month. […] [cost threshold for parallelism value to be set]( - Hi Everyone, Why and when should be set 'cost threshold for parallelism'? We are on SQL 2017 Enterprise Edition. What should be the value set for cost threshold for parallelism instead of default value 5 and why ? what benefit do we get if we set a high value and what should be the starting […] SQL Server 2016 - Administration [Alwyason availability group can set primary role permanently?]( - Hi. Two node windows clusters Node1 and Node2 and configured alwayson availability group. Will it possible to Alwyason availability group can set primary role permanently any one of the node? Thanks  [Does multi-subnet with standardeditionofsqlserver supports DB Mirror Or AG group]( - Does multi subnet failover with standard edition of sqlserver supports DB Mirror or AG groups 1. can db mirror configured in different multi subnet with standard edition 2. can db always on configured in different multi subnet with standard edition SQL Server 2016 - Development and T-SQL [get week number of month]( - I want to query a table that has a datetime column ([CreateDateTime]) and get the week number for that specific month. 2023-03-01 09:30:00 2023-03-12 11:15:22 2023-03-22 14:22:18 2023-04-02 15:12:30 I want to get these results from the above dates: 1 3 4 1  SQL Server 2019 - Administration [Changing Database page Verify on a Database in an AG]( - I have a database on SQL 2019 in an Availability Group (AG) and I want to change the Page Verify attribute from NONE to CHECKSUM on all of the nodes in the AG for this database. The Page Verify attribute is the same on the database on all nodes of the AG. I do not […] [High tempdb usage alternatives]( - Hi We have an stored procedure which runs frequently and it contain logic in which data are first dumped in temproray table and then moved to physical table .due to large data and frequent run of sp .tempdb database get filled up quickly .We cannot take server restart everytime so whats alternative to temp variables […] [high latency in sql server transactional replication]( - Hi I am new to replication and we have database of size around 2TB and its has been set up for replication .there are 4 subscriber to it. Tables(articles) of database has been divided into 14 publishers. Issue sometime we face high latency on server with lakhs of undistributed command so what should i check. […] [upgrade evalution editon reporting services to Stanadard]( - Hi All, I installed reporting services evaluation edition for testing purpose, but now 180 day trial period is expired and I am unable to generate reports. Is it possible to upgrade it with SQL server 2019 standard edition, without disturbing the existing configuration. I Installed SQL server 2019 database engine on same machine, but while […] SQL Server 2019 - Development [Need to output table data into multiple CSV files]( - Hello, I created the below script to output 999 rows from SQL Server table into multiple CSV files. The SQL table has about 45,000 rows. We can only output max of 999 rows into the CSV files. I installed powershell sql server module. Since this is a powershell script, I may need to modify the […] [need some help with spilt raw data]( - Hello,  I am trying to split data, looks like there is some special character or white space that is not letting me to split. i try to split with space as delimiter even though there is space between two A2339 MC it will not split. -- test date DECLARE @TEST_STR TABLE ( TEST_STRING NVARCHAR(255) […] [Convert Binary or Hex field to show text information]( - Hi all, I have an SQL database table with events, and within it a field of binary (32) datatype. I want to convert the info in binary field and show me the information that refers to the event. The info is of this type: 0x0000000000000000000000000000000000000000000000000000000000002000. Can someone help? Thanks!!  [Convert columns result into rows with comma and single quotes]( - Hi All, Hi All, I want to convert columns result into rows with comma. Ex: Result should be - 'SQL1','ABCD100','XYZ123' #code: create table #tbl_db (name varchar(100)) insert into #tbl_db values ('SQL1') insert into #tbl_db values ('ABCD100') insert into #tbl_db values ('XYZ123') select * from #tbl_db Result needed: 'SQL1','ABCD100','XYZ123'  Powershell [Write https request to azure blobstorage]( - Hi All, I have been trying to download a csv file which is produced by calling an api and then to store the csv file directly in azure blob storage. I am able to download the file on my local machine and then copy it to azure blob storage. But I would like to copy […] Employers and Employees [Master Data - Relational or Dimensional modeling?]( - Dears, In my company, we have certain master data domains like customer; Vendor; Product; Materials; Bill of Materials. So far we have been doing our modeling using relational modeling in the cloud. It consists of entire tables that we push from SAP ECC and SAP MDG like MARA; MVKT, etc... then we have views on […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -