Datatype question

What is the difference between decimal(9) and decimal(9,0)?
Thanks!There is no difference, because the default scale is 0.
For more informations, see:
http://msdn.microsoft.com/library/e...des_04_82ic.asp
Razvan|||thanks!
"Razvan Socol" wrote:

> There is no difference, because the default scale is 0.
> For more informations, see:
> http://msdn.microsoft.com/library/e...des_04_82ic.asp
> Razvan
>

Datatype Question

I've got numbers stored in my table as varchar which i need to cast to a
datatype so that i can perform an arithmetic operation on.
34.2348905444367
45.08070345435
34.6546456354354353
43.6540697929
the problem is, I need them to be cast so that i can perform calculations on
them. I need the specific value back, and not something that is rounded up
or down.
If I use float I get extra numbers appearing at the end, (ie. there is a
rounding issue)
If I try to cast as decimal(12, 16)
The scale must be less than or equal to the precision.
How can i simply get the true value of the record ?
Many thanks.decimal(28, 16)
28 = total length
16 = positions to the right of the decimal point.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Douglas Adams" wrote:

> I've got numbers stored in my table as varchar which i need to cast to a
> datatype so that i can perform an arithmetic operation on.
> 34.2348905444367
> 45.08070345435
> 34.6546456354354353
> 43.6540697929
> the problem is, I need them to be cast so that i can perform calculations
on
> them. I need the specific value back, and not something that is rounded u
p
> or down.
> If I use float I get extra numbers appearing at the end, (ie. there is a
> rounding issue)
>
> If I try to cast as decimal(12, 16)
> The scale must be less than or equal to the precision.
>
> How can i simply get the true value of the record ?
> Many thanks.
>
>|||The following works for me. But don't confuse how the number is
*displayed* with the way it is stored
CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO T1 (x)
SELECT 34.2348905444367 UNION ALL
SELECT 45.08070345435 UNION ALL
SELECT 34.6546456354354353 UNION ALL
SELECT 43.6540697929 ;
SELECT CAST(x AS DECIMAL(20,16)) FROM T1
Result:
34.2348905444367000
34.6546456354354353
43.6540697929000000
45.0807034543500000
(4 row(s) affected)
David Portas
SQL Server MVP
--|||Thanks guys
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121852998.202064.66570@.g43g2000cwa.googlegroups.com...
> The following works for me. But don't confuse how the number is
> *displayed* with the way it is stored
> CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
> INSERT INTO T1 (x)
> SELECT 34.2348905444367 UNION ALL
> SELECT 45.08070345435 UNION ALL
> SELECT 34.6546456354354353 UNION ALL
> SELECT 43.6540697929 ;
> SELECT CAST(x AS DECIMAL(20,16)) FROM T1
> Result:
> --
> 34.2348905444367000
> 34.6546456354354353
> 43.6540697929000000
> 45.0807034543500000
> (4 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
>

Datatype problem?

Hello
We ahve a problem with datatypes. I the cube whe have 2 mesasures (Item_Sold and Sales). Item_sold is Numeric datatype and Sales is Currency. In the virtual cube whe divide the to in to a average price (Sales/Item_sold).
We user Excel show the cube.
For integer numbers we get no problem but for item_sold with decimal values (like 1,43) the division get currupt. It seems that it only devide the integer so average measures of:
Item_sold=2,5
Sales=500
...will be 250 (500/2). And if the item_sold is smaller than 1 (for units smaller than 1 kilo) the average value will even more strange.

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

What is happening is that when something is being divided by 0.XX its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

sql

Datatype problem?

Hello
We ahve a problem with datatypes. I the cube whe have 2 mesasures (Item_Sold and Sales). Item_sold is Numeric datatype and Sales is Currency. In the virtual cube whe divide the to in to a average price (Sales/Item_sold).
We user Excel show the cube.
For integer numbers we get no problem but for item_sold with decimal values (like 1,43) the division get currupt. It seems that it only devide the integer so average measures of:
Item_sold=2,5
Sales=500
...will be 250 (500/2). And if the item_sold is smaller than 1 (for units smaller than 1 kilo) the average value will even more strange.

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

What is happening is that when something is being divided by 0.XX its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

Datatype problem?

Hello
We ahve a problem with datatypes. I the cube whe have 2 mesasures (Item_Sold and Sales). Item_sold is Numeric datatype and Sales is Currency. In the virtual cube whe divide the to in to a average price (Sales/Item_sold).
We user Excel show the cube.
For integer numbers we get no problem but for item_sold with decimal values (like 1,43) the division get currupt. It seems that it only devide the integer so average measures of:
Item_sold=2,5
Sales=500
...will be 250 (500/2). And if the item_sold is smaller than 1 (for units smaller than 1 kilo) the average value will even more strange.

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

What is happening is that when something is being divided by 0.XX its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

Datatype problem: development vs production servers

This is driving me nuts: On my development machine the code runs finebut generates an error on the production server. Both are running SQLServer 2000 and ASP.NET 1.1

The datatype of the field in question isdatetime.
The webform has a calendar for a user to select and automaticallyinsert the date into the textbox. The update command in the webform is:
cmdInsert.Parameters.Add("@.citation_date", CDate(txtDate.Text))

This works without a hitch on my development system, but on the production server it generates the following error:
Cast from string "19-12-1997" to type 'Date' is not valid.

WHY?
It has to do with the locale information (country, language, etc.) for the computer. Check to make sure the server is set to whatever you're using on your local development PC. I'm not familiar with setting/changing these since I only use U.S. format and English.|||

jcasp wrote:

It has to do with the locale information (country,language, etc.) for the computer. Check to make sure the serveris set to whatever you're using on your local development PC. I'mnot familiar with setting/changing these since I only use U.S. formatand English.

You are right. I'm inputting U.S format of date for the time being until I've figured a way around it. Thanks!|||Use YYYY-MM-DD format, then it doesn't matter what culture you are in.

DATATYPE PROBLEM(cross)

I have a column in my table BizdekiFiyat . The datatype = float length =8
(to save money values).. It is impossible to change these attributes for
some reasons.
It has records like This
BizdekiFiyat
110
24
29.5
31.35
I use Vb.Net . I use ExecuteReader To select values from my db..
After first attemp
Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
IT returns
110
24
295
3135
Dim BizdekiFiyat As String
It returns right results.
110
24
29.5
31.35
There is a problem with decimal records when i want to evaluate this
values..
For example
Dim BizdekiFiyat As String
BizdekiFiyat = BizdekiFiyat * 1.05
It is supposed to be
29.5 * 1.05 =30.975
31.35*1.05=32.9175
but it returns
309,75
3291,75
How can i solve this problem ?"Savas Ates" <in da club> wrote in message
news:OEhAfpwLGHA.648@.TK2MSFTNGP14.phx.gbl...
>I have a column in my table BizdekiFiyat . The datatype = float length =8
>(to save money values).. It is impossible to change these attributes for
>some reasons.
>
> Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
> IT returns
> 110
> 24
> 295
> 3135
Integer datatype will always truncate your decimal fraction values.
I've had data dimension problems trying to use the Decimal datatype for
holding (SQL) decimal data returned through parameters using MS's EntLib
DAAB. I resolved this by using .NET's Double datatype (though I'd prefer to
know why .NET's decimal gave me the problem in the first place).

> Dim BizdekiFiyat As String
> It returns right results.
Because the value is being represented and stored as a string (just like
typing into a textbox), not a numeric type, so...

> There is a problem with decimal records when i want to evaluate this
> values..
> For example
> Dim BizdekiFiyat As String
> BizdekiFiyat = BizdekiFiyat * 1.05
> How can i solve this problem ?
You're expecting .NET to intelligently convert your datatypes for you, which
it is valiantly trying to do. You should consider setting Option Strict on
(Tools | Options | Projects | VB Defaults) to prevent loose data typing and
late binding. You should strongly type your datatypes as a matter of god
practice. When you need to convert datatypes, dothis explicitely using
CType(sourceObj, targetType), or the shorthand versions such as Cint(value),
CDbl(value), etc.
As for your calculations: e.g. using Double to store your values, create a
function which you'll call when necessary to do your calculations:
private function MultiplyBizdekiFiyat(Byval origValue as double, Byval
MultiplyBy as double) As Double
'Perform the calculation
MultiplyBizdekiFiyat = origValue * MultiplyBy
'Return the value to the calling method.
return MultiplyBizdekiFiyat
end function
Hope that helps
Al

datatype problem

how can i transfer incoming data from flat file which would be a string to my sql table of column int...

i have a problem with datatype can i conver string to int how should i do it...new to it

please help!!

This is what the Data Conversion Task is for. U could also use the Derived Column Task. Place it between source and destination, convert your column there.

What more can I say?

Pipo1

|||Use a derived column transformation.

This is one example of an expression you could use: (DT_I4)[Your_Column]sql

DataType Problem

Hi,

I want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?

Thanks in Advance.

You can use byte array (byte[]) for BLOB data type (text/ntext/image) in SQL Server:)

datatype problem

Hi Everyone,

In C# file:

bool abc; (it is working)

But in sql file: (bool is not working. What datatype should use for bool in the sql file?)

CREATE TABLEsb_payment_history (

sb_dispensing bool NULL <================ not working because bool is not known.

)

GO

Thanks,

May

You should use the bit datatype in your SQL table.

Datatype Problem

Hello
I am new to SQL Server and have migrated an Access DB to SQL Server
I can connect to the database, pull content, etc but a strange thing happen
when I use the "text" datatype. I have a table called "tbl_news
where a field is called "news_story" this is a large text block that my clien
can cut and paste text from MS word. Since it needs to hold large amounts ot tex
I used the MEMO field-type in Access. When I used the TEXT field in SQL serve
some weird stuff happens. When I query the record in a recordset and display
the results on a standard ASP page - one or another fields will not print out
No matter what I try - it seems that one field won't display and there is no rhyme or reason.
When I look in the DB I see the DATA but it wont print on the screen like response.write rs("news_story"
What would cause a field not to print? somewhat hap-hazardly
SHould I use the text datatype? For ex. when I use vchar it doesnt happen. But my tex
gets truncated. Please Help - I am ready to pull my hair ouTo read from TEXT column you should use READTEXT statement,
but you can try to play with SET TEXTSIZE.
Please read "Retrieving ntext, text, or image Values" topics in BOL.
HTH
Igor Raytsin
"Padpros" <anonymous@.discussions.microsoft.com> wrote in message
news:892D6AA1-FCF8-4051-9586-B0FFAA1ED99D@.microsoft.com...
> Hello,
> I am new to SQL Server and have migrated an Access DB to SQL Server.
> I can connect to the database, pull content, etc but a strange thing
happens
> when I use the "text" datatype. I have a table called "tbl_news"
> where a field is called "news_story" this is a large text block that my
client
> can cut and paste text from MS word. Since it needs to hold large amounts
ot text
> I used the MEMO field-type in Access. When I used the TEXT field in SQL
server
> some weird stuff happens. When I query the record in a recordset and
display
> the results on a standard ASP page - one or another fields will not print
out.
> No matter what I try - it seems that one field won't display and there is
no rhyme or reason.
> When I look in the DB I see the DATA but it wont print on the screen like
response.write rs("news_story")
> What would cause a field not to print? somewhat hap-hazardly?
> SHould I use the text datatype? For ex. when I use vchar it doesnt happen.
But my text
> gets truncated. Please Help - I am ready to pull my hair out
>

Datatype Problem

Hello,
I am new to SQL Server and have migrated an Access DB to SQL Server.
I can connect to the database, pull content, etc but a strange thing happens
when I use the "text" datatype. I have a table called "tbl_news"
where a field is called "news_story" this is a large text block that my clie
nt
can cut and paste text from MS word. Since it needs to hold large amounts ot
text
I used the MEMO field-type in Access. When I used the TEXT field in SQL serv
er
some weird stuff happens. When I query the record in a recordset and display
the results on a standard ASP page - one or another fields will not print ou
t.
No matter what I try - it seems that one field won't display and there is n
o rhyme or reason.
When I look in the DB I see the DATA but it wont print on the screen like re
sponse.write rs("news_story")
What would cause a field not to print? somewhat hap-hazardly?
SHould I use the text datatype? For ex. when I use vchar it doesnt happen. B
ut my text
gets truncated. Please Help - I am ready to pull my hair outTo read from TEXT column you should use READTEXT statement,
but you can try to play with SET TEXTSIZE.
Please read "Retrieving ntext, text, or image Values" topics in BOL.
HTH
Igor Raytsin
"Padpros" <anonymous@.discussions.microsoft.com> wrote in message
news:892D6AA1-FCF8-4051-9586-B0FFAA1ED99D@.microsoft.com...
> Hello,
> I am new to SQL Server and have migrated an Access DB to SQL Server.
> I can connect to the database, pull content, etc but a strange thing
happens
> when I use the "text" datatype. I have a table called "tbl_news"
> where a field is called "news_story" this is a large text block that my
client
> can cut and paste text from MS word. Since it needs to hold large amounts
ot text
> I used the MEMO field-type in Access. When I used the TEXT field in SQL
server
> some weird stuff happens. When I query the record in a recordset and
display
> the results on a standard ASP page - one or another fields will not print
out.
> No matter what I try - it seems that one field won't display and there is
no rhyme or reason.
> When I look in the DB I see the DATA but it wont print on the screen like
response.write rs("news_story")
> What would cause a field not to print? somewhat hap-hazardly?
> SHould I use the text datatype? For ex. when I use vchar it doesnt happen.
But my text
> gets truncated. Please Help - I am ready to pull my hair out
>

datatype performance

Hi, I'm a webmaster of http://www.jivejewelry.com. Somehow the website seems slows. The developer told me that a datatype in the database design could be causing the problem. I don't believe it that is possible. Is this actually possible? Please help.

You need to be more specific. Could a database datatype cause a problem? Sure, if you store all integers as strings, that could slow thngs down. That would not be the most likely scenario.|||

Any suggestion on how I can find the specific cause?

|||

Find a page that is slow. Debug the page, using F10 to stp over statements until you find one or more that are really slow. Step into that until you find what is slowing things down.

Alternately, sprinkle code with Trace statements and turn on tracing to determine what is taking time.

|||Easiest way to see if it's the database: Turn on SQL ServerProfiler. Default options should be fine. Connect it to theproduction server and let it run for a while. Now look at querieswith high values in the Duration column. Finally, go fix them.
Note, running Profiler against a production databasecan causeperformance degredation if your server is especially hammered. Ifyou're not maxing out the server though, this probably will not be anissue.
|||A good example of Datatype for an online store is MONEY and NUMERIC, money may give you rounding problems, numeric will not but it is bigger than money. Hope this helps.|||

Ok. I found the query that got high duration. Reading the execution plan for the query, it does not makes sense. I don't know what to do. Or, how to fix it.

|||Run it in the Query Analyzer and click on execution plan which will show what you need to cut out of the query. You could also post it so someone can run it and fix it for you. Hope this helps,sql

Datatype of column dynamically

Hi All,
How can I get the datatype of a column using a query?
Thanks,
SanjeevUse view information_schema.columns.
Example:
use northwind
go
select
data_type
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 'orders'
and column_name = 'orderid';
AMB
"mahajan.sanjeev@.gmail.com" wrote:

> Hi All,
> How can I get the datatype of a column using a query?
> Thanks,
> Sanjeev
>|||Thanks!
I was going to use a join on sysobjects, syscolumns and systypes to get
it but this looks better!

Datatype nvarchar(max) not accessible

Hi,

trying to input

create table T (c1 nvarchar(max));

in MS SQL Server Manangement Studio Express results in an error :

Fehler beim Analysieren der Abfrage. [ Token line number = 1,Token line offset = 30,Token in error = max ]

create table T (c1 nvarchar(4000));

is processed w/o errors.

I′ve installed SQL Server 2005 Express Ed. SP2

Microsoft SQL Server Management Studio Express 9.00.3042.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML 2.6 3.0 4.0 5.0 6.0

Microsoft .NET Framework 2.0.50727.42

Betriebssystem 5.1.2600

Any ideas?

Thanks in advance

Werner

This command should work without problems, and did on my computer. Could you check the error log to see if there is more informaiton about the failure there?

Mike

|||

Hi,

thanks for your reply. I think I found the reason.

Being a newcomer I accidently selected "SQL Server Everywhere" and generated a compact sdf-DB. This type of server doesn′t support nvarchar(max) (which is not mentioned in any documentation available in the net).

Setting up a new db (conversion of the sdf to mdf format is not possible (?)) fixed the problem.

btw, I can imagine using large datafields even on a PDA, so what′s the reason for not supporting this datatype (I do see a parallel to those guys of TI in the late 70s who invented the datetime format for PCs, saving 2 byte...)

Thanks anyway for your fast reaction and your help offered

Werner

DataType Money

Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00

Thanx

That kind of formatting is best done at the application level.|||

You could use the ToString("c") to display as currency on your page.

Double TotalCost = 786.0000;

lblCost.Text = TotalCost.ToString("c");

Your label should now be set to $786.00

|||

If you are binding it, you can use <%# Bind("YourColumnName","{0:c}") %> and will display $786.00
If you dont want to display the currency, repace c with your own format like #0.00 at it should display 786.00

Datatype mismatch in export

I am exporting my sql table to the access database. My text fileds in SQL are stored as varchar(50) in SQL server 2005. but when I export the table to Access, These fileds get converted to 'memo' fields. I tried converting these memo fields in access to text field but Access does do that, throws an error message saying not enough disk space.

When I try to convert these 'varchar' fields in sql to text fileds, sql throws a 'time out ' message nad does not convert it to text.

What do I do?

Thanks

As per the guidelines of Microsoft, the equivalent of 'VARCHAR' in Sql server is 'TEXT' in MS-Access. How are you exporting Sql server data to Access? Are you using SSIS? On a side note, equivalent of 'MEMO' is 'TEXT' in Sql Server. I wouldn't recommend you to change the data type in Sql Server to change to 'TEXT'.

Text data type in Sql Server is used to store large texts that go beyond 8KB in size. Usually, sql server keeps a 16 byte pointer to the text data in the page. It can contain upto 2 GB size data. In Sql Server 2005, usage of Text data type is not recommended and varchar(max) is recommended.

http://articles.techrepublic.com.com/5100-6345-5033381.html
https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0561.mspx?mfr=true

datatype map - Native ODBC driver

Hi,

Our software uses native ODBC driver and Visual C++ 2003 to communicate with SQL Server 2005.

SELECT xml_schema_namespace(SCHEMA_NAME(schema_id) ,name) FROM sys.xml_schema_collections.

SQLDescribeColW returns -152 as the datatype for the column in the above select statement. However, I’m unable to find a preprocessor definition for -152. The datatype looks like an nvarchar. Is it an nvarchar? Where can I find the definition for this datatype?

I have discovered similar problems while bringing back SQL_VARIANT types. The returned subtype of a SQLVariant comes up with -16 and -25. I cannot find definitions for these sub types either.

Any help is appreciated.

Regards,

Venkat

-152 is the XML data type and is defined in SQLNCLI.H as SQL_SS_XML. SQLNCLI.H is the header file for the SQL Native Client ODBC driver and OLEDB provider for SQL Server 2005.

I would advice you use the SQL Native Client ODBC driver for the best performance and functionality with SQL Server 2005. You can download the redistributable from http://www.microsoft.com/downloads/details.aspx?FamilyId=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&DisplayLang=en

Regards,

Uwa.

|||

Thank you very much!

Do you know where i can find information about SQL variant sub types -16 & -25? Subtype -16 looks like an integer (4bytes) while subtype -25 looks like a bigint (8Bytes)

Regards,

|||

-16 = "SQL_INTEGER + SQL_SIGNED_OFFSET" ( = 4 + (-20) )

-25 = "SQL_BIGINT + SQL_SIGNED_OFFSET" ( = -5 + (-20) )

So, basically they're SIGNED LONG and SIGNED BIGINT respectively, which explains their sizes of 4bytes and 8bytes.

You can find these defined in the header file SQLEXT.H:

#define SQL_BIGINT (-5)
...

#define SQL_SIGNED_OFFSET (-20)
#define SQL_UNSIGNED_OFFSET (-22)

....

#define SQL_C_SBIGINT (SQL_BIGINT+SQL_SIGNED_OFFSET) /* SIGNED BIGINT */
...

#define SQL_C_SLONG (SQL_C_LONG+SQL_SIGNED_OFFSET) /* SIGNED INTEGER */

As Uwa mentioned above, you'll need to search SQLNCLI.H for the SQL Server 2005 types. Here you can find (-152) defined:

#define SQL_SS_VARIANT (-150)
#define SQL_SS_UDT (-151)
#define SQL_SS_XML (-152)

|||Thanks a lot!sql

DataType Issue

Hi

Using SQLserver 2000 I have to enter a huge data (nearly 8000 charcters) into a field, which data type is suitable for that and what is the method to store data.

You might need to use a VARCHAR(8000) or a text datatype; however, there is not enough information.

Is your data divisible into different definite pieces? Do you need to support unicode? Is your data numeric? binary? Is your data XML Is there additional data that needs to be stored with the 8000 characters?

Datatype for Primary key fields ...

Hi,
As far as my understanding goes, normally PK would be set on fields whose
datatype is INT. But in one of the project I saw 99% of the tables they have
used Varchar datatype for PK fields.
This internally means that it would string comparisons. I was arguing that
SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
something? Any pointers on this topic would be of great help to me.
Best Regards
Vadivel
http://vadivel.blogspot.comVadivel wrote:

> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they ha
ve
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
This is the wrong question because the datatype for keys is determined
by the data you need to model in the table. For example how are you
going to represent names using an INTEGER?
If you meant to ask "what should I use for an artiificial surrogate
key?" then you can search the archives of this group for many previous
discussions on that topic.
David Portas
SQL Server MVP
--|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
If you don't use surrogates, I'd say that most natural primary keys are
strings.
Even telephone numbers, license plate numbers, zip codes, serial codes,
credit card numbers, bar codes, invoice numbers, bank account numbers... are
in reality, strings.|||Are you speaking of a natural or surrogate primary key?
http://www.aspfaq.com/show.asp?id=2504
Natural keys can be a combination of most any data type. However, if the key
in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) then
do try to define it as integer, becuase it's storage will be smaller than
character, and it will thus result in less memory usage and fewer index
pages to traverse.
If you plan to use an identity column as a surrogate primary key, then I
don't see a reason to use anything but an integer.
http://www.windowsitpro.com/Article...ArticleID=23449
Data Type Performance Tuning Tips for Microsoft SQL Server
http://www.sql-server-performance.com/datatypes.asp
In general integer based comparisons are more efficient than Char or VarChar
comparisons, but I don't know of SQL Server specifically not being not good
at character comparisons relative to any other DBMS system such as Oracle or
DB2.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com|||I know that telephone numbers, Credit card nos, Zipcode all would be varchar
fields only. As we won't be doing any mathematical calculation based on that
data there isn't a need for us to go for INT datatype. Even though u would
have those fields as Varchar in ur DB won't you have a ID field in that
table? Won't that ID field be of INT datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
"Raymond D'Anjou" wrote:

> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> If you don't use surrogates, I'd say that most natural primary keys are
> strings.
> Even telephone numbers, license plate numbers, zip codes, serial codes,
> credit card numbers, bar codes, invoice numbers, bank account numbers... a
re
> in reality, strings.
>
>|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
>I know that telephone numbers, Credit card nos, Zipcode all would be
>varchar
> fields only. As we won't be doing any mathematical calculation based on
> that
> data there isn't a need for us to go for INT datatype. Even though u would
> have those fields as Varchar in ur DB won't you have a ID field in that
> table? Won't that ID field be of INT datatype?
> Best Regards
> Vadivel
Read JTs response including the links.
Your ID field (column) is a surrogate.
The use of surrogates (including Identity) has been discussed in this
newsgroup "ad nauseum".
Even though you can use surrogates as keys, there should always exist a
"natural" primary key in your tables.|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u8lYzCMAGHA.3936@.TK2MSFTNGP12.phx.gbl...
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
> Read JTs response including the links.
> Your ID field (column) is a surrogate.
> The use of surrogates (including Identity) has been discussed in this
> newsgroup "ad nauseum".
> Even though you can use surrogates as keys, there should always exist a
> "natural" primary key in your tables.
One other thing:
Concerning your "performance hit" using INTs versus VARCHARs.
From what I've read, especially in today's databases, if there is one it's
negligable.|||Thanks for the links JT.
Lets assume that I have a table with just two fields,
CityID Varchar(20) -- PK
CityName Varchar(50)
Now I would be using this field CityID in some other table as a FK field. Is
that ok? Or is it advisable / mandatory to have another column with INT
datatype and use it in other tbls as FK?
Best Regards
Vadivel
http://vadivel.blogspot.com
"JT" wrote:

> Are you speaking of a natural or surrogate primary key?
> http://www.aspfaq.com/show.asp?id=2504
> Natural keys can be a combination of most any data type. However, if the k
ey
> in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) th
en
> do try to define it as integer, becuase it's storage will be smaller than
> character, and it will thus result in less memory usage and fewer index
> pages to traverse.
> If you plan to use an identity column as a surrogate primary key, then I
> don't see a reason to use anything but an integer.
> http://www.windowsitpro.com/Article...ArticleID=23449
> Data Type Performance Tuning Tips for Microsoft SQL Server
> http://www.sql-server-performance.com/datatypes.asp
> In general integer based comparisons are more efficient than Char or VarCh
ar
> comparisons, but I don't know of SQL Server specifically not being not goo
d
> at character comparisons relative to any other DBMS system such as Oracle
or
> DB2.
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
>
>|||If CityID is the primary key, then that is the column you want to use as the
foreign key when joining referencing tables. However, I don't understand why
CityID would be 20 characters long. Is this something like an ISO code
assigned to every city on the planet? If CityID contains embedded attributes
like geographic coordinates or nation, state, county codes, then split those
attributes out as seperate columns.
Provide more details about what CityID means and how it's values are
assigned.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:67DAA04A-A277-49AB-8745-521699D2F06F@.microsoft.com...
> Thanks for the links JT.
> Lets assume that I have a table with just two fields,
> CityID Varchar(20) -- PK
> CityName Varchar(50)
> Now I would be using this field CityID in some other table as a FK field.
> Is
> that ok? Or is it advisable / mandatory to have another column with INT
> datatype and use it in other tbls as FK?
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "JT" wrote:
>