SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Using Tools that Fit

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.

Andy Warren from SQLServerCentral.com

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

ADVERTISEMENT
Database DevOps

Free whitepaper: Solving the database deployment problem with Database DevOps

Don’t let the database be a blocker to DevOps success. Learn how Database DevOps helps your team deliver value quicker while keeping your data safe. Download the free whitepaper

SQL Monitor

Don’t just fix SQL Server problems, prevent them from happening

SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. Download SQL Monitor now and get a 14 day free trial

Featured Contents

 

The Shortcomings of Predictive Accuracy

Nick Burns from SQLServerCentral.com

In prediction, accuracy is key. But it's not all it's cracked up to be as we will explore. More »


 

Why don’t you unit test SQL Server code?

Additional Articles from Redgate

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 »


 

SQL Graph Objects in SQL Server 2017: the Good and the Bad

Additional Articles from SimpleTalk

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 »


 

From the SQLServerCentral Blogs - VLFs the Forgotten Foe

mrathbun from SQLServerCentral Blogs

How many of you check the amount of Virtual Log Files (VLFs) your transaction logs have? Working as a consultant now,... More »


 

From the SQLServerCentral Blogs - The Transient Database Snapshot Has Been Marked Suspect

Andy Galbraith from SQLServerCentral Blogs

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):

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 =  'name@domain.com'
 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 =  'name@domain.com'
 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?

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 Contribution Center.

ADVERTISEMENT

Exam Ref 70-761 Querying Data with Transact-SQL

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

Yesterday's Question (by Steve Jones):

I have this R code, setting an American football position list:

football <- c("CB", "OLB", "LT", "WR", "SS", "DE", "LG", "HB", "CB", "MLB", "C", "QB", "FS", "DT", "RG", "FB" ,"CB", "SLB", "RT", " ", "CB", "DE", "TE", "WR")
dim(football) <- c(4, 6)

When I print this, I get these results:

     [,1]  [,2] [,3]  [,4] [,5]  [,6]
[1,] "CB"  "SS" "CB"  "FS" "CB"  "CB"
[2,] "OLB" "DE" "MLB" "DT" "SLB" "DE"
[3,] "LT"  "LG" "C"   "RG" "RT"  "TE"
[4,] "WR"  "HB" "QB"  "FB" " "   "WR"
 

The defense is the first two rows of the matrix. If I just want to show the defense, what do I type?

Answer: football[1:2,]

Explanation:

The first two rows are retrieved by using the matrix notation and including the range of rows with a colon. As in "football[1:2,]"

Ref: Matrix tutorial - click here


» Discuss this question and answer on the forums

Featured Script

SQL Procedure for adding Database in existing Always-On HADR.

SQL Master from SQLServerCentral.com

There are two procedures which will be used  as :

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.

/****************************************************************************
Procedure Name : usp_db_restore
Objective : This procedure is used for Database resoration in norecovery mode which will be used in procedure for adding databases in existing HADR Always-On.
Author : Rahul Biswas
Create Date : Rahul Biswas
Modified By : Rahul Biswas
Modified Date : 31th AUG 2017
Modifications :
--exec msdb.dbo.usp_db_restore 'rahulnew', 'c:\test\Rahul\', 'B','c:\test\Rahul\','c:\test\Rahul\'
--exec msdb.dbo.usp_db_restore 'DatabaseName', 'BackupPath', 'BackupType','DatafilePath(Includes.mdf and .ndf)','LogFilePath'
--The backup types are: B = Full Backup, D = Differential Backup, L = Log Backup
*****************************************************************************/
2. Procedure 'usp_hadr_add_database' is used for adding database in Always-On. Linked server of each secondary replica is required with same name as sql server name on primary replica..
/****************************************************************************
Procedure Name : usp_hadr_add_database
Objective : This procedure is used for Adding database in HADR Always-On Configuration
Author : Rahul Biswas
Create Date : Rahul Biswas
Modified By : Rahul Biswas
Modified Date : 31th AUG 2017
Modifications :
prerequisite : Linked server of each secondary replica is required with same name as sql server name on primary replica.
Example : EXEC MSDB.DBO.usp_hadr_add_database 'AG_TEST','RAHUL','\\TEST\TEST\','\\TEST\TEST\','D:\TEST\','D:\TEST\'
Example : EXEC MSDB.DBO.usp_hadr_add_database 'AvailibiltyGroupName','DBName','FullBackupPath','TransactionLogBackupPath','DataFilePath(Includes .mdf and .ndf)','LogFilepath'
*****************************************************************************/

More »

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 2016 : SQL Server 2016 - Development and T-SQL

how to send the data to api using sql server - Hi all,          using sql server how to send the ap.please send me some sample code. Regards pols

remove spaces in strings - hi , i hvave a value something like this column scrapetype "WebCrawler.Crawlable.EURRUSSIA.C_Darty ". My join is on this column and it does not...

Solved - Can I join these tables with a query to obtain the final result? - Hi all, I'm wrapping my head around a problem that I'm not being able to solve. Perhaps one of you might...

Query to get last record change per account number - Hello Everyone, I needed a little help! What i wanted to achieve is that, from a table i have several data per...


SQL Server 2014 : Development - SQL Server 2014

Update data with same values - Hello, I have a table with the header and child record in the same table. I need to be able to...

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,...


SQL Server 2012 : SQL 2012 - General

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...


SQL Server 2012 : SQL Server 2012 - T-SQL

Column headers from a date range and populating a matrix - Hello everyone… I have an interesting problem and, while I can think of a couple of horrible solutions, I’d like the...


SQL Server 2008 : SQL Server 2008 - General

Index Seek not possible if you want every row of table, correct? - I have a table, it has a clustered index on customer_ID. I need to return every row of this table via...

Update Statistics.. - Hi Experts, Trying to figure out if it might help in solving any performance issues by introducing an additional manual update...


SQL Server 2008 : T-SQL (SS2K8)

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...

Slow queries during busy times - We have a SQL 2008 Enterprise Edition server with 32 GB of memory and 16 CPU. During quiet times of the...


SQL Server 2008 : SQL Server 2008 Administration

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...


Reporting Services : Reporting Services

Dynamically read images from share path and display in report - Hi  Will it be possible to display the images in SSRS from FTP path ? Two table invoice  & image_info -- table invoice Invoice...


Programming : XML

Help with XML Splitter De-entitization, please. - I'm writing an article on performance testing and one of the functions I'm testing is an XML CSV splitter. Here's...


SQLServerCentral.com : Articles Requested

The OUTPUT clause - Looking for a couple articles here. One on basics of OUTPUT for inserts and deletes. One for updates and combining...

Basics of Statistics (data distribution) - I would like a basic article to explain how statistics are captured for SQL Server tables and updated (auto/manual). A...


Career : Certification

70-473 Anyone studying for this, or passed it? - Hello, I'm studying for this exam, just wondering if anyone else was? Regards, D

This email has been sent to {user_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.
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: webmaster@sqlservercentral.com