More On Column Order Indexes – Part 2 (2020-12-11)

 SQLServerCentral Newsletter for December 11, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [More On Column Order Indexes – Part 2]( - [SQL Developer – A great tool and better ways to use it]( - [Database Updates: Rolling Back and Fixing Forward]( - [From the SQL Server Central Blogs - Basic JSON Queries–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Building a Raspberry Pi cluster to run Azure SQL Edge on Kubernetes]( Question of the Day - [The Git Editor]( The Voice of the DBA  Daily Coping Tip Leave a positive message for someone else to find 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. Reflections It's nearing the end of the year, and usually this is a time where I look back at the current year and then forward to the new one. I'll do some of that from a technical standpoint the last week of the year, but this week I wanted to do this from a personal standpoint. I read Jen McCown's "[Reflections Not Resolutions](" recently and it stood out to me as a way to look back at my life and career. Jen takes a moment to think about how her life and career may have gone, without much judgment, but with some appreciation. A positive view. For me, I think professionally I turned inward a bit this year. Without events to go to, no travel, and struggles to cope at times, I could view my professional year like this: - I didn't travel at all for work, but I did keep presenting at virtual events, and even learned to enjoy the experience, something I've disliked in the past. - I am amazed that I've written over [180 coping tips]( as part of the Redgate Community Circle, and used them to help me maintain productivity. - I struggled with maintaining connections with individuals and teams at Redgate, but I learned to appreciate how valuable those connections are. I am also looking forward to finding ways to improve this. Personally, I found some insightful things. - The only airplane trip I had this year was before the pandemic, getting family together for my wife's birthday in Las Vegas. Despite the effort, hassles, and money, I see the value in celebrating events together - I learned to listen, sympathize, and allow my kids to struggle with virtual school while just enjoying the chance to be with them - Coaching is a real joy for me. Having the spring season cut short, and next year's season move was a challenge, but I really have enjoyed the limited opportunities I had to work with 2 groups of young athletes. - I rode horses more this year than ever before, acquiring a small modicum of skill, but enjoying the time to be with my wife in nature. I'll save my wishes for another day, but it was good for me to think about the losses, changes, gains, good times, and bad as I tried to put these thoughts down. I hope you take a few minutes and try it as well. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [More On Column Order Indexes – Part 2]( Mike Byrd from SQLServerCentral.com Continued analysis of index architecture with revised clustered index. Part 2 of Column Order Indexes [SQL Developer – A great tool and better ways to use it]( Additional Articles from SimpleTalk SQL Developer is a popular free tool for working with Oracle databases. In this article Srinivasan Suresh explains several features of SQL Developer that can make you more productive. [Database Updates: Rolling Back and Fixing Forward]( Additional Articles from Redgate Phil Factor's take on the best ways to reverse or accelerate your way out of the problems caused by a failed database update. From the SQL Server Central Blogs - [Basic JSON Queries–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Recently I saw Jason Horner do a presentation... From the SQL Server Central Blogs - [Building a Raspberry Pi cluster to run Azure SQL Edge on Kubernetes]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold A project I’ve been meaning to work on for a while has been to build my own Kubernetes cluster running on Raspberry Pis. I’ve been playing around with Kubernetes...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Git Editor I want to enable emacs as my editor of choice when using git for commit messages for all repos. How do I do this? 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 TUF File in Log Shipping When does SQL Server create a TUF File in Log Shipping? Answer: After each restore when the secondary is in standby Explanation: This is the file created in log shipping when the secondary is in STANDBY mode, used for the undo before the next log restore. Ref: What is a TUF FILE? - [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 [Error SSMS multisubnet failover]( - Good morning all , I have an on alwayson listener in multisubnet mode with 3 IP inputs if I try to connect to the listener even with the multisubnetfailover = true option I have this error [SQL Server Performance Counters]( - Here's a question that feels like I'm asking if a tree falls in the woods and nobody is there to see it, does it make a noise? With that - Performance Counters (SQL Server specific ones), do these run in the background the entire time? Or does starting up Perfmon and tracking them start them […] [SQL Server Mirroring - Principal cannot set endpoint to Mirror]( - I followed the below guide & I am stuck on the very last step where you set the endpoint to reference the mirror server. The result is the same even if I use SSMS. I am able to telnet successfully on port 5022 from principal to mirror. I am also able to telnet successfully on […] SQL Server 2017 - Development [Bulk Insert - An unexpected end of file was encountered in the data file.]( - Dear Group: I am trying to perform a BULK INSERT using the code below. However, this is causing the following errors: I know the problem, but not sure of how to solve for it (if it can be solved with BULK INSERT). The issue is that our tech department's standard is that every file has […] SQL Server 2016 - Administration [SQL Server Load]( - Hi, The application team is pushing more than 7 lakhs records per day to SQL Server. The SQL Server version is 2016 with ALWAYS ON. During one point of time the SQL Server hungs and I need to failover manually. How much load can SQL Server withstand How much connections can it take at a […] SQL Server 2016 - Development and T-SQL [How to aggregate Dimension values properly]( - Ahoi, im trying to deal with a user request to combine 2 values of a column based on values of a different attribut. I managed to make it work like this, creating 2 hidden memembers and combining those in my cube script. --Hidden 1 CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop Webshop] AS ([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[Webshop]), VISIBLE = […] SQL 2012 - General [questions about sys.sysprocesses/sys.dm_exec_sessions and sys.dm_exec_requests]( - I have some questions  about sys.sysprocesses/sys.dm_exec_sessions and sys.dm_exec_requests, please kindly give your guidance and comments, thanks! 1. under Microsoft SQL server Management Studio, when I execute select * from sys.dm_exec_sessions and select * from sys.sysprocesses, they have same pieces of records, so spid of sys.sysprocesses is the same thing with the session_id of sys.dm_exec_sessions, is […] SQL Server 2012 - T-SQL [Converting minutes to hh:mm:ss]( - I'm trying to develop a formula to convert minutes (my raw data) to hh:mm:ss format. E.g.: 526.30 minutes = 31,578 seconds which should read 08 hrs. 47 minutes and 8 seconds or 08:46:08 ~ I think (?) SQL Server 2019 - Administration [How to mantain index in SQL and size of Database]( - Hi everyone, I'm trying to figure out how to manage the maintenance of the indexes I just created in some tables of my database. Once I run the Rebuild, the size of the database increases and by shrink, the indexes are fragmented again. Is there a way to clean up the indexes without affecting the […] [Interpreting Index Usage Stats on an Availability Group]( - I've been querying sys.dm_db_index_usage_stats on the nodes of an availability group, and I've noticed an exponential difference between the number of user_seeks on the primary node versus either of the secondary nodes. On the table I'm analyzing, the primary node has many indexes with user_seeks greater than 100,000, whereas on the secondary nodes not one […] SQL Server 2019 - Development [All month/yr when no data across different column combinations.]( - was hoping to get some help as it appears I'm not close. Trying to get all year/mo combinations even when there's no data...based on year/mo, Site, Dept and Cat. Appreciate any help!  create table #Date_Dim (Date date ) Insert into #Date_Dim values ('2020-01-01') ,('2020-01-02') ,('2020-01-03') ,('2020-01-04') ,('2020-02-01') ,('2020-02-02') ,('2020-02-03') ,('2020-02-04') create table #Data (Date […] [Ideas for Auditing]( - I'm currently looking to design some auditing on my SQL application and am wondering what would be the best way. Basically when a user Inserts or Updates I would like to capture what the previous value was and what the new value is. This would be across about most of my tables and could be […] Azure Data Factory [Set Variable to lookup first row value with white space in property name]( - Hi, How can I set the value of a variable from a Lookup activity when thw property name hase a white space? I'm trying to do it this way but fails: "value": "@activity('LookupMaxRecordId').output.firstrow.Record ID#", Find the complete code below: Thanks! ************* { "name": "pipeline8", "properties": { "activities": [ { "name": "LookupMaxRecordId", "type": "Lookup", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false […] Reporting Services [Query in SSRS report, double hop to linked server]( - I have 3 (old) existing SSRS reports that were developed and deployed on Server A. They each use the same 2 shared Data Sources (RD and DR). 1 of the Data Sources(DR) is a linked server, Server B. The reports have queries against the same datasets. 2  of the datasets  use the shared […] Data Transformation Services (DTS) [vbs failing]( - This vbscript for Excel that opens a pipe delimited file is failing srccsvfile = Wscript.Arguments(0) destxlsfile = Wscript.Arguments(1) Set objExcel = GetObject(,"Excel.Application") objExcel.Visible = false objExcel.displayalerts=false Set objWorkbook = Workbooks.OpenText (srccsvfile, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|") Set wkbTemp = ActiveWorkbook Set objWorksheet1 = objWorkbook.Worksheets(1) It's failing on the OpenText […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -