Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
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 his sabbatical.

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

Redgate Data Masker
 
  Featured Contents

Incremental Package Deployment – A SSIS 2016 Feature

shubhankarthatte from SQLServerCentral.com

This article provides step by step instructions to deploy individual SSIS packages in a project deployment model.

Changing SET options in a Procedure or Trigger leads to Recompilation (PE012)

Additional Articles from Redgate

Phil Factor delves into SQL Prompt's performance rule, PE012, which will advise you if it detects the use of the SET statements within a stored procedure or trigger, which might cause unnecessary recompilations, though the issue extends to other types of batches.

Performance Tuning Means 3 Things

Additional Articles from Brent Ozar Unlimited Blog

Brent Ozar discusses performance tuning and how From the outside it looks more complicated than it is, but at the end of the day, it always comes back to just 3 knobs and knowing which ones you can turn.

From the SQL Server Central Blogs - Temporarily enable TLS 1.0/1.1? – Be sure you check this keys

Cláudio Silva from Cláudio Silva

There are some actions that we know that will have to be repeated from time to time, but the surprise comes when it’s time to do so and the...

From the SQL Server Central Blogs - SQL Homework – February 2020 – Write a blog post.

Kenneth.Fisher from SQLStudies

Following along with last month’s documentation homework this month I want you to write a blog post. But Ken, what ... Continue reading

 

  Question of the Day

Today's question (by Kathi Kellenberger):

 

Using TOP with ROW_NUMBER

Which query will generate ten “random” sample rows from the Sales.SalesOrderDetail table (AdventureWorks2017) with new ID numbers (NewOrderID) that continue after the highest SalesOrderID in the table? In other words, if the highest SalesOrderID is 75123, then the NewOrderID in the rows returned will be between 75124 and 75133. Query #1
SELECT TOP(10) SalesOrderID, ProductID, OrderQty, 
	ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID
FROM Sales.SalesOrderDetail AS SOD
ORDER BY NEWID();
Query #2
WITH SalesID AS (
	SELECT TOP(10) SalesOrderID,  ProductID, OrderQty,  
            MAX(SalesOrderID) OVER() AS MaxID
	FROM Sales.SalesOrderDetail AS SOD 
	ORDER BY NEWID())
SELECT TOP(10) SalesOrderID, ProductID, OrderQty,  
        ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MaxID AS NewOrderID
FROM SalesID;
Query #3
SELECT SalesOrderID, ProductID, OrderQty, 
	ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID 
FROM Sales.SalesOrderDetail 
WHERE ROW_NUMBER() OVER(ORDER BY SalesOrderID) <= 10;
 

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by sergey.gigoyan)

XACT_STATE(), @@TRANCOUNT and ROLLBACK

Let’s assume we have an empty table in the TestDB database with the following structure:

USE TestDB
GO

CREATE TABLE TestTable
(
	ID INT PRIMARY KEY,
	Val INT
)
GO

CREATE UNIQUE INDEX UIX_TestTable_Val ON TestTable (Val)
GO

There are transactions in the code sections below that will fail because the duplicate values are inserted:

Section 1

USE TestDB
GO

BEGIN TRY

	BEGIN TRANSACTION

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(2, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	COMMIT

END TRY
BEGIN CATCH

	IF XACT_STATE() = -1
	BEGIN
		ROLLBACK
		PRINT 'Transaction is rolled back'
	END

END CATCH

 

Section 2

USE TestDB
GO

BEGIN TRY

	BEGIN TRANSACTION

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(2, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	COMMIT

END TRY
BEGIN CATCH

	IF XACT_STATE() = 1
	BEGIN
		ROLLBACK
		PRINT 'Transaction is rolled back'
	END

END CATCH

 

Section 3

USE TestDB
GO

BEGIN TRY

	BEGIN TRANSACTION

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(2, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	COMMIT

END TRY
BEGIN CATCH

	IF XACT_STATE() > 0
	BEGIN
		ROLLBACK
		PRINT 'Transaction is rolled back'
	END

END CATCH

 

Section 4

USE TestDB
GO

BEGIN TRY

	BEGIN TRANSACTION

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(2, 100)

	INSERT INTO TestTable(ID, Val)
	VALUES(1, 100)

	COMMIT

END TRY
BEGIN CATCH

	IF @@TRANCOUNT > 0
		ROLLBACK
	PRINT 'Transaction is rolled back'

END CATCH

In three of these sections of code, the situation is correctly handled, allowing the transactions to be rolled back successfully. Which three?

Answer: 2, 3, 4

Explanation: In all four cases, the duplicate value violation is caught and handled by the CATCH block. Additionally, in all of these statements, the ROLLBACK command is under the IF condition. The IF condition is true in the second, third and fourth queries because there is an active transaction and, therefore, @@TRANCOUNT=1 and XACT_STATE()=1 (and obviously >0 ). In the first query, however, the IF condition is false. This is because XACT_STATE()=-1 when there is an uncommittable transaction. In our case, we do not have an uncommittable transaction. Thus, XACT_STATE() is not equal to -1. Consequently, the IF statement is false and, therefore, the ROLLBACK command is not issued in the first query, leaving the transaction open. Ref: https://dbprofi.com/xact_state-vs-trancount/

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
FTS - Does Crawl Completed??? And wait untill next query can get the results! - Hi, Actually with Full Text Search FTS we have a table on which we just insert thousand of rows; and then in very next statement try to fetch data with contains query. The index is not built within this span of time (even Auto) so what would be the solution to this problem; or even […]
SQL Server 2017 - Development
Optimizing view and Table - We have a table (tblASAP_Detail_v3) which we fill from different sources and which was derived from an Excel with around 100 columns. This table will then be used in a view we use for an Excel Pivot. The table has grown and grown and needs around 3.7 GB for 2.2 Million records. (Every year it […]
SQL Server 2016 - Administration
installed SSIS can\'t connect - I installed SQL , SSRS and SSIS on new server.  Can't connect to Integration Services in mgmt studio.    I tried this still will not connect name will not show up in drop down box.  Using mgmt studio 2018 on server did not think that made a difference   http://slavasql.blogspot.com/2016/07/ssis-rpc-server-is-unavailable-error.html
Restore publisher database from SQL 2012 to 2016 - We have already restored databases from SQL 2012 to SQL 2016 and setup transactional replication. But, I am needing to restore the publisher from SQL 2012 to SQL 2016 again. Is there way to achieve this without having to rebuild replication\taking new snapshot and reinitializing all subscriptions?     Thanks in advance.
Development - SQL Server 2014
How can we ensure whether database is migrated successfull from DBA Side - Hello Team, Could you please provide me is there any T-SQL Scripts to validate whehere database is migrated successful from SQL sevrer 2008 R2 to SQL Server 2012/2014/2017/2019. Example: Tables,SPs, Viewes, Functions and Linked server,etc. Regards, Naveen M
SQL 2012 - General
High CPU utilization due to too much idle sessions on DB instance. - Hi guys, need some advice on what sort of troubleshooting can I do in event of the DB server CPU went up to 100% and causes all the processing got slowed down. Upon checking I found that there's about 4000+ idle sessions on the DB when on normal days the max it reaches is only […]
How to run dynamic pivot table without using exec(sql statment) - I working on sql server 2012 I need to run query below without using exec(sql statment) I try that but i face problem on single quotes on this line '+@column+' my statment i need to run as below : DECLARE @Columns as VARCHAR(MAX) SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName) FROM --distinct FT.FeatureName […]
How to add feature value without prevent group data based on itemid pivot table? - How to add feature value without prevent group data based on itemid pivot table? I work on SQL server 2012 I make pivot table based on itemId it work good but after add featurevalue data repeated and not grouping How to add Featurevalue without prevent repeated data on pivot table ? desired result ItemCode IPN […]
SQL Server 2019 - Administration
SQL Server 2019 Memory Management - I have SQL Server 2019 Standard Edition in Windows 2019 box with 32Gb RAM.  I set the SQL Server's maximum server memory to 24Gb which leaves the remaining 8Gb to be used by OS and 2 web apps deployed on the same server.  Checking the RAM used by SQL Server on the first day via […]
IBM INFORMIX ODBC DRIVER 64 Bits version - hello Beautiful people as the topic says i want to install IBM INFORMIX ODBC DRIVER 64 Bits version but i cant find it anywhere i even uninstalled my ibm informix odbc driver package from my pc and re-installed it again to check if i missed something to checkmark at the installation process, but even that […]
SQL Server 2019 - Development
Deploying SQL Instance(s) for Development work - How do you deploy development instances in your environment?  I'm curious what others are doing, and what the pros and cons are that they see.  Especially interested in peoples opinions and experience around each dev having an instance on their local machine. The three scenarios I am considering are for a new data warehouse project […]
Outstanding balances for invoice table - I have a table with cust references payment type pi or pa, amounts and transaction dates The table design is actually not great because the if the incoming and outgoings transactions were always linked by reference then I could use that but unfortunately they're not and I have to work with what I have. so […]
SQL 2019 Bug : "SELECT @local_variable" - Hi, It seems that the statement "SELECT @local_variable (Transact-SQL)" no longer works as it is documented by SQL Docs an now returns incorrect data. According to SQL Docs : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15 SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If […]
Analysis Services
Migration from Multidimensional model to Tabular model - Hello Experts, I am currently working on a project to migrate a multidimensional cube to a tabular model. Do you know if there is a tool or a method for converting multidimensional XML to tabular model XML?
Integration Services
Bulk loading data using For Loop container with variable number - Hi there I am developing an SSIS package to bulk load data. Now the data can be high in volume (300,000 to 2 million records) What i want to design is a package that chunks up high volumes of data into chunks of 100,000 records . I would use a for loop container to process […]
 

 

RSS FeedTwitter

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

 

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