If I have a primary key of say id and I do a simple query for the key such as, Will it find one row and then stop looking as it is a primary key, or would it be better to tell mysql to limit its select by using LIMIT 1? For instance: Answer Does using “LIMIT 1” speed up
Tag: sql
Join two tables where table A has a date value and needs to find the next date in B below the date in A
I got this table “A”: and this table “B”: Now I am looking for a way to JOIN those two tables having the “value” column in “B” mapped to the dates in “A”. The tricky part for me here is that table “B” only stores the change date and the n…
Dynamically name indexes in SQL Server 2005?
As most people who work with Sql Server know, you can declare a temporary table like so: …this will create a primary key and a default constraint on this temporary table; the names for these objects will be unique, dynamically created by Sql Server. Is it possible to create a dynamically named index for…
Referring to a Column Alias in a WHERE Clause
I get “invalid column name daysdiff”. Maxlogtm is a datetime field. It’s the little stuff that drives me crazy. Answer Normally you can’t refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.) But, as mention…
Using SELECT for simple BOOLEAN evaluation
I want to test some evaluations without working on any table. For example, you can write I want to achieve something like this: I know that most engines don’t have the concept of a boolean data type, but I don’t know how their internal work (even if I guess everything <> 0 is true, like in C…
Set variable value to array of strings
I want to set a variable as a string of values. E.g. declare @FirstName char(100) select @FirstName = ‘John’,’Sarah’,’George’ SELECT * FROM Accounts WHERE FirstName in (@FirstName) I’m getting a …
SQL Query to update a column based on the values of other columns in the same table
Ok this is difficult to phrase, so here goes… I am using MS SQL Server 2008 R2. I have a temp table that lets say has two already populated columns. There is a third empty column I want to populate based on the value of the first two columns. What I want to do is create a guid (using NEWUID())
Function as parameter to another function in Postgres
Can I create a user defined function in Postgres either through the C-Language Function API or by using pl/pgsql which accepts a callback function as parameter? As far as I see there is no way to do this through the C-Language API since it only accepts sql datatypes and there is no datatype for function. But …
Superkey, candidate key & primary key
Can any kind soul clarify my doubts with a simple example below and identify the superkey, candidate key and primary key? I know there are a lot of posts and websites out there explaining the differences between them. But it looks like all are generic definitions. Example: So from the above example, I can kno…
Best way to delete millions of rows by ID
I need to delete about 2 million rows from my PG database. I have a list of IDs that I need to delete. However, any way I try to do this is taking days. I tried putting them in a table and doing it in batches of 100. 4 days later, this is still running with only 297268 rows deleted.