is it faster to check a Date for Null or a bit for = 1/0
I'm just wondering what is it faster in sql can have a column of Date and to check it for null or to have a Date and a bit and to check the bit for 1/0 is the bit going to be faster ? ...
sql
Insert or Update without a loop?
I have table with two columns: ItemMaster (Item INT, Quantity INT) If an item is already there, then I should update the Quantity. Otherwise, I have to insert a Record in this table. Is this possible without Loop? I'm using SQL Server 2005. ...
Can I (theoretically) use a Collection (e.g., Array, List) as a foreign key in a relational Database schema?
Is is possible to use a Collection such as a Multiset or Array as the foreign key in a database scheme? Background: A student proposes to use such a construct (don't use a join table for the n:m association) to store the following object structure in a database public class Person { String name; List<Resource> res; … } pu...
Methods and recommendations for testing persistence of entities
Everyone loves unit testing. But testing persistence of entities is a bit different. You are testing a process occurring across multiple layers using different languages. Your tests have side effects (in the sense that rows are being added / modified etc). I would like to know how you do this. For example, do your tests create a whole n...
Django queries: how to annotate with a filtered count?
Suppose I have a Book model with a language field and a foreign key to a Publisher model. Currently I use a Count annotation in a custom Publisher manager to allow me to add to the admin a sortable column with the number of books by each publisher. (see http://stackoverflow.com/questions/3491766/how-to-add-a-sortable-count-column-to-the...
Selecting records based on two other tables
I have a query with inner join to another table, with this I want also include records which are contained in another column. Example: select name, address from table1 inner join table2 on table1.id = table2.id With this, I want to also include rows which are having table1.recno = (1,2,4). How could I write query for that? One opt...
sql server 'in' or 'or' - which is fastest
Ok, so I have a query: select distinct(a) from mytable where b in (0,3) What is going to be faster, the above or select distinct(a) from mytable where b = 0 or b = 3 Is there a general rule? Thanks ...
Update multiple rows with different values in SQL
I have a table like this :- Product ID Weight A 100 B 100 C 100 D 100 E 100 I want to change it to:- Product ID Weight A 501 B 601 C 701 D ...
Parsing a SQL Server string into substrings
I have a column of data I've imported form a lotus notes database which consists of a contacted address field. The items in the address are separated by ASCII (13), ASCII (10) What the easiest way to split this address up in to separate columns? ...
T-SQL: How to return 0 rows in from stored procedure, and how to use XACT_ABORT and TRY/CATCH
I'm writing a stored procedure and I want to return 0 records when something fails. I can't seem to figure out how to just return 0 rows? I've used SELECT NULL but this returns 1 row with a NULL in row 1 col 1. I have also tried not specifying any SELECT statements in my error code path but when testing the value of @@ROWCOUNT after the ...
MySQL: what is the best way to create access system?
I have table called page which represents every single page in my website. page_id | page_subject | page_path ---------------------------------- 1 | Foo | /Foo 2 | Bar | /Bar I also have table called group: group_id | group_name --------------------- 1 | Users 2 | Admins Goal: how to def...
sql question what is this statement meaning
What does this statement meaning, could somebody elaborate me on this please (@diplomaPercentage is null OR diploma_percentage>=@diplomaPercentage) ...
Database trigger or a common method in code?
I have a table where I want to Log activities of some part of my application. A record will be inserted (may be updated in future) in this table when record inserted/updated in some other table. E.g. If record is inserted in Orders table an entry will be inserted in Log table. If record is inserted in Booking table an entry will be in...
How many times are the results of this common table expression evaluated?
I am trying to work out a bug we've found during our last iteration of testing. It involves a query which uses a common table expression. The main theme of the query is that it simulates a 'first' aggregate operation (get the first row for this grouping). The problem is that the query seems to choose rows completely arbitrarily in some ...
Threading issues with SQL and C# where different threads get the same number, but shouldn't
My scenario: n number of records in table, threads trying to access the table. One has to get first number and delete it, others have to get the second, third, etc., one by one. But the problem is some of the threads get the same number. How do I avoid this? My code: private void Form1_Load(object sender, EventArgs e) { for (int j...
Is there any single sql command to export entire database from one sql server to another ?
I was asked in the interview tell me the different ways of exporting database from one sql server to another, I knew only about creating a .bak file and then restoring it to another sql server which I told them. However, they asked me about a single SQL INSERT command which will perform this task. I have googled it and can not find it....
what type of data structure should I use to hold table rows?
I'm new to java and just getting into querying databases. so far I have my results in a ResultSetMetaData. I'm think that for each row in the dataset I should add it to some form of collection? Can anyone tell me the best practice for this? thanks, Jonesy ...
convert row into columns in oracle10g
how can i convert rows in to columns in oracle 10g( like pivot in oracle 11g),so that i can add multiple 'and' conditions for the primary key. ex: select emp_name from emp where empid = 1 and emp_age = 21; where empid = 12 and emp_age = 23; without using 'in' ,i have to get records which satisfies all the above condtions(Like 'and ' ...
How do I get ID of an exiting table entry or insert a new one in case it doesn't exist efficiently?
I'm using SQLite to maintain a database which contains, among other things, a table with paths. The CREATE statement for the table looks like this CREATE TABLE path (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, UNIQUE(name)); I'm looking for a short way to say 'insert this given path into the ...
is using views faster or slower than just writing the whole sql from scratch
I have 3 tables (A,B,C) and I need quite often to query the result of A inner join B union A inner join C I'm just thinking which way is going to be faster: to write in each query the whole thing from scratch to create a view and use it in queries ...