In multiple courses, books, and jobs, I have seen text fields defined as VARCHAR(255) as kind of the default for "shortish" text. Is there any good reason that a length of 255 is chosen so often, other than being a nice round number? Is it a holdout from some time in the past when there was a good reason (whether or not it applies toda...
I see VARCHAR(255) being used all the time instead of VARCHAR(256), but I also see VARCHAR(16) being used instead of VARCHAR(15). This seems inconsistent to me. If an extra byte is being used to store the VARCHAR's length, shouldn't this rule also apply to the smaller lengths like 2, 4, 8 to be 1, 3, 7, instead? Or am I totally missing ...
In a slightly heated discussion on TDWTF a question arose about the size of varchar columns in a DB. For example, take a field that contains the name of a person (just name, no surname). It's quite easy to see that it will not be very long. Most people have names with less than 10 characters, and few are those above 20. If you would mak...
Hello! In MYSQL you can choose a length for the VARCHAR field type. Possible values are 1-255. But what are its advantages if you use VARCHAR(255) that is the maximum instead of VARCHAR(20)? As far as I know, the size of the entries depends only on the real length of the inserted string. size (bytes) = length+1 So if you have the wor...
Disclaimer: I'm very new to SQL and databases in general. I need to create a field that will store a maximum of 32 characters of text data. Does "VARCHAR(32)" mean that I have exactly 32 characters for my data? Do I need to reserve an extra character for null-termination? I conducted a simple test and it seems that this is a WYSIWYG ...
I have a bunch of NVARCHAR columns which I suspect contain perfectly storable data in VARCHAR columns. However I can't just go and change the columns' type into VARCHAR and hope for the best, I need to do some sort of check. I want to do the conversion because the data is static (it won't change in the future) and the columns are indexe...
Hi, I have a table with a single unique VARCHAR(512) field. I want to have another table hold a foreign key reference to this first table. Both tables use InnoDB. If I add a VARCHAR(512) key to the second table and add a foreign key constraint on it will the 512 bytes long data be held twice? If so, is there a way to hold only a refere...
Hi I recognize that an email address can basically be indefinitely long so any size I impose on my varchar email address field is going to be arbitrary. However, I was wondering what the "standard" is? How long do you guys make it? (same question for Name field...) Thanks, Mala update: Apparently the max length for an email address is...
I’m in need of some clarification of the maximum length of a varchar field in MySQL. I’ve always thought the max length was 255 (255 what? Characters I’ve assumed, but this might be a source of my confusion). Taking a look at the tables of a database set up by an external company we’re working with, I see a field set-up as varchar(20000...
If there are 2 columns in database, eg. code varchar(3) name nvarchar(50) how to tell hibernate to pass varchar for searching by code? In the hibernate mappings string is mapped to nvarchar and it produces queries like: Select code, name From table where code=N'AAA' (instead of code='AAA') This is very bad as it causes index scan i...
HI there, i was wondering if there was a way of making a nextval sequence for a varchar value? I am using iSQL plus, oracle. For e.g. I have a sales table which consists of sale_id, prod_id, cust_name, sale_name. where i insert the data by doing the following: insert into sales select sale_id_seq.nextval, trunc(dbms_random.value(1,100))...
Hello, My question is what is better to use in generating columns in SQL. Should the size of nvarchar (varchar) be multiply of 2 (32, 64, 128) or it's doesn't matter and we can use fully numbers example '100', '50' ? Thank You very much for answers with reasons Greeting's to all ...
I have to different databases one is mySQL and the other is Sybase IQ. Both have the same table layout and datatypes. One of the fields has a bit datatype. Unfortunately when I pull the mySQL table into Powercenter it makes it converts it to a string(1). Now when I am trying to write the data from Powercenter to the Sybase IQ table u...
Hi, i have a very large table which is currently approx 70M rows and growing daily by the thousands , this schema is tipping over every day now so i'm moving to a partitioned table and redesigning the ddl . the table is basicly a collection of NOT NULL INTEGERS(some medium some INT some tiny) which need to have a unique constraint for a...
A lot of Database schemas seem to follow the following standard: (2^n)-1 for large fields: varchar(511) varchar(255) varchar(127) ...then (2^n) for smaller ones varchar(64) varchar(32) varchar(16) varchar(8) I understand why numbers of (2^n)-1 are used, what I don't understand is why it is not necessary to continue the trend down ...
Hi All, Can anyone provide me a quick input on how mysql treat varchar data type size. For Example: - If I have a column in table with field with Varchar(15) and if I try to insert data of length 16, then it gives an error stating "Data too long for column 'testname' at row 1". Does anyone know why varchar fields in mysql take fixed...
I'm trying to optimize my PostgreSQL 8.3 DB tables to the best of my ability, and I'm unsure if I need to use varchar_pattern_ops for certain columns where I'm performing a LIKE against the first N characters of a string. According to this documentation, the use of xxx_pattern_ops is only necessary "...when the server does not use the s...
I want to create a table in MS SQL Server 2005 to record details of certain system operations. As you can see from the table design below, every column apart from Details is is non nullable. CREATE TABLE [Log] ( [LogID] [int] IDENTITY(1,1) NOT NULL, [ActionID] [int] NOT NULL, [SystemID] [int] NOT NULL, [UserID] [int] NOT NULL, [LoggedOn...
What is the meaning of varchar(-1) in SQL Server 2008? Is it an alternative for varchar(max)? ...
Hey guys well I'm working on this system and it giving me sooo much trouble. I just want to quit at this point. Long story short I made a few changes to how a mySQL table works, I went from storing a comma separated list for a few different fields to storing the same data each in its own field. I then rewrote the code to work with the ne...