How to define data types

  • ocean3300

    SSChasing Mays

    Points: 621

    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.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    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

  • Jonathan AC Roberts

    SSCoach

    Points: 17204

    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.

  • WILLIAM MITCHELL

    SSChampion

    Points: 13678

    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

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    WILLIAM MITCHELL wrote:

    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

  • Thom A

    SSC Guru

    Points: 98563

    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. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    yes he can be slightly "pernickety"... 🙂 but he does have a good knowledge of standards (that I ignore) 🙂

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    Thom A wrote:

    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

  • Thom A

    SSC Guru

    Points: 98563

    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, tinyint would be ideal.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    Thom A wrote:

    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, tinyint would 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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply