alter table

[arnet11]

Suppose we insert two rows with exactly same data in sql server, now i want one to be deleted, how to do that. In oracle it is possible by use of rowid, do we have any thing as such in sql server???

 

[Arg81]

urm, don't quite understand your question. Two Rows with EXACTLY the same data? So you have no uniqueness in your tables? That is very very bad database management!!!


In VB.net, i.e. in the forum you have posted in, your dataAdapter (as you say you are using VS2003) creates SELECT, INSERT, UPDATE and DELETE methods.

You would use the DELETE method which uses the Primary Key of the row to delete that row.



In SQL server manager, you would just highlight the row and press your delete key

 

[cjard]

Suppose we insert two rows with exactly same data in sql server, now i want one to be deleted, how to do that. In oracle it is possible by use of rowid, do we have any thing as such in sql server???

Uh.. you dont have a primary key on your table then? Tut tut!

 

[cjard]

You would use the DELETE method which uses the Primary Key of the row to delete that row.

The only way to get a table to have two 100% identical rows is for it to have no PK. Because it has no PK, VS wont make any U/D queries for it. even if it did, it wouldnt work.. Because no data is distinct enough to uniquely identify the one row to be deleted!

 

[Arg81]

The only way to get a table to have two 100% identical rows is for it to have no PK. Because it has no PK, VS wont make any U/D queries for it. even if it did, it wouldnt work.. Because no data is distinct enough to uniquely identify the one row to be deleted!

Good point - was in the middle of editing my post when yours came through!

 

[arnet11]

well, I think I posted that at wrong place, I should placed in sql section or so,i am not concerned with how to do this with vs.net, and I know having no primary is just a very bad DB. but i would like to know if there is any thing like rowid or of that sort in sql server

 

[TechGnome]

USe a SELECT DISTINCT to select all the unique rows in your table.... insert the data into a new table, preferably one that has a PK on it.... drop the old table. Rename the new table to the same as the old table.

-tg

 

[cjard]

I know having no primary is just a very bad DB. but i would like to know if there is any thing like rowid or of that sort in sql server

There isnt.. The only way a rownumber can be generated is (i recall) the summation of a column in a half-cartesian join.. There are quite a few things that are a bit lame about SQLS (you can use Oracle if you want; it has a free version)

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133

No PK is not necessarily a bad DB. some tables have no relation to others and no need for updates. These tables dont need primary keys (logging tables etc)


I approve of TG's suggestion - for sake of preserving indexes etc I normally:
CREATE TABLE tmp_deleteme as SELECT * FROM originaltable
DELETE FROM originaltable
INSERT INTO originaltable SELECT DISTINCT * FROM tmp_deleteme


This way any indexes and other things (triggers?) that you have made on the table, dont get lost. Just remember to disable any INSERT or DELETE triggers that are time consuming otherwise those ops will take a long time. Do also consider making a PK on this table!