The difference between BIN2 and Case-Sensitive collations in SQL (2024-03-15)

SQLServerCentral Newsletter for March 15, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [The difference between BIN2 and Case-Sensitive collations in SQL]( - [Automating BCP with PowerShell]( - [From the SQL Server Central Blogs - Friday Flyway Tips–Undoing Development Changes]( - [From the SQL Server Central Blogs - Webcast on SQL Server Administration]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [Cloning MSDB]( The Voice of the DBA  Growing Skills at Work Redgate released the results of their [State of Database Landscape]( recently, and I was part of [a webinar where Beca Parker, Ryan Booz, and I discussed the results](. As we were talking, one of the things that struck me was the high number of people who had picked skills as being a challenge for their organization. In a single environment, [skillset requirements and training were the number one challenge]( and it was listed as [the number one obstacle to implementing a DevOps approach](. While this industry is full of self-taught individuals who have spent time learning new technologies and tools, it's somewhat amazing that training continues to be an issue. Many organizations limit their training budgets and time allocated to employees while continuing to expand the number of technologies and platforms they use. This is one reason why I think changing database platforms to avoid licensing costs is unlikely to save you money anytime soon. Retraining staff and developing competence takes time. Perhaps this is also one reason why many companies look to the cloud, thinking that they can reduce the amount of upskilling needed by their staff if the cloud vendor manages the systems. That might be true, but there is a lot of knowledge is still needed about how the underlying technologies work, especially if employees are to efficiently write code or make configuration changes in the system. The cloud can be more cost-effective, but it can easily be less cost-effective. Knowledgeable employees can make a difference. I do see plenty of organizations subscribe to online learning, but often I find these courses good for basics, but not necessarily helpful for specific situations and just-in-time knowledge requirements for projects. There more custom training, or at least, group support, is needed to ensure that your staff is learning efficiently. Far too many people can't handle open-ended training from an online platform and complete more than one course. Training isn't enough, as there also needs to be some practical application of new skills in your environment. Practicing [katas]( as a group, coaching each other, and applying skills to actual projects while measuring results is important. It takes some commitment from both employees and organizations to get better. Far too often, I also see staff unmotivated to learn and grow, for a variety of reasons, but that's a different problem that we have to work to solve together. The world of technology in business is growing increasingly complex. We find more and more organizations adding technologies, especially new database platforms, and we lack skilled people to work with them. That's a constant challenge, but one that creates opportunities for you if you find ways to improve yourself. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [The difference between BIN2 and Case-Sensitive collations in SQL]( Jovan Popovic from SQLServerCentral Here you will learn about the key differences between the binary and SQL/Windows collations. You will see that even with all sensitivity flags enabled, SQL/Windows collation cannot behave the same way as the binary collations. [External Article]( [Automating BCP with PowerShell]( Additional Articles from SimpleTalk I was talking with a good friend that has an environment with terabytes of information and to create a homolog or dev environment to him is a pain. He comes to me with a solution : A PowerShell script that gets only the first 1000 lines of each table, creates the inserts and schema files [Blog Post]( From the SQL Server Central Blogs - [Friday Flyway Tips–Undoing Development Changes]( Steve Jones - SSC Editor from The Voice of the DBA I had a customer ask about undoing changes made by developers, similar to what SQL Source Control does. I had to do a little research to show how to... [Blog Post]( From the SQL Server Central Blogs - [Webcast on SQL Server Administration]( K. Brian Kelley from Databases – Infrastructure – Security I will be giving another Microsoft SQL Server-based webcast, this time on the administration of Microsoft SQL Server. The webcast is scheduled for March 26, 2024 at 1 PM... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Site Owners from SQLServerCentral Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Cloning MSDB Can I execute this code in SQL Server 2022? dbcc clonedatabase(msdb, msdb_clone) 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) Converting to Bits I have this code: CREATE TABLE BitTest( insertdate DATETIME DEFAULT GETDATE(), a INT) GO INSERT dbo.BitTest (a) VALUES (0), (1), (0), (2), (5), (0) GO ALTER TABLE dbo.BitTest ALTER COLUMN a BIT GO What happens when I run this? Answer: This works, and all 0s stay 0s, and all other values convert to 1s Explanation: This works, and all non-zero value are converted to 1s. Ref: Bit - [ [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 2016 - Development and T-SQL [Opening an SSIS package created in 2016 with a later version of Visual Studio]( - I have SSIS packages created in SS2016(i.e. 130). If I use a visual studio version newer than the SSIS package, will it convert the package to a later version? I would think if that's the case then i will have a problem running this upgraded SSIS Package because I'm still running SS2016. I would think […] Administration - SQL Server 2014 [SQL server disk block size formatted as 4KB runs slow]( - Hello All, One of my SQL server 2014 all the disk are formatted as 4KB. I see Microsoft recommends to have 64KB. I feel the query running slow and most of the query waits shows as two waits 1. PAGEIOLATCH_SH 2.CXPACKET and overall server wait also same. Please share your experience. SQL Server 2019 - Administration [Failover Cluster configuration in windows 2019 for 2 subnets]( - Today I am in critical situation for Production server build with failover cluster for 2 different sub nets with 3 nodes cluster. SQL1.domain.com xx.aaa.bb.cc1 SQL2.domain.com xx.aaa.bb.cc2 DR.domain.com xx.aa1.bb.zzz While validating a Failover Cluster over two member servers I get the following two errors: Connectivity to a writable domain controller from node SQL2.domain.com could not be […] [Server_principals sid changed compared to database_principals sid for AD-group]( - Yesterday I configured some new databases and granted a couple of AD-groups auth to perform their stuff. Today, the admin-group could not create objects in their database. Ad-group membership was OK Account was not locked out. Only thing I did after this initial setup is upgrade the SQL Server 2019 to CU25, upgrade the Windows […] SQL Server 2019 - Development [find the number of values less than the current row]( - Hi everyone I am working on a query where I need to find the number of values that are less than the current row's value going back X days. For this example, let use X =5 but it can be any number. Sample data: Explanation: Start at Day 11. Day 11 value is 42. I […] [over clause]( - Posting Error please ignore [how to speed this query up?]( - hi everyone I have a query I use at work. I use percent_rank to rank sales by company for the last 50 days. For example, if I run the query today then I want to look at the last 50 days of data (including today) and then rank the sales for those 50 days. So […] [Pre-defined filters for stored procedures]( - Hello all, I have defined a set of filters and assigned each set a FilterID. I want to pass this FilterID to a stored procedure and filter the results accordingly. My working example inserts the broadest set of data into a temp table and runs conditional deletes to get the appropriate results. Before I delve […] Reporting Services [Tablix shows only the first entry when several entries exists with the same time]( - Hi @all, I'm using a tablix in SQL Server 2019 Reporting Service to show some entries from a SQL database. It works fine and query is very simple. Select [TimeStamp], [Data1], [Data2], [Data3] From Table Where [TimeStamp] between @Start and @End  But there is an issue when the TimeStamp value is identical for some […] Integration Services [DB Native versus Win Authentication with SSIS Packages and Deployment]( - Hi there, I have done a migration of Informatica PowerCenter ETL project to MS SSIS project using Visual Studio 22 Everything is now working fine when I run the packages one by one within the VStudio on my laptop. The next step would be to deploy my project to the Sql Server SSIS Catalog. I […] SQL Server 2022 - Administration [facing deadlocks issue from last week]( - I am facing few deadlock issue recently and when i tried to debug the issue using the XML file generated from the Extended event, i was not able to figure out the exact issue. The text seems very generic to me. Below the content of the XML file […] [Alway on Availability group cluster less]( - Hi for dr purposes i need to setup an on prem db in replica with a db on azure on a iaas vm  i'll use sql always on availaility group in clusterless ag mode , if i need to test the secondary node do i have to do a failover? what about the fallback? […] SQL Server 2022 - Development [Table partitioning questions]( - We have state of the art latest and greatest managed SQL Azure MI Business critical. Table has 84 M rows, transactions table with data from 2019 to 2024 and beyond, I was asked to implement partitioning to improve performance and management , I read around and will use the GUI and implement partitioning by month […] [Analysis services tabular models - how to detect tables which have had errors]( - I am using a query (using TMSCHEMA) to derive the tabular tables to be processed. The aim is to process as much as possible in an iterative way and skip the ones which have an error i nthe processing run. Running the query and execute the processing works with a stored procedure. The query works […] [SQL Server Count Query]( - Hi All, I have a data in column A in where in need to achieve the count which I have mentioned in column B. Please find the attached excel dataset for reference. Thanks   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -