I trust you'll bear with an SQL Server newbie with what may seem a rather inane request. I am designing a web app in Web Designer 2005 Express with SQL Server Express. Unfortunately, I'm finding a little confusing with some of the data types when designing tables. I have tried to find information on the various Microsoft sites (general site, MSDN, here) and while I found one document that had a table comparing data types in different implementations of SQL, it wasn't at all helpful. Most of my confusion is with the various string and char types; the numeric types seem pretty straight forward for the most part. However, it might be helpful to know the difference between money and smallmoney/datetime and smalldatetime, particularly space/size information and formatting options (unless the latter is up to the interface). It would also be helpful to know which string/char types correspond to any counterparts they might have in, for instance, Access (with which I am already quite exprienced). Or any particular quirks or idiosyncracies they might have. I don't expect anyone to write a full tutorial, but if someone could point me in the direction of a good online doc, it would be most appreciated. You might well ask, why not use Access databases? I would answer...I like to learn new stuff!
Thanks much.
Hi Eyetech... I agree, there seem to be lots of data types to chose from. Sorry, I cant do a case study on them all for you as I too am a relative newbie... I can share one experience though... I found a key difference between the nchar and vchar types. It seems the nchar forces the lenth of the data stored to be whatever length the field is declared as - padding with spaces when needed. I found all those padding spaces to be a real pain in my code... I've since moved away from nchar in favor of vchar. I suppose there may be some performance issues there, but so far the code's a lot more friendly. -- Curt
|||
curtisdehaven:
I found a key difference between the nchar and vchar types. It seems the nchar forces the lenth of the data stored to be whatever length the field is declared as - padding with spaces when needed. I found all those padding spaces to be a real pain in my code... I've since moved away from nchar in favor of vchar.
Thanks Curtis. Since my first post, I did some Googleing and found some very helpful information on a third-party website. It includes a table with the actual numeric ranges for numeric types (eg: int: integer data from -2^31 through 2^31-1 stored in 4 bytes). It also explains something that may be the cause of your woes in using nchar type. nchar is actually for storing unicode char data which uses 2 bytes instead of 1 for each character. char supports the 1-byte ASCII characters padded to fixed lengths while varchar is the variable length type. It is recommended that if you don't actually have to store unicode, avoid using the 'n' types and stick to char/varchar. It will certainly take up less space in your database in the long run since nchar and nvarchar will always take up double the space of their char/varchar counterparts.
Being new here, I'm not sure if it is appropriate to post links to outside sources. If someone can confirm that it is ok, I'll gladly add the link for others to refer to.
|||Hi eyetech,
Yes, you can post links to outside resource. Thank you for sharing your knowlege with all the people here.
|||
The .NET Char is the nineth integer and Unicode by default so when you are using database Char/Varchar you are using bytes until 255, the reason Membership data types are Nvarchar instead of Varchars. The SQL Server team have prepared a comprehensive chart of the three type your application uses SQL Server, ADO.NET and FCL(framework class library). Hope this helps
http://msdn2.microsoft.com/en-us/library/ms131092.aspx
SQL Server data type
CLR data type (SQL Server)
CLR data type (.NET Framework)
varbinary
SqlBytes, SqlBinary
Byte[]
binary
SqlBytes, SqlBinary
Byte[]
varbinary(1), binary(1)
SqlBytes, SqlBinary
byte, Byte[]
image
None
None
varchar
None
None
char
None
None
nvarchar(1), nchar(1)
SqlChars, SqlString
Char, String, Char[]
nvarchar
SqlChars, SqlString
SQLChars is a better match for data transfer and access, andSQLString is a better match for performing String operations.
String, Char[]
nchar
SqlChars, SqlString
String, Char[]
text
None
None
ntext
None
None
uniqueidentifier
SqlGuid
Guid
rowversion
None
Byte[]
bit
SqlBoolean
Boolean
tinyint
SqlByte
Byte
smallint
SqlInt16
Int16
int
SqlInt32
Int32
bigint
SqlInt64
Int64
smallmoney
SqlMoney
Decimal
money
SqlMoney
Decimal
numeric
SqlDecimal
Decimal
decimal
SqlDecimal
Decimal
real
SqlSingle
Single
float
SqlDouble
Double
smalldatetime
SqlDateTime
DateTime
datetime
SqlDateTime
DateTime
sql_variant
None
Object
User-defined type(UDT)
None
Same class that is bound to the user-defined type in the same assembly or a dependent assembly.
table
None
None
cursor
None
None
timestamp
None
None
xml
SqlXml
None
