Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Best Practices for Index Column Order, Money Data Type and DBA Learning Resources


By:   |   Updated: 2019-08-26   |   Comments (1)   |   Related: More > DBA Best Practices

Problem

A webinar about SQL Server Development best practices was hosted on MSSQLTips.com. In this webcast, several tips and tricks were shared which could save a lot of time and headaches when writing T-SQL code. You can view the webcast on-demand here. At the end, there was room for questions, but since not every question could be answered in the allotted time frame, we will tackle them in this tip. Some questions are already answered in part 1.

Solution

Q: What is the best SQL Server data type for storing money?

Although there is a (small) money data type, I think the numeric/decimal data type is better suited for storing currency values. The main reason is the possibility for rounding errors. Let’s take a look at a small example:

DECLARE @Denominator MONEY = $301.00;
DECLARE @Numerator MONEY = $189.50;
 
SELECT Percentage =(@Numerator / @Denominator) * 100;			

SQL Server returns the following:

money in SQL Server

When we perform the same calculation in Excel, we get a slightly different result:

money in Excel

The main reason behind this is that the money data type is basically an integer data type (big int for money, int for smallmoney), which means it has limited precision, leading to the rounding errors. The numeric data type doesn’t have this issue. You can find more info in the article Avoid use of the MONEY and SMALLMONEY datatypes.

One notable exception might be when you’re using Analysis Services Multidimensional (SSAS MD) as your OLAP solution. When SQL Server is the source for SSAS MD, it might be an option to use the money data type instead of numeric or floats if you can live with the fixed precision. The reason is because SSAS MD doesn’t really handle numeric/floats really well. Because money is basically an integer data type, SSAS MD processing will be faster when money is used. A pattern might be to store the data as numeric in the tables, but convert them in a view to the money data type. SSAS MD will then read the view when processing. An example is given in the SQLBits session Optimising Cube Processing by Bob Duffy.

Q: Do you know any good resources to learn more about SQL Server database administration?

MSSQLTips.com obviously has a wealth of information: tutorials, articles and many webcasts. There’s a whole section on database administration alone. Another good resource is the book Troubleshooting SQL Server: A Guide for the Accidental DBA or the Accidental DBA series at SQLSkills.com. I learned about database administration by pursuing the Microsoft SQL Server certificates. They have changed a lot in the last years, but the MCSA – Database Administration certification should give you a good overview of how to manage SQL Server. Even if you’re not interested in taking the exam, the preparation materials are excellent material to get you started in the world of SQL Server.

Q: Why does column order matter for a SQL Server index?

In a SQL Server index, the data is sorted according to the columns specified in the index. Meaning, if the index specification states (column A ascending, column B descending), it means the data is first sorted by column A ascending. For the same values of A, the data is then sorted by column B descending. Since the data is first sorted by column A, you cannot easily find a specific value for column B without knowing the value of A. If you don’t know the value for A, you have to scan the entire index to find all the value of B you’re looking for.

Let’s illustrate with an example. In a classic telephone book, the telephone numbers (the records of the table) are first sorted by city, then by family name and finally by street address (at least, that’s how it was done in Belgium). So let’s suppose we’re looking for the telephone number of the family Anderson in the city of Denver. First you look for Denver, then for Anderson. If there are a lot of Andersons in the city, you’d have to know the street address to find the correct telephone number.

Now suppose you’d want to find the telephone number of the Anderson family, but you don’t know which city they live in (you do know the street address though). This would mean you’d have skip to each city in the telephone book, then look for Anderson and check the street address. It’s easy to see this is a lot more work and the same is true for indexes in a database.

We can demonstrate the behavior in the AdventureWorks sample database. The table Sales.SalesOrderDetail has the following primary key defined:

index definition

First SalesOrderID, then SalesOrderDetailID. When filtering on a specific SalesOrderID, we can see the index is being searched:

index seek on first column

The same is true when filtering on a SalesOrderID and a SalesOrderDetailID:

index seek on first column and second column

However, when filtering on SalesOrderDetailID, the seek turns into a scan:

index scan

More data is being scanned, which means the query might be slower. If you’re wondering why in the first two queries the clustered index was used (which is the index implementing the primary key constraint) and in the last query a non-clustered index: because SQL Server knew it had to scan all the data, it’s a better idea to scan a smaller non-clustered index instead of the wider clustered index, minimizing the number of bytes that have to be read.

Next Steps
  • If you haven’t already, you can view the recording of the webcast here.
  • There are many other webcasts available, on a variety of topics. You can find an overview here.
  • Be sure to check out part 1 of this tip series, where other questions were covered.


Last Updated: 2019-08-26


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 26, 2019 - 5:42:05 PM - Ray Herring Back To Top

In response to data types for storing money:

You should never use Float/Real for storing money.  This data type supports a very large range but the accuracy varies over that range and so your result is very dependent on where your values are in the range.

Numeric/Decimal is a fixed point or fixed scale representation also.  You can specify the number of decimal positions and the number of significant digits but once defined those parameters are fixed.

You can (will) experience the same rounding issues with this data type.  The rounding errors are present even if you use a scientific calculator or paper and pencil.  There are value combinations that are just not evenly divisable.  e.g. 100/3.


Learn more about SQL Server tools