|
Home > Archive > MS SQL Server > October 2006 > Column type to select data from big tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Column type to select data from big tables
|
|
| MerlinXP 2006-10-24, 6:30 pm |
| Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.
| |
|
| The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" < MerlinXP_NOSPAM@NOSP
AM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@TK2MSFTNGP04.phx.gbl...
> Hello,
>
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
>
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
>
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
>
> M.
| |
| Roger Wolter[MSFT] 2006-10-24, 6:30 pm |
| If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" < MerlinXP_NOSPAM@NOSP
AM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@TK2MSFTNGP04.phx.gbl...
> Hello,
>
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
>
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
>
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
>
> M.
| |
| MerlinXP 2006-10-24, 6:30 pm |
| Użytkownik Roger Wolter[MSFT] napisał:
> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.
|
|
|
|
|