Calculating Elapsed Times (SQL Spackle) (2024-09-20)

SQLServerCentral Newsletter for September 20, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Calculating Elapsed Times (SQL Spackle)]( - [Setup Excel as Front End Application for SQL Server]( - [From the SQL Server Central Blogs - Redgate Clone Graduating Images]( - [From the SQL Server Central Blogs - SQL Server Quickie #47 – Azure SQL Database]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [Disable All the Indexes]( The Voice of the DBA  Bad Stored Procedures I don't see a lot of SQL at The Daily WTF, but [this one]( was great. It's a stored procedure that was likely just converted from embedded code, as noted by the poster. It's a strange set of code, that doesn't quite make sense to me, and I can't imagine why someone wrote it. Arguably, this is no better than having this code in a C# or ASP.NET application. Or is it? I think it is better. If I saw this code in a review or even in a production database, I could work on cleaning it up, adding protection against SQL Injection, and even tuning how it works to reduce the load on the database. I could likely wrap testing around this and get it deployed way quicker than if I were trying to update the source code for an app. More importantly, this is centralized code. If this is called from multiple places in the app code, I've fixed it once, not requiring an app developer, who has other work being piled on them, to spend time updating repeated sections of the code. Even better, I could refactor some of the schema behind this stored procedure and easily find that my changes might affect this code. I could add a feature flag to this procedure and slowly migrate my schema in the background, without disturbing the user, again because the code is centralized. That's a technique that most developers use in C#/Java/Python/etc., so why not in SQL? I find it very interesting that a lot of developers refactor their classes and methods to better adhere to SOLID or some other practice, and they are happy to remove repeated code in their language. Yet, they don't want to implement a stored procedure or function into their calls, essentially creating a database method for the things they need. The more I work with legacy systems, the more value I see in using stored procedures. Every developer ought to know how to build them, and every developer ought to be able to create them in dev systems so they can easily deploy database and code changes together. More importantly, they can also share the load of tuning queries with operations staff, who may notice things in a production environment that are not apparent in development ones. The big challenge in all of this is that database tooling is immature. Capturing your database code in source control is hard, and often it is a separate process from the one you follow for application code. I see some companies (including my employer) trying to make this easier, but there is a long way to go, and a lot of habits to change for developers.  Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Calculating Elapsed Times (SQL Spackle)]( Dwain Camps from SQLServerCentral.com Calculating elapsed time between time values can be an interesting problem that is easily solved even when data anomalies are present. [External Article]( [Setup Excel as Front End Application for SQL Server]( Additional Articles from MSSQLTips.com In this article, we walk through how to set up a simple Excel interface to work with SQL Server data to insert, update, delete, and select data. [Blog Post]( From the SQL Server Central Blogs - [Redgate Clone Graduating Images]( Steve Jones - SSC Editor from The Voice of the DBA I wanted my own custom image in our internal Redgate Clone cluster, so I decided to make one. This is an overview of how this works. This is a... [Blog Post]( From the SQL Server Central Blogs - [SQL Server Quickie #47 – Azure SQL Database]( Klaus Aschenbrenner from Klaus Aschenbrenner Today I have uploaded SQL Server Quickie #47 to YouTube. This time I’m talking about Azure SQL Database. If you are interested in learning more about this very interestingh... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Disable All the Indexes How do I disable all the indexes on the dbo.Logger table? 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) Concat Addition What is the result of this code? SELECT CONCAT(1, 2, 3) + 4 Answer: 127 Explanation: The concat function will return 123 as a string. This is implicitly converted to 123, which added to 4 gives 127. Ref: Concat - [ [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 2016 - Development and T-SQL [Tablock to avoid deadlock]( - Hi, We have 4 stps. the flow of the stored procedure (stp) is as below stp A is main stp that is called by the application. Inside A, stp B C and D are called within Begin Tran. Also in each stp B C D explicit transaction is included. Below is the flow of stp […] Development - SQL Server 2014 [How to load AML files SSIS or any other option]( - Team, Please suggest or if you have an example please share the link for loading AML (anti moneylaundering)files into SQL Server. Meaning file having multiple recs in the .txt/.cvs file. Loaded to multiple tables .. Basically it is AML files if anyone who has done that do suggest Thanks in advance  SQL Server 2019 - Administration [Error Login from an untrusted domain]( - Hi All, I found something a little strange in my SQL Server Error Log, There is an error message: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: XXX.XXX.XXX.XXX], but I tried to access / login from the server using Management Studio and the same user has […] [SPID is suspended but the wait type is NULL]( - Hello experts, I have always assumed that if there is no wait going on for a SPID, that its status would be runnable. I have seen just now with a large insert query that my assumption is wrong. Could someone explain how a SPID remains suspended if the wait type is NULL? Thanks for any […] [backups failure]( - Hello All, We have a domain account running sqlserver service.this account has r/w access to a network share, but backups are still failing. what are we missing here ? Even the the said computer$ has r/w to the network share I can access the network share from the said server. But when I run the […] [Unable to begin a distributed transaction.]( -   create table #failover_logs   (     id int identity,     logdate datetime,     processinfo nvarchar(100),     logging nvarchar(max)   ) --DROP TABLE #failover_logs       insert into #failover_logs       ( […] [Capture long transact query using SQL Server performance condition alert feature]( - I created a SQL Alert trying to capture long query transaction under the SQL Server Agent\Agent with the options below. However, it does not have the feature to capture what query is causing the long transaction. I was thinking of adding the sp_WhoisActive to capture the long transaction query but it did not have SQL […] SQL Server 2019 - Development [divided by zero error in SQL please provide solution]( - divided by zero error in SQL please provide solution SQL Azure - Development [Azure Purview Scanner Requires Database Master Key to produce Lineage, but why?]( - I am studying for the Azure Data Engineer certification and the material covers Purview linage scanning of SQL Server databases. It says there must be a master key setup in the database for lineage to be produced, but I cannot find anything that explains why. SQL Azure - Administration [Statistics Update being mis-triggered by OlaHallenGren]( - Hi Guys, I have a very problem with Azure SQL DB taking more time to update stats than index rebuild. I am trying to filter by using on update stats if modifications are 40% but I think it getting triggered even if the modifications are less than 1%. I am taking about column stats here […] Suggestions [Gateway timeout when posting reply but...]( - I posted a reply to a topic today and, after a minute or so, received a 504: Gateway Timeout error. Seeing that error, I thought my post had failed and was going to redo it but luckily, I checked first and to my surprise my reply was posted successfully. So, this was an "error: process […] SQL Server 2022 - Development [Beginner questions]( - Hello everyone and thank you for joining the forum. I am new to SQL and have a few beginner questions. 1. i have installed Microsoft SQL Server and SSMS as well. I just did it and got it to work and I can write queries in SSMS. Unfortunately I don't know if I forgot something […] [talend "template" feature vs ssis]( - Hi we will be using a talend template shortly to abstract the schema definition of spreadsheets such that we can plug and play different excel layouts into a general talend etl "program" that lands that data into different configurable sql destinations. i believe the template is a flat file with one entry for every column. […] [executing a proc on another server from a proc on th e1st server]( - Hi, this is a second post trying to help a friend on a slightly different subject. as shown at he is forced into splitting up some db's to multiple servers...without changing code.  So on db Q (now on server x) he still wants to execute a stored proc that executes a stored proc on […] [simple linked server plumbing]( - Hi, as a favor to a friend who needs to split databases between servers, and doesnt want to change code, he has a select on a.dbo.view that used to run from db Q easily because db A was on the same server.  I havent done one of these in a long time but shouldnt he […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -