If you have CREATE [TEMPORARY] TABLE syntax, i.e. TEMPORARY being the keyword to determine the type of table, would not the syntax ALTER [TEMPORARY] TABLE satisfy the needs of altering a base table or a temporary table as well as simply error handling.
I've not used the TEMPORARY table name ontop of a base table name, or ontop of a Cluster table (one of those lovely quiz questions), but I see no reason why you would want to restrict temporary names not being the same as base tables, other then a best practice for sane object naming. Having # as a reserved and invalid character I have no problem with. On Thu, Mar 25, 2010 at 3:56 PM, Roland Bouman <[email protected]>wrote: > Hi Brian, All, > > I can't seem to find anything in the (2003) standard that indicates a > temporary table can in fact have the same name as an existing base > table or view. > Perhaps I am not looking in the right place, but that would solve it: > simply don't allow creation of temporary tables if such a table exists > already in that schema. > > For the ones that you create under the hood, you could either create > them in a special schema reserved for that purpose, or otherwise > prefix them with a reserved prefix. > > (I'd still like to learn where the standard says you can mask an > existing table with a temporary one - genuinely interested, I just > can't seem to find it) > > On Thu, Mar 25, 2010 at 11:06 AM, Brian Aker <[email protected]> wrote: > > Hi! > > > > You can have a table which is both a temporary and a standard table in > the > > same schema. Awesome... there are a lot of good reasons for this. > > > > You cannot though do an alter table on the standard table if the > temporary > > table is around (which I personally think is bad). > > > > For error messages? You don't know what the error message was on... sure > if > > you are sitting there you might know that it was the temp table (since a > > temporary table always gets referenced before the standard table), but in > > error messages? There is no way to know. > > > > We internally have three basic forms of tables. Temporary, Standard, and > > "Internal". We use internal for results/alter/etc. Those come up in error > > messages from time to time, but since the names are gibberish it is > pretty > > simple to know when they are at fault (though not perfect). > > > > We have internally "temporary" reserved as a schema for our own use (same > > with data_dictionary and information_schema). Temporary you can't see > > though. Since "internal" tables live there, I've been having error > messages > > write out as "internal.#sql34324" as the sql path for error messages. > Right > > now temporary and standard are the same... but I would like to change > that. > > > > SqlServer uses a # symbol to represent those tables. I think we should do > > the same for error messages. In fact, I think we should reserve the "#" > > character for our own internal use and not allow its usage as the first > > character. > > > > Thoughts? I want some bit of difference in the names in the end. Trying > to > > figure out errors when I don't know if it was a transient table or not is > a > > bit of a pain. > > > > Cheers, > > -Brian > > > > > > _______________________________________________ > > Mailing list: > > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > > Post to : [email protected] > > Unsubscribe : > > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > > More help : https://help.launchpad.net/ListHelp > > > > > > -- > Roland Bouman > http://rpbouman.blogspot.com/ > > Author of "Pentaho Solutions: Business Intelligence and Data > Warehousing with Pentaho and MySQL", > http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html > > _______________________________________________ > Mailing list: > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > Post to : [email protected] > Unsubscribe : > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
