| A community of more than 1,600,000 database professionals and growing |
| | 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.comJoin the debate, and respond to today's editorial on the forums |
|
| ADVERTISEMENT | | 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 |
| | 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 |
|
|
|
| | | 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 » |
| 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 » |
| 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 » |
| 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 » |
| 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 » |
|
|
| | 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 |
|
|
| | 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. 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... 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,... 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... 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... 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... 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... 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... 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... 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... 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... 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 newsletter@newslettercollector.com. 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 |
|
|