| Author |
 Topic Search  Topic Options
|
Misty 
Senior Member
Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Data Types in SQL Server
Posted: 01 October 2004 at 1:28am |
What kind of data type should I use in a SQL Server database for the following:
1.) Yes/No field
2.) Memo field
|
 |
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 01 October 2004 at 2:51am |
|
A boolean (Yes/No or True/False) can be a bit field. Then when you get
the field with your asp.net page you need to use CBool() e.g.
Dim MyBoolean As Boolean = CBool(ds.Tables(0).Rows(0).Item("MyBoolean"))
because bits are stored as 1's and 0's.
For a memo field you can either use a varchar with a high length (that
way you can compare and search the contents of the field) or you could
use a Text field which has some massive length (but you can't compare
or search the field)... It depends on what you want to do really and
how big you want the field to be.
|
|
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 01 October 2004 at 2:57am |
|
For Yes/No and for all boolean types I use the bit type which will store (1/0) and can be checked against True/False.
Memo, use text type, then give it a lenght as needed, 8 for long memos and give it 16 for very long memos.
|
|
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 01 October 2004 at 9:32am |
|
DOn't know where you heard that. Text can hold 2{30}-1 characters, there is only one text type. The data size is always 16 and represents just a pointer to the external (not stored in the table) 8KB pages of text data.
|
|
|
|
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 01 October 2004 at 4:04pm |
|
You are right, text always looked wierd to me now I know why, funny I used text with sizes <> 16, maybe some old SQL server version, not sure, I also prefer varchar's, more easily digestable.
So what would be the perfect scenario to use text types? Thx.
|
|
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 October 2004 at 12:47pm |
|
text are generally blob fields. varchar is limited to 8000 charachters so whenever you need to store more, you'll go with text.
|
|
|
|
Misty
Senior Member
Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2004 at 7:18pm |
|
I am trying to figure out something about the datatype, bit. Is 0 actually equal to true? Is 1 actually equal to false?
|
|
Gullanian
Senior Member
Joined: 04 January 2002
Location: England
Status: Offline
Points: 4373
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2004 at 7:57pm |
|
I think it's the other way round. Test it in your application is probably the best way to find out.
Heres a good article on data types in SQL Server:
http://www.sql-server-performance.com/datatypes.asp
Varchar is a good one to use if you are under 8,000 characters as it
only takes up the space it uses (it doesn't take 8,000 character spaces
up for each record if you only store 3 characters)
|
|
