A Tidy Database is a Fast Database: Why Index Management Matters (2024-11-11)

SQLServerCentral Newsletter for November 11, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Tidy Database is a Fast Database: Why Index Management Matters]( - [How Many Indexes Is Too Many?]( - [From the SQL Server Central Blogs - Monday Monitor Tips: Projecting Disk Space]( - [From the SQL Server Central Blogs - Mastering the ORDER BY Clause in SQL Window Functions]( - [The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel]( Question of the Day - [Comparing Images]( Featured Script - [Concatenating Multiple Row Values into a Single Comma-Separated List]( The Voice of the DBA  Time to Change Your Team I assume most of you work with others in a team. Even if you are the data specialist and others work on different technologies, you still have a team. How long has your current team been together in this form? Have you had a stable team that might have grown, but the rest of the individuals and roles/responsibilities stay the same? Or has your team changed makeup, roles, responsibilities, or something else? I don't see a lot of organizations that change their team structures often. There may be people who come and go from a team, but the core structure remains the same. Even when your company might reorganize a bit, often it's teams that shuffle between managers, but mostly remain the same. There certainly are exceptions, and some large orgs (Microsoft, Amazon, etc.) regularly shuffle lots of people around, but I'm not sure the teams change their makeup or their mandate much. I was thinking about this as I read [an article on knowing when to restructure your team](. I won't recommend you read it as I think seems to imply restructuring technology teams will make them perform better and start meeting all the commitments that have been made. While I do think that a well-led team can perform better, restructuring your teams isn't likely to make them more efficient and productive. That being said, I do think the article raises some good questions about how you might evaluate your team. There are certainly times when an IT team, whether in development or operations, might start to miss deadlines or may seem to work inefficiently from the outside. We are human, and humans can get complacent, or they might focus on tasks or work that they want to complete, ignoring work they don't enjoy. The latter might be things the business needs, and restructuring the team isn't going to fix that. Either the current staff has to be managed more closely to get them to focus on necessary work, or maybe different people should be assigned to those projects or tasks. Just remember you work with humans, and they often struggle with change. Change might be necessary, but a little empathy helps us cope with the challenges and learn to work together in our new structure. If we don't have that, likely nothing gets better and we fall into old habits. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [A Tidy Database is a Fast Database: Why Index Management Matters]( Phil Grayson from SQLServerCentral Learn about the ways in which you might better manage indexes for a better performing and efficient database. [External Article]( [How Many Indexes Is Too Many?]( Additional Articles from Brent Ozar Blog Let’s start with the Stack Overflow database (any size will work), drop all the indexes on the Users table, and run a delete: [Blog Post]( From the SQL Server Central Blogs - [Monday Monitor Tips: Projecting Disk Space]( Steve Jones - SSC Editor from The Voice of the DBA One of the things that many DBAs struggle with is managing space across an estate. There might be one or two servers that you watch closely, or that are... [Blog Post]( From the SQL Server Central Blogs - [Mastering the ORDER BY Clause in SQL Window Functions]( Tracy McKibben from RealSQLGuy - Helping You To Become A SQL Hero This article provides a comprehensive overview of the ORDER BY clause within SQL window functions. We'll explore how it interacts with PARTITION BY and the standard ORDER BY clause,... [Definitive Guide to DAX cover]( [The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel]( Site Owners from SQLServerCentral Now expanded and updated with modern best practices, this is the most complete guide to Microsoft’s DAX language for business intelligence, data modeling, and analytics. Expert Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Comparing Images I am building an ETL process between these tables in SQL Server 2022 set to 160 compatibility level: CREATE TABLE Image_Staging ( imageid INT NOT NULL CONSTRAINT Image_StagingPK PRIMARY KEY , imagestatus TINYINT , imagebinary IMAGE); GO CREATE TABLE Images ( imageid INT NOT NULL CONSTRAINT ImagesPK PRIMARY KEY , imagestatus TINYINT , imagemodified DATETIME , imagebinary IMAGE); GO I want to run this query to check if the images already loaded exist. This will help me decide if I need to insert or update an image. What happens with this query? SELECT i.imageid FROM dbo.Image_Staging AS ist INNER JOIN dbo.Images AS i ON ist.imagebinary = i.imagebinary; 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) Backing up the Database Encryption Key In my SQL Server 2022 database, I run this: USE Sales; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCert; GO This works, but I want to prepare for the future and potential issues. How do I back up my DEK? Answer: This key cannot be backed up. Just backup the database Explanation: The DEK cannot be exported or backed up. Just backup the database. Ref: CREATE DATABASE ENRYPTION KEY - [ [Discuss this question and answer on the forums](  Featured Script [Concatenating Multiple Row Values into a Single Comma-Separated List]( Sukhdevsinh Dhummad from SQLServerCentral In scenarios where you need to consolidate multiple rows into a single, comma-separated value, you can achieve this using FOR XML PATH. This script demonstrates how to retrieve volunteer data and display the days they have selected for participation. -- Create Volunteers table CREATE TABLE Volunteers ( VolunteerID INT PRIMARY KEY, VolunteerName VARCHAR(100) ) -- Create DaysOfWeek table CREATE TABLE DaysOfWeek ( DayID INT PRIMARY KEY, NameofDay VARCHAR(20) ) -- Create VolunteerDays cross-reference table CREATE TABLE VolunteerDays ( VolunteerID INT, DayID INT, PRIMARY KEY (VolunteerID, DayID), FOREIGN KEY (VolunteerID) REFERENCES Volunteers(VolunteerID), FOREIGN KEY (DayID) REFERENCES DaysOfWeek(DayID) ) -- Insert sample data into Volunteers table INSERT INTO Volunteers (VolunteerID, VolunteerName) VALUES (1, 'John'), (2, 'Joseph'), (3, 'Mary') -- Insert sample data into DaysOfWeek table INSERT INTO DaysOfWeek (DayID, NameofDay) VALUES (1, 'Monday'), (2, 'Tuesday'), (3, 'Wednesday'), (4, 'Thursday'), (5, 'Friday'), (6, 'Saturday'), (7, 'Sunday') -- Insert sample data into VolunteerDays cross-reference table INSERT INTO VolunteerDays (VolunteerID, DayID) VALUES (1, 1), -- John -> Monday (1, 3), -- John -> Wednesday (2, 2), -- Joseph -> Tuesday (2, 6), -- Joseph -> Saturday (3, 5), -- Mary -> Friday (3, 7) -- Mary -> Sunday -- Below qyery uses FOR XML PATH to achieve results SELECT VOL.VolunteerName, STUFF( ( SELECT ', ' + DOW.NameofDay FROM VolunteerDays VDY INNER JOIN DaysOfWeek DOW ON VDY.DayID = DOW.DayID WHERE VDY.VolunteerID = VOL.VolunteerID FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS SelectedDays FROM Volunteers VOL [More »](  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 2016 - Administration [Data cleansing/conversion tool HPE]( - Hello.  I am looking for a tool Data cleansing/conversion, was recommended HPE any recommendation about this tool or any others ?  Thank you SQL Server 2016 - Development and T-SQL [strange selection for update]( - Hi, yesterday I had to update several rows of a table. The rows for update where selected by a select-statement for testing the results; the selection for the update-statement selected this sub-selection but I missed that I renamed the primary key within the selection; normaly I would think that SQL should raise an error (because […] [Review what columns are used]( - I have a couple of databases  One would be regarded as the source and another as the destination The destination is kind of a subset of source having gone through an ETL process Although the E part is scripted access as both databases reside on the same server  So, there are a number […] [Merge rows in SQL]( - I have a table like below ID Ident source val opendate closedate IsActive -------------------------------------------------------- 101 euid01 File01 x010 01-01-2023 01-01-2999 1 101 euid02 File01 x111 01-01-2023 01-01-2999 1 101 euid02 File01 x222 01-01-2023 01-10-2024 0 102 euid01 File11 x010 01-01-2023 01-01-2999 1 102 euid02 File12 x333 01-01-2023 01-01-2999 1 102 euid02 File10 x444 01-01-2023 01-10-2024 […] Development - SQL Server 2014 [How can I tell if an UPDATE command updated any rows?]( - I am calling an Update routine from C# and need to determine if the Update routine found a matching row to update. If it does not, I call the Insert Routine. I was hoping that if no row was updated, I would get an error which would tell me that I need to do the […] SQL 2012 - General [How to determine if a DB is currently being used]( - Hi - I'm looking for advice regarding the best & quickest way to establish whether or not a SQL2012 DB is being used. Many thanks Dax SQL Server 2019 - Administration [Query is timing out]( - I have a view on ServerA that fetches some data from ServerB and ServerC. The linked servers are configured properly. The query itself runs fine on ServerB and ServerC. But when I run it from ServerA, I get 'Query timeout error', yet some days, it runs fine. Some days it executes in a minute but […] SQL Server 2019 - Development [How to pass a variable in look up transformation in SSIS ? With Oracle database]( - We have a need to pass a variable in the look up query against Oracle database using SSIS, is there a way to pass a Sql query output (variable) in SSIS ? [problem with PIVOT Table]( - Thanks in Advance! I'm having trouble grouping all counts and sums on one line for each county which should not look like (Code attached) Note one county is repeated and most are like that: County   MH  SA DD Cost Camden  0    0  1   152.88 Pitt   […] Reporting Services [Report server errors]( - could not load folder contents The server principal "NT SERVICE\SQLServerReportingServices" is not able to access the database "ReportServerTempDB" under the current security context. How do I fix this ? Does this have anything to do with SPNs ? Integration Services [Visual Studio 22 / SSIS Project / (Project) Connection problem]( - Hello, First of all, I find it odd/annoying that I can't exclude a Project level connect from a package Connection Managers... at least I can not do it. Why should they show / fill the space in packages with nothing to do with those connections. But even more annoying is that when you have a […] SQL Server 2022 - Administration [System views in a contained availability group]( - I have a query that runs in a job to check on orphaned users. On a server with a contained availability group it gives false positives. I pinned it down to the following different result of the same query. When I am in SSMS and connected to one of the servers in the AG. I […] [SSISDB Folder creation Error]( - We recently installed SSIS on SQL Server 2022 machine. Trying to create a folder under SSISDB integration Services catalog is giving this below error. Operation 'Create' on object 'CatalogFolder[@Name='Testing1']' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc) Cannot find either column "internal" or the user-defined function or aggregate "internal.is_valid_name", or the name is ambiguous. Cannot find the folder 'Testing1' […] [Blocking Connection to Server]( - Good Afternoon, We recently procured a service management software for our company, which uses SQL server 2019( 15.0.2125.1) as backend. The client application uses sql usernames to login into the application . I find this as a security issue, as any internal user can directly connect to the server either through ODBC or through SSMS […] SQL Server 2022 - Development [Update Function]( - Hello everyone, I need your help. I have a “datetime” column (data type is Varchar(64) )with the following content: Sun Sep 29 2024 09:28:55 GMT+0000 (Coordinated Universal Time) I would like to update the column so that I only get the time in the format HH:MM:SS. I have tried many solutions, but none of them […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -