Today we have a guest editorial from Andy Warren as Steve is out of the office.
As IT professionals and knowledge workers we don’t need much in the way of personal equipment. A carpenter or plumber might have as many as a hundred tools and assorted items to support their day to day work. What do we need? A computer, monitor, keyboard, mouse (or trackball or trackpad), a place to sit or stand, and probably a chair. Given that you have all of those items, which matters most, or at all beyond having it? Not a question with a one size fits all answer, but still interesting.
At work I use a name brand $20 wireless keyboard. It works. I can’t say I’m less productive because of it. Same for the mouse. Dual 23” or 24” monitors, an OK (at best) chair, and a nice standing desk. Better than some places for sure. At home I use three 27” monitors, a standing desk, a USB dock, a chair I like, a mechanical keyboard I like, and now that I think about it, an OK mouse. The monitor arm I use at home is on the list to be replaced - functional, but just not what I want long term. Three 27” monitors is perhaps right on the edge of too much, its close to 6 feet from side to side even when mounted on an arc. Maybe I’ll change that, too!
Thinking about all of that, I ordered myself a mechanical keyboard for work, knowing that I might not like it (this one has cherry red switches, the one at home uses brown) and that it might bother colleagues. Just unboxing it evoked some interest and soon others joined as we discussed who used what, and then someone brought out a key switch sampler that we all spent a couple minutes clicking. I watched someone who uses one of those flat Apple keyboards break into a smile when they tried the green switches and heard/felt the crisp click.
My point isn’t to say you have to have a lot of equipment, or the best equipment. Plenty of people do great work on a laptop with no accessories. I can’t tell you that you would be more productive if you spend more more money on a keyboard, or switch to using two, or three, or six monitors. No, it’s to remind you, even challenge you, to revisit your tool set once in awhile and think, does this work for me well enough, is it time to make a change? Humans are adaptable, sometimes too much so; we’ll keep using that keyboard with the key that sticks long beyond the point of common sense.
For me, a little change here and there is good. I think my next big experiment will be trying a 32” or 34” monitor because scrolling to look at query plans isn’t fun and zooming in and out isn’t fun either. Drop me a note in the forums, I’d love to hear what you use and like (or don’t like) about the tools you use.
This post will show the benefits of test-driven development and including automated SQL Server unit testing within your release pipeline. Even if you have a large code base and no existing unit tests, you can start introducing tests now to make your database code more robust to change. More »
Graph databases are useful for certain types of database tasks that involve representing and traversing complex relationships between entities. These can be difficult to do in relational databases and even trickier to report on. Until now, we have had the choice of doing it awkwardly in SQL Server or having an ancillary database to tackle this type of task. SQL Server 2017 will be bringing graph capabilities to the product but will these features prove to be good enough to allow us to dispense with specialised Graph databases? Dennes Torres decided to find out. More »
Yet another tale from the ticket queue...
The DBCC CheckDB was failing on INSTANCE99 and after some investigation it looked like... More »
Question of the Day
Today's Question (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?
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
5 points in this category: System Versioned Temporal Tables.
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
Prepare for Microsoft Exam 70-761–and help demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data management, queries, and database programming. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steve Jones):
I have this R code, setting an American football position list:
1. Procedure 'usp_db_restore' is used for restore database on each replica associated with Always-On HADR (Secondery replicas). This procedure will be used internally in my next procedure to add database in Always-On.
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.