February 21, 2020 at 3:17 pm
What are the things you consider when you create a new table and define data types of each columns? I usually get historical feed files and I load these data and check the maximum length of each column values. Then, I try to define the data type based on the historical data sets. For example, the maximum length value of ColumnA is 54, then I set the data type to something like varchar(80) in case there may be longer values in the future.
Just wanted to see how others define the data types when creating a new table. Thank you.
February 21, 2020 at 3:54 pm
the only piece of advice I can give you is don't use varchar for a date field, don't use float for anything and think about the maximum size of any indexes you may need
MVDBA
February 21, 2020 at 4:07 pm
Normally data feeds have a specification that will specify the maximum length of a column and whether or not it's mandatory etc...
Make a column not null if it's mandatory.
February 21, 2020 at 7:57 pm
I would compare the contents to other similar data that you might have elsewhere, for consistency. For example if you are using varchar(80) for names of people or places, then you would structure the new table to match.
That being said, you might also consider using a staging table with much larger data types, and then copy from there into the destination.
HTH
February 24, 2020 at 9:08 am
I would compare the contents to other similar data that you might have elsewhere, for consistency. For example if you are using varchar(80) for names of people or places, then you would structure the new table to match.
That being said, you might also consider using a staging table with much larger data types, and then copy from there into the destination.
HTH
Staging tables - always...
but i'm sure Joe celko could help you out on the ISO standards for field lengths of things like names(seriously who has a varchar(80) name)
MVDBA
February 24, 2020 at 9:32 am
Staging tables - always...
but i'm sure Joe celko could help you out on the ISO standards for field lengths of things like names(seriously who has a varchar(80) name)
Joe will also, however, tell you that you can't use numerical data types for Unique Identifiers, as datatypes that contain numbers are for maths only. So if you do need to store data that consist only of numbers, but you'll never do any maths on, he "advises" you use 10 bytes to store a 4 byte integer. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 24, 2020 at 9:42 am
yes he can be slightly "pernickety"... 🙂 but he does have a good knowledge of standards (that I ignore) 🙂
MVDBA
February 24, 2020 at 9:47 am
MVDBA (Mike Vessey) wrote:Staging tables - always...
but i'm sure Joe celko could help you out on the ISO standards for field lengths of things like names(seriously who has a varchar(80) name)
Joe will also, however, tell you that you can't use numerical data types for Unique Identifiers, as datatypes that contain numbers are for maths only. So if you do need to store data that consist only of numbers, but you'll never do any maths on, he "advises" you use 10 bytes to store a 4 byte integer. 🙂
I just re-read this ….. "only use numerical types for maths"??????? how about a foreign key to person.status - if there are only 3 statuses then tinyint…. massive storage saving
MVDBA
February 24, 2020 at 10:01 am
I just re-read this ….. "only use numerical types for maths"??????? how about a foreign key to person.status - if there are only 3 statuses then tinyint…. massive storage saving
I don't disagree, tinyint would be ideal.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 24, 2020 at 10:06 am
MVDBA (Mike Vessey) wrote:I just re-read this ….. "only use numerical types for maths"??????? how about a foreign key to person.status - if there are only 3 statuses then tinyint…. massive storage saving
I don't disagree,
tinyintwould be ideal.
where is the emoji that says "I can't believe someone that clever said something that dumb"? its gotta be a double face palm ?
MVDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply