Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2 (2021-05-12)

SQLServerCentral Newsletter for May 12, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2]( - [Automating Azure DevOps with Logic Apps]( - [Take our survey to win a $500 Amazon gift card]( - [You are cordially invited to attend 2021 Virtual Data.SQL.Saturday.LA event!]( - [From the SQL Server Central Blogs - Query to find Nonclustered Index clashing with Clustered Index]( - [From the SQL Server Central Blogs - Modern Data Warehouse explained]( Question of the Day - [A Scope Question]( The Voice of the DBA  Daily Coping Tip Turn housework or chores into some fun form of exercise 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. Incident Response Data I was watching a PoSh session at the [PowerShell + DevOps Global Summit]( recently where a sysadmin had a series of scripts to run when there was a problem. One of these was [Rapid Response](, which gathers information from a machine(s) and stores it in a series of files. It's a grab bag of various items, but the data can be used to help determine what's wrong. Some of us have monitoring tools for our databases, and some don't. I'm wondering, in each case, is there a set of data you want or need when an incident occurs? Do you have separate types of incidents that require disparate data? Perhaps you respond differently to performance issues than security incidents than hardware problems and want different types of data gathered. I know that in the past, I've often had scripts I ran to respond to some issues, but not others. I've also depending on monitoring systems (bought or built), but usually they don't have all the information I need when something goes wrong. Capturing all the data I need in an incident is often too much to store for any length of time, but it is data that I need for specific issues. Having a series of automated processes that might start collecting data when an incident occurs, perhaps filtered based on an instance, database, user, or some other value, would be helpful. However, I think I'd need a lot of incidents to build the list of scripts myself for different issues. A crowd sourced series of scripts, developed by people responding to different problems, would likely be the best way to capture this information. I do see some good resources ([PDF](, [GH]() for certain types of problems, but in order for these to be useful to you, some knowledge and familiarity is needed. You need to know what scripts are useful in which situations. This is really the best reason for blameless RCA (root cause analysis) work after problems occur. If you have runaway blocking, constant security probes from unknown clients, or any other issue, it becomes important to analyze what happened and how people responded. Build up a protocol for how to respond and ensure that the knowledge is distributed to others that might need it. Practice running scripts and looking at information, perhaps even in a controlled replay of the problem. When an incident takes place, you'll be glad you are prepared. Whether it's small or large, a little practice will help you get through things more efficiently, and likely with less stress. 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. [Automating Azure DevOps with Logic Apps]( Additional Articles from SimpleTalk Azure Logic Apps can be used to automate steps like sending approval emails. Elie Bou Issa explains automating Azure DevOps with Logic Apps. [Take our survey to win a $500 Amazon gift card]( Additional Articles from Redgate Take the 2021 State of Database Monitoring survey and tell us about how you monitor your databases, instances and servers. By taking the survey, you’ll contribute to (and get early access to) the leading industry-wide report on database monitoring, and be entered to a prize draw for a $500 Amazon gift card. [You are cordially invited to attend 2021 Virtual Data.SQL.Saturday.LA event!]( Steve Jones - SSC Editor from SQLServerCentral We are reaching out to all people that enjoyed SQL Saturday in LA 2017, 2018, 2019, and 2020 events to let you know that the 2021 event is fast approaching. Only 30+ days remain until the June 12 Virtual Data.SQL.Saturday.LA event. Please register soon to reserve your spot by using hyperlink, register using a form below or just use a QR code. From the SQL Server Central Blogs - [Query to find Nonclustered Index clashing with Clustered Index]( Brahmanand Shukla from SQL Server Carpenter It’s being quite a long time when I made the last post. Actually, I was lacking a topic that should trigger me to write. Finally, I got one when... From the SQL Server Central Blogs - [Modern Data Warehouse explained]( James Serra from James Serra's Blog I created a short YouTube video (20 minutes) that is a whiteboarding session that describes the five stages (ingest, store, transform, model, visualize) that make up a modern data...   Question of the Day Today's question (by Steve Jones - SSC Editor):  A Scope Question If I have this code, what is printed out? temp = 10 def func(): global temp temp = 20 func() print(temp)  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 Number Subquery I have this data in a table: gapID ----- 1 2 3 4 6 7 8 10 14 15 16 17 If I run this code, what values are returned? SELECT gapID FROM gaps AS g1 WHERE NOT EXISTS (SELECT gapID FROM gaps AS g2 WHERE (g2.gapID = g1.gapID - 1)) ORDER BY gapID Answer: 1, 6, 10, 14 Explanation: This query finds the beginning of each contiguous sequence (or the beginning of each island). Ref: An Efficient Set-based Solution for Islands and Gaps - [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 - Development [Load a tabel using stored procedure.]( - Hello! I have a already created stored procedure named Membership which now is used to provide input for another application. Now the application is being replaced and I need to store the output of the procedure in a tabel in our database. The procedure gives me aggregated Member data (like in Year 2020, in the […] SQL Server 2016 - Administration [Daily Autocheck for SQLInstances]( - [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | SELECT ServerName | Format-Table -HideTableHeaders The above code is not returning all instances within a domain. There is not a lot available here. Are there any prerequisites to be performed(on servers) for the above class to list all instances within a domain ? Thanks [data file size difference]( - why is that when I run the sp_help (database name) or any query which gives datafile/log file name and size, it mentioned one value but when I go to the path where the data file is and check on property it gives another value I tried to restore the db but it still no change […] Administration - SQL Server 2014 [RO-AG With Mirror On Different Compat Level]( - Good Morning Gentlemen, We have a 2014 SQL Server that is in a read only availability group with another 2014 sql server. We recently discovered that one of the databases on the primary is set for 2008 (100). We would like to update just the secondary database compatibility to 2014, I was wondering if anyone […] Development - SQL Server 2014 [Conditional where clause]( - Hi , I wrote a stored proc where I need to evaluate a bit flag and if true append "NOT IN" clause. I don't want to use prepared statement. I tried following but it failed. Can I use Case or anyother statement to make it work ?  table Test (id int, name varchar(10); table […] SQL Server 2019 - Administration [Licensing question]( - Thinking of getting a perpetual standard licensing with certain number of cores. Was wondering what difference it makes it to get the standard licensing under software assurance. The perpetual should still be able to receive the cumulative updates and services packs, correct? Also, apart from memory and the cores cap on standard we should still […] [Data Analization]( - Dear Forum, I would like to ask the following question: We have 50 excel tables. Each table has about 30 attributes. All tables together comprise around 1,2 Million rows. --> Is there a ready-to-use solution which would allow us to analyze this data?  Thanks SSRS 2016 [Fuzzy lookup gives error with nvarchar(max)]( - Hi guys I have an Excel source file and I want to do a fuzzy lookup, matching it to a column surname(nvarchar(max), null) in a table in the database. This database column in fuzzy lookup is a unicode text stream (DT_NTEXT). I place a data conversion between the Excel file and the fuzzy lookup and […] General [Self-Joined to Find if Archived Versions of Record Exist w/o Duplicate Records]( - I have a table keeping client notes that can be updated and the previous note archived using an archived field. When a note is updated, the archived field is populated with the date on the old versions of the note. This allows us to see the current version (the one where archived is null) and […] Powershell [Best way to automate password reset of sql account]( - I'm looking to have a timer job every 90 days that runs a ps script that resets a sql account password to meet security requirements. The idea is to auto generate a random strong password (i have this part working), then encrypt and store the password into a cryptex file, so other applications can use […] [how to get directory contents to file]( - Hi all, I have a task that costs me a lot of time Maybe it is possible to automate this. I have a directory (Files) with a varying number of sub directories These subDirs are removed from time to time (back up), but I need to know what the filenames originally were. The dir looks […] Integration Services [SSIS File System Task to move csv Files between folders]( - I am trying to add a File System Task to an existing package to move csv files from one folder to another on my local machine. I already have Connection Managers setup for both folders. When I try to configure the DestinationConnection and SourceConnection it tells me that "Type of connection" MyFolderName (i.e my source) […] [Protection Level for SSIS Packages are missing]( - Hello Everyone I have SQL server 2019 with CU8 and Visual Studio 2019 I was given the packages from the data scientist and when i was looking at them i found that the Protection Level information was missing from the package properties window. Even the security section which holds the protection level is missing. Anyone […] PostgreSQL [Search ALL tables for a String Value using DBeaver]( - Hi Forum, I'm usually a SQL Server coder & I've started using DBeaver to do a value search on a Data Hub. I've looked around but I can't find the logic to do this & then Loop it to return the Table & DB name. There are some answers, but they;re past my understanding of […] Resumes and Job Hunters [Data/Database Career Options and Growth]( - I am a database professional who would like some feedback regarding some recent career choices I have made. At the beginning of the year, I became open to new opportunities and within the same week, had two job offers. One was a Database Developer role and the other was an ETL Developer. Although the Database […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -