Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Can you point to some articles that say that smaller data types = more processing? Keeping in mind that even with SSDs most workloads today are I/O-bound (or memory-bound) and not CPU-bound.</p> <p>Particularly in cases where the PK is going to be referenced in many tables, it will be beneficial to use the smallest data type possible. In this case if that's a <code>SMALLINT</code> then that's what I would use (though you say there are about 200 values, so theoretically you could use <code>TINYINT</code> which is half the size and supports 0-255). Where you need to exercise caution is if you aren't 100% sure that there will always be ~200 values. Once you need 256 you're going to have to change the data type in all of the affected tables, and this is going to be a pain. So sometimes a trade-off is made between accommodating future growth and squeezing the absolute most performance today. If you don't know for certain that you will never exceed 255 or 32,000 values then I would probably just an <code>INT</code>. Unless you also don't know that you won't ever exceed 2 billion values, in which case you would use <code>BIGINT</code>.</p> <p>The difference between <code>INT</code>/<code>SMALLINT</code>/<code>TINYINT</code> is going to be more noticeable in disk space than in performance. (And if you're on Enterprise, the differences in both disk space and performance can be offset quite a bit using data compression - particularly while your <code>INT</code> values all fit within <code>SMALLINT</code>/<code>TINYINT</code>, though in the latter case it really will be negligible because the values are unique.) On the other hand, the difference between any of these and <code>GUID</code> is going to be much more noticeable in both performance and disk space. Marc gave some great links from Kimberly; <a href="http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html" rel="nofollow">I wrote this article</a> in 2003 and while it's a little dated it does contain most of the salient points that are still relevant today.</p> <p>Another trade-off that sometimes needs to be considered (though not in your specific case, it seems) is whether values need to be unique across multiple systems. This is where you might need to sacrifice some performance in order to meet business requirements. In a lot of cases folks take the easy way and resign themselves to <code>GUID</code>. But there are other solutions too, such as identity ranges, a central custom sequence generator, and the new <code>SEQUENCE</code> object in SQL Server 2012. <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx" rel="nofollow">I wrote about <code>SEQUENCE</code></a> back in 2010 when the first public beta of SQL Server 2012 was released.</p>
    singulars
    1. This table or related slice is empty.
    1. POChoosing SQL Server data types for maximum speed
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAaron Bertrand
    1. USAaron Bertrand
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POChoosing SQL Server data types for maximum speed
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe thing with tinyint is that line numbers are not consecutive, and the range is 1 - 800. I am sure that they're not going to exceed 255 values, so I could use tinyint but that would mean to store the number in a separate column. Sure some additional 200 smallints are worth for 200.000 tinyints instead of smallints in referencing tables, but what I can think of right now is the pain when I'm going to debug, and sure it's gonna be a lot of it.
      singulars
      1. PO
      1. USTiborg
    2. COThen I would say stick with `SMALLINT`. As I alluded to above, you're not really gaining much at all between `SMALLINT` and `TINYINT`, and I would prefer to stick with the natural key when it exists than to create some arbitrary surrogate. Even though the "natural" key is in itself just a surrogate from another system.
      singulars
      1. PO
      1. USAaron Bertrand
    3. COYes, thanks for the advice. I think I read articles that said smaller data types = more processing a long time ago and this idea stuck in my head. I found some of them but they are also old, and they also mentioned that it's not the case with present-day systems.
      singulars
      1. PO
      1. USTiborg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload