|
Well, if you really can't sleep at night because of holes in your identity sequence, then you can manually assign IDs to new records (although this might require turning off your sequencer, or removing the "IDENTITY" attribute, then re-initializing it afterward) and use-up those holes.
Some months back, someone much smarter than I (Michael Potter[^]) suggested something similar to the following to find holes within a sequence.
SELECT TOP 1 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;
You could just turn off your sequence entirely, and use the return from the above when it is not null, then when it does return null, use the following.
SELECT MAX(ID)+1 FROM myTable;
However, there are issues with not using the DB provided sequencer. For example, you'll have to make sure concurrency works correctly. This may involve locking tables, etc.
David
---------
Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code.
http://yosefk.com/c++fqa/picture.html#fqa-6.6
---------
|
|
|
|
|
i think i wont need sequencer because i got a trick from your last sql query
thanks man it may solve my problem
Becoming Programmer...
|
|
|
|
|
If this is a real project (and not a homework assignment), then I strongly suggest you re-read what Colin wrote. He is absolutely correct. Performance aside, a real problem with renumbering keys is making sure you GET IT ALL CORRECT (all the other tables that reference this key must also change). My previous post really dealt with adding new records to a table with holes in the identity field; not with RE-NUMBERING existing records.
EXERCISE CAUTION, and unless really necessary, follow Colin's advice and just live with the holes.
Good luck.
David
---------
Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code.
http://yosefk.com/c++fqa/picture.html#fqa-6.6
---------
|
|
|
|
|
yeah i m still following Colin's advice.... but i just wanted to know that is there any inbuilt function or any sp in sql that can re-numbering... and i found no
so now, i can live with holes, but whenever i see my table with holes i get frustrated and think this isnt a good way to live with holes
one more question i have that is :
sql have any sp or function that can return the index of the numered rows ?
for example:
5,.....,........
8,.....,........
10,.....,........
i wanna know the index of 8 that mean it should return 1 (0 based index)
for 10 it should return 2
i dont think it will be, but just wanna know
thanks for keep repling
Becoming Programmer...
|
|
|
|
|
SQL databases don't have any concept of "intrinsic ordering". To the DBM, the example records you listed are just data -- they are not "record[0], record[1], record[2]". The DBM has no concept that "5,.....,........." is before "8,.....,........." is before "10,.....,........" etc.
You can specify the order in which records are PRESENTED TO YOU in response to your query by appending an "ORDER BY" clause. Further, some DBMs (or maybe all of them) can have a "clustered index" on a table. This index relates to the natural storage order of records within the database, but is more of an internal book-keeping thing than an exposed property for you to attempt to use. ANY field (even if it is not indexed) can have an ORDER BY clause attached to it within a query, and the order in which the records are returned will change in response to the clause.
The only reason you should be thinking about intrinsic ordering is if you are really concerned about performance, and are attempting to manually manipulate the clustered index (usually by properly selecting the primary key). Even then, you need to clearly understand the distinction between a database index, and intrinsic order. SQL databases store and use indices: NOT intrinsic order.
Again, good luck.
David
---------
Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code.
http://yosefk.com/c++fqa/picture.html#fqa-6.6
---------
|
|
|
|
|
now i think that i should leave the table as its now XD
thanks
Becoming Programmer...
|
|
|
|
|
hey man at last i got a bit that what i wanted, a single line of 5 words can do that
yeyeyeye
however it wont rearrange but it can fills holes when new data insert
DBCC CHECKIDENT ("TeacherSubject", RESEED, 0)
Becoming Programmer...
|
|
|
|
|
Thanks for sharing it.
What language is that anyway? SQLServer? Oracle? I don't recognize CHECKIDENT() as a native SQL keyword. What exactly does CHECKIDENT() do?
David
---------
Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code.
http://yosefk.com/c++fqa/picture.html#fqa-6.6
---------
|
|
|
|
|
i think it nothing do more than reseeding the identity, firstly i thought it will solve my problem but it solved my half problem not full
yeah its sql server language
Becoming Programmer...
|
|
|
|
|
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Bs_Proc_Serial'
AND type = 'P')
Drop Proc Bs_Proc_Serial
GO
Create Proc Bs_Proc_Serial
@TableName Char(25) ,
@ColumnSearch Char(25)
AS
/* G e t S e r i a l I d D y n a m i c */
Declare @Ex NVarChar(500)
Declare @Par NVarChar(100)
Set @Par= N'@Serial bigint'
Set @Ex = N'Select @Serial = Count(*) From ' + @TableName
Set @Ex = @Ex + N' while (@Serial < 100000000 )'
Set @Ex = @Ex + N' begin '
Set @Ex = @Ex + N' if Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= RTRIM(@Serial)) '
Set @Ex = @Ex + N' begin '
Set @Ex = @Ex + N' If (@serial < 10 ) '
Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) '
Set @Ex = @Ex + N' Break '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' If (@Serial < 100 ) '
Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) '
Set @Ex = @Ex + N' Break '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' If (@Serial < 1000) '
Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) '
Set @Ex = @Ex + N' Break '
Set @Ex = @Ex + N' End '
Set @Ex = @Ex + N' Set @Serial = @Serial + 1 '
Set @Ex = @Ex + N' End '
Set @Ex = @Ex + N' if (@serial < 10 ) '
Set @Ex = @Ex + N' Select ''00'' + Rtrim(@Serial) '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' if (@Serial < 100 ) '
Set @Ex = @Ex + N' Select ''0'' + Rtrim(@Serial) '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' Select Rtrim(@Serial) '
-- Set @Ex = N'Select Count(*) From '
-- Set @Ex = @Ex + @TableName
Execute sp_executesql @Ex,@Par
Go
Exec Bs_Proc_Serial 'Ic_Category' ,'CategoryId'
sql server msg:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@Serial'.
what's problem?
123
|
|
|
|
|
Try adding:
PRINT @Ex
so that you can be sure of the SQL string that you are building up.
Regards
Andy
|
|
|
|
|
I think you should launch sp_executesql by providing a value for @serial
Ex :
Execute sp_executesql @Ex,@Par,@serial=0
But I don't get the point of your SP, maybe should @serial be an OUTPUT param ?
|
|
|
|
|
i am providing the code below
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds, "offensive_issue");
da.Fill(ds, "crime_mgmt");
|
|
|
|
|
JOIN the tables in your database query.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hai all,
i had one column Emp_ID,location,.... in view(vw_Employee) and
one colmn Qurry_ID,location,Emp_ID in table(tbl_querries)
i just want to know,
how to find the count(Emp_ID) and count(Qurry_ID) by using innerjoin depending on distinct location
thanks in advance
Thanks
Subbu.
|
|
|
|
|
This is the syntax for doing a count of distinct records from a table:
SELECT COUNT(DISTINCT column(s)) FROM table
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
The background
I'm planning on setting up a secondary database at work which will act as a view only platform for our accounting system.
The reason behind this is that we have 32 licenses for our account system and by using replication I can create another database allowing view only permissions - hence doubling our licenses. In effect I will be creating a new accounting system which will be a replication of our live system(updated every hour).
I've done a fair bit of reading from both Microsoftt, Wrox "Professional SQL Server 2000 Programming" book,idiots guide to sql server etc...
However I still want something based a bit more on experience before I start setting up replication on my business server.
Replication tends to get presented in such a dry and theoretical manner!
Can anyone recommend a good link/article on the internet for me?
Or is the answer just to dive in and learn from practice?
Regards
Guy
-- modified at 4:44 Saturday 17th November, 2007
You always pass failure on the way to success.
|
|
|
|
|
|
Thank you for the link. 
My plan is to have the Publisher/Distributor and Subscriber on the same server.
We are doing things on the cheap here.
In your experience will this cause a marked slowdown in the the machine?
Thanks again
Guy
You always pass failure on the way to success.
|
|
|
|
|
i want to know how can i delete (or disable) all users (dbo, guest, etc.) from my database and set one user for my database.
in fact i don't want other users could to connect to mydatabase in any sql server.
|
|
|
|
|
hi
Use enter prise Manager To remove Users ..Follow these steps
1)To remove a user account from a database role
2)Expand a server group, and then expand a server.
3)Expand Databases, and then expand the database in which the role exists.
4)Click Roles.
5)In the details pane, right-click the role to which the user account belongs, and then click Properties.
6)Select the user to remove, and then click Remove.
|
|
|
|
|
thank you,
so i will remove all users (dbo, ..) from my database and set one user to it and detach it from sql server, now i have a mdf file that just one user could connect to it. let me know in any machine with sql server express just my user could connect to it and others (dbo, ...) could not connect to my database?
in fact i don't want any user from any machine could connect or view my database.
|
|
|
|
|
hi all,
if u create view from single table it will allow updating view
,but if u create view from two tables it is not allowing to
update view .... can any one explain this .....?
suman
|
|
|
|
|
This makes sense for many reasons:
1-What if the view does not contain the PKs of one or both of the tables?
2-What if the join returns the same record more than once (as when joining master and detail tables)?
For more information check: Modifying Data Through a View[^]
|
|
|
|
|
Hi,
can anyboby tell how to write triggers as my req is if a row is inserted in table1 the same row has to be inserted on table2..
regards
padma
padma
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
