Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2166
  • Last Modified:

Identity Type and Varchar vs. nVarchar

hi, I'd like to know why many experts are avoiding to use same starting point values for Primary Key columns in different tables, i.e. if you have [table1] with Primary Key starting off with 1 and you have another [table2] with also Primary key starting off with 1000 can't you have both columns in both tables starting off with value 1..? It really doesn't make sanse...

and another question, when should I use nvarchar instead of varchar and why..? I look at Northwind datatbase and no columns are varchar type, they are nvarchar.
0
davidlars99
Asked:
davidlars99
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
TMacTCommented:
Hi,

By using different values for starting points, you have one additional source of information when debugging. If you are troubleshooting, you can inspect the value of the PK and make sure you are retrieving values from the correct table.

... TMacT
0
 
davidlars99Author Commented:
thanks TMacT, but I don't understand what you by [additional source of information when debugging] and what about the other question
0
 
TMacTCommented:

If you are developing an application that will read/write the database, and you print out the variables, you may see a problem if, for example, you know that all "StockID"s should begin with 21000 and instead begin with 15000, which is an EmployeeID number series.

NVarChar supports unicode characters, so they require 2 bytes per character of storage, but support international multi-byte character sets.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
davidlars99Author Commented:
sorry pal, I don't get it, besides it must be pretty easy to guess where "StockID" come from and where "EmployeeID" come from

let me ask you one more question, If I don't need to store international multi-byte characters then I don't need NVarchar right..? is there any other cases when I should choose either one of them..?
0
 
mcmonapCommented:
Hi davidlars99,

>>many experts are avoiding to use same starting point values <<
Where did you get this from?  I also can see no benefit to this.  There are certain applications where it can be useful to retain a table holding an integer that represents a current Primary key value, this would make every primary key value unique in the database - although these can be difficult to implement with locking and concurrency issues.  Our document management software works in this way.

You are correct that if you are not supporting international character sets you do not need to use nVARCHAR, I generally don't and so far it hasn't caught me out yet - but you do need to be certain.
0
 
rdrunnerCommented:
Allways keep your ID fields INT... There is no real reason to use anything different. Each Table has its own sequence and an index should mean nothing to you so it really doesent matter if both indexes start at 1 or 100000. Its only the housenumber of the row you are using and its very easy to compare 2 INT values for your server.

If you have to work with more then one server you should switch your index field toa GUID which will be unique when its generated .
0
 
davidlars99Author Commented:
@ mcmonap, can you please give me an exmple on this
> although these can be difficult to implement with locking and concurrency issues

@rdrunner
you are absolutely right using GUID in multi database is the best choise, that was a problem in older DBMSs when they didn't support GUID right..?


0
 
davidlars99Author Commented:
BTW, is it a good idea to have letters instead of numbers in Primary Key, like Northwind's Customers table's CustomerID..?
0
 
mcmonapCommented:
Hi davidlars99,

An example of the issues with concurrency would be when two users wich to create a new record and both have to request a new key for their record - one is going to have to wait for the other.

For me by far the biggest advantage of using a number for the primary key is that you can use the IDENTITY property to set the number to auto-increment, no worries with populating this primary key field and no worries about duplication since it is all handled internally by SQL server.  This is article has details about primary key fields:
http://www.sql-server-performance.com/q&a5.asp
0
 
TMacTCommented:
Hi davidlars99,

I guess in attempting to answer you question, I tried to determine why you "would" use different starting values in PK columns.

I am in agreement with rdrunner and mcmonap that INT is the better choice for a PK, and that there is not a benefit to "avoiding to use same starting point values"

In 95% of systems that I have designed or worked on, the Primary Key for each table is a Identify Column of type INT. It auto increments, is very quick to retrieve and creates one of the smallest indexes. It is also and efficient variable in most programming languages.

... TMacT

0
 
cyimxtckCommented:
The one issue that you can face in using int as the primary key is that you must perform an action (DML) before you can retrieve it.

For example I am architecting a SQL Server 2008 DB/Application and you cannot say "SQL Server, give me a unique 'key' please."  SQL Server has no functions like Oracle does (Sequence  SEQ.NEXTVAL) such that you can get a unique key without performing DML.  With that in mind I used GUID for our application because I cannot use NEWSEQUENTIALID (it can only be used when insering into the database similar to INT in that it must be assigned from the table) I can use NEWID() in a procedure.

Something like:

declare x as GUID
set x = NEWID()

exec SPROC_PersonID
(
  x
, y
, z
)

exec SPROC_PersonAddress
(
  x
, y
, z
)

maybe you are using a linked server and one goes to another DB.

If you are in fact using DML 100% of the time in order to create a need for ID to be needed/created you can use the SCOPE_IDENTITY.

declare x as GUID
set x = SCOPE_IDENTITY()

exec SPROC_PersonID
(
  x
, y
, z
)

exec SPROC_PersonAddress
(
  x
, y
, z
)

Hope this helps,

B
0
 
rdrunnerCommented:
Warning about the last comment...

The scope_Identity function will return the last ID that was created by SQL Server in the current function scope. (e.g.: Not created by a trigger logging something etc...)

So this function must be called AFTER you insert a row into a table.

Take a look here:
http://msdn.microsoft.com/en-us/library/ms190315.aspx

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now