Solved

Identity Type and Varchar vs. nVarchar

Posted on 2004-10-20
12
2,142 Views
Last Modified: 2013-12-03
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
Comment
Question by:davidlars99
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 5

Expert Comment

by:TMacT
ID: 12358406
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
 
LVL 13

Author Comment

by:davidlars99
ID: 12358512
thanks TMacT, but I don't understand what you by [additional source of information when debugging] and what about the other question
0
 
LVL 5

Expert Comment

by:TMacT
ID: 12358798

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
 
LVL 13

Author Comment

by:davidlars99
ID: 12359435
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
 
LVL 15

Accepted Solution

by:
mcmonap earned 200 total points
ID: 12359671
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
 
LVL 11

Assisted Solution

by:rdrunner
rdrunner earned 150 total points
ID: 12359708
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Author Comment

by:davidlars99
ID: 12359780
@ 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
 
LVL 13

Author Comment

by:davidlars99
ID: 12359831
BTW, is it a good idea to have letters instead of numbers in Primary Key, like Northwind's Customers table's CustomerID..?
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12361652
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
 
LVL 5

Assisted Solution

by:TMacT
TMacT earned 150 total points
ID: 12361907
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
 

Expert Comment

by:cyimxtck
ID: 26131890
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
 
LVL 11

Expert Comment

by:rdrunner
ID: 26157935
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 42
Link SQL table to Webpage 9 38
Distinct values from two tables 14 18
Split Data in 1 column into 2 columns 8 15
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now