Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2 (2022-11-16)

SQLServerCentral Newsletter for November 16, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2]( - [Flyway Enterprise is here...]( - [SQL LIKE Statement for Various Text Patterns]( - [From the SQL Server Central Blogs - Moving dates to a weekending date]( - [From the SQL Server Central Blogs - Creating a STONITH resource for a pacemaker cluster on VMWare virtual machines]( Question of the Day - [The External Pushdown]( The Voice of the DBA  Daily Coping Tip Change your normal routine today and notice how you feel 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. Self-Service Databases When I first started work as a software developer, I knew that getting an environment set up where I could compile a project might take a few hours or a few days. The complexities of how people built software projects, the dependencies, and more were handled in a very immature manner. These days I can often download a project from GitHub and compile it in minutes, with many of the issues handled automatically with various tools such as complex config files and package managers. In the world of database development, I often find people struggle to get a database set up with a project. Whether you are coding in SQL, C#, or Java, many organizations haven't got a standard way of bringing up a database environment for developers. Some have a shared database for a team, which can be better, but can also lead to collisions of code as the pressure to get software built grows and team size increases. This leaves out the challenges of data, which can be changed by others, or lack the size/shape/skew to properly reflect what the production code has to handle. There are no shortage of bugs that come from incomplete data sets used in development that are unrepresentative of the live database. Do you have a way for developers to self-service the database assets they need? Whether this is a new dev database, a refreshed one from prod, or even a point in time from development. Is there a way developers can get a database into the state that matters for them. When developers are ready to work on a problem, the last thing we want is a significant delay to them getting started. One of the things DevOps stresses is that we want to reduce friction and tedious, manual work were possible. Let the computer manage the tedious things. This should be how we manage those database environments. We also want some consistency among all developers, QA, CI, and any other environment, to ensure we can reproduce problems to troubleshoot issues. The customers I work with that have the smoothest development processes, and best ROI from the resources spent on development, have a way to hydrate a consistent database environment for their developers. More importantly, the developers can self-service this. When a single developer, or a team, decides to reset their environment, they can do so, applying in-flight code changes from version control to bring back the database to the state it was previously in. Application developers do this all the time, as they restart their application. Removing their abliity to change code, restart the app, test it, and observe results easily would be crippling. Yet, we rarely reset dev databases, and often, don't have consistency between environments. Self-service is one of the key indicators of a mature software development organization. This is easily achievable for many app projects, but not simple for databases. Relatively few groups have figured out how to do this well, but those that do are much more productive and efficient than everyone else. Traits that are becoming more important in this highly competitive world with increasingly fickle customers. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQL Server Indexes]( [Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2]( David Durant from SQLServerCentral.com By this stage, you should be familiar with the basics of SQL Server indexes. We've discussed what an Index actually is, as well as some of the most common types you're likely to encounter. Now that we've seen some simple examples of how Indexes can be useful, we're going to delve deeper into nonclustered indexes, as we'll see how they can improve the performance of more complex queries. [External Article]( [Flyway Enterprise is here...]( Additional Articles from Redgate  [External Article]( [SQL LIKE Statement for Various Text Patterns]( Additional Articles from MSSQLTips.com In this article, we look at how to use SQL LIKE statement to find various text patterns contained within a set of text along with many different examples of how to use LIKE. [Blog Post]( From the SQL Server Central Blogs - [Moving dates to a weekending date]( sqlrunner from SQLRunner In publishing, and Im sure many other industries, we get data at both the daily level and the weekly level. To properly tie these two pieces of data you... [Blog Post]( From the SQL Server Central Blogs - [Creating a STONITH resource for a pacemaker cluster on VMWare virtual machines]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold A while back I wrote a post about creating a pacemaker cluster to run SQL Server availability group using the new Ubuntu images in Azure. Recently I had to...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The External Pushdown One of the options for query options available in T-SQL is the EXTERNALPUSHDOWN option. When is this used? 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 VALUES Limit What is the limit for rows in a Table Valued Constructor? Answer: 1000 Explanation: The limit is 1000 rows. Ref: Table Valued Constructor - [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 [Db is slow]( - Hi SQL Gurus, One of our production database is running very slow. I checked blocking , I didn't see any blocking. Also, check for running processes and look for any waittype. I see null. How to get more insights on a running sql server for a specific databases? What all can he checked in this […] [Datafile full, but it isn't a table]( - Hi all, I currently have a SQL Server 2017 with a filegroup full. I temporarily added a file as a workaround, but need to solve the issue. The problem is that it is not a table that is filling up the space, numbers are completely different (table size will make it currently around 10% of […] SQL Server 2019 - Development [Float]( - Hi, I am having problem with generating a Float which generates a output of adding 1 every second month. Currently, I have a solution of dividing the target by 365, but then I wont get the value to change exactly when the month starts. My data consists of date, employee id and target Hope one […] [Sql subquery poor perf - Need to rewrite to optimise]( - I have a sql query as below which takes endless time to execute. Any help in rewriting this query to better way would be appreciated greatly. Select PolNr , (select value from tblPolSpec d where m.PolNr=d.PolNr and d. Category='Holder' ) as HolderName , (select value from tblPolSpec d where m.PolNr=d.PolNr and d. Category='Status' ) as […] [Issue with converting Varchar to Numeric so I can add them]( - I have this so far . . . SELECT * INTO #tempValue FROM ( SELECT t.HTS_98_Value, t.HTS_99_Value, t.HTS_B_Value, t.HTS_A_Value, t.HTS_C_Value, t.Customs_Entry_Number, SUM(convert(numeric,replace(isnull(hts_a_value, '0'), '','0')) + convert(numeric,replace(isnull(hts_b_value, '0'), '','0'))) AS New --HTS_A_Value + HTS_B_Value) AS new --+ HTS_B_Value + HTS_A_Value + HTS_99_Value) AS Header_Val FROM #temp1 t GROUP BY t.HTS_98_Value, t.HTS_99_Value, t.HTS_B_Value, t.HTS_A_Value, t.HTS_C_Value, t.Customs_Entry_Number ) […] [Tools needed for designing SSIS package]( - Hi All, I am using SQL 2019 developer edition. I want to develop and test SSIS package, which tool I need to download and install it. Searched in google and found Visual studio , Azure studio and SSDT etc., Do I need all three. SQL Azure - Development [Azure Data Studio Intellisense Cache Notebooks vs SQL Files]( - Hi, I've noticed that my SQL notebooks will take a long time to refresh the intellisense compared to a .sql file. When I create a new object, no matter how many times I "Refresh Intellisense Cache", the object will not show up in intellisense until I close the notebook, reopen, reconnect, etc (and even then, […] General Cloud Computing Questions [Cloud Hosting]( - Hello. How much does it cost to host an Unifi controller in the cloud (Linode, Digital Ocean, AWS, Azure)? I'm an individual user with a VERY small setup (two desktops, iPhone, and an iPad). My Unifi setup will be: USG AC-Pro Switch 60W I'm wondering if anyone else with a small setup has real-world experience […] Azure Data Factory [Question about using Azure Data Factory with MySQL]( - I went ahead and signed up for the free Azure trial and created my first data factory last night. Right now I have 3 databases running on my computer. SQL 2019 Developer Edition Full Install SQL 2017 running in Docker container MySQL running in Docker assuming it's the most recent version. Using Data Factory as […] Reporting Services [Multi valued parameter is passing multiple values as one string]( - So I've got this report that has two date parameters and a multi choice var char parameter. When I choose 1 option it runs fine, but when I select more than one it failes because the parameter is being passed as a single string with no commas seperating the values.  I tried adding an […] SSRS 2016 [Definition of the Report is invalid when Deploying Report]( - I'm a little new to SSRS and I hope I can get some direction. I have a Report which consists of 4 different Subreports all in their own rectangle to facilitate page breaks (See attachment). Some of the Subreports were copied and slightly altered (new text) and some are used as is. They all run […] General [Unsecured site error in Edge Only]( - some of the pages showing unsecured in Edge even though the SSL certificate is binded (the same error doesn't happen in other browsers... The site is loading as IE5 compatibility mode) Can someone please help! its urgent. Suggestions [Plase fix the "Post Offset" problem.]( - Clicking on post notifications takes us to the correct thread but frequently takes us to the wrong post. I've determined that the issue is that posts on any given thread that are marked as "SPAM" are not counted in the "15 posts per page" count. Any chance of getting this fixed (along with the broken […] [Email Notifications is broken again.]( - Howdy folks... The email notifications for subscribed posts is broken and has been since about 26 Oct 2022. At least that seems to be when I stopped getting them. Others (on "The Thread") have confirmed this issue, as well. The emails for what followers post is fine, though. MySQL [Best method to migrate 8 GB database from MSSQL to MYSQL]( - All, Best method to migrate 8 GB database from MSSQL to MYSQL. The server is in windows 2019 with express installed. I have used workbench with ODBC - It says all the time not responding I have tried to install VS 2019 and 2022, both are not working in windows 2019 server to test SSIS. […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -