Stack Overflow
Stack Overflow

Questions tagged [database-agnostic]

This tag specifies that the question is independent of any particular database and the sql should work in any rdbms.

100 questions
1799
votes
27 answers

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (MySQL, Oracle, SQL Server,…
Claude Houle
  • 41,064
  • 8
  • 33
  • 42
165
votes
4 answers

Database cluster and load balancing

What is database clustering? If you allow the same database to be on 2 different servers how do they keep the data between synchronized. And how does this differ from load balancing from a database server perspective?
Malik Daud Ahmad Khokhar
  • 13,470
  • 24
  • 79
  • 81
102
votes
6 answers

Function that creates a timestamp in c#

I was wondering, is there a way to create a timestamp in c# from a datetime? I need a millisecond precision value that also works in Compact Framework(saying that since DateTime.ToBinary() does not exist in CF). My problem is that i want to store…
Kostas Konstantinidis
  • 13,347
  • 10
  • 48
  • 61
37
votes
6 answers

Should many to many tables have a primary key?

If I have two objects that have a many-to-many relationship, I would typically model them in my database schema with a many-to-many table to relate the two. But should that many-to-many table (or "join table") have a primary key of its own (integer…
ashes999
  • 9,925
  • 16
  • 73
  • 124
33
votes
8 answers

When to use database views and when not?

This question is about database views, not materialized-views. Pros: Query simplification. Avoid repeat the same joins on multiples queries. Avoid magic numbers. Cons: Hiding real queries (may be you are repeating joins). What else?
FerranB
  • 35,683
  • 18
  • 66
  • 85
24
votes
6 answers

Does "group by" automatically guarantee "order by"?

Does "group by" clause automatically guarantee that the results will be ordered by that key? In other words, is it enough to write: select * from table group by a, b, c or does one have to write select * from table group by a, b, c order by a, b,…
Tomas
  • 57,621
  • 49
  • 238
  • 373
22
votes
4 answers

Difference between Restrictions.like and .ilike in Hibernate Criteria API

Hibernate's Criteria API has Restrictions.ilike function which has the following contract: A case-insensitive "like", similar to Postgres ilike operator That's cool. But the same class also has like function, having much more vague…
mindas
  • 26,463
  • 15
  • 97
  • 154
20
votes
8 answers

Best Schema to represent NCAA Basketball Bracket

What is the best database schema to represent an NCAA mens basketball bracket? Here is a link if you aren't familiar: http://www.cbssports.com/collegebasketball/mayhem/brackets/viewable_men I can see several different ways you could model this data,…
Ryan Guill
  • 13,558
  • 4
  • 37
  • 48
17
votes
9 answers

What data type is recommended for ID columns?

I realize this question is very likely to have been asked before, but I've searched around a little among questions on StackOverflow, and I didn't really find an answer to mine, so here goes. If you find a duplicate, please link to it. For some…
Tomas Aschan
  • 58,548
  • 56
  • 243
  • 402
16
votes
6 answers

Fast Relational method of storing tree data (for instance threaded comments on articles)

I have a cms which stores comments against articles. These comments can be both threaded and non threaded. Although technically they are the same just with the reply column left blank when it's not threaded. My application works on sqlLite, MySQL…
Stewart Robinson
  • 3,459
  • 4
  • 21
  • 27
16
votes
2 answers

How to get fully materialized query from querydsl

I am trying to use querydsl for building dynamic queries for dynamic schemas. I am trying to get just the query instead of having to actually execute it. So far I have faced two issues: - The schema.table notation is absent. Instead I only get the…
MickJ
  • 2,127
  • 3
  • 20
  • 34
14
votes
4 answers

Creating indexes with descending key columns with Liquibase

I am wondering if there is a generic way to create an "ordered index" with liquibase. Something that will produce these kind of SQL statement: CREATE INDEX idx_name ON my_table (m_column DESC) I need that for oracle, postgresql, mysql, and sql…
poussma
  • 7,033
  • 3
  • 43
  • 68
10
votes
2 answers

DB agnostic SQL for CURRENT_TIMESTAMP

I'm currently using Oracle, but most of the SQL is db agnostic. One exception is CURRENT_TIMESTAMP which fails in MSSQL. Is is possible to move to a more portable syntax for this too?
qwerty
  • 3,801
  • 2
  • 28
  • 43
8
votes
6 answers

Select the maximum value for each (Col1,Col2)

Lets assume the following table: Name SubName Message Time USA MA M1 1 USA NY M2 2 USA WA M3 3 USA MA …
Ahmad Mushtaq
  • 1,395
  • 1
  • 10
  • 32
8
votes
5 answers

How do you write your applications to be database independent?

My boss asks me to write only ANSI SQL to make it database independent. But I learned that it is not that easy as no database fully ANSI SQL compatible. SQL code can rarely be ported between database systems without modifications. I saw people do…
Dennis C
  • 24,511
  • 12
  • 71
  • 99
1
2 3 4 5 6 7