Stairway to Columnstore Indexes Level 6: Updating and Deleting Data in a Columnstore Index (2024-09-04)

SQLServerCentral Newsletter for September 4, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Columnstore Indexes Level 6: Updating and Deleting Data in a Columnstore Index]( - [I Must Be an Idiot: Automatic Tuning Never Works for Me.]( - [Level up your learning in 2024 with PASS Data Community Summit]( - [From the SQL Server Central Blogs - State of the Database Landscape Survey 2024]( - [From the SQL Server Central Blogs - Microsoft Fabric reference architecture]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [CONCAT NULLs]( The Voice of the DBA  Fifty Percent Most of us will have more than one job in our career. In fact many of us will likely find a new job in the next five years. I hope I'm not in that group, but I recognize that it's a possibility. We never know when our situation will change, or our employer's situation will change. That is one reason I recommend you keep your resume up to date and continue to work on improving your skills. I saw [an office hours short]( recently from Brent Ozar, in which someone had asked him if they should apply for a job even though they didn't meet all of the requirements or know all of the desired technologies. Brent recommended the person apply, and his reasoning was that often a DBA (or other data pro) often gets asked to do a variety of tasks in an organization. The DBA job often crosses lots of boundaries and may end up working on a Active Directory issues, reporting, ETL, and more. When A DBA leaves a job, the organization looks for a replacement that can handle that same wide variety of things. I think this is good advice, and I usually tell people that if you meet 50% of the requirements, apply. Part of this is Brent's reasoning, part of it is because I've had to write job descriptions, part of it is because I've interviewed people. While we often have a standard description, usually I then think about how our technology matrix has changed over time and add items to the job description. If I ask another employee what else we might need in a candidate, more requirements get added. At some point, the job description isn't realistic anymore and it's unlikely we'd ever find a person that meets 100% of our desires. The other thing I've learned in interviews is that sometimes a candidate impresses us and makes the interviewer think about the position differently. Maybe the candidate is impressive enough in some areas that we can ignore their deficiencies in others. Sometimes the candidate might say something that causes me (as the interviewer) to pivot the job slightly to address other possibilities I think of at that moment. Job hiring isn't a meritocracy. It's a mix of many things, often depending on what the interviewer thinks at that time, how the candidate impresses them, and maybe some restrictions on who they can hire. I've seen companies that won't hire developers without a CS degree, regardless of experience. I've seen situations where the hiring manager creates a new position because the candidate is so desirable. I've sometimes chosen a weaker technical candidate over a near expert in an area because of soft skills. There are lots of variables, and you can't control them, but you can control a few things. You can work on the way you communicate with others and improve your skills, both verbal and written. You can also showcase your ability to learn and adapt, whether that's with stories and examples or a blog that you can point to, documenting the work you've done on your skills. This is a tough time for many tech professionals, but it's also a time where there are many open positions in lots of companies. Part of this is an mismatch of candidates and opportunities not finding each other. Part is a more discriminating approach from companies who don't just want people, but talented people, talented in multiple ways including soft skills. You never know when you'll need a new job, so make sure you're prepared to impress others. Make sure you spend time on your career on a regular basis. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Columnstore Indexes]( [Stairway to Columnstore Indexes Level 6: Updating and Deleting Data in a Columnstore Index]( Hugo Kornelis from SQLServerCentral.com This level looks in detail at what happens when we update or delete data from a clustered columnstore index, the impact it has on concurrent data access, and how without careful maintenance the efficiency of columnstore indexes can degrade over time. [External Article]( [I Must Be an Idiot: Automatic Tuning Never Works for Me.]( Additional Articles from Brent Ozar Blog I don’t get it. I’ve given this feature one chance after another, and every time, it takes a smoke break rather than showing up for work. [Technical Article]( [Level up your learning in 2024 with PASS Data Community Summit]( Additional Articles from PASS Connect, share and learn with the global data community in Seattle this November. Whether you're a DBA, Developer, Architect, Engineer or you're just getting started in your data career, there's a huge range of content for you to choose from (over 150 sessions and counting across 5 different tracks). Speakers this year include Steve Jones, Grant Fritchey, Ryan Booz, Bob Ward, Deepthi Goguri and Jennifer Stirrup. Check out the full program and register before September 18 to secure your ticket at the standard price. [Blog Post]( From the SQL Server Central Blogs - [State of the Database Landscape Survey 2024]( Grant Fritchey from The Scary DBA Hello all! This post is nothing but a simple request. Please, if you have a few spare minutes, meander on over to this link and fill out the State... [Blog Post]( From the SQL Server Central Blogs - [Microsoft Fabric reference architecture]( James Serra from James Serra's Blog Microsoft Fabric uses a data lakehouse architecture, which means it does not use a relational data warehouse (with its relational engine and relational storage) and instead uses only a... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Additional Articles from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Jones - SSC Editor):  CONCAT NULLs What is the result of this code? DECLARE @name VARCHAR(10) = 'Steve'; DECLARE @title VARCHAR(10); SELECT CONCAT (@name, 'Jones', ', ', @title); The answers use brackets to delimit the results. Ignore them. 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) Trigger Order II I have a table in my SQL Server 2022 database with two triggers, triggertest_tri_1 and triggertest_tri_2. I run this code to first set an order for my triggers. EXEC sp_settriggerorder @triggername = 'triggertest_tri_2', @order = 'first' , @stmttype = 'INSERT' GO However, I realize I've made a mistake, so I run this: EXEC sp_settriggerorder @triggername = 'triggertest_tri_1', @order = 'first' , @stmttype = 'INSERT' GO Answer: An error is returned Explanation: An error is returned here. You can only have one trigger set to First, so trigger 2 must be set to None. Ref: sp_settriggerorder - [ [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 [Does a Query Lock to the Table_Name or Table_ID]( - SQL 2017 ENT SCENARIO: Table_1 has a Sch-M lock on during an intended updating process. A QUERY is executed against Table_1 and is queued up, waiting for the Sch-M lock to release. While the QUERY is queued up on Table_1, Table_1 is renamed to Table_1_OLD A newly designed Table_2 is then renamed to Table_1 QUESTION: […] SQL Server 2017 - Development [update field on a select query]( - I have a sql query that runs on demand to generate a report. I want to update a field REPORTSENT to a Y on the selected results. So the next time the report is requested only the Records with a REPORTSENT = N will be returned. Normally would run the query for the records and […] SQL Server 2019 - Administration [Cluster Timeout Settings to handle transient network outages]( - I have an Availability group with 2 replicas on the same subnet. We've been experiencing short network outages that last a few seconds and cause connections to timeout. This causes the AG to fail completely (not failover automatically) and we have to manually bring it back online since the DBs are marked "not synchronizing" on […] [Question about SQL Certs and DNS aliases]( - About 15 years ago we started creating DNS aliases for each database. This allowed us to move databases without needing to change client connections. We now use these aliases in all applications, reporting, ETLs, etc... When we upgrade SQL we create new servers and migrate the databases, using the aliases made this simple. We also […] SQL Server 2019 - Development [ssis dashboard suddenly missing from reports option under catalog/ssisdb]( - hi i rt clicked ssisdb today under integration services catalog , chose reports and didnt see the dashboard option under my regular id. my id has sa. so i connected with my admin which also has sa and did see it. losing permissions would have occurred in the last 24 hrs. i saw in one […] SQL Azure - Development [Hybrid Cloud Server Integration with SQLServerCentral’s SQL Server 2022]( - Hello everyone, I’m currently exploring the use of hybrid cloud servers and am focusing on integrating them with SQLServerCentral’s SQL Server 2022. While hybrid cloud environments offer flexibility, managing and optimizing them with SQL Server 2022 presents several challenges. Here are some specific areas where I’m seeking advice: Integration Best Practices: What are the best […] SQL Azure - Administration [SQL Server 2022 on Azure VM]( - I was provisioned this DB on an Azure VM. I needed to uninstall and reinstall it in order to set the proper collation. I dont know how it was originally setup or if it goes by another namelike "Managed SQL Instance" as another department handles setting these up. When they learned of my uninstall/reinstall they […] General [Performance of Derived table in large data(12 crores and more)]( - Using Derived Table in query on large data (12 crores and more) results in CPU utilization greater than 90% resulting in performance degradation. Why Using Derived Table in query utilizes high CPU when it is created in memory Powershell [dump to csv -- image column]( - If there is an Image Column in mssql table and I wanted to use export-csv to dump this info to file how do you handle that column to be able to import that csv to another DB. Thanks. SQLServerCentral.com Website Issues [Future Post]( - This post is from the future! How did that happen? SQL Server 2022 - Administration [How to connect to Developer Server Edition]( - Hi, I am attempting to connect to a local instance of sql developer installed today. When attempting to connect to the Server Name: EHL5CD4254Q4G with Windows Authentication ( its a domain joined laptop btw), I get an error: TITLE: Connect to Server ------------------------------ Cannot connect to EHL5CD4254Q4G. ------------------------------ ADDITIONAL INFORMATION: Login failed for user 'mydomain\myaccount'. […] [Alias woes on SQL Server 2022]( - Our company is moving to the cloud. I have SQL Developer Edition 2022 on Windows Server 2022 Datacenter setting up our QA SharePoint Farm.. I've created a named instance running over port 55055 using TCP/IP. THis is what I have in my on premises SQL server that we set up about 10 years ago. The […] SQL Server 2022 - Development [Text column is taking More time while Querying, Need to Replace DATALENGTH]( - select * from dbo.resolutions r where R.documentid = 828222222 and DATALENGTH(r.resolution_text) > 0 resolution_text Text column is taking More time while Querying, Need to Replace DATALENGTH, when i replace datalength condition as "r.resolution_text is not null" the above query is returning data [SSIS package project connections don't work on upgrade]( - We're upgrading from SQL Server 2016 to 2022. The SSIS package project level connections don't work as expected. One queries the server as an anonymous logon instead of the domain account associated with the SQL Server Agent. One even affects both the original server and the new one to which it is pointed. I have […] [2022 azure vm cant run same ssis pkg we run on prem]( - Hi, we set up an environment on our new azure 2022 vm with a cloned "prod target warehouse", ssis project clone from prod, cloned sql agent etc etc. amongst other things the ssis points at a new erp's sales data we are testing with in just one agent step/ssis project execution for that 2019 std […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -