Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Datatype performance Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 3:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 21, Visits: 1,116
Hello

This may be a stupid question but are there any performance difference between different data type.

For this query :

select * from Table where column = @column

is a bigint column gonna be faster than a binary(8) or a char(8) column ?

Is this the same for an insert with an unique key or in a table join ? is there a datatype that will perform better?

There is surely an article on this subject somewhere , but i wasnt able to find one.
Post #1393243
Posted Thursday, December 6, 2012 12:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
Usually integer columns are a bit faster than string columns.
So for a primary key it's a good idea to take an integer column, instead of a string column or a GUID.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1393350
Posted Thursday, December 6, 2012 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 21, Visits: 1,116
Thank you for the info

If string is the slowest, is there any difference between a integer and a binary field ?
Post #1393627
Posted Thursday, December 6, 2012 10:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
Faster in this case does not mean noticably or measurably faster. Maybe a few microseconds for a simple comparison like that. Joins show more of an effect, but you still need to be doing huge joins to notice.

Use the appropriate data type for the data value that you are going to store. Test. If performance is unacceptable, then come back and do strange things (like binary(8) in the name of performance)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1393628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse