Why does the use of temp tables with a SELECT statement improve the logical I/O count? Wouldn't it increase the amount of hits to a database instead of decreasing it. Is this because the 'problem' is broken down into sections? I'd like to know what's going on behind the scenes. ...
For example which is better: select * from t1, t2 where t1.country='US' and t2.country=t1.country and t1.id=t2.id or select * from t1, t2 where t1.country'US' and t2.country='US' and t1.id=t2.id better as in less work for the database, faster results. Sybase, and there's an index on both tables of country+id ...
This is a 3 part question regarding embedded RegEx into SQL statements. How do you embed a RegEx expression into an Oracle PL/SQL select statement that will parse out the “DELINQUENT” string in the text string shown below? What is the performance impact if used within a mission critical business transaction? Since embedding regex into...
When using Linq to SQL with stored procedures, what is the best way to re-generate the C# code generated by Visual Studio? For example, when adding a column to your SP's result set and need to have Visual Studio re-create the class describing the result set, I'd expect to open the DBML designer, right-click the SP in question, and sel...
What are the possible causes of premature redo log switching in Oracle other than reaching the specified file size and executing ALTER SYSTEM SWITCH LOGFILE? We have a situation where some (but not all) of our nodes are prematurely switching redo log files before filling up. This happens every 5 - 15 minutes and the size of the logs in ...
Where can I get the 64 bit bootstrapper for SQL Server Express 2005 64 bit. The default bootstrapper is 32 bit only. This will not install on Vista 64 bit. ...
Is there a way to use sql-server like analytic functions in Hibernate? Something like select foo from Foo foo where f.x = max(f.x) over (partition by f.y) ...
I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this: INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES (SELECT LongIntColumn1, Avg(CurrencyColumn) ...
Is there a general rule of thumb to follow when storing web application data to know what database backend should be used? Is the number of hits per day, number of rows of data, or other metrics that I should consider when choosing? My initial idea is that the order for this would look something like the following (but not necessarily,...
I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in 'yyyy-mm-dd' format (without time part). Please help, thanks. ...
In how many languages is Null not equal to anything not even Null? ...
I've been asked to implement some code that will update a row in a ms sql database and then use a stored proc to insert the update in a history table. We can't add a stored proc to do this since we don't control the database. I know in sprocs you can do the update and then call execute on another stored proc. Can I set it up to do thi...
I have a column containing the strings 'Operator (1)' and so on until 'Operator (600)' so far. I want to get them numerically ordered and I've come up with select colname from table order by cast(replace(replace(colname,'Operator (',''),')','') as int) which is very very ugly. Better suggestions? ...
How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)? The dump should be in SQL format, as it should be easily re-entered into the database latter and should be done from the command line. Something like sqlite3 db .dump but without dumping the schema and selecting which ...
I'm having an issue with a query that currently uses LEFT JOIN weblog_data AS pwd ON (pwd.field_id_41 != '' AND pwd.field_id_41 LIKE CONCAT('%', ewd.field_id_32, '%')) however I'm discovering that I need it to only use that if there is no exact match first. What's happening is that the query is double dipping due to the use of LIKE, ...
I'm trying to build a .NET Managed data provider and I need to be able to parse SQL commands. What are good, free (as in beer) objects to do this? - I haven't seen any standard .NET assemblies for this yet. Thanks, Eli. ...
Any advise on how to read auto incrementing identity field assigned to newly created record from call through java.sql.Statement.executeUpdate ? I know how to do this in SQL for several DB platforms, but would like to know what database independent interfaces exist in java.sql to do this, and any input on peoples' experience with this a...
I just got surprised by something in TSQL. I thought that if xact_abort was on, calling something like raiserror('Something bad happened', 16, 1); would stop execution of the stored procedure (or any batch). But my ADO.NET error message just proved the opposite. I got both the raiserror error message in the exception message, plus ...
We are using PowerDesigner at work for database modelling. But there is a hell of a price tag on that piece of software. And frankly, all I use is physical diagrams for MS SQL, which is about 1% of what PD knows. Are there any good alternatives? I know about Visio and MS SQL Diagrams, but looking for other options. ...
I have a SQL Insert query inside a stored proc, for inserting rows into a linked server table. Since the stored proc is getting called within a parent transaction, this Insert statement tries to use a DTC for inserting rows into the linked server. I would like to avoid DTC from getting involved. Is there any way I can do that (like a ...