Indexes: When Column Selectivity Is Not Always A Requirement (2023-01-02)

SQLServerCentral Newsletter for January 2, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Indexes: When Column Selectivity Is Not Always A Requirement]( - [Should You Use SQL Server 2022’s STRING_SPLIT?]( - [The Importance of a Good Data Model in Power BI Desktop]( - [From the SQL Server Central Blogs - How to Specify the Format of the Request Body of an Azure Function]( - [From the SQL Server Central Blogs - PASS Summit 2022 - Day 3 in Pictures]( Question of the Day - [Kicking off 2023]( The Voice of the DBA  Daily Coping Tip Start the new year off with something new – listen, watch, read something completely different. I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. No Time for Testing This editorial was originally published on 28 Sep 2015. It is being republished as Steve is on holiday. There is a bit of [a rant from John Welch on testing your data manipulation]( that I like. I think some of the impact is lost because the end shows an advertisement for a product that helps here, but the points made are good. We all want to test, we think it's hard, we don't have time, and our businesses live with the issues from limited testing. I'm not a fan of Test Driven Development, as John is. Usually this is because I'm not always 100% sure of the results I want or have been given. I've often been given a request to do x and as I get involved, I find that the requirements might be incomplete, or even wrong, and they'll change. As a result, I like to write a little code, get some idea of what I want to return or change, and then write a test that verifies what I've done is correct. It's a subtle difference, and maybe I'm doing TDD in the wrong order, but I like to get code, test it, then think about potential issues (which I might find as I write code) and write a few tests for the things that I've missed. However I do believe we need to test our code. We all do test our code, even if it's with a few before/after queries. What I don't get is why we don't just mock up a quick test that we can run in an automated fashion. It's not much more work, and then we can more easily re-run the test later to ensure any refactoring or optimizations we make continue to work as expected. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Indexes: When Column Selectivity Is Not Always A Requirement]( Mike Byrd from SQLServerCentral While preparing for my SQL Saturday Salt Lake City presentation, Climbing the B-Tree, I ran into one of the “word of mouth” facts that the first column of an index should be highly selective, i.e., it should only point to one or a few rows. This is not always the case and I’ll show you […] [External Article]( [Should You Use SQL Server 2022’s STRING_SPLIT?]( Additional Articles from Brent Ozar Unlimited Blog SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that’s the only thing they improved – there’s still a critical performance problem with it. [External Article]( [The Importance of a Good Data Model in Power BI Desktop]( Additional Articles from MSSQLTips.com  [Blog Post]( From the SQL Server Central Blogs - [How to Specify the Format of the Request Body of an Azure Function]( Koen Verbeeck from Koen Verbeeck This is a bit of a niche blog post, because you don’t actually need this to make your Azure Function work :). When you create a new HTTP-triggered Azure... From the SQL Server Central Blogs - [PASS Summit 2022 - Day 3 in Pictures]( @DBA_ANDY from Nebraska SQL from @DBA_ANDY Other PASS Summit 2022 Days - Pre-Cons | Day 1 | Day 2 -- Day 3 of Summit 2022 was "Community Day" with a "community keynote" given by the amazing Kimberly Tripp (@kimberlyltripp)...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Kicking off 2023 Which one kicks off the new year? 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) The New Year 2023 This weekend is the end of 2022 and the start of 2023. I hope you celebrate responsibly, but to get you started, I wonder how long it's been since I started working with SQL Server. I set up my first production database on Dec 31, 1991, 1800, in Virginia. Given I am in Colorado, how many days will it be for me when Jan 1, 2023 starts at midnight on Saturday? Note: Virginia is Eastern Standard Time on Dec 31 and Colorado is Mountain Standard Time. Answer: SELECT DATEDIFF(DAY, '1991-12-31 06:00:00 -5:00', '2023-01-01 00:00:00 -7:00') Explanation: The DATEDIFF() function can take datetimeoffset values. In this case, we use the start date and the end date as parameters (not end, start). We add -5 as the offset for Virginia from UTC and -7 for Colorado. Time zone info is in a DMV. Ref: - DATEDIFF - - sys.time_zone_info - [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 [SQL Server database monitoring tool.]( - Hi Expertise, Kindly suggest SQL Server database Centralize monitoring tools with historical reports for all target DB Servers ON-Prime and CLOUD based setup. Thanks  SQL Server 2016 - Development and T-SQL [Query help]( - I am trying to avoid writing a stored procedure and trying to create a view that will return the data in the second set of columns (F through I) in my attached data based off the first set of columns (A through D). Is this even possible and if so, how? Any help is appreciated. […] SQL 2012 - General [In Spark SQL, how can I optimise non-equi-joins?]( - I have two data frames that I need to link using a non-equi-join (also known as an inequality join) with two join predicates. One dataframe is a histogram DataFrame[bin: bigint, lower_bound: double, upper_bound: double] The second dataframe is a set of observations DataFrame[id: bigint, observation: double]. I need to figure out which bin of my […] SQL Server 2019 - Administration [Windows Login name change]( - Hi, I have a situation where a user has had their windows login name renamed in AD. I believe their SID stayed the same just the name changed. This causes some problem in SQL. When I try looking for the new user in SSMS (Domain\New_user) its not there. If I query sys.database_principals for the new […] [Execution of SP]( - One of the store proc failed as part of schedule job which is not a SQL agent job it is a part of nightly batch job. However, the logs from the batch job shows it failed with incorrect syntax. I don't have parameters to plug in and reproduce the issue. Is there a way to […] [NT Service\\MSSQL$SQLEXPRESS login failure alert every seconds]( - Hi All, After seeing log of login failures and attack, we have removed all unwanted logins from SQL including NT Service\MSSQL$SQLEXPRESS service account. It is a express none of the application, windows scheduler or any service using this login, still I am seeing error very often every seconds. Error: If the event originated on another […] [SQL merge replication setup from 2008r2 to SQL 2019 AWS]( - Hello all, I am trying to configure a merge replication between servers and getting an error when starting setting up the subscriber. The publication and distributor are set up but when configuring the subscription on another server I get this error after connecting and browsing the publication. SQL Server could not retrieve information regarding the […] [Resource Governor Classification Anomaly]( - So I've got this interesting situation that I can not find any documentation on. We have resource governor enabled, a classifier in place that appropriately assigns connections into workload groups. For example, user1 connects in, the classifier fires off and assigns user1 to WorkloadGroup-A Great! However, the strange part is... on occasion User1 will end […] [Updating .NET Core on SQL Server --> Dangerous?]( - I was recently informed by our Help Desk that there is a version of the .NET core on our production SQL Server 2019 which is going out of support. The Help Desk wants to upgrade it. In preparation for this upgrade project, I tried to research what aspects of SQL Server might be affected. I'm […] SQL Server 2019 - Development [Controlling the evaluation of scalar functions]( - I am facing a strange issue and, even though I found a solution, I wonder if a better solution exists. I have a table containing a multi-lingual dictionary in a xml field. A mock-up of the table can be created and filled using the following statement: CREATE TABLE #Dict (ID INT PRIMARY KEY, XmlValue XML) […] [Embadded Select in another Select and using apostrophes / quotes]( - Hello, I have problem with below script. I'm trying to run below script on few servers in one go. I would like to firstly check if this procedure already exist, if yes - do nothing, if no - create. IF (OBJECT_ID('[dbo].[sp_db_files_sizing_info]', 'P') IS NULL) BEGIN DECLARE @sql NVARCHAR(MAX) SELECT @sql = ' CREATE procedure [dbo].[sp_db_files_sizing_info] […] SQL Azure - Development [Azure DevOps and branches]( - Hi all  Firstly, full disclosure, we're only getting started with this so late to the party (and complete novices).  Tools we're using (not just for DevOps):- SQL Clone SQL Compare SQL Prompt SSMS (V18)  We're just getting started with Clone and found it to be very useful. The bit that's not so […] Azure Data Factory [Please help , how to write data in data lake ?]( - I want to read data from json file and write in a data lake by azure data factory, so can anyone tell me the procedure I am new to data engg field, Thanks in advance SSRS 2016 [About changing database using a parameter]( - Hi, I need to change the DataSet source database. I've created a parameter name "database" and the I've create a DataSource like "DataSource=server;initial catalog=" & Parameters!database.Value Then I can choose the parameter (database) from a dropdown. Everything works if I run the report from the ssrs web page. But If I set the parametr programmatically […] Data Transformation Services (DTS) [C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Tasks folder]( - I have installed Visual Studio Community 2019 and I'm collaborating with another employee but I do not have the same version as her. What is the VS or DTS version that installs the libraries for this folder C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Tasks? I have all other versions 110, 110, 120, 130, and 140.  Is […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -