I have a read query that I execute within a transaction so that I can specify the isolation level. Once the query is complete, what should I do? Commit the transaction Rollback the transaction Do nothing (which will cause the transaction to be rolled back at the end of the using block) What are the implications of doing each? usin...
I've got a database with three tables: Books (with book details, PK is CopyID), Keywords (list of keywords, PK is ID) and KeywordsLink which is the many-many link table between Books and Keywords with the fields ID, BookID and KeywordID. I'm trying to make an advanced search form in my app where you can search on various criteria. At th...
Hello, For some reasons, I would like to do an explicit quoting of a string value (becoming a part of constructed SQL query) instead of waiting for implicit quotation performed by cursor.execute method on contents of its second parameter. By "implicit quotation" I mean: value = "Unsafe string" query = "SELECT * FROM some_table WHERE s...
In the tradition of this question and in light of the documentation, how does one make this function deterministic: ALTER FUNCTION [udf_DateTimeFromDataDtID] ( @DATA_DT_ID int -- In form YYYYMMDD ) RETURNS datetime WITH SCHEMABINDING AS BEGIN RETURN CONVERT(datetime, CONVERT(varchar, @DATA_DT_ID)) END Or this one (because of t...
Martin Fowler defines an elegant object model for the scheduling of recurring tasks here, which maps to OO code very nicely. Mapping this to a relational database schema for persistence, however, is tricky. Can anyone suggest a schema + SQL combination that encapsulates all the functionality he describes, particularly in the image on p...
If a "WITH NOLOCK" query hint is used on a View in SQL Server, does it propagate that hint to the view definition itself, even if NOLOCK is NOT used for the raw tables in the View definition? The reason to need this is that sometimes the support staff wants to do huge time-consuming queries but would rather not force this lock on all que...
I feel like this is a stupid question because it seems like common sense . . . but no google search I can put together seems to be able to give me the answer! I know how to get data OUT of a sqlite3 database using the .dump command. But now that I have this ASCII file titled export.sqlite3.sql . . . I can't seem to get it back INTO the...
I have a table that has, in essence, this structure: key value ------ ------ 2 val1 3 val2 5 val3 The keys are sequential integers from 1 up to (currently) 1 million, increasing by several thousand each day. Gaps in the keys occur when records have been deleted. I'm looking for an SQL query t...
I have a query (which was created by LINQ to SQL) to get me a list of 'site visits' that were made between a certain date range which resulted in an order (orderid is not null). Theres nothing wrong with the query. I just need advice on creating the correct index for it. I was playing around trying different combinations on a production...
I am looking for a Java open source beautifier or reformatter for SQL that I can use to clean up DDL statements that I am generating with openArchitectureWare. Nothing in the answer to "Online Code Beautifier And Formatter" is of use to me and I have not been able to get Simple SQL Formatter to work for me. ...
The query used inside the IN() returns: 1, 2. However the whole query returns 0 rows which is impossible since they are present. What am I doing wrong here? SELECT DISTINCT li.auto_id FROM links AS li JOIN group_data AS gi ON li.auto_id = gi.autoid AND li.user_id = gi.userid WHERE gi.groupid IN ( SELECT CA...
I need some urgent help! I put together a sample scenario of my issue and I hope its enough for someone to point me in the right direction. I have two tables Products Product Meta I need a result set of the following ...
In my specific case, I have two kinds of "messages" that I need to retrive and paginate. Let's omit the details, and just say that the first kind is in a model called Msg1 and the other is called Msg2 The fields of these two models are completely different, the only fields that are common to the two models are "date" and "title" (and o...
I am translating SQL Server SQL Statements into their ANSI generic equivalent at present, and am stuck with a recursive statement using a WITH statement. For the sake of concentrating on the issue, I'll simplify the issue as follows If I have two tables ReportingUnit col1: Key col2: ParentReportingUnitKey Facility col1: Key col2...
I have a sql query that runs super fast, around one second, when not using variables, like: WHERE id BETWEEN 5461094 and 5461097 But when I have: declare @firstId int declare @lastId int set @firstId = 5461094 set @lastId = 5461097 ... WHERE id BETWEEN @firstId and @lastId ... the query runs really slow, finishing only after ...
Hi, I'm currently using the following SQL for retrieving the last seven days worth of entries from a table: purchased >= date_sub(now() ,interval 7 day) However, I need to change this so it retrieves the last full weeks worth of entries (midnight Saturday to midnight Saturday). So basically, throughout the week the results never chan...
Hello, Here's a simplified version of my scenario: I have a table named Project that I reference via an 'id' field. I have a table named Photo that has a field named 'project_id' which I use to relate multiple photos to a single project. The Photo table also has an auto-incrementing 'id' field that I use for ordering, amongst other th...
I have two tables: a source table and a target table. The target table will have a subset of the columns of the source table. I need to update a single column in the target table by joining with the source table based on another column. The update statement is as follows: UPDATE target_table tt SET special_id = ( SELECT source_specia...
Given a collection of user specified tags how do I determine which ones are not in the tags table with 1 SQL Statement? Assuming a table schema tags (id, tag) and I'm using mysql, if there's an optimization I'm unaware of. thanks ...
How would you handle cross database queries in different environments. For example, db1-development and db2-development, db1-production and db2-production. If I want to do a cross-database query in development from db2 to db1 I could use the fully qualified name, [db1-development].[schema].[table]. But how do I maintain the queries a...