This editorial was originally published on May 17, 2013. It is being re-run as Steve is out of the office.
I was reading about version control systems (VCS) recently, brushing up on some skills, and saw this quotein a thread:
"There is no excuse for not using version control, even for a small project developed by single developer. Setting up local version control is beyond trivial, benefits huge. Any developer not knowing that cannot be considered good nor experienced."
That's quite a pronouncement, and one that I believe is very true. No matter what type of development you engage in, I'd expect that you'd understand the benefits of using version control, and the dangers of not using it. It's just like never backing up your system. I'd think that any developer that cares about their craft and is a professional has used version control. The really good ones will insist upon it.
However I know that the decision to use a VCS is not always made by a developer. The company building the software might feel differently, and while I've always asked for a VCS, I have ended up with a series of folders on a share, named for dates, each containing a zip file of all our code at the end of that day. It was the bare minimum of version control I could live with, and fortunately we got by with just two people coordinating work. Any more than that and I'd insist on some type of VCS.
This week, I wanted to ask how many of you voluntarily or involuntarily might be forced to do something similar.
How many of you skip source control for certain apps?
Even if you have source control for those large, multi-person teams, are there apps that you avoid putting into a VCS? What about your database code? I think it's important that you keep all your code, whether for the front end application or database objects, in some type of Version Control system. If your boss won't buy one, then check out Git or Subversion, both of which are open source and free.
Let us know this week how you feel about source control and whether or not you decide the effort isn't worthwhile for your projects.
Data Platform MVPs and SQL Server experts share their tips on how to standardize formatting whilst stripping out the repetition of coding with SQL Prompt. Over these 20 short videos we will help you write better, shareable SQL faster. Check out the tips
The industry standard for comparing and deploying SQL Server database schemas
Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial
Why are practices like version control, continuous integration and automated deployment being introduced to application development but left on the shelf when it comes to the database? In search of some answers, Redgate spoke to Donovan Brown, Principal DevOps Manager at Microsoft and DevOps advocate. Also known as The Man in the Black Shirt, his unofficial tagline is #RubDevOpsOnIt. He lives DevOps - here's what he had to say. More »
SQL Server 2016 introduced a new feature called Distributed Availability Group. A Distributed Availability Group is a special type of Availability Group that spans two separate Availability Groups. Edwin Sarmiento explains. More »
I love dbatools, and I’ve been trying to explore the various cmdlets over time, both to practice my PoSh and... More »
Question of the Day
Today's Question (by Henrico Bekker):
When querying a CostmosDB through the DocumentDB API, to return only properties where the property exists and indicating if the property has been assigned a value, which syntax must preceed the property test/check in the WHERE clause?
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth
1 point in this category: CosmosDB.
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
Learn effective and scalable database design techniques in a SQL Server 2016 and higher environment. This book is revised to cover in-memory online transaction processing, temporal data storage, row-level security, durability enhancements, and other design-related features that are new or changed in SQL Server 2016. Get your copy today from Amazon.
Yesterday's Question of the Day
(by Uwe Ricken):
The development team has heard from the brand new feature of System Versioned Temporal Tables in SQL Server 2016 has implemented it on the table [dbo].[Employees]. The application is using different access patterns to modify the data in this table. There are direct statements coming from the application or the application is firing long running stored procedures which make several changes to multiple tables. The update on the [dbo].[Employees] table is the last statement in the procedure! Sometimes it happens that the stored procedures fail and will rollback all changes.
The demo table with activated System Versioned Temporal Tables is implemented with the following code (simplyfied for QotD!):
CREATE TABLE dbo.Employees
Id INT IDENTITY(1, 1) NOT NULL ,
EMailAddress VARCHAR(255) NOT NULL ,
ValidFrom DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL ,
ValidTo DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL ,
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
CONSTRAINT pk_Employees_Id PRIMARY KEY CLUSTERED (Id ASC )
SYSTEM_VERSIONING = ON
(HISTORY_TABLE = dbo.EmployeesHistory)
ALTER TABLE dbo.Employees
ADD CONSTRAINT df_ValidFrom
DEFAULT ( GETUTCDATE()) FOR ValidFrom;
ALTER TABLE dbo.Employees
ADD CONSTRAINT df_ValidTo
DEFAULT ( '99991231 23:59:59' ) FOR ValidTo;
Content of Stored Procedure
The workflow of the Stored procedure is extrem simplyfied. Please note the begin transaction at the beginning of the code. The WAITFOR is a placeholder for long running queries within the transaction. The last statement will be the update of the employee!
RUN THIS CODE IN A NEW QUERY WINDOW!
-- copy this code into a new windows and execute it!
BEGIN TRANSACTION T1
RAISERROR (N'Now follows a very long running query/batch...', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:30';
SET EMailAddress = 'firstname.lastname@example.org'
WHERE ID = 1;
COMMIT TRAN T1
Content of adhoc query from application
The following statement is coming from the application (simplyfied!).
RUN THIS CODE IN A NEW QUERY WINDOWS WHILE T1 IS RUNNING!
BEGIN TRANSACTION T2
SET EMailAddress = 'email@example.com'
WHERE ID = 1;
COMMIT TRAN T2
The users of the application occasionally see a break in the application and an error occurs with the following message:
"Msg 13535, Level 16, State 0, Line 14
Data modification failed on system-versioned table 'dbo.Employees' because transaction time was earlier than period start time for affected records."
What is going on here?
Answer: It is a standard behavior of System Versioned Temporal Tables and the error need to be handled in an execption routine
The correct answer is no. 5! "It is a standard behavior of System Versioned Temporal Tables and the error need to be handled in an execption routine"
is nonsence because System Versioned Temporal Tables is a feature which has been implemented with SQL 2016. Earlier versions cannot implement it.
The latest SP of SQL Server came with a big improvement for Standard Edition. System Version Temporal Tables is a feature which is available in ALL editions of SQL Server 2016 from the very beginning.
It is not a bug although a few fellows have mentioned it as such. The error statement is clear pointing to the root cause of the problem.
Why should the application avoid adhoc statements? The problem is not the adhoc statement but the chronology of the beginning of the transactions. An adhoc statement is an implicit transaction which follows the same rules than explicit transactions (as in the demo!)
The only correct answer is 5 because the "problem" is a standard functionality of System Versioned Temporal Tables under ANSI 2011.
Microsoft SQL Server need to have a valid and stable time stamp for the time, when the original record will become invalid. That "could" be the time when the DML-operation starts but Microsoft SQL Server is always referring to the time when the transaction starts!
When T1 starts, it has a time stamp which is before the time stamp of T2. When T2 steps into the process it is a later time stamp and there is - at that time - no X-lock on the resource. So T2 can change the record and set the ValidFrom to the beginning time of T2.
When T1 comes to the update it wants to update the ValidFrom to the earlier timestamp and this operation fails.
The only chance to cover this is a redesign of the stored procedure and implementation of a BEGIN TRY CATCH Block.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.