Voice of the DBA

Author: way0utwest

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

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

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

    loginmap

    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.

    loginmap2

    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.

    loginmap3

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

    loginmap4

    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.

    loginmap5

    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.

  • Intertia

    Inertia can be hard to overcome

    This editorial was originally published on August 22, 2006.

    My son asked me about physics the other day and what it meant. He’s been on a bit of a science kick, looking to understand and explain his world. I can understand that because most of us are curious about how things work in our world, not necessarily in a scientific way, but just in general.

    I somewhat butchered the definition because I was caught up slightly in the actual concepts, but one of the things we talked about was inertia. Maybe because I’d seen this article on overcoming institutional inertia the same day.

    We all have things at work that we do a certain way. Maybe we learned it that way, maybe we’re more comfortable, or maybe someone yelled at us daily until we did it that way. Regardless, fighting the way people work is a difficult thing to undertake. The article gives a few examples of process changes and how they were effectively moved forward and how the issues were resolved at a high level. It’s common sense if you’re wondering, use diplomacy and take your time.

    I’ve seen changes occur from both sides of the fence, from someone trying to implement change and from someone who was told changes were occurring. I was never completely comfortable in either situation mostly because with change there’s usually conflict. Someone doesn’t want to change.

    As DBAs we often try to control our world and ensure a high degree of stability, often at the cost of slowing or severely impeding changes in our systems. While I think it’s good to aim for a stable, reliable environment, I don’t think this should necessarily impede change.

    Understanding the business needs and goals is critical both for DBAs in protecting their systems and developers in building new applications. In either case, it also means that the needs of your organization need to dictate how quickly things change.

    Not an employee’s personal feelings.