Index and data type

If I was to create a column within a table that will be used as an index,
will it be better to declare the column as an interger or varchar for
performance?
Please explain your answer.
Thanks
ScottYou don't create columns based on what type of index you will use. You base
the datatype on what the data is that you need to store. If the data is of
an integer type then use an integer. It almost never makes sense to store
integer data as a varchar.
--
Andrew J. Kelly SQL MVP
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:379A584C-B485-479F-9A1B-0749956480DA@.microsoft.com...
> If I was to create a column within a table that will be used as an index,
> will it be better to declare the column as an interger or varchar for
> performance?
> Please explain your answer.
> Thanks
> Scott|||In addtion to Andrew's comment, I would like to point out that integer as a
fixed length column does have advantage over varchar in some cases.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O5vYwqoAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> You don't create columns based on what type of index you will use. You
> base the datatype on what the data is that you need to store. If the data
> is of an integer type then use an integer. It almost never makes sense to
> store integer data as a varchar.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:379A584C-B485-479F-9A1B-0749956480DA@.microsoft.com...
>> If I was to create a column within a table that will be used as an index,
>> will it be better to declare the column as an interger or varchar for
>> performance?
>> Please explain your answer.
>> Thanks
>> Scott
>|||Thanks for your response. You are right. I wasn't thinking about a real
world implementation, just curious about which would be faster. Please don't
think about the design and implemenation, just what would be faster.
Thanks.
Scott
"Andrew J. Kelly" wrote:
> You don't create columns based on what type of index you will use. You base
> the datatype on what the data is that you need to store. If the data is of
> an integer type then use an integer. It almost never makes sense to store
> integer data as a varchar.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:379A584C-B485-479F-9A1B-0749956480DA@.microsoft.com...
> > If I was to create a column within a table that will be used as an index,
> > will it be better to declare the column as an interger or varchar for
> > performance?
> >
> > Please explain your answer.
> > Thanks
> >
> > Scott
>
>|||Please explain. I'm curious.
Scott
"Wei Xiao [MSFT]" wrote:
> In addtion to Andrew's comment, I would like to point out that integer as a
> fixed length column does have advantage over varchar in some cases.
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O5vYwqoAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> > You don't create columns based on what type of index you will use. You
> > base the datatype on what the data is that you need to store. If the data
> > is of an integer type then use an integer. It almost never makes sense to
> > store integer data as a varchar.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> > news:379A584C-B485-479F-9A1B-0749956480DA@.microsoft.com...
> >> If I was to create a column within a table that will be used as an index,
> >> will it be better to declare the column as an interger or varchar for
> >> performance?
> >>
> >> Please explain your answer.
> >> Thanks
> >>
> >> Scott
> >
> >
>
>|||Integers will be more efficient than varchars in several ways. One is that
the size is generally smaller to hold the same value when you get over 4
digits long. An INT will always be 4 bytes even for a number of 2 billion
where are the varchar will take one byte for each character. The sorting is
also faster on an integer than a varchar or almost anything else other than
maybe binary.
--
Andrew J. Kelly SQL MVP
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:6E815CA0-BE3B-44D6-BB7F-ECCBDF50E27E@.microsoft.com...
> Thanks for your response. You are right. I wasn't thinking about a real
> world implementation, just curious about which would be faster. Please
> don't
> think about the design and implemenation, just what would be faster.
> Thanks.
> Scott
>
> "Andrew J. Kelly" wrote:
>> You don't create columns based on what type of index you will use. You
>> base
>> the datatype on what the data is that you need to store. If the data is
>> of
>> an integer type then use an integer. It almost never makes sense to store
>> integer data as a varchar.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
>> news:379A584C-B485-479F-9A1B-0749956480DA@.microsoft.com...
>> > If I was to create a column within a table that will be used as an
>> > index,
>> > will it be better to declare the column as an interger or varchar for
>> > performance?
>> >
>> > Please explain your answer.
>> > Thanks
>> >
>> > Scott
>>|||Thank you for your help.
Scott
"Andrew J. Kelly" wrote:
> Integers will be more efficient than varchars in several ways. One is that
> the size is generally smaller to hold the same value when you get over 4
> digits long. An INT will always be 4 bytes even for a number of 2 billion
> where are the varchar will take one byte for each character. The sorting is
> also faster on an integer than a varchar or almost anything else other than
> maybe binary.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:6E815CA0-BE3B-44D6-BB7F-ECCBDF50E27E@.microsoft.com...
> > Thanks for your response. You are right. I wasn't thinking about a real
> > world implementation, just curious about which would be faster. Please
> > don't
> > think about the design and implemenation, just what would be faster.
> >
> > Thanks.
> > Scott
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> You don't create columns based on what type of index you will use. You
> >> base
> >> the datatype on what the data is that you need to store. If the data is
> >> of
> >> an integer type then use an integer. It almost never makes sense to store
> >> integer data as a varchar.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> >> news:379A584C-B485-479F-9A1B-0749956480DA@.microsoft.com...
> >> > If I was to create a column within a table that will be used as an
> >> > index,
> >> > will it be better to declare the column as an interger or varchar for
> >> > performance?
> >> >
> >> > Please explain your answer.
> >> > Thanks
> >> >
> >> > Scott
> >>
> >>
> >>
>
>