I'm just trying to get a general idea of what views are used for in RDBMSes. That is to say, I know what a view is and how to make one. I also know what I've used them for in the past. But I want to make sure I have a thorough understanding of what a view is useful for and what a view shouldn't be useful for. More specifically: Wha...
What is the best, DBMS-independent way of generating an ID number that will be used immediately in an INSERT statement, keeping the IDs roughly in sequence? ...
I recently sped up a complicated query by an order of magnitude by giving SQLite a good index to work with. Results like this make me wonder if I should index a lot of other fields that are commonly used for JOINs or ORDER BY clauses. But I don't want to get overzealous and have it backfire on me: I assume there must be some reasons not ...
My company has me working on finishing a back end for Oracle for a Python ORM. I'm amazed at how much differently RDBMSes do things even for the simple stuff. I've learned a lot about the differences between Oracle and other RDBMSes. Just out of sheer curiosity, I'd like to learn more. What are some common "gotchas" in terms of portin...
In MSSQL 2005 I just struck the infamous error message: Introducing FOREIGN KEY constraint XXX on table YYY may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Now, StackOverflow has several topics about this error message, so I've already got the...
Yesterday while working on a project I came up on a peculiar 1:1 relationship which left me wondering - how to best implement this (clearly, we had done it wrong :D) The idea is that there are two types of entities, A and B. They can each exist on their own just fine, but they can also have a link between them. If there is a link, then ...
I often see people who write SQL like this: SELECT * from TableA LEFT OUTER JOIN TableB ON (ID1=I2) I myself write simply: SELECT * from TableA LEFT JOIN TableB ON (ID1=I2) To me the "OUTER" keyword is like line noise - it adds no additional information, just clutters the SQL. It's even optional in most RDBMS that I know. So... why...
I am working on an online survey. Most questions have a scale of 1-5 for an answer. If we need to add a question to the survey, I use a simple web form, which does an INSERT into the appropriate table, and voila! surveys are asking the new question -- no new code or change to the database structure. We are being asked to add survey ques...
This question is related to this post: http://stackoverflow.com/questions/1764469/sql-design-for-survey-with-answers-of-different-data-types I have a survey app where most questions have a set of answers that are 1-5. Now we have to do questions that could have a variety of different answer types -- numeric, date, string, etc. Thanks to...
Basically I have a one-to-many table. I want to append the columns of the larger table to the result set of the smaller table. I will end up with a result set the same size of the smaller table, but again, with the relevant information from the many sided table. One catch: the many sided table doesn't have a primary key defined, althoug...
You often see database fields set to have a magnitude of 255 characters, what is the traditional / historic reason why? I assume it's something to do with paging / memory limits, and performance but the distinction between 255 and 256 has always confused me. varchar(255) Considering this is a capacity or magnitude, not an indexer, why...
This are few of the fundamental database questions which has always given me trouble. I have tried using google and wiki but I somehow I miss out on understanding the functionality rather than terminology. If possible would really appreciate if someone can share more insights on this questions using some visual representative examples....
I want to retrieve all records from one table when there are no matches in the second table. So it is kind of the opposite of an inner join. ...
I'm working on a web app for a quality control checklist. I already have a table set up, but I have a hunch that our model is sub-optimal and I could get some better performance. Please not that I'm using mysql, so I'm limited to its capabilities. Each checklist has dozens, sometimes hundreds of questions. Each question has between 2 an...
Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be very useful, thanks. And for the sake of not being too vague, let's say we are using a mai...
I have a stored procedure that goes something like this (pseudo code) storedprocedure param1, param2, param3, param4 begin if (param4 = 'Y') begin select * from SOME_VIEW order by somecolumn end else if (param1 is null) begin select * from SOME_VIEW wher...
Say I have a table X with 100 records in it and that running a select * from X takes 100 seconds. How long should I expect the query select top 10 * from X to take? I'd expect that the relationship is more or less linear so 10 seconds. Is this correct, or is the relationship non-linear in some way? ...
No worries! It looks more complex than it actually is! Just get down to the drinks! TLDR-version: How to efficiently query and update entities having relationships to other entities? Here's an interesting data modeling scenario with two tables that has been puzzling me: Entities { ID, Name, ScalarValue } ComponentEntities { Aggregate...
a) I have 1000000 domain names b) Every domain has about 100000 sites c) each site has about 10000 visits daily / (5000 unique visits daily) d) As the owner of all those websites, I want to see, how many visitors on selected sites I had in a selected periods of time, for example: How many unique visitor were from 4th December 1987...