If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
CREATE TEMPORARY TABLE
On Sat, 10 Jun 2006 13:37:13 GMT, jacksonmacd
wrote: I guess in this case, the "... if you can calculate it at will..." part isn't true! Thanks for the clarification - and yes, that's certainly a case where the rule isn't applicable. If you can't, you can't! John W. Vinson[MVP] |
#22
|
|||
|
|||
CREATE TEMPORARY TABLE
Pat Hartman(MVP) wrote: Yes [SQL Server] Views are similar to queries except that views don't take parameters Jet VIEWs (SQL language keywords in uppercase, not shouting g) do not take parameters either. An Access query which uses the PARAMETERS keyword is the equivalent of a Jet PROCEDURE (http://office.microsoft.com/en-us/as...322191033.aspx). Put another way, if you use Jet's CREATE PROCEDURE (parameter list) AS syntax then open the resulting object's SQL pane in Access, you'll see the above syntax replaced by the construct PARAMETERS parameter list; I'm not sure that [SQL Server Views] are updatable Well, not all Jet VIEWs/Access Query's are updatable and the same applies to SQL Server. Broadly speaking, if a row in the view can be mapped to a row in a base table then the VIEW/Query is updatable. Both engines allow VIEWs to be updateable; the question is, when does each engine give up trying to determine whether a VIEW is updateable? It is tempting to assume SQL Server would try harder but that may not be correct e.g. for testing whether there are cascade cycles in DRI (foreign keys) across multiple tables, the Jet 4.0 implementation is better than that of SQL Server 2005 because SQL Server gives up earlier. Regardless which engine wins that particular race, SQL Server come out top because it supports INSTEAD OF triggers.e. you can make an otherwise non-updatable VIEW appear updatable by trapping INSERT, UPDATE and DELETE operations and handling them 'manually' in a trigger. SQL Server's WITH CHECK OPTION is also useful in that it does the opposite i.e. restrict some operations on an updateable VIEW without the overhead (e.g. maintenance) of an INSTEAD OF trigger. Arguably, without these extensions Access/Jet needs to be smarter at determining whether a view is updateable. I really don't know where you are going with this. Sharing knowledge for the benefit of the reader. I hope that by proffering information you will do the same e.g. you know that some Access Queries are updateable but do you know what makes it updateable or otherwise? an example of a Query that a human could easily map to a base table but on which the engine chokes? a link to Jet's functional spec in this area vbg? If you (or anyone else) does, they could post it here. Jamie. -- |
#23
|
|||
|
|||
CREATE TEMPORARY TABLE
John Vinson wrote: If you can calculate it at will (in the query's output), *WHY* store it? Store it when the cost of the calculation is higher than the cost of a simple read. See: http://www.dbazine.com/ofinterest/oi-articles/celko4/ Jamie. -- |
#24
|
|||
|
|||
CREATE TEMPORARY TABLE
Pat Hartman(MVP) wrote: temporary tables ... cause database bloat Pat, are you able to address my question (up thread and reposted here) about bloat, please? I've had the same mdb file for years which I use to test all my Jet SQL code. I must have run literally thousands of SQL DDL statements via ADO of the CREATE/ALTER/DROP TABLE/VIEW/PROCEDURE/CONSTRAINT/INDEX family. I compact the file probably less frequently than every six months because I always have to find the JRO code to do it. The mdb file, around 100mb, never seems to recover more than about 3%. Any idea what's going on here? I'm tempted to conclude that CREATE/DROP TABLE does not cause much file bloat but there could be some other factor (my file is bloated beyond repair, JRO is rubbish, etc). Alternatively, perhaps the bloat problem is external to the engine e.g. there is something in the Access UI or DAO that causes bloat with Jet SQL DDL avoids? Any ideas? Thanks, Jamie. -- |
#25
|
|||
|
|||
CREATE TEMPORARY TABLE
Single table select queries are always updateable as long as you have update
authority, use NO aggregate functions, and additionally if the table is linked, it must have a primary key or unique index. Multi-table select queries are updatable using pretty much the same rules. However, the rules apply to BOTH tables so even if you are only trying to update tblA, you must have update authority to tblB if it is included in the query. That is why when you join a totals query to a table, you can't update the table even though logic says you should be able to. Jet overrules you and refuses and that brings us back to the original issue of temp tables. Search Access help for "updateable queries" for more info. I believe that there is also an article in the MSDN library. "Jamie Collins" wrote in message oups.com... Pat Hartman(MVP) wrote: Yes [SQL Server] Views are similar to queries except that views don't take parameters Jet VIEWs (SQL language keywords in uppercase, not shouting g) do not take parameters either. An Access query which uses the PARAMETERS keyword is the equivalent of a Jet PROCEDURE (http://office.microsoft.com/en-us/as...322191033.aspx). Put another way, if you use Jet's CREATE PROCEDURE (parameter list) AS syntax then open the resulting object's SQL pane in Access, you'll see the above syntax replaced by the construct PARAMETERS parameter list; I'm not sure that [SQL Server Views] are updatable Well, not all Jet VIEWs/Access Query's are updatable and the same applies to SQL Server. Broadly speaking, if a row in the view can be mapped to a row in a base table then the VIEW/Query is updatable. Both engines allow VIEWs to be updateable; the question is, when does each engine give up trying to determine whether a VIEW is updateable? It is tempting to assume SQL Server would try harder but that may not be correct e.g. for testing whether there are cascade cycles in DRI (foreign keys) across multiple tables, the Jet 4.0 implementation is better than that of SQL Server 2005 because SQL Server gives up earlier. Regardless which engine wins that particular race, SQL Server come out top because it supports INSTEAD OF triggers.e. you can make an otherwise non-updatable VIEW appear updatable by trapping INSERT, UPDATE and DELETE operations and handling them 'manually' in a trigger. SQL Server's WITH CHECK OPTION is also useful in that it does the opposite i.e. restrict some operations on an updateable VIEW without the overhead (e.g. maintenance) of an INSTEAD OF trigger. Arguably, without these extensions Access/Jet needs to be smarter at determining whether a view is updateable. I really don't know where you are going with this. Sharing knowledge for the benefit of the reader. I hope that by proffering information you will do the same e.g. you know that some Access Queries are updateable but do you know what makes it updateable or otherwise? an example of a Query that a human could easily map to a base table but on which the engine chokes? a link to Jet's functional spec in this area vbg? If you (or anyone else) does, they could post it here. Jamie. -- |
#26
|
|||
|
|||
CREATE TEMPORARY TABLE
Every time you create an object in the database, that object takes up space.
Deleting the object does not free up space. The only way to regain waste space is to compact the database. The compact process simply copies all objects from the existing .mdb to a new .mdb. When the copy is done, the original .mdb is deleted and the copy is renamed. Usually when people create temp tables, they populate them. So, when they are deleted, much more waste space is created. You seem to be just creating the object and deleting it but never populating it. That is why you don't recover much space when you compact. If you think you have bloat that hasn't been cleaned up, you can use the \decompile argument when you open Access. This is an undocumented tool that removes all p code from the database. It will sometimes clear up a corruption problem that nothing else seems to fix. "Jamie Collins" wrote in message oups.com... Pat Hartman(MVP) wrote: temporary tables ... cause database bloat Pat, are you able to address my question (up thread and reposted here) about bloat, please? I've had the same mdb file for years which I use to test all my Jet SQL code. I must have run literally thousands of SQL DDL statements via ADO of the CREATE/ALTER/DROP TABLE/VIEW/PROCEDURE/CONSTRAINT/INDEX family. I compact the file probably less frequently than every six months because I always have to find the JRO code to do it. The mdb file, around 100mb, never seems to recover more than about 3%. Any idea what's going on here? I'm tempted to conclude that CREATE/DROP TABLE does not cause much file bloat but there could be some other factor (my file is bloated beyond repair, JRO is rubbish, etc). Alternatively, perhaps the bloat problem is external to the engine e.g. there is something in the Access UI or DAO that causes bloat with Jet SQL DDL avoids? Any ideas? Thanks, Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table problem | Redwood | Database Design | 29 | April 3rd, 2006 04:58 PM |
Displaying File properties on a Access 2000 Form | Chris Fillar | General Discussion | 2 | March 16th, 2006 02:22 PM |
Need to Improve Code Copying/Pasting Between Workbooks | David | General Discussion | 1 | January 6th, 2006 03:56 AM |
Create Table Primary Key after Make Table Query And Update Table | RNUSZ@OKDPS | Running & Setting Up Queries | 1 | May 3rd, 2005 08:07 PM |
how to create and delete a temporary table | Tellabs T. Burr | General Discussion | 0 | February 11th, 2005 05:25 PM |