number of characters when creating table
SQL Programmer
In SQL 2005 Standard, I created a simple 8-column table. I specified 23 as
the number of characters for the first column and 25 as the number of
characters for the final column. The first column is a char column, while
the final is a varchar column as follows:
DocNo (char(23), not null)
Company (varchar(25), not null)
Is 23 and 25 the maximum number of characters for those two fields? So, if
data in either field has less than 23 and 25, respectively, no issues will
result. Correct? Likewise, if there are more than 23 and 25 characters,
then the data will not be inserted into those two fields. Also correct?
Can I change the maximums at any time, or will I lose data? There is no
data in the table right now. I just want to plan for the future.
Is there any harm in setting a maximum of say 40 or 50 in each?
Thanks!
SQL Programmer (it's just a name)
Scott Morris
> if
> data in either field has less than 23 and 25, respectively, no issues will
> result. Correct?
Correct. However, you seem to not quite grasp the difference between char
and varchar. BOL should be your first visit.
> Likewise, if there are more than 23 and 25 characters,
> then the data will not be inserted into those two fields. Also correct?
In general, yes.
> Can I change the maximums at any time, or will I lose data? There is no
> data in the table right now. I just want to plan for the future.
You can change them at any time, without losing /changing existing data.
> Is there any harm in setting a maximum of say 40 or 50 in each?
Do the proper data analysis and identify the appropriate lengths you need
first. You can change them, but doing so has a tendency to cause ripple
effects throughout your system once you've started creating code based on
the initial sizes.
Dan Guzman
> Company (varchar(25), not null)
> Is there any harm in setting a maximum of say 40 or 50 in each?
Be aware that char is fixed length and varchar is variable. Every row will
reserve space for the defined maximum length of DocNo. SQL Server will
require space for the actual data length (not maximum size) in the varchar
Company column.
If you increase the length of a char column later, every row in the table
will be updated to the max size. Increasing the max size of a varchar is a
meta-data only change so no data are actually updated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
Peso
> if
Yes
> data in either field has less than 23 and 25, respectively, no issues will
> result. Correct?
Yes
Likewise, if there are more than 23 and 25 characters,
> then the data will not be inserted into those two fields. Also correct?
Maybe. It depends on your settings wether to automatically truncate the
inserted text or not.
> Can I change the maximums at any time, or will I lose data? There is no
> data in the table right now. I just want to plan for the future.
Even if there are data, increasing the maximum value will not lose data for
you
> Is there any harm in setting a maximum of say 40 or 50 in each?
>
Most likely not. It depends on the other columns in the table, and your fill
factor for your present indexes.
//Peter Larsson