Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate Data Masker
The Voice of the DBA
 

Pulling the Plug

This editorial was originally published on February 28, 2019. It is being re-run as Steve is on sabbatical.

I've worked on some large scale software projects. For the purposes of this discussion, I'll define a large scale project as one that takes more than a two man-years to develop. I've never spent two years on a project, but I certainly have been involved with some that took more than two people, all more than a year to complete to some level. Even while working in a DevOps style environment where we released software every week, I would count a project that followed this process across a year for multiple developers as a large scale project.

When working on a large project, it's hard to sometimes keep perspective on whether to keep going or stop and change directions. We often try to continue to improve and fix a project, even when it is not going well. There's a term for this: Concorde fallacy. I've seen this similar situation occur pre-Concorde in other industries, who often have made an investment and continue to do so because of the weight of that previous investment on them. If I've spent $20 or $20mm on a project and I am evaluating whether to spent an equivalent amount moving forward, I can't continue to worry about the money I've already spent.

That money is gone whether I stop now or continue on. What I ought to do is look forward and decide if future spending is worth the investment. Certainly my reputation, and often some pain for switching or decommissioning existing work is to be considered, but that's part of the value and too often we become afraid of abandoning something we ought to get rid of for a newer, better something else.

This is discussed a little in the case of legacy technology in this piece about pulling the plug. If you start to avoid maintenance, especially in this day and age of additional regulation, you need to upgrade. If your staff or users are starting to pick other software because you can't maintain your application, you ought to abandon it and move on. Does some application take too many resources to keep going? Look for something else. In fact, some portion of your staff ought to be making evaluations on alternatives. Not as a full time job, but with some framework to help them perform a cost benefit analysis.

This doesn't mean pick the shiny new thing or move to the cloud or make everything self-service, but it should be an evaluation of how well you can get software to your users. One of the best lessons of The Phoenix Project, to me, is that continuing to bet on a bad software project isn't as good as building a plan to get replace it with something better, often something that is built with a more agile, DevOps process. This may not be the cheapest or quickest way to move, but if can often be the best way to change.

Steve Jones - SSC Editor

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

 
  Featured Contents

Introduction to PIVOT operator in SQL

Gouri Shankar from SQLServerCentral

Learn how to use the PIVOT operator in SQL in a step-by-step manner.

Efficient Solutions to Gaps and Islands Challenges

Additional Articles from SimpleTalk

Window functions are useful for solving many SQL queries. In this article, Ed Pollack demonstrates how they can be used to analyse baseball winning streaks.

Updates to the Deployment Suite for Oracle

Additional Articles from Redgate

Redgate has added new capabilities to the Deployment Suite for Oracle, so it now supports automated migrations-based and state-based Oracle deployments on both Linux and Windows. They've introduced Redgate Change Control, a new capability for understanding development changes, generating migration scripts for version control, and getting precise control over the migration path during automated deployments. Our Schema Compare and Data Compare for Oracle command line tools have been extended to Linux as well as Windows, and we’ve added static code analysis that encourages teams to follow good coding practices on Windows or Linux.

From the SQL Server Central Blogs - Tell Me About Your Worst Database Code Offenders

kleegeek from Technobabble by Klee from @kleegeek

For years, I’ve done consulting around helping organizations tune their database platforms, both from within the database and underneath, to help boost performance and availability. Some database design or...

From the SQL Server Central Blogs - Profiler: Time To Go

Grant Fritchey from The Scary DBA

I’ve decided that, in fact, it is time to start moving people off the ancient technology, Profiler. Before, I always said, stay where you’re comfortable. However, keeping people comfortable...

 

  Question of the Day

Today's question (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?

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 Partial Dataframe

I have this dataframe in R:

> fantasy.playoffs
                Team  Score           Opp OppScore
1 Way0utwest Cowboys 135.40                 NA
2         SSC Ravens  66.26 Green Machine    66.26
3              Ditka  85.20                 NA
4   Orange Engineers 111.52     Mexicanos   111.52

When I run this, what is returned?

> fantasy.playoffs[2:3,]

Answer: The two rows starting with SSC Ravens and Ditka

Explanation: This syntax returns the rows between 2 and 3, inclusive. Dataframes are 1 based, so this is the two rows with Ditka and Orange Engineers. Ref: R Data Frame - https://www.datamentor.io/r-programming/data-frame/

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.
SQL Server 2016 - Development and T-SQL
Memory grant - SQL can't estimate OpenJson querying and is Granting excessive memory approx close to 1 GB by looking in the execution plan warning. It also uses a lot of memory to query out the Hash Joins. Has anyone came across this issue?
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

 

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