Tag Info

Hot answers tagged

40

Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given. I don't believe the physical storage difference is worth ...


16

I do not know about MySQL and PostgreSQL, but let me treat this a bit generally. There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''. This clearly demonstrates that it is not necessary to distinguish between both. There are some annoying consequences: You set a varchar2 to an empty string like this: Update mytable ...


15

Celko's book is a good resource - if a bit overly "academic" at times. I also have really found this method, known as 'closure tables' to work fairly well. But if you're using a database that allows recursive CTE's (such as recent PostgreSQL or SQL Server 2005 or newer), they're really the best way to go. If you're on Oracle, there's always the venerable ...


15

PostgreSQL does not currently have it. But you are right, with MVCC it should theoretically be possible. In fact, the old university Postgres had an equivalent feature called time travel, but it was later removed because it caused too much overhead and the practical use was limited. While it would probably be possible to resurrect it, since then there ...


14

The reason why this works in PostgreSQL is that the system catalogs are regular tables. So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on. Since tables are transactional anyway, you'd almost have to go out ...


14

It depends on the domain you are working on. NULL means absence of value (i.e. there is no value), while empty string means there is a string value of zero length. For example, say you have a table to store a person' data and it contains a Gender column. You can save the values as 'Male' or 'Female'. If the user is able to choose not to provide the gender ...


13

Oracle provides several sample schema, but as with Rolando's answer, you would have to convert these for the other databases. My answer in a nutshell is Don't use identical scripts. The problem with using scripts that are as similar as possible for each database is that you will be using least common denominator functionality. Using a database requires ...


11

I can only attempt to answer for Oracle and postgres. After using Oracle exclusively for years, and postgres only for the last two years or so, I love postgres. There are so many small ways it is more convenient to use than Oracle, and it shares many of the crucial benefits (such as MVCC). It is easier to administer, reliable, has excellent documentation, ...


10

A recursive CTE is going to be your easiest solution. SQL 2005 and current versions of PostgreSQL support CTEs. If you're using SQL Server 2008 or newer, you could use the HIERARCHYID datatype. You can find a good example of this at HierarchyID: Model Your Data Hierarchies with SQL Server 2008


9

Oracle Apex. A handy, easy-to-use web application environment built right into the database. Quite simply it makes it very simple to deploy 'single-box' applications with the web ui/application logic/database in a single integrated package. PS. 11g XE (currently in beta) expands the storage to over 10GB.


8

Easily - just use RMAN to clone it. I think you mean in Oracle terminology, clone the database into a new instance on the same server. If you really do mean clone the schema into the same Oracle instance, then the easiest way is Datapump.


8

From the documentation: Mnesia is a distributed Database Management System, appropriate for telecommunications applications and other Erlang applications which require continuous operation and soft real-time properties. It is one section of the Open Telecom Platform (OTP), which is a control system platform for building telecommunications applications. ...


8

SQL Server 2000, 2005, 2008 has this capability GRANT { ALL [ PRIVILEGES ] } | permission [ ( column [ ,...n ] ) ] [ ,...n ] [ ON [ class :: ] securable ] TO principal [ ,...n ] [ WITH GRANT OPTION ] [ AS principal ] ADDED In SQL Server 2005, 2008 it is possible to encrypt a column of data by using symmetric encryption, see ...


7

Most don't? Bummer. I principally use SQL Server and it does. I know Oracle doesn't but I thought Oracle might be an aberration. In SQL Server, I'm quite certain you can run multiple DDL statements in a single transaction although I also think there's a couple of restrictions (which I have all forgotten). You can do a create or an alter or a drop of ...


7

Will/can Solr/Lucene searches be faster than PostgreSQL even if no full-text search is involved? Yes. As per your quoted example, it can be many times faster than a relational database for certain use cases. Not surprising really. Solr is a search engine. PostgreSQL is a relational database engine. Solr is built from the ground up to do one thing ...


6

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything. Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL. Of course as each company seeks to differentiate their products, they will develop additional ...


6

In MSSQL (2005 and later editions) you can use Common Table Expressions for reading hierarchies, see this MS blog post for a couple of examples. I have been recommended a book on the subject more generally which is "Trees and Hierarchies in SQL for Smarties" by Joe Celko - though I've not actually looked at the book myself yet.


6

One thing worth keeping in mind is that when you have a field that is not required, but any values that are present must be unique will require you to store empty values as NULL. Otherwise, you'll only be able to have one tuple with an empty value in that field. There are also some differences with relational algebra and NULL values: NULL != NULL, for ...


6

Are you really trying to use Windows 8? No version of Oracle is supported on Windows 8 yet and, if history is any guide, I wouldn't expect any of them to work without patches that aren't available yet. Is Windows 8 even in public beta yet? I'd hate to try to take a class using an early beta operating system. Oracle 11.2 is supported on Windows 7 (which ...


6

Sorry for being late to the party. :) Here's my answer, based on having used Mnesia since 1996 and various other database technologies since 1988. Mnesia and MySQL are indeed different beasts, and which one is the best depends very much on how you intend to use it. If your application is written in Erlang, Mnesia allows you to store the data in the same ...


6

Here is Microsoft's take: http://www.microsoft.com/sqlserver/2008/en/us/compare-oracle.aspx and here is Oracle's take: http://www.oracle.com/us/products/database/039433.pdf


5

In SQL Server we can rollback DDL statements, it's not using auto commit at the end of the statement. In other DBMS I don't know, but I remember that in Oracle one can't do the same. I believe it's specific to each DBMS, not sure what would the SQL standard say about this, but I'm sure no producer implements 100% the standard. There's a similar question on ...


5

Firstly, I will say that this workload is by no means heavy by Oracle standards; thousands of commits/sec are possible, easily. Secondly, however, what matters here is not your database and it's not your server: it's your storage. There are many options here; you won't go too wrong with something like NetApp (I don't work for them, just a satisfied user) and ...


5

Oracle has shared query parsing, so a SELECT * FROM table_a done by one session is (normally) the same as that of another session. That would break if one session thought there was ten columns in the table and another thought there were eleven.


5

It's been a while since I used Red Gate, but the VS2010 has it matched from what I remember, with options to include or exclude by object types, and generate scripts to match the two schemas; the VS tools takes a while to run, I remember the Redgate to be pretty quick.


5

I tried to use the VS tool yesterday with my production SQL 2000 instance, comparing to my dev 2008 instance, and it refused to work with anything prior to SQL 2005. Red Gate definitely does not have such a restriction. It even works reasonably well (not 100%) with another database we have that runs in 6.5 Compatibility Mode.


5

At one time or another I've worked with all of the databases you mention. Unfortunately I have found that it doesn't take very long at all for the syntax to deviate in to their own flavours, for anything other than the simplest SELECT, INSERT, UPDATE and DELETE. When it gets to some of the categories you suggest it will quickly get vendor specific. I've ...


5

Although Oracle's other flashback options are not available in Express Edition, Flashback Query is. According to the question on it, no other database has this feature that allows a select statement to query data as of a point in time in the past. Flashback data can be joined with current data and inserted into current tables making it useful for undo type ...


5

You can create an index on an expression (which you cannot do in MySQL): CREATE INDEX table_idx ON emp(substr(last_name,1,8)); But that won't help anything unless you also use that expression in the where clause, e.g. where substr(last_name,1,8) = 'foobar' What exactly is the usage of an index in MySQL that doesn't index the whole value?


5

You might want to look at what features you need or want, then make a decision on which platform. Do a little reading on each product and see what interests you, then see if the other supports the same thing, or something similar. To me, since they both support the same core features, price will determine route, unless there's something in the other product ...



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