NexusDB

Q: How to I query a date in SQL? What's the date format?

NexusDB SQL requires the use of standard SQL format along with the date/time type keywords. See the SQL reference for more details.

Example:

Select * from mytable where mydate = Date'2002-11-24'

Q: Do you have some more Info on SQL 92, 99 and 2003?

SQL92:

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (last full standard draft before approved; ~1.7megs)

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt (bnf)

http://developer.mimer.se/validator/parser92/index.tml (conformity checker)

http://csf.colorado.edu/local/sql/

SQL99:

http://www.ncb.ernet.in/education/modules/dbms/sql99index.html (last full standard draft before approved)

http://developer.mimer.se/validator/parser99/index.tml (conformity checker)

SQL2003:

http://www.wiscorp.com/sql/sql_2003_standard.zip (last full standard draft before approved; careful: ~10megs!)

http://developer.mimer.se/validator/parser200x/index.tml (conformity checker)

General:

http://www.wiscorp.com/SQLStandard.html

http://members.tripod.com/er4ebus/sql/ch01.htm (Teach Yourself SQL in 21 Days)

Q: Why is my LiveResult joined query so much slower than an equal canned query?

What you have to realize is that there are optimizations that the query engine cannot apply to a live cursor. For a live query, the engine can only use one index to generate the result, as you can't have two indices applied at the same time. For canned queries, the engine can apply any number of index ranges one after another and then combine the results into one.

Generally, for live cursors, the engine tries to apply the index and range with the highest probability of limiting the result set and then filters for any conditions that are not caught by the applied index and range using a separate expression filter, which is then evaluated dynamically for each row. For this reason, in queries where you have multiple disjunctions and/or conjunctions that fit indices, the canned version of the query will typically be more efficient. The difference becomes more and more pronounced as you add constraints to the live result.

Say you do something along the lines of

 SELECT * FROM SomeTable WHERE B > 3 AND B < 1000 ORDER BY A;

On a live result, even if you have an index on SomeTable.B, the query engine can't use it to limit the result because the ORDER BY stipulates that the index on A must be the active one in the returned live cursor. For a canned result by contrast, the engine will first use the B index when selecting the rows for the result and then sort the result - now stored in a temp table - manually on A.

Q: Why can i delete tables contraints but not from SQL?

As there is no central database repository keeping track of all table relations. The SQL engine checks for this; the core doesn't. RI on the other hand is always enforced, no matter how you delete/up`/insert a record.