We are using SQL2005 but this question can be for any rdbms. Which is more efficient, when selecting all columns from a view Select * from view or Select col1,col2.....from view ...
I have a data warehouse containing typical star schemas, and a whole bunch of code which does stuff like this (obviously a lot bigger, but this is illustrative): SELECT cdim.x ,SUM(fact.y) AS y ,dim.z FROM fact INNER JOIN conformed_dim AS cdim ON cdim.cdim_dim_id = fact.cdim_dim_id INNER JOIN nonconformed_dim AS dim ON d...
I generate a ClearQuest query using the Query Wizard. In the Query Editor, I am able to select a filter for a given field. However, I want to refine the query using the SQL Editor, but then I loose the ability to have a dynamic filter. How can I resolve this. ...
How can I disable all table constrains in Oracle with a single command? This can be either for a single table, a list of tables, or for all tables. ...
Anyone know what group I need to belong to show up in the sp_help_operator list? ...
When I started writing database queries I didn't know the JOIN keyword yet and naturally I just extended what I already knew and wrote queries like this: SELECT a.someRow, b.someRow FROM tableA AS a, tableB AS b WHERE a.ID=b.ID AND b.ID= $someVar Now that I know that this is the same as an INNER JOIN I find all these queries in my c...
How would I disable and later enable all indexes in a given schema/database in Oracle? Note: This is to make sqlldr run faster. ...
This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null value (bad data) which caused that query to return a count of 0 records. I sort of understand why but I could use some help fully g...
I have a many to many index table, and I want to do an include/exclude type query on it. fid is really a integer index, but here as letters for easier understanding. Here's a sample table : table t eid | fid ----+---- 1 | A 1 | B 1 | C 2 | B 2 | C 3 | A 3 | C 4 | A 4 | B 5 | B Here are some sample queries I want...
I have a SQL script that I want to output progress messages as it runs. Having it output messages between SQL statements is easy, however I have some very long running INSERT INTO SELECTs. Is there a way to have a select statement output messages as it goes, for example after every 1000 rows, or every 5 seconds? Note: This is for SQL An...
I have several long running report type transactions that take 5-10 minutes. Would I see any performance increase by using stored procs? Would it be significant? each query runs once a night. ...
I have an application which behaves as a slideshow for all pictures in a folder. It is written in Borland's C++ Builder (9). It currently uses some borrowed code to throw the filenames into a listbox and save the listbox items as a text file. I want to update this so that the filenames are stored in a proper database so that I can incl...
I want to index this view but because it has subquery i cant index. Can anyone suggest how to change this view so that i can index it. ALTER VIEW [dbo].[Recon2] WITH SCHEMABINDING AS SELECT dbo.Transactions.CustomerCode, dbo.Customer_Master.CustomerName, dbo.Transactions.TransDate, dbo.Transactions.PubCode, dbo.Transactions.TransTyp...
Background Normal rails eager-loading of collections works like this: Person.find(:all, :include=>:companies) This generates some sql which does LEFT OUTER JOIN companies ON people.company_id = companies.id Question However, I need a custom join (this could also arise if I was using find_by_sql) so I can't use the vanilla :includ...
Ok I have a table that has redundant data and I'm trying to identify all rows that have duplicate sub-rows (for lack of a better word). By sub-rows I mean considering COL1 and COL2 only. So let's say I have something like this: COL1 COL2 COL3 --------------------- aa 111 blah_x aa 111 blah_j aa 112 blah_m...
Not sure how to ask a followup on SO, but this is in reference to an earlier question: http://stackoverflow.com/questions/94930/fetch-one-row-per-account-id-from-list The query I'm working with is: SELECT * FROM scores s1 WHERE accountid NOT IN (SELECT accountid FROM scores s2 WHERE s1.score < s2.score) ORDER BY score DESC This selec...
I have a number of code value tables that contain a code and a description with a Long id. I now want to create an entry for an Account Type that references a number of codes, so I have something like this: insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id) ( select account_type_standard_seq.nextv...
What is the SQL command to copy a table from one database to another database? I am using MySQL and I have two databases x and y. Suppose I have a table in x called a and I need to copy that table to y database. Sorry if the question is too novice. Thanks. ...
In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'? Duplicated here. ...
I am pretty much a Sybase expert, an experienced developer, and very comfortable learning new stuff. I need to write a bunch of SQL to run against an Oracle database. Can someone recommend a book, training course or other resource to give me what I need to know in the shortest possible time? I don't need Oracle for Dummies, but someth...