|
I agree with ArielR. Very nice. I'd have said it wasn't possible if I hadn't seen your solution.
Correct me if I'm wrong, but doesn't your solution only return the first and last numbers from a missing block...
1,2,3,9,10
It won't return all the missing numbers: 4 thru 8, will it? Just 4 and 8; right?
I also found it difficult to see how it was working, so I dumbed it down for my own understanding:
SELECT (T.ID - 1) AS MissingID
FROM tblMyTable AS T
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable);
Of course, this will pick up numbers that don't belong in the table, like 0 (zero), so I added a predicate similar to yours:
SELECT (T.ID - 1) AS MissingID
FROM tblMyTable AS T
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable)
AND T.ID > @startnum;
Of course this ONLY picks up numbers of the form (ID-1) where ID IS in the table, and ID-1 is not. It won't pick up others numbers from a block of missing numbers.
Now a concern:
I *THINK* that each element from the (T.ID - 1) set will be subject to a linear scan of the entire (SELECT ID FROM tblMyTable) result set (well, it should stop as soon as a hit occurs). This will be an O(N^2) operation. I also *THINK* that if we just sorted the exclusion set:
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable ORDER BY ID)
the DBMS should recognize the order, and do a binary search instead of a linear scan, reducing the overall select to O(N*ln(N)) (of course the ordering operation itself may be an O(N*ln(N)) operation). This may seem insignificant, but if you're searching a million record table, N^2 could be huge! In this case, N*ln(N) could be 50,000 times faster than N^2.
Then I began wondering if it could be reduced it to an O(N) operation just like a person would code it manually in a procedural language if they knew the two sets were ordered. Perhaps a LEFT JOIN like kubben suggested would do it:
SELECT Missing.ID FROM
(SELECT (ID - 1) AS ID
FROM tblMyTable
WHERE ID > @startnum
ORDER BY ID) AS Missing
LEFT JOIN
(SELECT ID
FROM tblMyTable
ORDER BY ID) AS Includes ON Missing.ID = Includes.ID
WHERE Includes.ID IS NULL;
Maybe someone who knows more about the guts of a DBMS (like Mike Dimmick) could set me straight here. I'd have liked to run some speed tests with huge tables, but just don't have the resources available to me.
David
|
|
|
|
|
You are correct in your speed assumptions. When used on an primary key, my code would result in a binary search of the index. Your method would be significantly faster on larger tables especially since NOT IN is a very slow operation.
You would still have to acquire the first ID in a missing hole. It would be easy enough to re-insert the correlated subquery (will slow it down a bit) by using your final query as the source for a new query.
The only way I can see for locating all missing IDs is to use a while loop, a cursor or building a table of integers as the first respondent mentioned. Normally, I would leave further analysis to a client application where iteration through the result set is a simplistic operation.
|
|
|
|
|
Hi Guys I have a small database program and I want to add an e-mailling functionality to it. Its quite a simple design program, I move through the records in the dataset by Previous and Next labelled Buttons...
I want to put another button on the same form that will send an e-mail to the record currently viewed on the the application. The e-mail will stay the same and only change the name and also send a little information from the database.
Is this chair taken
|
|
|
|
|
Ok what programming language. If it's .NET then use the System.Net.Mail.SmtpClient to send the mail out.
|
|
|
|
|
How can I make this below statement work for MSSQL 2005
UPDATE TABLE
SET FIELD2 = (SELECT COUNT(*) FROM TABLE)
WHERE FIELD1 = @Parameter
I'm not sure if it will work as is. I was sort of hoping there was an eval fuction, but I haven't found one.
|
|
|
|
|
This should work:
Declare @cnt int
Select @cnt = count(*) from table
UPDATE TABLE
SET FIELD2 = @cnt
WHERE FIELD1 = @Parameter
Ben
|
|
|
|
|
Thanks that looks exactly like what I need to do.
|
|
|
|
|
Ok Is there something I need to do to make it do these statements in a .NET application using a SqlCommand. Possible following each command set with the word GO
Declare @cnt int
GO
SELECT @cnt = COUNT(*) from TABLE
GO
or a \n
|
|
|
|
|
It would be better to put the code into a stored procedure and call the procedure from the sqlCommand object.
Ben
|
|
|
|
|
In general SQLcommands in .net don't like the hard core sql stuff. They just want a select, update, insert, delete or call of a stored procedure.
Ben
|
|
|
|
|
Ok I maybe calling the
DECLARE @cnt int
Select @cnt = count(*) from PlaceInReferralLine
from more than one place so I'm assuming the best thing to do would be put just this code in
the stored procedure.
I'll give that a try, because the orginal code and the stuff I modified kept coming up with a SYNTAX ERROR near @cnt. Of course they couldn't atleast tell me which one was causing the problem from the debugger. Hehe.
Ok. While I am used to getting and retrieving data from different databases I'm still what I consider to be an extreme novice as I only know how to do the basic select, insert, etc.. stuff
So now for a few more questions
1. How do I get it to return the count from the store procedure.
CREATE PROCEDURE dbo.StoredProcedure1
DECLARE @cnt int
Select @cnt = count(*) from TABLE
RETURN @cnt <-I'm assuming
or maybe more simply
CREATE PROCEDURE dbo.StoredProcedure1
RETURN Select count(*) from TABLE
All this is being done in Visual Web Developer from MS.
2. Then I assume to get the data from the stored procedure
INSERT INTO TABLE
(col1, col2)
VALUES ('value1', dpo.StoredProcedure1)
|
|
|
|
|
Ok, there are several ways of doing it. I would guess the easiest is to just return an output parameters. so your SP looks like:
CREATE PROCEDURE dbo.StoredProcedure1
@param1 int,
@cnt int output
as
Select @cnt = count(*) from TABLE where column = @param1
RETURN 0
<pre>
So then when you call the stored procedure you will have to add two sqlparameters to your sqlcommand. The @cnt needs to be marked as direction = output
After you call to executenonquery on the stored procedure you can then check the parameters and get the output value. Then use this value when doing the insert or update.
You can't actually call a stored procedure in an insert statement.
You can call a function, but there are other limitation with functions.
Hope that helps.
Ben
|
|
|
|
|
Ok. I'm beginning to wonder if I shouldn't just do the entire set of procedures in a single stored procedure because I can't take the chance that during the UPDATE or INSERT operations that the record count will change. So maybe I should do something similar to the following.
<br />
create procedure dpo.StoredProcedure1<br />
@cnt int = 0<br />
@AccountID int<br />
@NewAccountID int<br />
AS<br />
SELECT @cnt = Count(*) FROM PlaceInReferralLine<br />
SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID<br />
<br />
SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1<br />
<br />
UPDATE UserAccounts SET ReferredBy = @AccountID WHERE AccountID = @NewAccountID<br />
<br />
INSERT INTO PlaceInReferralLine (AccountID, PlaceInReferralLine) VALUES (@NewAccountId , @cnt)<br />
UPDATE PlaceInReferralLine SET PlaceInReferralLine = PlaceInReferralLine - 1<br />
<br />
UPDATE PlaceInReferralLine SET PlaceInReferralLine = @cnt WHERE PlaceInReferralLine = @AccountID<br />
RETURN 1<br />
RETURN 1 <- Intended for the entire procedure being successful. Which I guess for .NET to do this I would need to execute as a SqlCommand.ExecuteScalar() operation.
Sorry if there is any confusion with both a col and table name PlaceInReferralLine
|
|
|
|
|
Yes that would be a good idea. Since you are going to just have one stored procedure you should remove the @cnt input parameter and add
Declare @cnt int
after the AS
and before the Select @cnt
You do not have to do an executeScaler to get a returncode. You always get a return code when executing a stored procedure. You can still use executenonquery.
Ben
|
|
|
|
|
Ok now to just go and learn how to use the result sets so I can do my if statements.
|
|
|
|
|
If you use executenonquery the returncode is the result of that call. So you don't need to worry about result sets.
Ben
|
|
|
|
|
I made a second error with the parameters. The only one I will want to pass is the @NewAccountID. The @AccountID must be set by
SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1
since there is no posibility of there everybeing more than one of the PlaceInReferralLine being equal to 1.
I also need to make sure
SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID
is empty or null before I continue to process. Otherwise the ReferredBy column could be changed.
I supposed I could to this at the program level rather than at the SQL Server level.
|
|
|
|
|
I think I would do this as well in the sql stored procedure. It would be the most efficent there.
Ben
|
|
|
|
|
Ok I've looked, I probably don't know exactly what I need so can you point me in teh right direction for information on how to use the results from the select statements directly within the stored procedure.
|
|
|
|
|
There are several things you can do. I think you said you didn't want the record to exist. I think the best way to do that would be:
if (Select count(*) from table where column = @param) = 0
begin
end
If the record might exist, but the value for the field is null you can do:
if (select isnull(column2,'') from table where column = @param) = ''
Begin
End
Ben
|
|
|
|
|
Ok so how would I do something like
@AccountID = Select AccountID FROM Table where Col2 = 1;
|
|
|
|
|
Declare @accountID int
select @accountid = accountid form table where col2 = 1
There are no ; in sql.
Ben
|
|
|
|
|
Hi there .
During transaction is began , I inserted new data in table , until I don't call commit() or rollback() functions , another processes can not access to this table.
I mean, if i try to add a new data in a table with transaction and before closing transaction(commit or rollback) another process can't read that table; then a exception will occurred.
what is the problem?
DMASTER
|
|
|
|
|
Developer611 wrote: what is the problem?
There is no problem. A transaction shouldn't last long enough for there to be a problem. I'm guessing the other process times out waiting for the table to become available. And while it is in use in a transaction you have made it unavailable.
The solution is to make your transactions shorter.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Thanks dear Colin . But how can I do this in short time . Did you have any solutions ?
DMASTER
|
|
|
|