Stack Overflow
Stack Overflow

Questions tagged [rdbms-agnostic]

For SQL related questions that is not targeted for a specific database product.

Use this tag when asking general SQL questions:
When targeting an unknown ,
or when targeting multiple systems.
(if, for instance, you are looking for a solution that will fit both MySql and ms-access).

33 questions
236
votes
12 answers

Why historically do people use 255 not 256 for database field magnitudes?

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…
Andrew M
  • 9,149
  • 6
  • 44
  • 63
97
votes
14 answers

What are views good for?

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…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
19
votes
6 answers

What is the problem with foreign key cascade multiple paths and cycles?

In SQL Server 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…
Vilx-
  • 104,512
  • 87
  • 279
  • 422
17
votes
4 answers

What are the disadvantages of having many indices?

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…
Andrew Watt
  • 2,757
  • 2
  • 20
  • 18
14
votes
5 answers

What Applications Don't Need ACID?

Sorry for the ignorant question, but what kind of applications wouldn't require an ACID compliant database server? I have a SQL Server background where ACID has always "been there", and now researching other DBMSs has me thinking. Most every…
skaz
  • 21,962
  • 20
  • 69
  • 98
14
votes
8 answers

Do you use the OUTER keyword when writing left/right JOINs in SQL?

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…
Vilx-
  • 104,512
  • 87
  • 279
  • 422
13
votes
7 answers

Database Optimization techniques for amateurs

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…
Zombies
  • 25,039
  • 43
  • 140
  • 225
9
votes
17 answers

SQL portability gotchas

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…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
7
votes
1 answer

recursive query for adjacency list to preorder tree traversal in SQL?

I am migrating data from one database schema to another. The old schema has a categorization system based on an adjacency list, with id, category, and parent_id. If one category is under a second, that category has the second's id as its parent id.…
user151841
  • 17,377
  • 29
  • 109
  • 171
5
votes
4 answers

SQL design for survey with answers of different data types

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…
user151841
  • 17,377
  • 29
  • 109
  • 171
4
votes
2 answers

Can an autoincrement ID ever change from the mid-transaction value upon commit?

The possibility of this happening seems extremely unlikely to me because of the problems it could cause, but I figured I'd ask the question anyway... Imagine a transaction where an autoincrement ID is involved and a value is assigned. Prior to…
Russ
  • 10,835
  • 12
  • 42
  • 57
3
votes
11 answers

What is the best way to generate an ID for a SQL Insert?

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?
Liam
  • 19,819
  • 24
  • 83
  • 123
3
votes
1 answer

Oracle AS keyword and subqueries

Just found out that Oracle does not like it when you use the AS keyword to alias a subquery: SELECT * FROM (SELECT * FROM products) AS p I need to keep my SQL queries as portable as possible. Will the removal of the AS keyword in the above query…
Max Toro
  • 28,282
  • 11
  • 76
  • 114
2
votes
2 answers

Performing Heavy Crunching On a Table Without Affecting the Table

I'm looking for some general advice on the best way to perform heavy crunching/data-mining on a database table, without affecting the performance of regular site queries on the table. Some of the calculations may involve joining several tables, and…
mellowsoon
  • 22,273
  • 19
  • 57
  • 75
2
votes
2 answers

How to prevent deep recursive queries with entities consisting of entities of the same type? [Cool example inside]

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…
randomguy
  • 12,042
  • 16
  • 71
  • 101
1
2 3