How much will it cost or save to rebuild that index? (2021-05-28)

SQLServerCentral Newsletter for May 28, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How much will it cost or save to rebuild that index? (SQL Oolie)]( - [Constraints and declarative referential integrity]( - [Introducing the PASS Data Community SUMMIT]( - [From the SQL Server Central Blogs - Nerd Journey Podcast with Nick Korte]( - [From the SQL Server Central Blogs - Creating a configurable SSMS script]( Question of the Day - [Finding an Open Alias]( [Redgate Community Circle]( Community Circle  Together we learn  [Visit now]( [Redgate logo]( The Voice of the DBA  Daily Coping Tip Write down a place or event that you want to attend in the future 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. Working Predictable Hours I was listening to Kendra talk recently and she said this: [Working predictable hours makes me better at my job](. Actually, she said she was not better at her job by working longer. Kendra has been very successful, she's driven and a bit of a workaholic. I used to think I was, but after spending a few years at Redgate with her, she can outwork me these days. Of course, she has a bit of youth on her side. We both, however, realize that more work isn't sustainable, or even good for either the employer or employee. There is plenty of research that shows working above 50 hours constantly makes you worse at your job, at least when you are doing thought work. For more physical work, your body breaks down, and likely you will struggle with health as you near retirement. Kendra has [a good list for setting boundaries]( in her life. Many of these are things that I've implemented over the years in my life. In the last few years I've gotten rid of many notifications that interrupt life, as well as set Do No Disturb hours on my phone. Rarely have I missed anything important, and I've avoided plenty of early am "quick questions" from the UK. Apparently they are as bad at calculating time zones as I am. At the same time, I like unpredictable schedules, but fairly predictable hours. I don't like a rigid schedule, and I try to ensure if I have standing items, they are on a few days so I have flexibility on other days. I like being able to take breaks during the day to go to the gym or work on something around the house. I certainly like the freedom to stop working on a Tuesday if I don't feel productive and making up time on Saturday or Sunday. This past year has really been a very predictable schedule for me and others because we couldn't go many places. We woke up, walked to another part of our house, worked, and then repeated that over and over. Some of us did a better job of learning to stop working after certain hours, and some did a worse job and spent more time working. No matter what type of structure you use, remember that you need to take care of yourself. Your physical and mental health aren't worth sacrifice for an organization. While you do owe your employer an honest day's (or week's) work, you also owe yourself a balance. Do your best work, which also means limiting how much you tackle every week. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [How much will it cost or save to rebuild that index? (SQL Oolie)]( Jeff Moden from SQLServerCentral What's the true and permanent cost of lowering the Fill Factor of an index in SQL Server? It's a lot more than many people think. 9 year SQL Server MVP veteran, Jeff Moden, demonstrates how to calculate the extra space that will be used by lowering the Fill Factor and saved by increasing it. [Constraints and declarative referential integrity]( Additional Articles from SimpleTalk Database systems have powerful features built right in that can ensure the integrity of data. In this article, Joe Celko discusses keys and constraints. [Introducing the PASS Data Community SUMMIT]( Additional Articles from Redgate Join the world’s largest gathering of data professionals at this year’s free online PASS Data Community SUMMIT. Connect and network with 1000s of your peers and learn from some of the brightest minds in the data industry. This year’s must-attend online event is completely free, so sign up now to be notified when registration opens. From the SQL Server Central Blogs - [Nerd Journey Podcast with Nick Korte]( kleegeek from Technobabble by Klee from @kleegeek I’m thrilled to have recently recorded a podcast with Nick Korte from Nerd Journey. He recently recorded a different podcast that talks about the journey from being an IT... From the SQL Server Central Blogs - [Creating a configurable SSMS script]( Kenneth.Fisher from SQLStudies Several times over the last few weeks I’ve gotten a request to create a new work database. The individuals from ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Finding an Open Alias If I want to assign an alias in my PowerShell environment, what command tells me the aliases that are in use in my system? 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) Combining Python Lists I have this code: a = [1, 3] b = [2, 4] c = [(x,y) for x in a for y in b] # nested iterators print(c) What gets printed? Answer: A single list of 4 tuples containing (1,2), (1,4), (3,2), and (3,4) Explanation: This comprehension produces a single list, but with the two for loops, we end up with tuples that have each item in the first list combined with each item in the second list. Ref: Data Structures - [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 [Moving Listener and Cluster AD objects]( - Hi our Ad administrator wants to move all the listener and SQL cluster objects into their own OU, even for live production servers. I'm fine with that but can anyone confirm whether moving the AD object will have an impact on the SQL servers. My gut says no it won't but they're production servers Cheers […] [Account the SQL service is Locked]( - Good morning all , Does the fact that the Active directory account for starting the SQL service is locked does this cause problems with the functioning of SQL server thanks [Find rogue process updating statistics]( - I have a 3rd party app and DB that requires all stats be updated using full scan, 100% sample rates. I have a maintenance plan to do this every weekend, but have found that something is erasing my work by updating stats with a small sample, less than 1% on tables with 100+ million records. […] SQL Server 2017 - Development [Parse a very large delimited string]( - Trying to parse a very basic table with 2 colums, and ~9,000 rows. Columns are ID and delimited_string. ID is a varchar (50), and delimited_string is a varchar (max). The max len of the delimited_string is 313,895 characters. The delimiter is a semi-colon. I've tried a bunch of different methods, but can't get the query […] [Updating schema reference automatically in stored Procedures]( - Requirement: Need to copy all objects (tables, fn, stored Procedures) from default dbo schema to new schema. This new schema is created programmatically on user action from the front end. Problem: Copying the objects is straightforward for tables. For stored procedures and functions also it is not too bad to make a copy. My issue […] [Generate scripts of creating multiple external tables]( - Hello, we are creating prod environment and need create multiple external tables (in Azure SQL) in PROD. How can I generate the script of creating external tables at once in lower environment? instead of clicking 'Scripts table as' one by one. Thanks in advance. SQL Server 2016 - Administration [TDE Understanding]( - Hi All I hope to get some clarity on a TDE configuration I am working on. I have multiple PROD servers and 1 QA server where we restore PROD to on a regular basis. Each server will have its own master key obviously and its own certificate. Question - Does one server certificate service all […] SQL Server 2016 - Development and T-SQL [SQL Agent job TIME token - leading zeroes removed]( - I'm using "$(ESCAPE_NONE(DATE))$(ESCAPE_NONE(TIME))" in a job step which, according to the documentation, is supposed to be the date in YYMMDD format and the time in HHMMSS format. However, the stored procedure I'm running (which has a VARCHAR parameter) is getting: YYYYMMDDHHMMSS - when the hour is between 10 and 23 YYYYMMDDHMMSS - when the hour […] SQL 2012 - General [How to find two latest prices for each ItemNo using SQL query]( - I have two tables: OrderItems tabel OrderId    OrderDate   ItemNo   ItemDescrepation  Unit Price 1001       03-15-2020   91570    Charger 12V        $50 1205       05-06-2020  91570    Charger 12V […] SQL Server 2012 - T-SQL [Performance issue (e.g. top 60000 takes 1000x longer than top 50000 on insert)]( - Hi all Short version: USE StagingDBtemp -- new empty database with same design as StagingDB created for no reason other than troubleshooting this IDENTITY INSERT Incoming ON INSERT INTO Incoming (ID, DataField1, DataField2) SELECT TOP 60000 ID, DataField1, DataField2 FROM StagingDB..Incoming -- note: ID is auto-incrementing INT and PK and there are no other indexes […] SQL Server 2019 - Administration [Database Usage by Login Account]( - I would like to see which accounts in SQL are actively using databases. When I look at results from a trace file, I can see individual users, who do not have specific permissions. For example, I see that "MyDomain\Joe_Smith" accessed "Sales_DB". although Joe_Smith doesn't have SQL permissions. I suspect they are in an Active Directory […] Reporting Services [? on changing option on Web interface]( - Hi, I would like Basic users to see just the "View" and "search" options, but admins to see all Any ideas? Thx   SSRS 2016 [Can Case be used to assign filter with =, =]( - When writing a SQL query, I want to have a parameter (say, Return) which will have the following values (the parenthesis text is what the end user sees, not the values): A (Budget) B (Actual) C (Encumbrance) D (Actual & Encumbrance)  In my dataset, the Tranact_Type are: 1 for Actual 4 for Encumbrance 5 for […] Integration Services [Package status = Succeeded despite errors]( - I have a package with an OnError event handler which records the failure in a database table. This is all working fine but looking at the "All Executions" report, the status is "Succeeded". This has the effect that the SQL Agent job which runs the package is also succeeding. Consequently no failure notification is raised […] [The SQL connection password is not saved after deployed to the server]( - I developed an SSIS package in Visual Studio 2019 and deployed it to SQL Server 2017. The package contains a SQL DB connection using Microsoft OLE DB Driver for SQL Server. It worked fine until today when I updated the package with an email address change, then deployed it back to the server. When executed […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -