Writing the code for a stored procedure

I second this post. Not only it is not "wise" to use SSN as the primary key for a table, but it is also not good from the performance, storage, and a few other angles.

The only worse practice then using SSN as PK, is using fully formatted SSN with dashes and all. What a waste of space and processing resources!:w00t:

Pat Reiter Old Hand Points: 378 More actions · Answer 4

Pat Reiter

Old Hand

Points: 378

March 14, 2008 at 2:51 pm

#789910

Agreed about SSN PK and about formatted PN. I'd store SSN payload with dashes to reduce annoyance of formatting for output. (I think that post-formatting would hurt report performance, but will not pretend to *know*).

Bob Fazio SSChampion Points: 10794 More actions · Answer 5

It is currently against the law to use the SSN as a PK (I went through much pain to change several databases). Ok, the law states that it can't be used as an employee number or something like that, but none the less you shouldn't do it.

I also know that the format is different between US and Canada which can cause issues.

I would ensure the format is correct going in. It will make searching the data easier after. The little space we are talking about is foolish to save in my opinion vs the time that would be lost to post process the data when needed.

Now if the data is NEVER used in a report, then I might say it would be worth it to remove '-'s

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Bob Fazio (3/14/2008)

Pat Reiter (3/14/2008)

Examples use CHAR(9), but compare to string of format '###-##-####' . Are the dashes not part of the string?

The example was wrong. It should have been 10

Heh... maybe 11, huh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Bob Fazio (3/14/2008)

It is currently against the law to use the SSN as a PK (I went through much pain to change several databases). Ok, the law states that it can't be used as an employee number or something like that, but none the less you shouldn't do it.

I also know that the format is different between US and Canada which can cause issues.

I would ensure the format is correct going in. It will make searching the data easier after. The little space we are talking about is foolish to save in my opinion vs the time that would be lost to post process the data when needed.

Now if the data is NEVER used in a report, then I might say it would be worth it to remove '-'s

Think about keypunch folks... reports aren't the most important thing and you're likely not going to be allowed to print (lots of laws on this) them except on very rare occasions anyway... let the "operators" type 9 digits instead of 9 digits with two {shift -} characters. Yeah, you could add the dashes to the input for a lookup, but why bother. And, for data transfers, the dashes are frequently frowned upon so you'd have to remove them for that, as well. For encryption, the fewer characters you have, the faster. Let whatever reporting tool you have format the dashes back in... it's a pretty standard formatting option in most reporting tools and things like Excel. Don't do it in SQL on the server... do it at the client.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

veeraitm Newbie Points: 3 More actions · Answer 8

veeraitm

Newbie

Points: 3

March 15, 2008 at 3:33 am

#789977

hai iam a sqlserver beginer i dont have idea about the stored procedure plese help me to i learn stored procedure with suitable example.

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

Jeff Moden (3/15/2008)

Bob Fazio (3/14/2008)

Pat Reiter (3/14/2008)

Examples use CHAR(9), but compare to string of format '###-##-####' . Are the dashes not part of the string?

The example was wrong. It should have been 10

Heh... maybe 11, huh?

Boy was that cough medicine strong :sick:

I never thought of a computer as heavy machinery 😉

Marios Philippopoulos SSC Guru Points: 57030 More actions · Answer 10

Use "SET NOCOUNT ON" at the start of your stored procedures to eliminate unnecessary network overhead due to retrieval of extra datasets any time data is fetched from/inserted into the database server:

CREATE PROCEDURE schema.sprocName

AS

SET NOCOUNT ON;

--rest of code goes here

In all "client" code, refer to your sprocs by their full name - schema.sprocName - instead of just "sprocName". That will help reduce overhead due to unnecessary recompilation.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Bob Fazio (3/15/2008)

Boy was that cough medicine strong :sick:

I never thought of a computer as heavy machinery 😉

😀 Man, thanks for having a good sense of humor. I've done very similar things and found that I can't respond to even the simplest of technical posts without that second cup of coffee :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Adam Haines SSC-Insane Points: 23217 More actions · Answer 12

Adam Haines

SSC-Insane

Points: 23217

March 15, 2008 at 10:58 am

#790037

Man, thanks for having a good sense of humor. I've done very similar things and found that I can't respond to even the simplest of technical posts without that second cup of coffee

Been there, done that. 🙂

My blog: http://jahaines.blogspot.com