|
I forgot to mention that there's an upper and lower limit of the slots. The limits may be 1 and 999 for example. Essentially, the query would be "pick any number between 1 and 999, that isn't found in the table".
For small ranges such as [1, 999], it's probably easiest to select everything and order by the primary key (a no op, since the primary key index is sorted already), and then scan for the next pair of adjacent slots X and Y where X + 1 != Y. That means that X + 1 must be available. This is however not feasible if the slot span is large-ish, because it'll degrade to a table scan... 
|
|
|
|
|
Don't know if you meant this but if you need single slots, could you use the same table and find rows that have gaps between a row and next row in order. Something like
select ...
from slots s1
where not exists (select 1
from slots s2
where s2.slotnumber = s1.slotnumber + 1)
This wouldn't lead to table scan since it's fully an index operation. However the first slot cannot be found using this so you would have to find it differently
|
|
|
|
|
I (well - my colleague to be honest) found a similar solution:
SELECT Slot + 1 AS NextSlot
FROM Slots
WHERE NextSlot NOT IN (SELECT Slot FROM Slots) Here I'll have to distinguish between no slots and all slots unavailable, because these two edge cases will produce an empty result set.
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
That would work also. However there's a slight difference between the variations. I believe your query will perform fine if the dbms is capable of doing and predicting index joins. If it's not, it can lead to table scan to create the intermediate result set from slots.
On the other hand the exists structure may perform well if the execution can be stopped immediately when the first row is found meaning that not exists will never be true (most of dbms's can do this).
So what I'm saying is that I think you should test both variations and get the execution plan to see which one (or perhaps some derived solution) will lead to best result.
|
|
|
|
|
This version also works with an empty table, but it still fails if you remove the first slot.
SELECT Coalesce(Min(s1.Slotnumber) + 1, 1)
FROM slots s1 LEFT JOIN slots s2
ON s1.Slotnumber = s2.Slotnumber - 1
WHERE s2.Slotnumber IS NULL
AND s1.Slotnumber<999
|
|
|
|
|
I forgot the most important thing: thank you for your effort! Much appreciated!
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
You're welcome 
|
|
|
|
|
CREATE TABLE slot(
id int not null,
slot1 bit,
slot2 bit,
slot... bit,
slotn bit,
...
)
J/k. I would use the associate table approach that contains the list of available slots exactly as you have mentioned. Its not fancy, but it is efficient and easy to read.
Need custom software developed? I do C# development and consulting all over the United States.
If you don't ask questions the answers won't stand in your way.
Doing a job is like selecting a mule, you can't choose just the front half xor the back half so when you ask me to do a job don't expect me to do it half-assed.
|
|
|
|
|
How about having a "Reserved" bit to set or clear as the slot is used and freed up?
A third-party product my former employer used did that, there was a table that always contained 8000 rows, and the system used them to hold values until it needed them, then freed them afterward.
|
|
|
|
|
I thought about that, but the situation is a bit more complicated than it seems. I have a bunch of satellites (clients) and a hub (server). The clients can hold up to 10k rows in each table. These tables will later be merged on the server, meaning that if there are N satellites, there may be up to N * 10k rows on the server. Using a status bit, there will be exactly 10k rows on the satellites, and N * 10k rows on the server. N is arbitrary. It could be 1 or it could be 50. I want to keep the system as fast as possible, because the server should be able to run on a fairly small sized machine.
I also prefer to not having hard coded values in a database, because frankly, you never know what can happen to the hard coded values. 
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
I have a table, which has two rows
1.EmailID
2.EmailAddress
I have Duplicate Email Addresses and i want to Delete Duplicate Email Addresses from my table except the max(emailId)
and I have millions of Duplicate EmailAddresses in my table
Plz ans me as soon as possible....
Thanks!
|
|
|
|
|
Don't double post. It's considered rude.
|
|
|
|
|
I have been Double posted in last two days but I do not recieve a single ans to help out from this satuation..........yyyyyyyyyyyyy
|
|
|
|
|
You mean you have been waiting 2 days when it takes 5 secondes to do a search. There are pleanty of answers here clickety[^]
Learn to use the tools you have available instead of whining that no one is helping you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I use every link . but there is no link to be fruite full
U know
|
|
|
|
|
if you can't take Mika's reply and apply it to your requirement then you need to do some thinking about what you are doing - he supplied a solution days ago. I give up, some people are just too lazy for words.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
U know i find the solution . thanku for your Stupid answers
you are only time wasted...............ok
|
|
|
|
|
|
One thing about forums is that when people answer you, it may not always be the kind of answer you expect. However, the answer is still meant to help you forward.
The worst thing you can do is that you start to offend the very people that put their own precious time to voluntary work in order to help other people.
Think about it: what kind of treatment would you expect the next time you ask for help if you start mocking the answers given to you.
So my advise is: Read the given answers, analyze them neutrally in your mind and learn from them. This helps you both in understanding technical issues but also to get answers more efficiently when working with forums.
|
|
|
|
|
Oh! And you are a jack*ss. NO ONE WILL HELP YOU NOW.
|
|
|
|
|
Yeah, you are not going to get ANY help at this point.
|
|
|
|
|
Hi,
I have created a dts package. all the values like Source Data connection, source userid and source pwd is passed through variables and for destination database all these values are hardcoded.
At design time I used a DSN which points to SourceServer1. Using this DSN it stores mapping between source and destination at design time.
After this I executed this pakage with the same DSN it worked perfectly but when I changed datasource of the destination from SourceSrver1 to SourceServer2, I am geeting error "SourceServer2 is not same as the SourceServer1". When I am passing all the connection info using global variables then why it is looking for the connection info that I provided at design time?
It's very urgent, any help is deeply appretiated.
Thanks in Advance.
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
Hey there!
I am trying to create a connectionstring to SQLServer 2005. The server uses only windows athentication. I am now writing an application that uses a .NET Data provider for SQL Server. Using the following connectionsstring is working:
Data Source=Server\inst01;Initial Catalog=TestDB; Integrated Security=SSPI;
What happens is that the user that is currently logged in, is also connecting to the database. My issue is now, that I want to connect to the database with a different user as the one logged in to windows. The user should provide user name (including domain) and password in my application and I want to build a connectionsstring so that the logged in user can connect to the database as a different user.
How must the connection string look like? Is ist possible at all?
Thanks,
Florian
modified on Monday, February 16, 2009 3:48 PM
|
|
|
|
|
I don't understand good enough your question but take a look this link http://www.connectionstrings.com/[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks, I had a long look at this. But it did not give me an answer.
What I want to do is the following: I want to connect to a SQL Server database with a special user that is not the user currently logged on to windows. And I do not have the option of configuring SQL Server logins only Windows Authentication can be used.
I hope I could make it a little bit clearer what I want.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
