sql
Recover unsaved SQL query Scripts in Oracle SQL Developer
This has saved my butt several times. It is really a problem when you lose unsaved code. For about a month I have been working on a big procedure, and forgot to save the code in SVN. If you read this and remember have such unsaved code, commit it immediately! 🙂 Because everything could happen … Read more
How to eager load objects with a custom join in rails?
Can you not add the join conditions using ActiveRecord? For example, I have a quite complex query using several dependent records and it works fine by combining conditions and include directives Contractors.find( :all, :include => {:council_areas => :suburbs}, :conditions => [“suburbs.postcode = ?”, customer.postcode] ) Assuming that: Contractors have_many CouncilAreas CouncilAreas have_many Suburbs This join … Read more
When will a FAST_FORWARD cursor have a work table (and is this something to avoid)?
Just a hunch, but normally a TOP-ORDER BY requires SQL Server to buffer the result in some way (either the index scan’s result or indeed the entire result in a temp structure, or anything in between). One could argue that for cursors this is also necessary even when ordering by the primary key (as in … Read more
In EF 4.1 DbContext how to trace generated SQL
The easiest way with DbContext and DbSet<T> is just to use ToString() on the IQueryable you have built. For example: var query = context.Blogs.Include(b => b.Posts) .Where(b => b.Title == “AnyTitle”); string sql = query.ToString(); sql contains the SQL command which will be issued to the DB when the query gets executed.
How do databases physically store data on a filesystem?
The answer to this question is both database dependent and implementation dependent. Here are some examples of how data can be stored: As a single file per database. (This is the default for SQL Server.) Using a separate file system manager, which could be the operating system. (MySQL has several options, with names like InnoDB.) … Read more
Enable Query Logging in SQLite 3
There is no easy way to do this like with MySQL, but there are some options: One: Some wrapper-libraries have something like this built-in. But to find a wrapper library you would probably first need to identify the target language. Perl DBI? Python? C++? Two: I would not (in any way) recommend the following for … Read more
Can SQL Profiler display return result sets alongside the query?
No, you can’t get the resultset produced by a query included in an SQL trace. You can only tell various statistics about the call (i.e. the query executed, duration, reads etc etc). Output parameter values are recorded in the trace (if you parse TextData), and you can get it to include the rowcount info in … Read more
How to select columns from a table which have non null values?
Have a look as statistics information, it may be useful for you: SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’); PL/SQL procedure successfully completed. SQL> select num_rows from all_tables where owner=”SCOTT” and table_name=”EMP”; NUM_ROWS ———- 14 SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns 2 where owner=”SCOTT” and table_name=”EMP” order by column_id; COLUMN_NAME N NUM_DISTINCT NUM_NULLS —————————— – ———— ———- EMPNO N 14 … Read more
Pass select result as parameter of stored procedure
1.One way is: a) Declare your variables b) Assign values to them with a single select statement c) Execute the procedure passing the local variables d) Execute the following in a loop using WHILE or CURSOR in order to apply this for all rows in TABLE1 DECLARE @param1 <DATATYPE>, @param2 <DATATYPE>, … SELECT TOP 1 … Read more