SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

The Voice of the DBA

The Library Job Hunt

I was reminded of the ways I've looked for jobs by a post from the Job Headhunter about library time. Rather than looking for what jobs are available, the recommendation is to take a targeted approach of using the library as a place to focus your energies and find the job that you really want, or at least type of job you want.

Far too many of us look for jobs when we need them, often feeling financial pressures to accept the first offer we get. The pace of hiring usually prevents us from considering multiple offers, as the timing of interviews may not align. We make decisions for expediency, more than any other reason.

We also usually fall from job to job, perhaps accepting a position that doesn't quite suit us, or keeps us in the same position when we'd rather move. Again, time and financial pressures can force bad decisions. There's also the fact that despite the training and schooling many of us have gone through, we don't usually think about other options in the hectic, day-to-day chaos of our lives.

The Job Headhunter recommends spending time in the library researching and thinking about different jobs. Certainly you could do this from the comfort of your home with technology, but I agree with the premise. The library can help you focus without the distractions at home. You can spend time thinking about your life, your goals, your interests, your skills, and more. Come up with a plan that drives your career forward. Certainly your research might give you some unexpected answer, but likely this will help you focus on some intersection of your skills and interests.

Life is short, and there are opportunities out there for you. Choose the ones you want, hone your skills, and actively pursue the employment that fits in your life. The work, the company, the location, whatever is most important to you.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL Clone

SQL Clone: Now supporting databases up to 64TB

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free

Redgate Hub

Stop your database being a bottleneck

The new Redgate Hub is the shortcut to the latest insights into database development. Constantly updated with invaluable content, it’s the one resource you need to solve problems, share ideas, discover new tool features, and expand your database skills. Discover the Redgate Hub

Featured Contents


Moving beyond principal components analysis

Nick Burns from SQLServerCentral.com

Principal components analysis is a standard, but usually terrible, technique for visualising complex data. We're using network diagrams to solve this problem. More »


Searching and finding a string value in all columns in a SQL Server table

Additional Articles from MSSQLTips.com

Sometimes there is a need to find if a string value exists in any column in your table. Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column". More »


Benchmark your Database DevOps maturity level

Whether you’re only just starting to explore the advantages of DevOps or you’re already fully immersed in the journey, including the database brings additional advantages. Take a few minutes to complete our Database DevOps Maturity Assessment and you’ll better understand how advanced your current processes are, receive recommendations for improvements, and see how your maturity level compares with that of your peers. More »


From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 67 – Impact Bubble Chart)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Impact Bubble Chart Custom Visual.  The Impact Bubble Chart is... More »


From the SQLServerCentral Blogs - Dynamic Threshold for Statistic Update - SQL Server 2016

By default, on a SQL Server Database, statistics are updated automatically when one hits a threshold of changes. If the... More »

Question of the Day

Today's Question (by Mike Tanner):

In mathematics, multiplication and division are said to be commutative, meaning that it doesn't matter which order the operation is performed in. 

The way SQL Server performs numeric calculations follows clear rules, including operator precedence and data type conversion precendence. Usually what SQL does is what you'd expect in mathematics, however, sometimes there can be unexpected results.

In this example

create table #t (a real, b int, c int);

insert into #t select 2,3,4;

select a*b/c + a*(b/c) as Total from #t;

What the value of Total? 

It's easy enough to cut and paste and try, but can you get the right answer yourself without running the code?

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 2 points in this category: Operator precedence.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Professional Microsoft SQL Server 2014 Integration Services

Master the fundamentals of Transact-SQL—and develop your own code for querying and modifying data in Microsoft SQL Server 2016. Led by a SQL Server expert, you’ll learn the concepts behind T-SQL querying and programming, and then apply your knowledge with exercises in each chapter. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

At Redgate, we sometimes owe each other beers for mistakes or insults rendered to another. I've got a table with a number of items being tracked.

Since we need to settle up tonight, who is owed the most net beer (owed - owes)? 

WITH myBeers
(SELECT OwedTo, OwedBy, EarnedDate, Beer
('Bart','Steve', '2017-01-25', 4),
('Bart','Andrew', '2017-01-28', 2),
('Andrew','Rob', '2017-08-20', 1),
('Bart','Andrew', '2017-05-08', 2),
('Ally','Tony', '2017-02-05', 2),
('Tugberk','Dave', '2017-05-28', 2),
('Dave','Tugberk', '2017-06-17', 3),
('Bart','Steve', '2017-02-18', 4),
('Bart','Andrew', '2017-06-19', 2),
('Tugberk','Dave', '2017-08-17', 2),
('Tony','Bart', '2017-01-29', 3),
('Andrew','Rob', '2017-08-10', 1),
('Tugberk','Bart', '2017-04-12', 3),
('Bart','Steve', '2017-05-11', 4),
('Bart','Steve', '2017-03-28', 4),
('Andrew','Rob', '2017-02-06', 1),
('Tony','Bart', '2017-04-26', 3),
('Tugberk','Bart', '2017-03-01', 3),
('Rob','Ally', '2017-08-29', 1),
('Bart','Steve', '2017-03-02', 4),
('Rob','Ally', '2017-09-02', 1),
('Bart','Steve', '2017-03-09', 4),
('Ally','Tony', '2017-08-06', 3),
('Bart','Andrew', '2017-06-01', 2),
('Bart','Steve', '2017-05-12', 4),
('Andrew','Rob', '2017-06-02', 1),
('Dave','Tugberk', '2017-03-15', 3),
('Dave','Tugberk', '2017-04-11', 3),
('Tugberk','Bart', '2017-04-10', 3),
('Tugberk','Bart', '2017-06-29', 4),
('Dave','Rob', '2017-03-09', 1),
('Bart','Andrew', '2017-03-26', 2),
('Tony','Dave', '2017-04-11', 1),
('Bart','Steve', '2017-04-20', 4),
('Tugberk','Dave', '2017-02-10', 1),
('Bart','Andrew', '2017-08-27', 2),
('Rob','Tony', '2017-04-05', 3),
('Dave','Tugberk', '2017-04-15', 3),
('Andrew','Dave', '2017-02-03', 1),
('Tugberk','Bart', '2017-05-06', 4),
('Rob','Tony', '2017-07-27', 3),
('Bart','Andrew', '2017-07-16', 2),
('Bart','Steve', '2017-03-12', 4),
('Bart','Steve', '2017-06-06', 4),
('Andrew','Bart', '2017-06-30', 3),
('Dave','Rob', '2017-01-15', 1),
('Bart','Andrew', '2017-03-03', 2),
('Andrew','Dave', '2017-04-23', 1),
('Tugberk','Dave', '2017-04-24', 2),
('Steve','Tony', '2017-06-08', 2),
('Tugberk','Bart', '2017-02-02', 3),
('Tony','Bart', '2017-01-29', 3),
('Tony','Bart', '2017-03-30', 3),
('Bart','Steve', '2017-02-09', 4),
('Ally','Tony', '2017-06-09', 3),
('Tony','Bart', '2017-02-11', 3),
('Andrew','Rob', '2017-01-15', 1),
('Tugberk','Dave', '2017-03-14', 1),
('Rob','Tony', '2017-04-25', 3),
('Dave','Tugberk', '2017-02-01', 3),
('Tony','Bart', '2017-09-04', 4)

) a(OwedTo, OwedBy, EarnedDate, Beer) ) SELECT * FROM myBeers

Answer: Bart


The net owed beer is:

  • Ally: 6
  • Andrew: -6
  • Bart: 18
  • Dave: 6
  • Rob: 4
  • Steve: -42
  • Tony: 1
  • Tugberk: 13

Therefore, Bart is a happy man.

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

SQL Server 2016 : SQL Server 2016 - Administration

Best way to keep users/logins/mappings syncrhronized in HAG between primary and secondary replicas? - We recently had a network issue and the availability groups failed over from the primary to the secondary.  The next...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Assign group identifer to records with Cursor - Hi, Can you please help with this? I have a table called HelpDesk with two fields(RowID and Name), And, I am...

Pivot basic question - All, Apologises for the basic question. I have the following pivot: DECLARE @SQLStr

Merge replication using Web Sync - I have setup Web sync for merge replication, which was working fine for a while. However I now have an issue...

SQL Server 2014 : Development - SQL Server 2014

Which is GOOD/BETTER out of two ways of writing a query ?? - Problem statement : I have 2 tables "DEPT" which contains department details and "EMP" which contains employee details. I am joining...

SQL Server 2012 : SQL 2012 - General

Does sparse columns really work? or at least, how does it work? - Hello to all I was doing some queries to check if i could get any advantage using sparse columns. But i...

Stored Procedure Doesn't return any records - HI All, I have a classic asp site that is connecting to SQL Server 2012 via ODBC and am login in...

execution plan is different on different servers. - Can someone shed light on why this might be? in production the query runs in 1 second, in dev it...

SQL Server 2012 : SQL Server 2012 - T-SQL

Execution time Improvement - Knowing the problem, but not the solution - Hi all, A query I've got isn't running particulary fast (a bout 2 and a bit minutes). I know, in the...

Need help for Genarte HTML with Rowspan. - Hi All, I want to generate the HTML with rowspan and colspan using sql query. I can create HTML table but unable...

SQL Server 2008 : SQL Server 2008 - General

Read Only permissions to a user - Hi Folks, I have a login with 'public' server role and mapped to many databases .  In response to a request to...

Occasional mysterious time outs on SQL Server db table - Hi there, We have users in the field who use a mobile web GIS application (using Java) which use forms to edit/update spatial...

Retrieve the password of sa account - I forgot the password of sa account, therefore I don't be able to login to sql server 2008 I have another...

SQL Server 2008 : T-SQL (SS2K8)

Replace ROWS UNBOUNDED PRECEDING on CTE to work With SQL Server 2008 - Hello comunity I will some help to modify my CTE for working on SQL Server 2008, this is my CTE: DECLARE...

SQL Agent Job Error - String or binary data would be truncated. (Error 8152) - I'm running into the following message, "String or binary data would be truncated. (Error 8152)" when running a...

Mortgage amortization table - *** EDIT 2015-03-04: please be advised the code I posted originally below was in need of some improvement, and Solomon...

SQL Server 2008 : SQL Server 2008 Administration

Database Mail Queue stuck in INACTIVE State - can't get Database Mail to work on Server - Please post to the following: http://www.sqlservercentral.com/Forums/Topic1276632-1550-4.aspx#bm1276973 I execute the following statement to check the status of Database Mail: EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'; The mail...

Reporting Services : Reporting Services

ssrs 2008 export to excel - In a ssrs 2008 report, the users have been exported the report in a PDF format. Now the users want...

Reporting Services : SSRS 2012

Group by Month - I used to be the go-to-guy for SQL and SSRS in another life. But alas, I'm now manager who rarely...

Data Warehousing : Integration Services

Need to use a script data source to pull in 10,000+ characters of data in one Excel column - I have spent two days trying to set up a data source for an Excel file with a variety of...

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