SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Reporting Services Editions

The Voice of the DBA

Why Early Code Review is Important for Database Deployments

Today we have a guest editorial from Kendra Little as Steve is away on holiday. This editorial was originally published on Feb 7, 2020.

We asked some new questions about code review and change management processes in the 2020 State of Database DevOps report, and the results are quite interesting. One of these new questions asked respondents how easy it is for team members to get code reviews early in the software development process for database changes. We clarified that “early” in the software development process means near the time the change is committed or merged into a main code branch.

We found that there appears to be a correlation between ease of code review and code quality: those who reported that it’s easy to get a code review (55% of respondents) were much more likely to report that only 1% or less of production deployments cause defects which require a hotfix as compared to other respondent groups who reported higher levels of defects.

Interestingly, those who find it difficult to get a code review (26% of respondents) reported somewhat higher levels of defects requiring hotfixes than the group of people who respond that they simply don’t have a practice of doing code review at all (16% of respondents).

Why might this be the case? For those with easy code review, it is likely that review is being done by someone familiar with the application and database domain. If this is the case, it is likely that familiarity helps the reviewer identify potential gotchas or defects in the code early on when the business case is fresh in mind, and the code can be most easily optimized.

For those who find it difficult to get a code review, the answer is likely more complex: potentially these folks are working across more application and database code bases and are in part less familiar with the codebases than those who don’t have a practice of doing code reviews at all. It may also be that those who don’t have the practice of code review are having architecture planning meetings or discussions instead of code review that provide better design ahead of time – we may wish to study this in more detail in future years to shed further light on this question.

In any case, this year’s data does suggest that providing an easy mechanism to get code reviews for database changes early in the software development process is associated with higher code quality.

If you work in a team where it’s difficult or impossible to get a code review, you can begin to make changes to create this culture.

Create a community of practice for database development in your organization– basically a user group at work which helps build expertise and relationships across teams. This is a great way to build the body of potential code reviewers
Identify team members who you may cross-train with, and regularly code review each other’s changes: while at first, you will not have domain knowledge for each other’s areas, you will quickly build this up with practice
Establish team members who may build up a specialization in reviewing database changes. These team members should also build up theexpertise to identify specific changes where it is critical to engage with database administrators for code review early

Kendra Little

Join the debate, and respond to today's editorial on the forums

Featured Contents

Exporting and Importing Registered Servers in SSMS

Frank Dolan from SQLServerCentral

This article shows how to import and export a list of registered servers from SSMS.

Learning Spark SQL String Functions with Explanations and Code Examples

Additional Articles from MSSQLTips.com

Learn how to use different Spark SQL string functions to manipulate string data with explanations and code examples.

From the SQL Server Central Blogs - Microsoft Purview new data governance features

James Serra from James Serra's Blog

Starting last week is a rollout of the public preview of a new and fully reimagined Microsoft Purview data governance solution. Data governance has become so much more important...

From the SQL Server Central Blogs - Elastic Jobs for Azure SQL DB

hellosqlkitty from SQLKitty

I know if you are a SQL Server DBA using Azure SQL DB, you’ve been sorely missing the agent. Enter Elastic Jobs to help you schedule jobs more easily...

Deciphering Data Architectures

Additional Articles from SQLServerCentral

Data fabric, data lakehouse, and data mesh have recently appeared as viable alternatives to the modern data warehouse. These new architectures have solid benefits, but they're also surrounded by a lot of hyperbole and confusion. This practical book provides a guided tour of these architectures to help data professionals understand the pros and cons of each.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Reporting Services Editions
In which editions of SQL Server 2022 can you run Reporting Services?
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 Marked Transaction

I want to mark a transaction in the log as a recovery point. How do I do this in my code if I use the transaction, myTran?

Answer: Do this with BEGIN TRANSACTION myTran WITH MARK

Explanation: The way to mark a transaction is with the BEGIN TRANSACTION [tran name] WITH MARK. Ref: T-SQL for Inserting Marks into a Transaction Log - https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-of-related-databases-that-contain-marked-transaction?view=sql-server-ver16#transact-sql-syntax-for-inserting-named-marks-into-a-transaction-log

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

Degraded Performance in 2017 vs 2008 R2 with inserts - We're finally upgrading from SQL Server 2008 R2 Enterprise to SQL Server 2017 Standard and have moved our test database to the new server. Both VMs are on similar hardware with 4 vCPUs. Of course, given the chasm of versions we expected much better performance. Unfortunately, that's not the case. Our inserts on the 2008 […]

SQL Server 2016 - Administration

User permissions and showplan - All, I'm trying to find a way to let some of our users see at least some details of a query plan particularly whether an index is being used. I looked at the set statistics statements because the user only needs permissions to run the query but they only specify tables and not indexes? I […]

SQL Server 2012 - T-SQL

Insert using table unput - I have a table that I need to run off a different server\db and looking for the most efficient way to call from an SP. I need to execute. the field(sqlToExec) as it's loaded with 1000's of Insert statements like below to update this same table on a different server and DB. If something error […]

SQL Server 2019 - Development

Create Copy of DB in same server. - Hi I want to create a copy of a DB on the same server. The account that creates the copy DB should NOT be able to read from the original DB but have full rights to the copy DB. Cannot use a snapshot, Changes will be made to the copy and the copy will be […]

Find the problematic Sql Query Caused Application Crash - Hi, Is there anyway I can find out the sql script ran on a particular date and which might have causes application down? I mean my application was down on 12 April 2024 and based on investigation it was found some sql query running causing the application down. How I can find out what all […]

Does it make a sense to update statistics right after tables truncated? - Hi all, We have a lengthy process that uses 30 tables, some of them are large. In the beginning we truncate them, then ETL data into them, then massage, perform some calculations, and finally reporting. I am tasked to clean up this process. Right after initial truncation we update statistics on all these tables. Does […]

Difference between equal directly and use SUBSTRING - Hello, I have a script that was running really long, and I have found why it was running so long, but I don't understand why. In the WHERE clause I have the following code: AND SUBSTRING(CDHDR_NEW.OBJECTID, 1, LENGTH_EBELN) = MM_EKKO_STAGING.EBELN This code is logically correct, even when actually in this particular case "LENGTH_EBELN" field is […]

Visualisation options as an alternative to SanKey diagram - I am building a report to show customer journeys and their arrears levels. I have 8 cohorts based on level of arrears and whether they are current or former tenants. (Lets call then C1-C4 and F1-F4) The busines would like to see on a rolling 4 period (current and 3 previous months) the number of […]

Should I create tables with dynamic SQL? - Hello all, I am thinking of creating a "shopping cart" in our application. My thoughts are using the User's user name as the table name, and the fields are already predetermined. When the user clicks "Add to Cart", the table would be created. This could potentially create up to 30 tables if people use this […]

SQL Azure - Administration

zero downtime deployments - Hello, We have azure MI business critical. active read/write, secondary read /only. from what I see most of the HA is handled behind the scenes using Always ON. We have a 30 min downtime every month during product deloyments but one high profile customer wants 0 downtime. How to achieve that with Azure MI Business […]

Azure Data Factory

Expression Error ( property 'Error' cannot be selected ) - Hello there. I have the following Pipeline I want to get the property error message from with every test fails, but i am stuck with the following error The expression concat( if(equals(activity('Test1').Error?.message, null), '', 'Test1'), if(equals(activity('Test2').Error?.message, null), '', 'Test2') ) cannot be evaluated because property 'Error' cannot be selected. Basically, if the property (error) […]

Reporting Services

SSRS Expression IIF Zero then ... Got #Error - Hello, Need some help for SSRS. The column % expression is =iif(sum(Fields!ColumnA.Value) = 0 or sum(Fields!ColumnB.Value) = 0,0,sum(Fields!ColumnB.Value) / sum(Fields!ColumnA.Value)) But I am getting #Error if ColumnA is zerro and no error if ColumnA and B had no value. Please advise. Thank you.

Strategies and Ideas

Do I need a recursive query for this? bill of materials (sort of) - I am working on a data warehouse project for a bakery. For the most part, the basic structure is like this: Purchase--(M,1)--Ingredient--(1,M)--Recipe--(M,1)--Product with one exception the "Recipe" part works fine. (IngredientKey, ProductKey, Weight). The part I'm having trouble with is that one ingredient (starter) consists of other ingredients (kinda like a subassembly, if you wanted […]

SQL Server 2022 - Development

should i prefer join or left join over exists or in - hi, I have seen people use exists when exists is required and no cols are required in select. But I notice sometimes exists slows down and join performs better q1) is there any news i should know , that one should not use exists or in , and should go for join etc etc […]

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.