Calculating Elapsed Times (SQL Spackle) (2024-0 9-20)

Laden...

2 maanden geleden

Html
Tekst

Problems displaying this newsletter? View online.

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

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.

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.

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...

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 - https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver16

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 https://www.sqlservercentral.com/forums/topic/simple-linked-server-plumbing 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 […]

This email has been sent to newsletter@newslettercollector.com. 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Deel deze op

Laden...

Andere nieuwsbrieven van Sqlservercentral.com

Anything is possible, if you put your mind to i t. Like reading all of these blogs! Sqlservercentral.com
Gisteren om 08:22
Improving Performance for Some LIKE “%stri ng%” Searches (SQL Spackle) (2024-11-29) Sqlservercentral.com
Afgelopen vrijdag om 08:09
Simplify Data Loading with Fivetran (HVR): Data Engineering with Fabric (2024-11-27) Sqlservercentral.com
Afgelopen woensdag om 08:03

Meer nieuwsbrieven van Sqlservercentral.com

Laden...

Calculating Elapsed Times (SQL Spackle) (2024-0 9-20)

Deel deze op

Andere nieuwsbrieven van Sqlservercentral.com

Gerelateerde nieuwsbrieven