Sean Lange (7/31/2015)
You should avoid the sp_ prefix on your procedures. It is reserved for MS and denotes it as a system procedure. The performance issues are mostly corrected but ambiguity and potential issues are still there. My preference would be to drop the prefix entirely but if you insist on them something like usp_ would be better.That being said I would be interested in seeing the script you came up with.
Normally when I build a fairly Generic Stored Procedure, I store it in the master and use:
sp_ms_marksystemobject '<Procedure_Name, sysname, ProcedureName>' -- System sp
To make it a 'system procedure', so it is available from all databases.
As I understand this is correct usage. (???)
Sean Lange (7/31/2015)
ben.brugman (7/31/2015)
Why so many tables without primary keys.Not many and most of them have primairy keys. (But not implemented primairy keys).
Not sure what that means. Your table either does or does not a primary key. Are you saying they are only logical primary keys and not defined as primary keys? Does those tables at least have a clustered index?
Primary Key is a conceptual idea, so it is there in the logical design.
In the implementation of a table, a primairy key can be implemented, or another surrogate key can be choosen for an implementation. There are even plenty of databases where a primary key can not be implemented.
I like to differentiate between a logical design and an implementation. For me the Primairy key belongs to the logical design, not implementing is an option. (Clustering/indexes are all parts of the implementation, not of the logical design).
The delete option of duplicates is mostly for short lived tables. (import/test etc.) Limited number of rows, no identities, no constraints.
Sean Lange (7/31/2015)
That being said I would be interested in seeing the script you came up with.
This will take some time, but I'll try to come up with a stored procedure which deletes duplicate rows of ANY table.
All thanks for the support an stimulating me in finding a solution.
Ben