• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2158
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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