Voice of the DBA

Author: way0utwest

Data Quality on the Open Web
Input from customers like this must include data quality checks

I used to hear that one of the strengths of Linux was the thousands of volunteers that would help you get a patch or a fix in record time when you reported an issue. That worked well, but not well enough for many companies that really wanted a company to stand behind patches. A few companies, like Red Hat, sold support agreements with the “free as in beer” OS that ended up costing companies almost as much as a regular license of another OS. While Linux is a great system, it hasn’t taken over the world like many people thought.

Lately there’s been a different flavor of open-ness on the Internet. It seems that so much of what we read and is pushed out to us as news or information is based on the crowd-sourcing of what’s popular. Facebook shows a “most active” view, Twitter has trending topics and re-tweets, and many news sites like Reddit use a crowd voting mechanism to help determine what you see first on their front page.

However there’s a downside to using these open systems. There’s the potential for abuse when a group of people get together. Google started using the open model on it’s map services to allow people to add businesses to maps. A very handy feature, butthe addition of a “mark this as closed” button allowed people to abuse this privilege. Whether it was competitors, vandals, or some criminal element isn’t known, but apparently the quality of data Google is providing on maps isn’t necessarily accurate. With many people using maps on iPhones and Android devices, this could damage businesses that add themselves to the mapping service. I think Google is playing a little fast and loose with their crowd voting on data points, but with so many companies looking to capitalize on the social networking phenomenon, I’m not surprised it’s being abused.

Whenever we build systems that take input from users, we have a maxim: garbage in, garbage out. Essentially we aren’t responsible for bad data, but many companies won’t feel that way. They will still feel that we ought to be better policing the data quality and not showing bad data in reports or downstream systems. As more and more companies look to incorporate data from customers into their systems, it becomes more important that data professionals incorporate automated scans and manual workflow checks before data moves from staging areas to prevent incorrect data from affecting our production systems.

Steve Jones

The Voice of the DBA Podcasts
- Watch the Windows Media Podcast – 24.0MB WMV
- Watch the iPod Video Podcast – 17.6MB MP4
- Watch the MP3 Audio Podcast – 4.0MB MP3
September 21, 2011
Decimal Data Types in SQL Server
I learned something new about decimal data types in SQL Server recently. If you do something like this:
```
DECLARE @d DECIMAL(10,10)
SET @d = 1.0
```
You get this:

Msg 8115, Level 16, State 8, Line 2

Arithmetic overflow error converting numeric to data type numeric.

That threw me for a minute since I’d never experienced it, but then I realized what was going on. The declaration of a decimal data type goes like this:

DECLARE @d DECIMAL( @p, @s)

Where we have
- d – the name of the variable
- p = precision, the number of digits in the number.
- s = scale, the number of digits for the decimal
You can think of this as the following,

@d = nnnn.ssss

Where the count of n’s and s’s must equal p.

In the first example above, I’ve essentially declared:

@d = 0.ssssssssss

There’s no room for a digit, other than zero, to the left of the decimal. So you get an error. If I’d added one more digit to the p variable, like this:
```
 
DECLARE @d DECIMAL(11,10)
SET @d = 1.0
```
I don’t receive an error. Likewise, I can add multiple digits to the other side like this:
```
DECLARE @d DECIMAL(10,10)
SET @d = 0.999999999
```
Works fine.

I had never run into this because I don’t ever declare these the same. I almost always go larger than I need, and allow for more decimals. So for US money, I often declare things at decimal(10,3), giving me more space than needed. It pays to think ahead, and declare your variables properly, and understanding how they are structured is part of that.

Note: this applies to numeric types as well.
September 20, 2011
Free as in Beer

My Favorite Beer

This editorial was originally published on Feb 7, 2006.

It’s amazing to me that someone can take the words from another and publish them as their own. I guess that’s why sites such as essayrelief.com. And why other sites like turnitin.com have sprung up to help combat that type of behavior. Actually if you want to get a great Christmas Present for your kid’s high school, get parents to raise money to subscribe to some plagiarism prevention service.

Actually a Google search on “essay help” turned up entirely too many places where you can get an essay written. Amazing how many of these tout their essays as “100% non plagiarized”. I guess if you hire writers to write something, but that’s a small step away from stealing someone else’s work for profit or gain. It’s just plain wrong to hire someone to do your work, as is starting to happen on Rent a Coder, where people are paying to get computer science assignments completed for them. There was a great Wall Street Journal article on this very topic.

And unfortunately, another article today from me on the same topic.

This is a great quote from the WSJ:

“This is a fairly simple program,” wrote “Goradia” of Sammamish, Wash. “It is my homework, but since I am busy, I want someone to do this for me.”

If it’s so simple, just do it. It’s your work, you are paying (in college) to learn, or investing in yourself in high school. Just do the work and accept the grade for your work. I cannot fathom why people do not want to earn their own accomplishments. I guess there always will be people that cheat, but it’s still amazing to me.

The only things you have in life are those that you earn and the mistakes you make. Enjoy them, relish them, learn from them, accept your failures and work to do better.

September 20, 2011
Map a Login – Basic Skill #3
This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

I wrote about the basic security model for SQL Server, and got a question about mapping users to logins. It actually is done automatically for you in the dialog when you create a login, but I thought I’d cover the basic process here in more detail.

Logins allow access to an instance of SQL Server. Users are the construct in a database that can be assigned permissions (explicitly or through a role). The mapping between a user and a login is what allows SQL Server to determine which logins get which rights.

Let’s look at an example. On one of my instance, I have a test login called “JoeLogin”. If I connect to the instance, I enter the credentials of “JoeLogin” and the password to connect.

Once I connect, however, the first thing the SQL Server database engine does is set my context to either the database I’ve specified, or my default database. This immediately maps me to a user in that database and allows me the connection to run commands, or it returns an error if I don’t have access.

In this case, I have access to my default database, and I can check on my user credentials with this code:
```
SELECT USER_NAME()
```
This returns “JoeUser”, which is my user name. You can read about user_name() here, but it is a system function that returns your current database user name.

My login has essentially been bypassed, and would only be used if I needed to check permissions to execute an instance level function, like setting a configuration value or viewing the error log. However I can check my login by using the SYSTEM_USER function.
```
SELECT SYSTEM_USER
```
This will return my login name, and it returns “JoeLogin” on my instance. I can easily see this in SSMS, in the right corner of the status bar at the bottom of the window.

The Mapping

We can view the mapping between users and logins in two ways. If you want to see where a user is mapped, you can right click the user and select properties.

This will bring up a dialog for the user, and at the top you can see the login mapped to this user:

If you want to see where a login is mapped, you can right click the login in the server level Security folder and select properties. This brings up the login dialog, and if you select the “User Mapping” item from the left pane, you will see the list of databases and the user mappings.

In this example, my JoeLogin has been mapped to two database. In db1, the default behavior is applied and the login is mapped to a user with the same name. In db4, I have changed the default and mapped to a user called “JoeUser”.

I haven’t run across a good reason to change the user name from the login name, and I don’t recommend it, but if you think you might have some issues, this is how you check things.
September 19, 2011