SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Scripts

The Voice of the DBA

The Randomness of Analog

One of the joys for much of my early life was walking through a library, looking for a book to curl up in the corner with and read. As a young boy, I would walk to a local library and read at the wooden tables before I had my own library card to check out books. Later, I moved and had a newer library with large, comfortable chairs in which to sit and read a few pages. I've enjoyed the same thing as an adult in various bookstores.

I didn't often have a specific book I wanted to read, so I'd randomly walk around, looking at spines, covers, and choosing a book in a somewhat random fashion. My fellow founder at SQLServerCentral, Andy Warren, also appreciated the randomness of browsing in a library for bookstore, discovering some new author or story to enjoy. Across the years, we've discussed and debated whether or not there was a way to duplicate this experience with technology.

These days I tend to buy or borrow all my books electronically. The convenience and unparalleled and time is valuable, and I certainly don't miss the days of packing 4-5 large books for a week long conference trip. However, Amazon and my local library tend to use recommendation algorithms, or popular titles as the presentation method for their sites. I have lost quite a bit of the ability to enjoy the randomness that comes from wandering and happening upon new titles. Andy feels the same way, though none of our brainstorming has produced a way to duplicate the feeling of wandering through bookshelves in an electronic fashion.

I'm not sure if there is or isn't a way to deuplicate this electroncially, but certainly the feel isn't the same on a screen. All too often our focus when working with data is narrowed to a limited set of choices. And often when we build applications and provide data to users, we are trying to be exacting and relevant, not random. So much of what we choose to do in software is to remove much of the randomness from our systems. Event the "browse" features are often scoped or focused in a particular topic, subject, or area.

This filtering to a particular bubble of data is one of those areas where we have tremendous power in shaping the world. The code and queries we write, the organization of our data, this will have an impact on our users, and I'm not always sure this is for the best. Perhaps overall this is more helpful, but it also serves to prevent us from viewing the forest, only seeing the trees. If you doubt this, try browsing the internet in Private mode sometimes and run searches. You might be amazed how different the Internet can look.

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 ( 4.2MB) 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

Free whitepaper: Solving the database deployment problem with Database DevOps

Learn how to extend DevOps practices to SQL Server databases, so you can spend less time managing deployment pain and more time adding value. Download the free whitepaper

SQL Prompt

Write, format, and refactor SQL effortlessly with SQL Prompt

Writing SQL is 50% faster with SQL Prompt. Your SQL code can be formatted just the way you like it, you can create and share snippets with your team, and with tab coloring you’ll never deploy to the wrong environment again. Download your free trial

Featured Contents


Pattern Recognition via Principal Components Analysis

Nick Burns from SQLServerCentral.com

We'll look at using principal components analysis to help visualise your data and detect underlying structure or patterns. More »


SQL Data Aggregation Aggravation

Additional Articles from SimpleTalk

When we have to deal with and store a lot of data, it makes sense to aggregate it so that we store only the information we actually need. If we get this right, this works well, but the design of the system takes care and thought because the problems can be subtle and various. Joe Celko describes some of the ways that things can go wrong and end up providing incorrect, inaccurate or misleading results. More »


Guide available for enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform

The EU Global Data Protection Regulation (GDPR) goes into effect on May 25, 2018 - and we know that many of you are searching for guidelines and recommendations for how to properly handle the data privacy and data protection requirements stipulated by the GDPR. This guide provides technical guidance and best practices for addressing data privacy concerns and enhancing your overall data protection and security strategy with Microsoft SQL technologies. More »


From the SQLServerCentral Blogs - Power BI Report Server (On-Premises) for All Data Sources is Available!

Aaron Nelson from SQLServerCentral Blogs

Power BI Report Server August 2017 Preview is now available. Think of this a “v.Next” of Power BI Report Server... More »


From the SQLServerCentral Blogs - SQL Server: SARGability part 2

Daniel Janik from SQLServerCentral Blogs

In case you missed the first post on this topic, you can read it here: SQL Server: SARGability. This post... More »

Question of the Day

Today's Question (by Steve Jones):

I want to create a large database in the year 2017, but I don't want to calculate a number of bytes. What are the options for the scale of numeric values in the MAXSIZE parameter? Meaning, as an example, can I use 10KB as a value instead of choosing 10240?

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.


Expert Scripting and Automation for SQL Server DBAs

Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Alan Burstein):

How many times will the following T-SQL query print the text: hi ?

      @x INT,
      @y INT = NULL;
  IF @x = @y
      PRINT 'hi';
  IF @x = NULL
     OR @y = NULL
      PRINT 'hi';
      PRINT 'hi';
      PRINT 'hi';

Answer: Once for "if not (null is not null)"


Provided that ANSI_NULLS are ON, The only T-SQL statement above that evaluates to true is:

if not (null is not null)

If ANSI_NULLS was OFF then all those statements would evaluate to true. 

NULL values are not equal to anything in SQL Server, not even other NULL values. NULLs are also not unequal to anything. Think of comparing NULLs like looking at two bags with an unknown quantity of apples and asking if both bags have the same number of apples: both "yes" and "no" are incorrect.

The exception (forgive the pun) is for set operators such as EXCEPT, INTERSECT and UNION where NULLs are treated as equal. Here' a good article by Robert Sheldon about this topic:How to Get NULLs Horribly Wrong in SQL Server

» Discuss this question and answer on the forums

Featured Script

Database Restoration SQL Procedure for SQL Server 2014.

SQL Master from SQLServerCentral.com

Procedure Name : usp_db_restore
Objective : This procedure is used for Database(Full, Differential, Transaction Logs) resoration (This script will restore database with recovery mode, you can alter the procedure acc. to your need)
Author : Rahul Biswas
Create Date : Rahul Biswas
Modified By : Rahul Biswas
Modified Date : 31th AUG 2017
E.g. as
exec usp_db_restore 'rahulnew', 'c:\test\Rahul\', 'B','c:\test\Rahul\','c:\test\Rahul\'
exec 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
Note : backup path includes backup name only not with extension for E.g. backup file as 'c:\test\Rahul\test.bak' than you've specify 'c:\test\Rahul\test' in the parameter.

More »

Featured Script

Add new Databases to a AG Setup with PowerShell

Edward Pochinski from SQLServerCentral.com

Edit the variables at the top with the correct names, ports and make sure SPN's are correct and in place I have had some issues using the main AG PowerShell setup script at the end snapping in the replicas due to missing SPN's. This is pretty simple to use and not much code considering what is accomplished as the end result. I hope you are able to make good use of the script. It is kinda fun to sit and watch PowerShell do the work. I added some write-host code so you know where the script is at while it is processing.

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

msdb data file shrinkfile - Large Database - Hi , The msdb data file on a prod db server  is 6 GB, Though I truncated the large tables  when...

Can you encrypt a replicated database if the source DB is not encrypted - Starting a project that requires data in the cloud and the boss wants the replicated DB to be encrypted but...

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

prereqs for ssdt 2015 - Hi.  I want to use ssdt 2015 to get an early glimpse of ssrs 2016.  We don't yet have a sandbox with...

SSIS Packages: SQL 2012 to SQL 2016 - We are testing our SQL 2012 applications against SQL 2016 to determine what (if any) changes are needed. Things have...

difficult (for me) query to count available items - I have a difficult sql calculation to make, and I dont't find the answer. Can someone help me? I have a...

Use of always encrypted columns in temporary table - I have implemented always encrypted for test purpose. It works fine until I query/update from physical tables. But inserting always...

SQL Server 2014 : Administration - SQL Server 2014

Rebuilt SQL Server 14 system databases, not functioning, pointing at non-existent files - I rebuilt system databases for a SQL Server 2014 installation. The server will still not restart, it cannot find MSDB and...

SQL Server 2014 : Development - SQL Server 2014

How to encrypt Credit Card numbers? - We have now finally moved all our customers to SQL 2014 from old or very old SQL versions and I...

SQL Server 2012 : SQL 2012 - General

Running total with reset condition - hi all, i would like a running total with a restart after a certain limit.  Let set the condition to be...

Hardening windows AD account running sqlserver service and sql agent / and or changing the password - Hi  I am looking into changing the AD account passwords running SQL services for all SQL server installations in our sqlserver...

SQL Server Standard Competencies - We want to hire people who have SQL Server skill, does anyone having standard competencies for SQL Server 2012 ?

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

AO Listener creation failed - Error 19471 - Hi. Now I am facing issues for creating AO listener in this setup. CNO pre-staged in AD cluster name object created...

SQL Server 2012 : SQL Server 2012 - T-SQL

Fulltext index population taking a *long* time - Is there *any* way to determine how far a fulltext index rebuild has progressed? I have a table which has...

SQL Server 2008 : T-SQL (SS2K8)

Query returns 122 million records in 9 hours - We have a very silly weekly process to to do the Full Load of 5 year data in to Qlikview Application...

Pass Distinct Results to New query (IN) - Good morning, I have written a query that returns up to 4  rows of a distinct result; Results 1. A 2. B 3. C 4....

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

Reporting Services : Reporting Services

This may be due to a connection failure, timeout or low disk condition within the database - Hi All, I am getting this error whenever I try to run a report through report server. It appears to be...

Data Warehousing : Integration Services

Facebook data with SSIS - Hi, Did anyone ever tried to import facebook data to SQL with SSIS? I know that I have to use...

SQL Server 2005 : Administering

Total Server Memory Vs Target Server Memory - Hi, We have SQL Server 2005 EE x64 with SP3. RAM = 16 GB Max Server Memory = 12 GB Min Server Memory = 0 default...

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