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

Understanding Your Value

Many of us work for some organization and get paid to do so. We might work for ourselves or others, but every so often we hope to get some renumeration for our efforts. For most of us, we negotiated some pay rate and then hope to get annual raises the reflect our work product.

The interesting thing that I've learned in many years of changing jobs and negotiating pay is that many of us have no idea what others doing similar jobs are being paid. There isn't a set scale of pay, unlike many other industries, and since our jobs can vary dramatically from organization to organization, despite having similar titles, we may learn that our compensation deviates dramatically from others in similar situations.

Last year SQLServerCentral ran a salary survey and then released the aggregated data. This was a quick attempt to help you understand where you might stand relative to others, and it was very popular. Brent Ozar took our survey a step further, with a more detailed set of questions and data. This year Brent has beat us to the punch, with a new survey for 2017. I encourage you to participate and help build a useful data set. I plan on doing our own in the new year, probably this month, and hopefully we'll have two good sets of data that you can review and analyze.

Most of us are data professionals, and we know that having more information helps us to make better decisions. Filling out these anonymous surveys can help others understand where they might stand relative to you and vice versa. This is the type of data that can enable you to better negotiate your own pay in the future. After all, there's nothing like presenting some data professional specific results to management to help your case that an adjustment might be needed.

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 ( 2.7MB) 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.

Database DevOps

Benchmark your Database DevOps maturity level

Get a better understanding of how advanced your current processes are, receive recommendations for improvements, and see how your maturity level compares with that of your peers. Complete the Database DevOps Maturity Assessment

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


Automatic Plan Correction

Wayne Sheffield from SQLServerCentral.com

SQL Server 2017 lets you solve parameter sniffing problems - Automatically! More »


Create Size-based Backups for SQL Server 2017

Additional Articles from MSSQLTips.com

Jeffrey Yao explains how to generate backups based on the amount of data that has changed for differential and transaction log backups. More »


How you can improve your SQL with code analysis in SQL Prompt

You could think of the code analysis feature in SQL Prompt as a machine-assisted code review. The rules that are used to check your code point out questionable areas that might be missed during regular testing. More »


From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 81 – Social Network Graph)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Social Network Graph Custom Visual. The Social Network Graph allows... More »


From the SQLServerCentral Blogs - Creating a Knowledge Base Chat Bot w/ Azure Bot Service

dustinryan from SQLServerCentral Blogs

A while back you may have caught my blog post introducing the Power BI Chat Bot 9000. Since posting that... More »

Question of the Day

Today's Question (by Steve Jones):

I have a table with a few rows in it.

ContactID FirstName
1         Tom
2         Peyton
3         Eli
5         Drew 

I have this code. What happens when I run this?

DECLARE @login VARCHAR(100);

SET @login = c.FirstName 
 FROM dbo.Contacts AS c

PRINT @login

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: T-SQL.

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

Which of these converts to this year? (choose 2)


  • 0x07E2
  • 11111100010


7E2 converts to 2018 from Base 16. We break this down as 

7 (*256) = 1792

E (* 16) = 224

2 = 2

This sums to 2018.

In base 2, we need 11111100010. This is a 12 digit numbers. We break this down as:

1 = 1024

1 = 512

1 = 256

1 = 128

1 = 64

1 = 32




1 = 2


These summed come to 2018.

» Discuss this question and answer on the forums

Featured Script

Powershell Script for regular DBA activities_GenerateScripts

Mayank Shukla from SQLServerCentral.com

Some of the common routine activities that we do on Sql Server are :

1. Backup

2. Restore

3. Generate table scripts

For the point mentioned 1 and 2 above, I had tried to ease with this powershell utility  http://www.sqlservercentral.com/scripts/Backup/151723/

In this post, I will talk about a utility which would ease generating table scripts even by an app developer without accessing SSMS.

The advantage of this powershell utility is:

1. It allows the user to exclude identity columns in the data scripts

2. It allows the user to specify the number of rows to be populated in the data scripts.

Let me do a walkthrough !!

On executing this script, it asks for :

  1. Server name
  2. Database name
  3. Mode of authentication : Windows/SQL
  4. If mode of authentication is SQL, then it would ask Sql User name and Password
  5. Table and Schema name for scripting
  6. Various options for scripting
  • Generate schema
  • Generate data
  • Check for object existence
  • Script indexes
  • Script foreign keys
  • Whether to exclude identity columns
  • Whether to limit the number of rows for data script

as seen in figure 1.0.

we get the scripts generated as shown in figure 1.1


If the user enables the feature to exclude identity columns by passing “y” to the option “Do you want to ignore identity columns while scripting data” as shown in figure 2.0 we get the output with the identity column “ResellerKey”  as shown in figure 2.1



The data script contains around 701 rows and we do not put a limit on number of rows as shown in figure 3.0 and 3.1



However, we may require a subset of rows for our testing purpose. Let’s say we need just 2 rows, then we need to pass below parameters:

Do you want to create data scripts for fixed number of rows  : y

Enter the number of rows required in the data script: 2

as shown in figure 4.0


We see that we got just 2 rows in the data script as showing in figure 4.1


How to use the script

Follow the below steps for executing the script:

1. Paste the powershell code mentioned in this article in a text editor and save the file with extension “ps1”, let’s say the file is saved as GenerateScripts.ps1

2. Let’s say the file is saved to a location : c:\utility\GenerateScripts.ps1, create a batch file with the name GenerateScripts.bat in the same location as the powershell file and paste below mentioned code in the batch file

@echo off
powershell -executionpolicy bypass -File .\GenerateScripts.ps1

3. In order to execute the powershell, execute the batch file GenerateScripts.bat

3. After executing this utility, the scripts are saved in the same folder within the file “scripts.sql” file.


To execute the option for exclude identity columns and limit the number of rows in data scripts, the user should be a member of db_ddlAdmin or db_Owner role.

I would love to hear the feedback and suggestions on further improvements to this script !!

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 2014 : Administration - SQL Server 2014

Regarding change the data type(Size) of column -

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