July 31, 2015 at 2:26 pm
Sean Lange (7/31/2015)
ScottPletcher (7/31/2015)
Technically "usp_" is illogical, since "sp_" stands for "special", but "usp_" wouldn't be meant as "user special".I would offer that a prefix of any kind is illogical since they don't provide any benefit. I would argue your point though. An acronym doesn't have to mean the same thing as another acronym just because they are spelled similarly.
I have seen people using the usp_ prefix stating it means "user defined stored procedure".
I agree.
And as an example, think ASP. I know what I am thinking of when I just typed it, but what could it mean?
July 31, 2015 at 2:30 pm
Lynn Pettis (7/31/2015)
Sean Lange (7/31/2015)
ScottPletcher (7/31/2015)
Technically "usp_" is illogical, since "sp_" stands for "special", but "usp_" wouldn't be meant as "user special".I would offer that a prefix of any kind is illogical since they don't provide any benefit. I would argue your point though. An acronym doesn't have to mean the same thing as another acronym just because they are spelled similarly.
I have seen people using the usp_ prefix stating it means "user defined stored procedure".
I agree.
And as an example, think ASP. I know what I am thinking of when I just typed it, but what could it mean?
A snake!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2015 at 2:31 pm
Lynn Pettis (7/31/2015)
Sean Lange (7/31/2015)
ScottPletcher (7/31/2015)
Technically "usp_" is illogical, since "sp_" stands for "special", but "usp_" wouldn't be meant as "user special".I would offer that a prefix of any kind is illogical since they don't provide any benefit. I would argue your point though. An acronym doesn't have to mean the same thing as another acronym just because they are spelled similarly.
I have seen people using the usp_ prefix stating it means "user defined stored procedure".
I agree.
And as an example, think ASP. I know what I am thinking of when I just typed it, but what could it mean?
I 100% agree on not prefixing it at all. But, if you're going to, don't use "usp_" either, since we are within the known context of SQL, and for those that are not ignorant of the actual meaning of "sp_", "usp_" is ambiguous and can easily be misunderstood.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 31, 2015 at 2:41 pm
ScottPletcher (7/31/2015)
Lynn Pettis (7/31/2015)
Sean Lange (7/31/2015)
ScottPletcher (7/31/2015)
Technically "usp_" is illogical, since "sp_" stands for "special", but "usp_" wouldn't be meant as "user special".I would offer that a prefix of any kind is illogical since they don't provide any benefit. I would argue your point though. An acronym doesn't have to mean the same thing as another acronym just because they are spelled similarly.
I have seen people using the usp_ prefix stating it means "user defined stored procedure".
I agree.
And as an example, think ASP. I know what I am thinking of when I just typed it, but what could it mean?
I 100% agree on not prefixing it at all. But, if you're going to, don't use "usp_" either, since we are within the known context of SQL, and for those that are not ignorant of the actual meaning of "sp_", "usp_" is ambiguous and can easily be misunderstood.
I don't see either as being ambiguous. Guess we'll have to agree to disagree. By the way, I would agree with not prefixing, but I also agree that one should follow company standards.
July 31, 2015 at 2:42 pm
Sean Lange (7/31/2015)
Lynn Pettis (7/31/2015)
Sean Lange (7/31/2015)
ScottPletcher (7/31/2015)
Technically "usp_" is illogical, since "sp_" stands for "special", but "usp_" wouldn't be meant as "user special".I would offer that a prefix of any kind is illogical since they don't provide any benefit. I would argue your point though. An acronym doesn't have to mean the same thing as another acronym just because they are spelled similarly.
I have seen people using the usp_ prefix stating it means "user defined stored procedure".
I agree.
And as an example, think ASP. I know what I am thinking of when I just typed it, but what could it mean?
A snake!!!
Well, that is one thing it could be, but I was actually thinking of TLAs.
July 31, 2015 at 2:48 pm
Lynn Pettis (7/31/2015)
Sean Lange (7/31/2015)
Lynn Pettis (7/31/2015)
Sean Lange (7/31/2015)
ScottPletcher (7/31/2015)
Technically "usp_" is illogical, since "sp_" stands for "special", but "usp_" wouldn't be meant as "user special".I would offer that a prefix of any kind is illogical since they don't provide any benefit. I would argue your point though. An acronym doesn't have to mean the same thing as another acronym just because they are spelled similarly.
I have seen people using the usp_ prefix stating it means "user defined stored procedure".
I agree.
And as an example, think ASP. I know what I am thinking of when I just typed it, but what could it mean?
A snake!!!
Well, that is one thing it could be, but I was actually thinking of TLAs.
Yeah just a poor attempt at humor. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2015 at 3:02 pm
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
July 31, 2015 at 3:36 pm
ben.brugman (7/31/2015)
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
Just going to say you're now doing a bunch of work because you chose not to enforce the primary key that could have been entirely avoided. And while you're right a primary key is a logical constraint it should be enforced somewhere, whether that's in the DB or in the application, but since you have users editing data directly in the database and doing so in a sloppy manner.....
July 31, 2015 at 4:10 pm
ZZartin (7/31/2015)
ben.brugman (7/31/2015)
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
Just going to say you're now doing a bunch of work because you chose not to enforce the primary key that could have been entirely avoided. And while you're right a primary key is a logical constraint it should be enforced somewhere, whether that's in the DB or in the application, but since you have users editing data directly in the database and doing so in a sloppy manner.....
Not necessarily. A pro forma primary key, such as identity, offers nothing in the way of actually preventing true data duplication. In fact, it's far easier to get duplicate data that way. That's why identity should be avoided unless it's absolutely necessary, and instead an actual data-based clustering key should be specified whenever possible.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 31, 2015 at 4:16 pm
ScottPletcher (7/31/2015)
ZZartin (7/31/2015)
ben.brugman (7/31/2015)
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
Just going to say you're now doing a bunch of work because you chose not to enforce the primary key that could have been entirely avoided. And while you're right a primary key is a logical constraint it should be enforced somewhere, whether that's in the DB or in the application, but since you have users editing data directly in the database and doing so in a sloppy manner.....
Not necessarily. A pro forma primary key, such as identity, offers nothing in the way of actually preventing true data duplication. In fact, it's far easier to get duplicate data that way. That's why identity should be avoided unless it's absolutely necessary, and instead an actual data-based clustering key should be specified whenever possible.
Works great until that primary data based key is no longer usable as a primary key due to changing system requirements. Been there, done that. Like the idea, but still like to create a SID key for a PK and code the data based key as an alternate (unique, not null) key so that if requirements change and a new data based "PK" needs to be identified we at least still have a means of identifying unique data.
And in actuality, the SID should not be exposed to the users. What they don't see won't hurt them.
July 31, 2015 at 4:52 pm
Lynn Pettis (7/31/2015)
ScottPletcher (7/31/2015)
ZZartin (7/31/2015)
ben.brugman (7/31/2015)
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
Just going to say you're now doing a bunch of work because you chose not to enforce the primary key that could have been entirely avoided. And while you're right a primary key is a logical constraint it should be enforced somewhere, whether that's in the DB or in the application, but since you have users editing data directly in the database and doing so in a sloppy manner.....
Not necessarily. A pro forma primary key, such as identity, offers nothing in the way of actually preventing true data duplication. In fact, it's far easier to get duplicate data that way. That's why identity should be avoided unless it's absolutely necessary, and instead an actual data-based clustering key should be specified whenever possible.
Works great until that primary data based key is no longer usable as a primary key due to changing system requirements. Been there, done that. Like the idea, but still like to create a SID key for a PK and code the data based key as an alternate (unique, not null) key so that if requirements change and a new data based "PK" needs to be identified we at least still have a means of identifying unique data.
And in actuality, the SID should not be exposed to the users. What they don't see won't hurt them.
The minor point is that it's additional overhead to gen and maintain an identity and its corresponding index that really isn't needed. The major point is that the vast majority of time, for many if not most people, the identity is instead made the PK and the clustering key, because it's "narrow and ever increasing". Identity has become a de facto "default" clustering key, when in fact no such thing should exist.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 31, 2015 at 5:04 pm
ScottPletcher (7/31/2015)
Lynn Pettis (7/31/2015)
ScottPletcher (7/31/2015)
ZZartin (7/31/2015)
ben.brugman (7/31/2015)
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
Just going to say you're now doing a bunch of work because you chose not to enforce the primary key that could have been entirely avoided. And while you're right a primary key is a logical constraint it should be enforced somewhere, whether that's in the DB or in the application, but since you have users editing data directly in the database and doing so in a sloppy manner.....
Not necessarily. A pro forma primary key, such as identity, offers nothing in the way of actually preventing true data duplication. In fact, it's far easier to get duplicate data that way. That's why identity should be avoided unless it's absolutely necessary, and instead an actual data-based clustering key should be specified whenever possible.
Works great until that primary data based key is no longer usable as a primary key due to changing system requirements. Been there, done that. Like the idea, but still like to create a SID key for a PK and code the data based key as an alternate (unique, not null) key so that if requirements change and a new data based "PK" needs to be identified we at least still have a means of identifying unique data.
And in actuality, the SID should not be exposed to the users. What they don't see won't hurt them.
The minor point is that it's additional overhead to gen and maintain an identity and its corresponding index that really isn't needed. The major point is that the vast majority of time, for many if not most people, the identity is instead made the PK and the clustering key, because it's "narrow and ever increasing". Identity has become a de facto "default" clustering key, when in fact no such thing should exist.
It Depends. Once again you seem to keep saying that certain things should never be done (I know, you didn't say it explicitly but we have had these conversations before). You also need to look at the environment that the system is deployed, not just the data that is being stored. There are reasons using an IDENTITY does make sense as a clustering key.
July 31, 2015 at 5:08 pm
Lynn Pettis (7/31/2015)
ScottPletcher (7/31/2015)
ZZartin (7/31/2015)
ben.brugman (7/31/2015)
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
Just going to say you're now doing a bunch of work because you chose not to enforce the primary key that could have been entirely avoided. And while you're right a primary key is a logical constraint it should be enforced somewhere, whether that's in the DB or in the application, but since you have users editing data directly in the database and doing so in a sloppy manner.....
Not necessarily. A pro forma primary key, such as identity, offers nothing in the way of actually preventing true data duplication. In fact, it's far easier to get duplicate data that way. That's why identity should be avoided unless it's absolutely necessary, and instead an actual data-based clustering key should be specified whenever possible.
Works great until that primary data based key is no longer usable as a primary key due to changing system requirements. Been there, done that. Like the idea, but still like to create a SID key for a PK and code the data based key as an alternate (unique, not null) key so that if requirements change and a new data based "PK" needs to be identified we at least still have a means of identifying unique data.
And in actuality, the SID should not be exposed to the users. What they don't see won't hurt them.
I was more responding to the sentiment that it wasn't necessary to enforce unique constraints in some way. Which whether you do it with a primary key or a unique constraint is intended to prevent the situation that led to this thread as the OP clearly does care about duplicate getting in.
August 3, 2015 at 1:42 am
Enforcing constriants often prevents actions. Most of the time it prevents 'illegal' actions. But sometimes it prevents actions, which are functional.
In general I am in favor to enforce constraints in the database whenever feasable.
Ben
August 3, 2015 at 1:50 am
This weekend I have been away from the keyboard. And as often the best idea's occure when away from the keyboard. E.g. on my bike (pushbike) or under the shower. This weekend I came up with the following:
select * into #D from D1
Delete D1
insert into D1 select distinct * from #D
This works for most tables.
But does not work for tables which have an identity and not for tables with the larger datatypes like XML.
Maybe I should take more showers.;-)
Thanks all for you time and attention.
Ben
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply