An Alternative (Better?) Method to UNPIVOT (SQL Spackle) (2024-07-26

SQLServerCentral Newsletter for July 26, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [An Alternative (Better?) Method to UNPIVOT (SQL Spackle)]( - [Migrate a SQL Server Database to a PostgreSQL Database]( - [From the SQL Server Central Blogs - MVP No more, But It’s OK]( - [From the SQL Server Central Blogs - T-SQL Tuesday #176: One piece of advice for Past Steve]( - [The Unicorn Project]( Question of the Day - [Internal Checkpoints]( Featured Script - [SQL Server Admin Role Alert Script]( The Voice of the DBA  I may need to re-think the use of table variables Today we have a guest editorial as Steve is out of the office. This editorial was originally published on Dec 11, 2018. I am not sure how many of you use table variables. I would presume many of you that have used them and got burned by them in the end. At first look, a table variable seems like a great way to use a sub set of data that you may need to join to or update in a SQL process. The initial testing goes well and everything seems to perform great. Then the code goes to production, the table variable ends up with way more data than you expected and the performance issues begin. If you aren’t familiar with table variables, they are similar to temp tables, except you don’t need to create and drop them like temp tables. You use a declare statement to define the table variable. There are some downsides to table variables. You cannot create non-clustered indexes, or create constraints, or default column values on them. You also cannot create statistics on them. These detractions aside, they can still be useful in the right situation. I don’t know how you are, but once I have had a bad experience with something I tend to shy away from it in the future. It has come to the point for me that, I won’t use a table variable unless I am extremely certain that the total number of rows in the table variable will only be a few hundred at most. Of course, this self-imposed limitation also limits their usefulness. I recently came across an article by Brent Ozar, discussing how table variables have changed in SQL server 2019. If you didn’t get a chance to read it, I would recommend it, you can see it [here]( He discusses how the current cardinality estimation for table variables is way off. In his example he has over one million rows in the table variable, but the query plan thinks there is just one row. You can see how that might cause some performance issues. He then shows the same example in SQL Server 2019 and the row estimation still isn’t perfect, but it is much better and so is the performance. So now I have a bit of a dilemma. After all of these years, having bad experiences with table variables, I may need to re-think using them in appropriate situations. Of course, there is still the issue of when will my company actually be up and running on SQL Server 2019, but that is a different discussion all together. At some point, I will be using SQL Server 2019 and table variables will be more or less fixed. I guess I will consider using them, it will just feel a bit strange, putting this tool back in the tool belt. How about you? Have you been burnt by bad performance of table variables? When you get up on SQL Server 2019 will you consider using them again? bkubicek [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [An Alternative (Better?) Method to UNPIVOT (SQL Spackle)]( Dwain Camps from SQLServerCentral.com Normalizing or UNPIVOTing data may be improved by using this lesser known approach in SQL Server 2008 or later. [External Article]( [Migrate a SQL Server Database to a PostgreSQL Database]( Additional Articles from MSSQLTips.com Learn about various options to migrate an entire SQL Server database to a PostgreSQL database. [Blog Post]( From the SQL Server Central Blogs - [MVP No more, But It’s OK]( Tracy Boggiano from Database Superhero’s Blog Embracing Limitations: Finding Strength and Purpose in Hard Times For four years, I was honored to receive the MVP award for my work in the The post MVP No more,... [Blog Post]( From the SQL Server Central Blogs - [T-SQL Tuesday #176: One piece of advice for Past Steve]( Steve Jones - SSC Editor from The Voice of the DBA I almost missed this month, so this is also a good #SQLNewBlogger post. I thought about it for a few minutes as I ate breakfast at my desk and... [The Unicorn Project]( Site Owners from SQLServerCentral In The Unicorn Project, we follow Maxine, a senior lead developer and architect, as she is exiled to the Phoenix Project, to the horror of her friends and colleagues, as punishment for contributing to a payroll outage. She tries to survive in what feels like a heartless and uncaring bureaucracy and to work within a system where no one can get anything done without endless committees, paperwork, and approvals.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Internal Checkpoints Certain internal SQL Server actions cause internal checkpoints. Which of these actions does not cause an internal checkpoint? 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) SQL Server on Linux Restrictions Which of these features are not available on SQL Server on Linux? (choose 2) Answer: Filestream, Merge Replication Explanation: Filestream and merge replication are not supported. CLR assemblies marked as safe are, but unsafe or external access are not. SQL Agent is available. Ref: SQL Server on Linux Unsupported Features - [ [Discuss this question and answer on the forums](  Featured Script [SQL Server Admin Role Alert Script]( SQLShark from SQLServerCentral The below code will send an email alert when someone is added to the SYSADMIN Role on your SQL Server set nocount on select Convert(varchar(35),@@servername) as 'Server_Name', Convert(varchar(25),[name])as 'Name', Convert(varchar(25),loginname)as 'LoginName', sysadmin, --hasaccess, Convert(varchar(25),dbname) as 'Default_Db', Createdate=Convert(varchar,createdate,100), [Updatedate]=Convert(varchar,updatedate,100) from master..syslogins where sysadmin = '1' and updatedate > Getdate()-1 --and sid != 0x01 --WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -7, GETDATE()); If @@rowcount = 0 return else Begin Print 'Worked' Declare @srvname varchar(55),@subject1 varchar(255) Select @srvname = @@servername DECLARE @xml NVARCHAR(MAX) DECLARE @body NVARCHAR(MAX) SET @xml =CAST(( select name AS 'td','',loginname AS 'td','',sysadmin as 'td','', dbname AS 'td','',Convert(varchar,createdate,100) AS 'td','',Convert(varchar,updatedate,100) AS 'td' from master..syslogins where sysadmin = '1' and updatedate > Getdate()-1 and sid != 0x01 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body =' Changed SQL SA Roles on Server '+ @srvname +' ' SET @body = @body + @xml +' Name LoginName SysAdminBit DefaultDb CreateDate UpdateDate  ' Set @subject1 = 'Debug Test SQL Sa Role Account Change Report on ' + @srvname EXEC msdb.dbo.sp_send_dbmail @recipients =N'someone@emailaddress.com', @body = @body,@body_format ='HTML', @subject = @subject1 , @profile_name ='Your_Mail__Profile' End [More »]( 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 - Development and T-SQL [Deadlocks with UPDATE statements using serializable transaction isolation level]( - We are seeing frequent deadlocks occurring due to a particular stored procedure that is using the SERIALIZABLE transaction isolation level. The stored procedure is essentially trying to ensure that the same reference number (concatenated from multiple fields) is never returned more than once. CREATE PROCEDURE dbo.sp_GenerateNextNumber ( @SequenceKey nvarchar(10), @ReferenceNumber nvarchar(25) = NULL OUTPUT ) […] [Error while Simultaneous Switching and BCP in same table]( - Hi, Our application is created in VC++. We have House keeping process in which the data from main table A is transferred to another table B using Switching method(yes both table has partition). the data in table A is inserted using BCP. The records get successfully inserted. Now when the insertion process is going on […] SQL 2012 - General [Behaviour since moving to Azure]( - Hello, all, Recently we have migrated a few AGs up to Azure. These are fairly old SQL 2012 servers, running in a 2-node cluster. We've been getting sporadic occurrences of WSFC errors, 41000 and 41005. Seems to a resource issue according the those error messages. The nodes are well-resourced and basically replicate what they were […] SQL Server 2019 - Administration [Unable to add the database back into AG]( - I took the full backup of the DB Backup database MyDB to disk = 'D:\backup\MyDB_Full.bak' with compression, copy_only, stats = 5 Restored on the secondary with replace. Restore was successful. When I tried to add the DB into AG, it threw an error. the mirror database has insufficient transaction log data to preserve. I then […] [AG-Group primary during reboot]( - New to ag groups. I have a 2 node aggroup. We are doing maintenance tonight and plan to reboot the secondary and have it come up and then do the primary. I am wondering what happens when the primary is rebooted with out triggering a failover to the secondary?  Does the listener move to […] [Sessions and CPU Threads]( - Hello everyone, I've got a question when it comes to best practices about application sessions in SQL Server. When your company developers write code and create applications to connect to SQL Server, and these sessions despite being connected to the SQL Instance for a few days ("connect time" from sys.dm_exec_sessions) is (2024-07-01 01:00:00) but the […] SQL Server 2019 - Development [error in both ssis and ssms - something about losing connections - new error]( - hi for about 4 or 5 days now, i've been seeing various connections (to our dw server) issues in ssis (excel to sql) under vs 2022 AND SSMS. the ssis error is shown below. in ssms it looks like this ...  The connection is broken and recovery is not possible. The connection is marked […] [How do I get a certificate for a SQL Server database?]( - I'm working on a new application at home, using SQL Server 2019 Developer Edition. I'm trying to scaffold a DBContext to my local database, but am getting this error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain […] [Summarize the proportions stored in the table]( - CREATE TABLE dbo.tab1 ( idrow INT NOT null IDENTITY, group1 VARCHAR(10) NOT NULL, c1 NUMERIC(10,2) NOT NULL, c2 NUMERIC(10,2) NOT NULL ) GO INSERT INTO tab1 ( group1, --group c1, --numerator c2 --denominator ) VALUES ('1', 1, 2), ('1', 2, 1.4), ('1', 3, 5.2), ('2', 1, 0.6), ('2', 4, 0.5), ('2', 3, 0.9) to store […] [Sum Nested Join Help]( - Hello all, I have a query where I am trying to do a left join to gather a sum and this one seems not simple. Here is what I have: SELECT C.CustomerId, C.TargetQty, C.GroupId, C.AcctCd FROM Customer C This is what I am needing to do and need the help. I have another table called […] SQL Azure - Administration [Copy Logins from on prem to Managed Instance]( - Hello, I try to migrate SQL on Prem Instances to SQL Managed Instances. I'm using the DBA Tools Start-dbaMigration. Everything is working, expect the migration of the Logins. I get the error: "Domain\User is a Windows Login, not spported on a SQL Managed Instance" We Sync the Users to the Azure with AD Connect so […] SQL Server 2022 - Administration [Use Polybase with ODBC to create external table]( - I'm trying to use the installed Polybase service on an SQL 2019 server to create an external table by using and ODBC DSN. The connection of the DSN is to a fairly exotic BBj server that hosts 3 databases. Somehow I just do not seem to get the proper syntax for creating the external table. […] SQL Server 2022 - Development [Issues adding and updating a column]( - Hi all  I'm hoping someone will able to say "you're an idiot because....." on this one.  We download a database but we have to add a column to a table and then update it. The code to add/update is as follows: IF NOT EXISTS ( SELECT * FROM UK_Health_Dimensions_New.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_SCHEMA= 'ODS' […] [how can vs see SSIS under my regular user id but not my admin?]( - Hi, we run vs 2022.  I'm stumped how when i run VS as admin i cant see ssis after hitting create new project unless i want to import ssis or tabular.  but under my regular id i can see new ssis, import ssis, import tabular and new ssrs after hitting crate new project.  its been […] [SSIS execute powershell fails with exit code 1]( - I'm trying to execute a simple, test powershell script (which works fine when I right-click it and run with powershell, .ps1 file) from SSIS. My ExecuteProcess task has these parameters: Executable: PowerShell.exe Arguments: -F "D:\Users\Folder\Working Files\Teton Information\TestPowershellScript.ps1" I get back exit code 1, failure. What am I doing wrong ?? I've tried it with and […]  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. [RSS Feed]( [Twitter]( ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -