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

The Danger of Algorithms

This editorial was originally published on July 27, 2009. It is being re-run as Steve is out on vacation.

There is a report that came out recently that shows how you can predict Social  Security numbers, an important piece of identity information in the US. This, according to this C|Net article, could result in massive fraud taking place if someone's birthday is disclosed.

So many sites want to get this piece of information from you, often to ping you on your birthday or give you some gift. I've always been wary, however, and usually put in April 1 instead of my real birthday. It makes for lots of birthday wishes on that day, but that's OK. I appreciate the thoughts, even if they are a few months off.

This does highlight the danger of using an algorithm to generate data. Unfortunately there are plenty of people out there that will maliciously find ways to mis-use data, and if they can guess how you generated the data, they can extrapolate that out to calculate what other data might exist in your system. I know most people that need to generate codes often don't spend a lot of time ensuring they've picked a good method from a security point of view.

The key here is to keep pieces of information somehow separate, to make it more difficult for a criminal of some sort to perform the extrapolation. That gets harder and harder to do, primarily because of the job many of us do. We gather data into SQL Server and other platforms, and make it easy to put this data together.

As with many of the problems I see in today's world, I don't have a perfect solution to this problem. However I think that many of us handle data insecurely, often comparing actual values when a hash, or digital signature might work instead. I know some of that is because we don't have great tools for working with digital signatures, but also because it's a complex process.

Credit card companies, banks, and other institutions often have complex rules for how they handle and process data. I think this more of their secure methods of handling data should be published and taught so that other companies can better learn how to build more secure applications.

Steve Jones

The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

You can also follow Steve Jones on Twitter:

Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

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

Fundamentals of SQL Server Replication eBook

Get to grips with SQL Server replication

In this FREE eBook, Sebastian Meine guides you through the fundamentals of SQL Server Replication, to the point where you should feel comfortable using it in production. Download the free eBook.

14 Backup Questions Answered

14 SQL Server Backup Questions You Were Too Shy to Ask

Read Grant Fritchey’s free PDF and get the answers to some of life’s big questions, like, “How do I retrieve a table from the log?” and, “Are SAN backups enough?” Download the free article PDF.

SQL Source Control

24% of devs don’t use database source control – make sure you aren’t one of them

Version control is standard for application code, but databases haven’t caught up. So what steps can you take to put your SQL databases under version control? Why should you start doing it? Read more to find out…

Featured Contents


Percentile calculations in DAX

Antonio Abalos Castillo from SQLServerCentral.com

It is not always easy to do what Excel does with PERCENTILE.INC. Learn what formulas to use in this article. More »


SQL Saturday #308 - Houston, TX

Press Release from SQL Saturday

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held May 10, 2014. Register while space is available. More »


Big Data Quality Metrics

Additional Articles from Database Journal

This article outlines how to measure resource usage, and use these measurements to develop quality metrics for Big Data applications. More »


From the SQLServerCentral Blogs - SQL Server Diagnostic Information Queries for April 2014

I made some small improvements to a few of the queries this month. I plan to add several more SQL... More »


From the SQLServerCentral Blogs - Central Management Studio CMS

David Postlethwaite from SQLServerCentral Blogs

By David Postlethwaite At my presentation on SQL Server Management Studio at SQL Saturday in Exeter I promised to write some... More »

Question of the Day

Today's Question (by Steve Jones):

When is an automatic checkpoint taken for memory-optimized tables in SQL Server 2014?

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

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 Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

How would I change the port that SQL Server listens on?

Answer: Use Configuration Manager and the TCP/IP properties for the instances protocols


The answer is to use the Configuration Manager in SQL Server to set a specific port for your instance.

Ref: Change the Port Number for SQL Server - http://www.sqlservercentral.com/articles/Port/75740/

» Discuss this question and answer on the forums

Featured Script

Exporting data with header using Powershell

Louis Li from SQLServerCentral.com

1. Save this script (e.g. csv.ps1)

2. Run .\csv.ps1 <server name> <database name> <SQL script file>

Output files will be generated under same folder this PowerShell script is in.

Note: In SQL Script file, multiple statements can be added and they will be saved into files sequentially. Filenames are sequential numbers.

For example, master.sql:

select * from sys.configurations


select * from sys.master_files


Run: .\csv.ps1 . master master.sql

2 csv files will be generated:



They contain results from these 2 queries.

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 2012 : SQL Server 2012 - T-SQL

Replace code in SP - Hi, In this SP [code="sql"]SELECT TOP (@topval) dbo.bigtb.fire, dbo.maingroup.maingroup, dbo.bigtb.subgroup, dbo.maingroup.groupnamefa, dbo.subgroup.subgroupfa, dbo.bigtb.title1, dbo.bigtb.tbody1, dbo.bigtb.film1, dbo.v_userpro.pageview, ISNULL(dbo.pictb.picurl1, '') AS picurl1, dbo.pictb.tooltip1, dbo.pictb.main1, dbo.bigtb.photo1,...

recursive queries - parent child - Hi everyone i need to make some parent-child transformation. here is test input data: [code="sql"] CREATE TABLE #TestTable( [Code7] [varchar](7) NOT NULL, [Code1] [varchar](1) NOT...

Get original order quantities and dates, and find any changes or additions to quantities in subsequent order version(s) - I am due to work on this problem, but I have a lot of trouble trying to figure out how...

What is wrong with this syntax? Query will work, but CTE will not "compile" - [code="sql"]with UnloadDates as select DISTINCT ShipmentID, (select Min(starttime) from tblInvoice I where I.ShipmentID = O.shipmentID and DataSent is null ) StartTime, (select Max(Endtime) from tblInvoice I where I.ShipmentID = O.shipmentID and DataSent...

More than 1 alphanumeric chars in a string - I have an employee table Surname GivenName ABC x.yz A.BC X.*YZ A*.BC xyz The query needs to get me the surname and givenname which have...

Unknown object type 'SEARCH' Error when I try to CREATE SEARCH PROPERTY LIST - I'm following along in Itzhik Ben Gan's book for Exam 70-461 Querying Microsoft SQL Server 2012, in chapter 6 on...

SQL Server 2008 : SQL Server 2008 - General

Adding parameters to SQL in Excel - Hello All, I have excel spreadsheets that I use to connect to SQL Server by ODBC for my end users. I...

how to setup SQL instance - hello everyone can you please help.I have downloaded SQL Server 2008(im a novice). i wnt to use management studio but cannot...

Error when opening BIDS 2008 R2 - SQL Server 2008 R2 - Urgent - Hi, from two days when I launch BIDS 2008 R2 I've an error that says it isn't possible to find one...

Creating .mdb file from sql server db - Hello, This is my first time asking a question in this forum. I hope y'all (yes, I'm from the south) can...

Distribution of data, percentile curve. - Dear Reader, Fan: Of Percentile curves in Excel. This presents in Excel a value (or a count of values) and the percentile...

Tables on diffrerent files - Is it a good practise to have my tables in different files. Say i am having 10 master tables. Can i create...

The client was unable to reuse a session with SPID 799, which had been reset for connection pooling. - Hi SQL server version : Microsoft SQL Server 2008 R2 (SP1) - 10.50.2772.0 (X64) Enterprise Edition Windows version : Windows NT 6.1 <X64>...

Log Shipping Restore operation "stuck" at 100% completion - I have a LSRestore job for a database that's been "stuck" at 100% for the last 3 hours. The t-log...

SQL Server 2008 : SQL Server Newbies

Error in Drop Create stored procedure. - New at SQL Server 2008 R2 ... The script below generates a stored procedure. Just recently I start getting an error...

SQL Server 2005 : SQL Server 2005 General Discussion

ALTER INDEX REORGANIZE Compacts pages that contain large object (LOB) data. Does rebuilding also do it? - hi, ALTER INDEX REORGANIZE Compacts pages that contain large object (LOB) data. Does rebuilding also do it? yours sincerely

SQL Server 2005 : SQL Server 2005 Integration Services

Conversion failed because the data value overflowed the specified type error - I have a .net etl package that imports data from a table in Oracle into SQL Server, however the job...

Data Warehousing : Integration Services

Avoid Loading the same Excel File twice to a OLE DB Destination - Hi Everyone, I have an SSIS package that loops through a folder looking for and then loading excel files into SQL...

SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Career : Certification

Upgrade Exams - AKA Certification Tribulations I've previous 2008 certification but, before Christmas, I failed upgrade exam 70-457 (by ONE question. gah.) Next time...

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 ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com