What happens when we drop a column on a SQL Server table? Where's my space? (2024-04-26)

SQLServerCentral Newsletter for April 26, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [What happens when we drop a column on a SQL Server table? Where's my space?]( - [SQL Logic]( - [From the SQL Server Central Blogs - 7 Best Online Advanced SQL Training Courses to Learn in 2024 and Beyond]( - [From the SQL Server Central Blogs - DevOps Book Recommendations]( - [T-SQL Fundamentals]( Question of the Day - [The Marked Transaction]( The Voice of the DBA  Good Luck is Needed with Old Versions Today we have a guest editorial from Grant Fritchey as Steve is away on holiday. This editorial was originally published on Feb 11, 2020. I’ve been extremely lucky in my career. I’ve either been in a job that required me to keep up with the latest technology, or I’ve worked for organizations that wanted to keep up with the latest technology. So, as each, shiny, new bit of functionality was unveiled, I had the opportunity to learn it and put it to work. That’s not the situation for everyone. There are people out there still struggling with most of their SQL Server instances on SQL Server 2008. I even ran into a person a couple of years ago who had several thousand instances of SQL Server 7 still running. I worked for an organization that lurked on eBay looking to buy old hardware in order to keep systems built in the 1970s running rather than take the time and trouble to upgrade those systems. So, I get it. You’re just not going to upgrade because, if it ain’t broke, why go to the time, trouble, and cost of fixing it? Why even bother upgrading at all? SQL Server 2008 is out of support, but it works. Windows 7 just recently was removed from support, but it works. Can’t you just run these things forever? Go to eBay and buy old hardware like my former employer if you have to. I suppose, for some, that will work. You’ll have SQL Server 2008 running for another 20 years or so, never getting a fully functional Extended Events suite of tools. However, for the vast majority of people, not only is this plan doomed to failure, but it’s probably going to fail sooner rather than later. A security vulnerability is going to be discovered and exploited at your cost. A new set of hardware is going to have incompatible drivers right after a failure of the old hardware. Something is going to happen, and now, instead of a planned upgrade, you’re going into emergency mode. I do understand not upgrading just because the new shiny has arrived. It’s not a fun position because the new shiny is new and shiny. However, it’s a perfectly sensible approach. Upgrade when you have a clear need and a viable path. On the other hand, I have a tough time understanding why you would refuse to upgrade once it becomes a dangerous proposition. Please, take a moment and tell me why rolling the dice and planning on good luck is the more sensible approach. Grant Fritchey [Join the debate, and respond to today's editorial on the forums](   Featured Contents [What happens when we drop a column on a SQL Server table? Where's my space?]( Cláudio Silva from SQLServerCentral You have dropped a column and wondering why you haven't recovered any space? Let's take a look. [External Article]( [SQL Logic]( Additional Articles from SimpleTalk In this article, I will discuss the history and thinking behind several types of logic that are typically associated with writing relational database code. [Blog Post]( From the SQL Server Central Blogs - [7 Best Online Advanced SQL Training Courses to Learn in 2024 and Beyond]( Tracy McKibben from RealSQLGuy - Helping You To Become A SQL Hero The world runs on data, and SQL is the key to unlocking its secrets. If you're ready to level up your SQL game, online training is a power move.... [Blog Post]( From the SQL Server Central Blogs - [DevOps Book Recommendations]( Steve Jones - SSC Editor from The Voice of the DBA I’m out today, coaching in Salt Lake City. However, I’ve been thinking about a few books after discussions with various customers and attendees at a few events. I wanted... [T-SQL Fundamentals]( [T-SQL Fundamentals]( Additional Articles from SQLServerCentral Master Transact-SQL's fundamentals, and write correct, robust code for querying and modifying data with modern Microsoft data technologies, including SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Marked Transaction I want to mark a transaction in the log as a recovery point. How do I do this in my code if I use the transaction, myTran? 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) Initial Config of tempdb What are the initial config sizes for the tempdb primary data files, secondary data files, and log files in SQL Server 2022? Answer: MDF: 8MB, NDF: 8MB, and Log: 8MB Explanation: The initial config values are all 8MB. Ref: tempdb - [ [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 2019 - Administration [given value of type String from the data source cannot be converted to type nvar]( - Hi, I have created a variable in Powershell to export to a SQL table. The variable looks like this $DateTime = $((Get-Date).ToString('yyyy-MM-dd-hh_mm_ss')) When I try to insert this into the table I get the following message: The given value of type String from the data source cannot be converted to type nvarchar of the specified […] [Problems Connecting to Sybase using ODBC Drivers]( - Hi All, I have recently migrated a sql database from an old server to a new one. This database is populated from an old sybase database. I have made the required changes to the SSIS job using visual studio and runs there fine. I have setup the ODBC DSN connectors and they test successfully. However […] SQL Server 2019 - Development [Match the String in a join]( - i have Two tables T1 , T2 Create table T1( Activity varchar (100)) Create Table T2 ( Activity varchar (100)) T1 table has data as below Activity (field) SendToUser% (data) T2 table data Activity (field) SendToUser-U12345 So , both tables should match ...because the string SendToUser% means "SendToUser"+"....." How can i use it in join? […] SQL Azure - Development [Tunning or alternative to PIVOT]( - Hi everyone, we have a table containing kind of key-value pairs, which are the columns of some entities. This is how master data is ingested in the platform. Then a query does some joins, filtering and finally a PIVOT to only output a row by entity, with all their columns denormalized. The query plan looks […] General [Identical SQL Jobs failing (SSIS package)]( - I have a SQL Server 2022 application running on 2 nodes redundantly, one of the jobs (SSIS) is failing on the primary node saying the service account can not find the specified file, the exact same job, works fine on the secondary node using the same service account (domain account). Job works fine when running […] Integration Services [SSIS package not parsing statement with exec (SP) with resultset]( - I have a package that loops through a bunch of SQL Servers and executes a stored procedure that uses temp tables. It was build in 2008 and I am now moving it to 2016 but having issues getting it to work in 2008 the OLE DB sql was simply "uspSystem_GetDatabaseInfo" That was giving me grief […] [SSIS Execute Package Utility Issue]( - Hi everyone, So I have a ssis package that performs various operations like import, export etc based on values provided in config files. Today I have added some more code in the script task that downloads a pdf file, converts it to excel format and then reads this file and perform ETL operations. It loads […] Design Ideas and Questions [EF Schema Deployment]( - Just looking for some background on if anything has changed around "code first" for building schema. I've always preferred designing first, then building the schema (usually in a database project) and handling changes to schema that way. I'm in the position now where there is a project where it appears the developers picked EF and […] [Designing two tables considering following scenario.]( - I want to design two tables considering the following scenario. Consider the image below:   I’m moving blue boxes (which shows up on screen after Show Options the button is clicked) from left to right (on the grid) after clicking Move Text Content! button. Here are the steps I follow: Select one option from […] SQL Server 2022 - Administration [Reporting Services Configuration Problems]( - Greetings! I am hoping to get some help trying to set up reporting services. 1st problem - when we load the Reports URL, it is requesting credentials to even get the dashboard to load. We have to provide an administrator account in order to get the URL to load. But once we do that, we […] [Backup to Azure blob storage via Maintenance Plan]( - Am trying to set up backups to a storage container on Azure government. If I backup a database using Tasks -> Backup database I am able to backup successfully; it generates the following script which works: BACKUP DATABASE [BLAH] TO URL = N' WITH NOFORMAT, NOINIT, NAME = N'BLAH-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS […] [Always On-manual failover - warning on possible data loss]( - Hi, I'm testing basic alwayson with SQL Server 2022 Standard on two servers (primary and secondary replica with an old Adventureworks database). The idea is to allow sql clients to continue to operate while one server is down for updating and rebooting (monthy windows and sql server updates). I'm testing the manual failover: Although the […] SQL Server 2022 - Development [How to use custom replication stored procedure for transactional replication]( - I previously posted a question about setting up replication with custom replication stored procedures (for delete especially) to allow for a longer retention period on the subscriber than on the publisher. The following two MSDN articles were referenced, and I thought I understood them. Transactional Articles - Specify How Changes Are Propagated Transactional Articles - […] [Contained availability groups error]( - Hello, I am testing the setup of contained availability groups. I am getting some strange errors while creating contained availability groups both through wizzard and with TSQL. I started with creating a contained availability group with the wizzard on MSSMS. Setup: I clicked the contained checkbox. The databases Meet prerequesites (FULL backup was made beforehand). […] [Seeding Contained availability groups]( - /    [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -