| A community of more than 1,600,000 database professionals and growing |
| | | The Control Poll 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 quote in 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. Steve Jones from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
|
| | ADVERTISEMENT | | | | Could your SQL coding be more efficient? 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 |
|
|
|
| | | | | John F. Tamburo from SQLServerCentral.com Most IT departments are moving toward ITIL-aligned practices. Gain a general understanding of the ITIL framework and how Database Administrators can take advantage of it. More » |
| | Additional Articles from Redgate 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 » |
| | Additional Articles from MSSQLTips.com 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 » |
| | Arun Sirpal from SQLServerCentral Blogs SQL Server Deadlocks – Also known as “The deadly Embrace” occurs when there are 2 processes and neither can advance and... More » |
| | Steve Jones from SQLServerCentral Blogs I love dbatools, and I’ve been trying to explore the various cmdlets over time, both to practice my PoSh and... More » |
|
|
| | | 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 Contribution Center. |
|
|
| |
| | Yesterday's Question of the Day |
| Yesterday's Question (by Uwe Ricken): Business Case 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. Test environment The demo table with activated System Versioned Temporal Tables is implemented with the following code (simplyfied for QotD!): Table definition 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 ) ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory) ); GO ALTER TABLE dbo.Employees ADD CONSTRAINT df_ValidFrom DEFAULT ( GETUTCDATE()) FOR ValidFrom; GO ALTER TABLE dbo.Employees ADD CONSTRAINT df_ValidTo DEFAULT ( '99991231 23:59:59' ) FOR ValidTo; GO 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'; UPDATE dbo.Employees SET EMailAddress = '[email protected]' WHERE ID = 1; COMMIT TRAN T1 GO 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 UPDATE dbo.Employees SET EMailAddress = '[email protected]' WHERE ID = 1; COMMIT TRAN T2 Question 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 Explanation: 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" Explanation Answer 1 is nonsence because System Versioned Temporal Tables is a feature which has been implemented with SQL 2016. Earlier versions cannot implement it. Answer 2 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. Answer 3 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. Answer 4 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!) Answer 5 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. » 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. Error moving log.ldf - Hi all, Tried moving log.ldf to another drive but hit error. Script and error as below... ALTER DATABASE dbName set offline ALTER DATABASE... Stalled Query? - Evening Guys, I am making a change to a table that contains NTEXT and an NVARCHAR(4000). The table contains about 100-million... Procedure executes even though nested sub-procedure missing or missing permissions - Hello fellow SQL Server pros... I am a bit confused by a behavior that we are seeing both in SQL... Replace the first charatcer in a String - I want to replace all the values in a Column that starts with 0 to start with 27. Please help. difficult (for me) query to count available items - I have a difficult sql calculation to make, and I dont't find the answer. Can someone help me? I have a... Reg : CPU utilization in server - Hi , I am using SQL server 2014 with below processor configuration for my EDW environment. 4 physical processors with 15 cores in... Moving db files of transaction replication subscriber(Pull) - Hi, I am maintaining a 4 TB database configured as a subscriber(pull) with transaction replication. Db has more than 70 data... Failover Cluster reliability in a shop with limited experience in clustering - We recently had a consultant assist in the setup of a 2-node (both VM's) Failover Cluster, with SQL 2014 installed. For... Call a webservice from T SQL - Good morning, I need to call a webservice from T SQL, how can I do? This call have user and password and i have... Determine Monday of next month and Monday of next week - I am trying to determine the first Mondayof the next Month when the first Monday of this month has passed.... Hourly Sum of Data - Hi, Newbie in SQL. I have 2 tables session & record. Session table stored job start & end time data in 2 session,... Remote query gets killed after ten minutes - Hello We have a server with sql server 2012 SP3 CU5, when we run a query directly at the server it... To automatically change the value of a column if a particular date time is reached - Hi, I am using SQL Server 2012 Express Edition. I need to automatically change the value of status field to "2" if... Query Help - Hi Team, I have data like below. CREATE TABLE #Table ( osid INT,Date DATETIME,Dgrt INT,Avol FLOAT,UpDnvl FLOAT ,Adnm INT ,RlSt INT ,GpRkINT INT ) INSERT #Table SELECT 14390 & Execution plan over estimation - Hi I'm running SQL on a 2008 box and it runs slowly. Looking at the actual execution plan there is an... Update Statistics.. - Hi Experts, Trying to figure out if it might help in solving any performance issues by introducing an additional manual update... PLE - Hi All, Appreciate if anybody can help understand why the physical memory is divided by 4 to determine ideal Page Life... huge diffence direct query vs query view - Engine: SQL Server 2008 R2 Service Pack 2 CU12 (I know, but I'm stuck with it) When I copy the code... Best way to move the log file to a different drive - I need to move the log file for a database to a different drive, what is best way to do... SQL Server job to delete old backups - Hi, I need to create a job that cleans backups and log backups that are older than a month. I do... |
|
| This email has been sent to [email protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected]. | 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. | This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: [email protected] |
|
|
