Efficient Table Migration to a New Schema in T-SQL (2023-11-03)

SQLServerCentral Newsletter for November 3, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Efficient Table Migration to a New Schema in T-SQL]( - [Microsoft at the PASS Data Community Summit 2023]( - [SQL Server Fragmentation Impact with Modern Hardware]( - [From the SQL Server Central Blogs - Deleting a Git Branch–#SQLNewBlogger]( - [From the SQL Server Central Blogs - SQL Server query runtime is not everything]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [The EXCEPT Query]( The Voice of the DBA  When Work Isn't Done Software development can be a challenge for each of us with lots of demands and the need to ensure your code solves a problem correctly, efficiently, and completely. Juggling the workload by yourself is one thing, but add in a team of developers, and the complexity quickly grows. The real world is chaotic and despite the best efforts of project managers and scrum masters, our software development life cycle doesn't always proceed smoothly. I wonder how many of you run into this situation and how you deal with it. Developer 1 gets a piece of work, let's call this A. They complete this and send it to the QA team. Somewhere during this process, Developer 2 get's a different piece of work (B) and writes code. They send this to QA before A is completely tested. Now, Developer 1 finds a mistake. Something doesn't work or they realize their solution is incomplete. QA is A + B, but A doesn't work and needs revision. B passes testing and needs to be deployed. If your codebase and QA have both A and B in them, how do you strip out A or B and ensure B is deployed to production but A isn't? If this is C# or Java, you might have one solution, even if both changes are in the same class. If this is database code, you might have a different set of issues to deal with. Really, the question is can you reorder work in your deployment process? I find many customers don't consider this when they are evaluating their software pipeline. They somehow assume if code gets to QA that it's good, which is nicely optimistic but not realistic. At some point, we'll deploy code to QA that doesn't work. The more developers we have, the more likely this is, and the more demands on our time, the more likely we need to reorder work and release one thing but not another. As a DB developer and DBA in a company 20 years ago, I built a process and forced us to reset the QA environment and redeploy B only (using a branch of code that stripped out A) for re-testing. This ensured that we tested what was going to be deployed. However, I find a lot of organizations can't do this or don't want to. They want to hope that a human and either extract all of B or strip out all of A and release partially tested code without issues. I find that to be a poor idea. In this era of regular staff changes, staff of varying quality, and the high complexity of software, this is asking for mistakes. With cheap hardware, virtualization, and the ability to provision copies of environments, we ought to do better. How do you handle this today? Depend on humans to not make mistakes? Hope for the best? Or follow a repeatable, reliable process that accounts for inexperience and human error? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Efficient Table Migration to a New Schema in T-SQL]( Parthprajapati from SQLServerCentral This article will explain why and how you can easily move tables to new schemas if the need arisees. [SQLServerCentral Article]( [Microsoft at the PASS Data Community Summit 2023]( Steve Jones - SSC Editor from SQLServerCentral Microsoft will be presenting a number of sessions at the PASS Data Community Summit 2023. Read about their plans for the event and register to come if you can. [External Article]( [SQL Server Fragmentation Impact with Modern Hardware]( Additional Articles from MSSQLTips.com Read this article to find out if you still need to worry about SQL Server index fragmentation with modern hardware. [Blog Post]( From the SQL Server Central Blogs - [Deleting a Git Branch–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA I had someone ask me recently about deleting branches. While I had known how to delete a local branch, I had to look up how to delete a remote... [Blog Post]( From the SQL Server Central Blogs - [SQL Server query runtime is not everything]( kleegeek from Technobabble by Klee from @kleegeek SQL Server query developers, listen up! Query execution time is not everything you should be worried about. You need to examine the parse and compilation time for each of... [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):  The EXCEPT Query I have a couple tables that are similar, but the data is different. CREATE TABLE Original ( productID INT , Productname VARCHAR(20)); CREATE TABLE NewVersion ( productID INT , Productname VARCHAR(20)); GO INSERT dbo.Original (productID, Productname) VALUES (1, 'PC Case') , (2, 'Motherboard') , (3, 'CPU') , (4, 'RAM') , (5, 'SSD'); INSERT dbo.NewVersion (productID, Productname) VALUES (1, 'PC Case') , (2, 'Motherboard') , (3, 'CPU') , (4, 'DRAM') , (5, 'SD'); How many rows are returned from this query? SELECT * FROM dbo.Original AS o EXCEPT SELECT * FROM dbo.NewVersion AS nv;  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) Multiple Encryptions I want to create a symmetric key with this command: CREATE SYMMETRIC KEY PIISymKey10 WITH ALGORITHM = AES_192 ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword' , ASYMMETRIC KEY HRProtection , CERTIFICATE MySalaryCert , ASYMMETRIC KEY HRProtection2 , PASSWORD = 'AnotherS$trinSD'; What happens when I run this? Assume that the asymmetric key and certificate exist in the database. Answer: This works fine Explanation: This works fine. You can encrypt the key with multiple methods of encryption. Ref: CREATE SYMMETRIC KEY - [ [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 [Troubleshooting a SQL Server Wait Resource Issue"]( - I'm encountering some wait resource entries in sysprocesses without specific details. Has anyone faced a similar situation? Any insights on how to decode or address this issue would be greatly appreciated. [help decoding a wait resource from sysprocesses]( - Hi After some help . I've got a bunch of wait resources from sysprocesses that don't include specific resource type i.e its not prefixed with KEY, or TAB etc This type of format 66:1:6666666 So I presume the first numbers are the databaseid, maybe the second number is file id , but I cant decode […] [What happens when an online index rebuild (not resumable) is cancelled?]( - TLDR: What is the expected behavior for an index rebuild in online mode (not resumable), which is cancelled on SQL Server 2017? Is it rolled back completely? left to be resumed? continues to cause overhead on writes? Details: Hello community! It has been a while since I posted. I have been away from SQL Server […] Administration - SQL Server 2014 [How to include newly system generated tables to SQL replication?]( - Hi there, I have this situation that I have been trying to get around for some time but I haven't been able to despite some considerable investigation. I have transactional replication between 2 servers, the standard run of the mill set up. I noticed after some time, the replication goes out of sync (or breaks) […] SQL Server 2019 - Administration [SSIS - "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.".]( - Hi All, SSIS error: Recently the following error "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". i occurring. The SSIS package is executed through SQL Agent using a Domain service account and running as a job for years No changes to SQL Server no changes to the fileserver permissions etc. 1st thing checked is […] [Replica stuck in resolving state during upgrade]( - Hi, Last week we tried to perform an upgrade on a 2 node sqlserver 2012 availability group. OS is windows server 2019. Cluster functional level is set to 9. We set secondary node to manual failover, assync commit, etc.. the secondary node was updated ok. On the primary, still sqlserver 2012, all was also good […] [SQL Service is not starting after upgrade...]( - Seems to be Windows is somehow decided to upgrade something in SQL Server and after that it can't start. SQL Service is starting correctly only with trace flag 902. How to fix the issue? Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 15151, state 1, severity 16. This is a […] [Can trigger execution be excluded only for a particular login?]( - Hi, I have an ERP application which connects to SQL 2019 database by an user - "vwuser". Whenever the users does transactions via ERP , data population happens. Very few people have backend access, but auditors are asking for tracking the data modifications that happen via Backend (say data corrections via Update / Delete). The […] [CMS server sync]( - I have an old and a new CMS server. I am trying to keep the CMS groups and registrations in sync. I am trying this with dbatools. Here is what I have, error at bottom.... Remove-DbaRegServer -sqlinstance (my new cms server) -Confirm:$false Remove-DbaRegServerGroup -sqlinstance (my new cms server) -Confirm:$false Export-DbaRegServer -SqlInstance (old cms server) -FilePath […] [can a DB user access that DB objects without a SQL server login?]( - assume this DB user (UserA) has no sql server login, not in any domain group that has a login. UserA is a windows user (user type) for Database A. UserA has only CONNECT permission to Database A. Can UserA without a login access any objects in Database A? if so, how? [Query related issue]( - I have a small query that runs in 1 sec. See an example below. Use DB1 GO select EMPLD, FirstName, Lastname from DB1..Employee where dept = 'IT'  But when I run the same query on the same server but in a different database, it takes 10x + more. Use DB2 GO select EMPLD, FirstName, […] Reporting Services [Dynamic Subscription query with parameters????]( - Hi,  I'm trying to create a query within a dynamic subscription with a parameter for a Start and End Date. The query will not validate when I try to use @StartDate and @EndDate Something like: WILL VALIDATE --- Select ID from mytable where date between '10/1/23' and '10/31/23' Once I change the date to […] Third Party Products [Is SQL Search 3 still supported?]( - I'm looking through my machine to see what I have installed and what I can remove. I found a product called SQL Search 3, which is by Red-Gate. Is it still supported? Article Discussions by Author [Handling a Cancelled Online Index Rebuild in SQL Server]( - I was wondering what the expected behavior is when an online index rebuild (non-resumable) is cancelled in SQL Server? Are there any potential risks or considerations to keep in mind? Through Mcdvoice homepage, customer or participant can give his honest opinion (positive or negative feedback) to improve product quality, service, store quality & norms. […] SQL Server 2022 - Administration [SQL Server - Kerberos Configuration Manager - Unable to connect]( - am currently working through replacing a development and production SQL server, moving from server 2012 to server 2022. These two servers have the SQL Service running as an AD account. Thus, I have gone through and configured the proper SPNs in Active Directory and configured Kerberos Constrained Delegation. Adding the SPN for the FQDN and […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -