Tag Info

Hot answers tagged

53

Disk space is cheap... that's not the point! Stop thinking in terms of storage space, think instead about buffer pool and storage bandwidth. At the extreme end, CPU cache and memory bus bandwidth. The linked article is part of the series highlighting issues with poor clustered key selection (INT vs GUID vs Sequential GUID) but it highlights the difference ...


18

In addition to the other answers... Rows and index entries are stored in 8k pages. So a million rows at 3 bytes per row isn't 3 MB on disk: it affects the number of rows per page ("page density"). The same applies to nvarchar to varchar, smalldatetime to datetime, int to tinyint etc Edit, June 2013 ...


18

OK, let's imagine that you have a distributed database. Let's say you have a node in Oregon and one in California. The CAP theory says that you will run into problems when setting up this type of database. For example, if you query data from one database, it needs to be the same as the data in the other database. This insures that whatever value you have ...


13

This could be viewed as a very speculative question. However, for the database community it should be a no brainer and a slam dunk. Edgar Frank Codd : Inventor of the Relational Model we all model databases with. Christopher J Date : Extensive writer of Database Theory. He keeps evolving database theory and expresses such in his current website. Donald ...


12

Normalization is concerned with all Candidate Keys. A Primary Key is just a candidate key. Primary keys are no different to any other candidate key. Potential confusion arises because in the early days of relational database theory the term Primary Key used to mean any and all candidate keys whereas modern usage is that Primary Key means only one key that ...


11

I would consider spending some time understanding the differences in approach between Ralph Kimball and Bill Inmon with regards to data warehousing concepts. A Dimensional Modeling Manifesto - Differences between ER modeling and DM modeling for your data warehouse. Ralph Kimball, 1997, DBMS Mag Differences of Opinion - Kimball's bus architecture and ...


9

David Hay's Enterprise Model Patterns. This is a beast of a book, but has some great patterns. Conventions of Thought. More stuff on MRP. A Meta-Data Map . Haven't read this one. Len Silverston's Data Model Resource Book Vol. 1. Your main data model patterns. Data Model Resource Book Vol. 2. Case studies by industry. Data Model Resource Book Vol. ...


9

Your second option is a better solution. Add a new column to your tickets table, called ticket_concert_number. The logic to begin at 1 for each new concert is something that's probably best controlled in your application. You should also have a column called concert_id so you know which concert the ticket is for. If you create a new table for each concert, ...


8

This question is really far too vague to answer effectively. There are dozens of "NoSQL" data stores out there which have various use cases. Here is a 10,000 foot view of what's out there. In my mind, there are basically 3 main categories of NoSQL data stores commonly used, key/value stores, document databases, and big data (hadoop). This is a somewhat ...


8

BOL: A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown. NULL means unknown. No other interpretation is valid. If that's ...


8

Michael Stonebraker and the team that did INGRES and POSTGRES were also pretty major contributors to the state of the art.


8

Chris Date, Edgar Codd and Ralph Kimball come to mind, but you'll need to be more specific. (You can also find most of these cats on wikipedia, google, bing, what have you. I'd supply links but I suspect the point of your question is because you need to do research, and don't want things handed to you.)


8

It's not only table storage that is a consideration. If you use indexes where the int column is part of a compound key, you would naturally want the index pages as full as possible, this being the result of index entries being as small as possible. I would definitely expect to find that examining index entries in BTREE pages would be a little faster with ...


7

DML can be considered to exclude SELECT statements. The Wikepidia.org entry for “Data Manipulation Launguage” describes it as follows: The purely read-only SELECT query statement is classed with the 'SQL-data' statements2 and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it ...


7

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc You just don't change the database state when you do. It boils down to whether you read "manipulate" to include "change state"


7

Your points are unrelated to database design: choice of natural or surrogate key is an implementation decisions after conceptual and logical models are complete In addition to comments and other answers: some natural keys work well such as currency or language codes (CHF, GBP, DE, EN etc) avoiding composite keys forces you to always join intermediate ...


7

You are confusing various concepts here, the major error being primary key != clustered key. Because of this misunderstanding, the majority of the guidance is incorrect. To be brutally honest, you are probably not well placed to be writing these guidelines. Primary key != clustered key Unique identifiers make for very poor clustered keys SQL Server is not ...


7

I will quote Dictionary.com, as I take this as the meaning of database: a comprehensive collection of related data organized for convenient access, generally in a computer. Under this definition, you can consider a database anything from a full-fledged RDBMS (SQL Server, Oracle, etc.) to a basic flat file. If it stores data, it technically can be ...


7

Best is to refer : Stairway to SQL Server Indexes You leave your house to run a few errands. When you return, you find a message from your daughter’s softball coach waiting for you. Three of the girls, Tracy, Rebecca, and Amy have lost their team caps. Could you please swing by the Athletic Products Store and buy caps for the girls. Their parents ...


6

A quick check on Wikipedia doesn't mentioned if an "outer join" implies left, right or full when this important bit is omitted. Practically, "outer join" by iself isn't supported. You normally require LEFT, RIGHT or FULL "natural" means "join on column with the same names" This means "Natural outer join" won't be recognised "Natural full outer join" ...


6

To me, a database is a thing that exists to store and retrieve data. We call Access a database, even though it's really just a pretty front end to a collection of files. Outlook (at least on the Mac) calls its message store a database. Some people even call Excel a database (but that kind of makes me snort - so there is a line somewhere). I think the ...


6

This is a great question and a set of great answers. I think one thing that is missing from the discussion is an answer which delves into the distinction between a database and a database management system (DBMS). I like the definition of database that Shark provided from dictionary.com. I think it really shows the need for the distinction between the ...


6

Given the schema Students(id:integer,grade:integer), you can solve the problem in tuple relational calculus by using the negation operator (¬). {T1.id | ∃T1 ∈ Students ¬(∃T2 ∈ Students (T2.grade > T1.grade))} This would return the id of all students in T1 where there is no student in T2 with a higher grade. Because T1 and T2 are from the same ...


6

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different ...


5

Bare with me, this is a complicated question to clarify and we may go through a few rounds of edit and commenting to plug the gaps. From the way your question is phrased I'm guessing you're not differentiating the atomicity, isolation, consistency and durability elements of ACID. When a database begins a transaction, all statements executed in that ...


5

All things become gain complexity when databases gets bigger: maintenance windows needs to be enlarged or rescheduled backups (the end-of-day full backup becomes an absurd time-eater, so you need an differential or even log backups and do the full once-a-week, maybe once-a-month) performances maintanances becomes an time-eater (creating an index on a ...


5

When you get into it -- really get into it -- storing componentized address data is an extremely complicated problem because of all the disparate and varied systems in use globally. I think whatever you develop needs to be balanced between flexibility, and storing only what your business needs to store. The biggest piece of the puzzle here is to move all ...


5

In SQL Server 2012 you can have read-only secondaries, which allow you to use Availability Groups to mirror a database or set of databases to another server, and perform read-only queries against them even as they are being mirrored. The downside: this requires Enterprise Edition on both nodes, which can only be licensed per core (not CAL), and the ...


5

If you're trying to get to the youngsters who've never searched for anything in their life without using Google, then why not try something like this: Imagine iTunes messed up your music library and every song on your iPod was mixed up in random order but given a sequential name like "track1234". If you wanted to find a certain song, all you could ...


5

I can only speak to MS SQL Server not MySQL I'm afraid. SQL Server breaks up a large database into small pieces called PAGES that are 8k in size. There are a couple of advantages here. With a large text file any time you make a chance you re-write the entire file. With a SQL Server when you make changes you only write down the PAGES that have changed. ...



Only top voted, non community-wiki answers of a minimum length are eligible