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.
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.
ASKER
thanks TMacT, but I don't understand what you by [additional source of information when debugging] and what about the other question
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.
ASKER
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..?
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..?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@ 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..?
> 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..?
ASKER
BTW, is it a good idea to have letters instead of numbers in Primary Key, like Northwind's Customers table's CustomerID..?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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