SQL datatypes

[Supragal]

I have no clue about this stuff. The datatype 'text'... is that limited to a specific number of charactors or anything?

 

I've googled it but it keeps going on abouts blobs and DB stuff

 

[Homer]

It's just a counter that goes up by 1 every second. It started at zero on 01/01/1970 and has been counting ever since.

 

You can convert here: http://www.onlineconversion.com/unix_time.htm

[mawby]

For mySql anyway...

 

CHAR( ) A fixed section from 0 to 255 characters long.

VARCHAR( ) A variable section from 0 to 255 characters long.

TINYTEXT A string with a maximum length of 255 characters.

TEXT A string with a maximum length of 65535 characters.

BLOB A string with a maximum length of 65535 characters.

MEDIUMTEXT A string with a maximum length of 16777215 characters.

MEDIUMBLOB A string with a maximum length of 16777215 characters.

LONGTEXT A string with a maximum length of 4294967295 characters.

LONGBLOB A string with a maximum length of 4294967295 characters.

 

CHAR and VARCHAR are the most widely used types. CHAR is a fixed length string and is mainly used when the data is not going to vary much in it's length. VARCHAR is a variable length string and is mainly used when the data may vary in length.

 

CHAR may be faster for the database to process considering the fields stay the same length down the column. VARCHAR may be a bit slower as it calculates each field down the column, but it saves on memory space. Which one to ultimatly use is up to you.

 

Using both a CHAR and VARCHAR option in the same table, MySQL will automatically change the CHAR into VARCHAR for compatability reasons.

 

BLOB stands for Binary Large OBject. Both TEXT and BLOB are variable length types that store large amounts of data. They are similar to a larger version of VARCHAR. These types can store a large piece of data information, but they are also processed much slower.

[Jake]

blobs!

 

Can you DESC the table? That'll tell you what that text col is limited to. (If that's what you're asking?)

[Jake]

It's just a counter that goes up by 1 every second. It started at zero on 01/01/1970 and has been counting ever since.

 

You can convert here: http://www.onlineconversion.com/unix_time.htm

I'm confused. What's that to do with what she asked? Am I being thick?

[mawby]

He may have read the question as SQL datetypes rather than datatypes.

[Supragal]

blobs!

 

Can you DESC the table? That'll tell you what that text col is limited to. (If that's what you're asking?)

 

I don't know what that means

 

But that sounds like what I am trying to find out.....

 

Homer - you scared me for a moment then

[Snooze]

In most respects, a TEXT field is just a VARCHAR with an unlimited(ish) length, I believe.

 

it can't actulaly be unlimited though, right?

[mawby]

I don't know what that means

 

But that sounds like what I am trying to find out.....

Just login to the db and type DESC {tablename};

 

EDIT - Replace {tablename} with the name of the table.

[Pete]

In most respects, a TEXT field is just a VARCHAR with an unlimited(ish) length, I believe.

Depends what system/language you're using...but it's usually fairly limited. That's what Memo and Blobs are for.

[mawby]

EDIT - Replace {tablename} with the name of the table.

[Supragal]

How do I know if it is a BLOB, will it actually say somewhere?

[Jake]

I don't know what that means

 

But that sounds like what I am trying to find out.....

 

Homer - you scared me for a moment then

Is it Oracle SQL or what?

 

At the SQL> prompt just type

 

DESC RosiesTableName

and it'll tell you what datatype the different columns are and in brackets it'll tell you the max they can hold. (If I've understood what you're asking corrrectly)

 

Also:

http://mg264.imck.us/g264/7159

 

[mawby]

How do I know if it is a BLOB, will it actually say somewhere?

It will say BLOB.

[grahamc]

Ok, desc does not work in sql

 

sp_help {tableName} works

 

ItemID uniqueidentifier

Path nvarchar

Name nvarchar

ParentID uniqueidentifier

Type int

Content image

Intermediate uniqueidentifier

SnapshotDataID uniqueidentifier

LinkSourceID uniqueidentifier

Property ntext

Description nvarchar

Hidden bit

CreatedByID uniqueidentifier

CreationDate datetime

ModifiedByID uniqueidentifier

ModifiedDate datetime

MimeType nvarchar

SnapshotLimit int

Parameter ntext

PolicyID uniqueidentifier

PolicyRoot bit

ExecutionFlag int

ExecutionTime datetime

 

from sql help:

 

text

Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

[Supragal]

Sorry thought I said it's SQL.

 

I tried that just now, it say's:

 

Msg 156, Level 15, State 1, line 1

Incorrect syntax near the keyword DESC

 

I am doing something wrong, right?

[Supragal]

Ok, desc does not work in sql

 

sp_help {tableName} works

 

ItemID uniqueidentifier

Path nvarchar

Name nvarchar

ParentID uniqueidentifier

Type int

Content image

Intermediate uniqueidentifier

SnapshotDataID uniqueidentifier

LinkSourceID uniqueidentifier

Property ntext

Description nvarchar

Hidden bit

CreatedByID uniqueidentifier

CreationDate datetime

ModifiedByID uniqueidentifier

ModifiedDate datetime

MimeType nvarchar

SnapshotLimit int

Parameter ntext

PolicyID uniqueidentifier

PolicyRoot bit

ExecutionFlag int

ExecutionTime datetime

 

from sql help:

 

text

Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

 

Ok, cool, that worked, thanks. Except I am really confused now as it says 'length' '16' which means nothing to me as clearly it is more than 16... so 16 must not mean 16 literally, right?

 

But the description you gave me does the job

 

Cheers guys You should come work here lol

[Snooze]

IIRC - for these kind of fields, the data is not stored in the table itself. Instead, the data is stored elsewhere and the table just stores a "pointer" to the data. It is just the pointer that is of size 16. The TEXT itself will be much bigger.

[grahamc]

Is it Oracle SQL or what?

 

At the SQL> prompt just type

 

DESC RosiesTableName

and it'll tell you what datatype the different columns are and in brackets it'll tell you the max they can hold. (If I've understood what you're asking corrrectly)

 

Also:

http://mg264.imck.us/g264/7159

 

 

IIRC - for these kind of fields, the data is not stored in the table itself. Instead, the data is stored elsewhere and the table just stores a "pointer" to the data. It is just the pointer that is of size 16. The TEXT itself will be much bigger.

 

exactly...

[Jake]

IIRC - for these kind of fields, the data is not stored in the table itself. Instead, the data is stored elsewhere and the table just stores a "pointer" to the data. It is just the pointer that is of size 16. The TEXT itself will be much bigger.

Ah, gotcha. Thanks

[Supragal]

Ok, what kind of elsewhere? Is that location the thing that has the 2^31-1 (2,147,483,647) characters?

[Snooze]

Ok, what kind of elsewhere? Is that location the thing that has the 2^31-1 (2,147,483,647) characters?

 

Not sure exactly elsewhere - I guess that's up to the specific database implementation - but that 2Gb size limit sounds right for a TEXT.

 

Note - I think the separate storage means that TEXT fields are a lot slower than VARCHARS for some substring queries (such as "LIKE" clauses), but I'm open to correction on that claim!

[grahamc]

I have never had to change the location, but I dont think you can. stored within the datafiles somewhere somehow