Solved

Identity Type and Varchar vs. nVarchar

Posted on 2004-10-20
12
2,148 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

860 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