SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Suggestions for Datatypes


Suggestions for Datatypes

Author
Message
Amit Lohia
Amit Lohia
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
Points: 966 Visits: 174
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aLohia/suggestionsfordatatypes.asp


Kindest Regards,

Amit Lohia
sushila
sushila
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3609 Visits: 639
Amit - good article and "nice to have" if you're stuck with maintaining databases developed by someone else.

I've had long and heated arguments with some of my colleagues on the choice of datatypes - I prefer to finetune everything and use just what I need and since I'm the developer it's easy for me to ensure this. However, I've still had to fight to assign "tinyint" to lookup tables where I know that the # of rows will NEVER exceed 50...someone with many years of dba experience under his belt told me that the reason to "play safe" and always keep with the int datatype (eg.) is for portability - so that if the database ever had to be imported into Oracle etc..there wouldn't be any conflicts!

What is your take on the portability issue ?!

Lastly, I quote from one of my favourite authors - Robert Vieira ("SQL Server 2000 Programming") -

"Choose what you need, but ONLY what you need. eg. if you're trying to store months (as the number 1-12) - those can be done in a single byte by using a tinyint. Why then do I regularly come across databases where a field that's only going to store a month is declared as an int (which is 4 bytes) ? Don't use nchar or nvarchar if you're never going to do anything that required Unicode - these datatypes take up 2 bytes for every one as compared to their non-Unicode cousins.

There is a tendency to think about this as a space issue - "Ah, disk space is cheap these days!" Well, beyond the notion that a name brand SCSI drive still costs more than I care to throw away on laziness, there's also a network bandwidth issue. If you're passing an extra 100 bytes down the wire for every row, and you pass a 100 record result, then that's about 10K worth of extra data you just clogged your network with - if you have 100 users performing 50 transactions per hour - that's over 50MB of wasted network bandwidth per hour."







**ASCII stupid question, get a stupid ANSI !!!**
RockPond
RockPond
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1

the line of thinking in your article that says "but do you really think the company will be using that application for next 74 years?) " is the same thinking that caused the Y2K problem. The application may be different but well defined data sources will outlast applications.

Good article, just a thought to consider.


sushila
sushila
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3609 Visits: 639
Have to agree - there are zillions of legacy apps out there and well defined datasources are few and far between....







**ASCII stupid question, get a stupid ANSI !!!**
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7821 Visits: 3290
Income tax was originally a temporary taxation. Speed limits on motorways were originally temporary so I would be wary of SMALLDATETIME. Admittedly we will all be dead by the time it becomes a problem but hey, youth loves a challenge so we will bequeath something positive for our great grand children.

I know that SmallInt/TinyInt save me storage space but I read somewhere that Windows only looks at 32 bit integers anyway and so everything gets converted to Int by default. This might be an obsolete observation.

The other thing to remember is that the word NEVER was invented so that God could have a laugh. There are currently 100 or so counties in the UK but if the "European Project" is successfully rammed down our throats who is to say that systems that track UK counties don't end up tracking more than 255 EU equivalents.

That said the intent of optimising your datatypes is best practice but you always need to consider the bloody-mindedness of people using the system

LinkedIn Profile

Newbie on www.simple-talk.com
Tatsu
Tatsu
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 307

I don't see a big problem with this. About all you need to do to fix it is change to datetime in the table and the stored procedures that use it. The application code will probably not need to change much (if at all) since most programming languages don't have different size date types. Hopefully all the data access code in the application would be auto-generated by 2079 anyway Then you would just need to rebuild the data access layer and you're done.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Steve Rosenbach
Steve Rosenbach
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 206

A good article, Amit - thanks.

I'd like to add another reason to use the smallest appropriate datatype - and that's the way SQL Server stores data.

Yes, disk drives are getting cheaper by the minute, but a page of data in SQL Server is still fixed at 8K.

For Index leaf pages, the smaller the datatype, the more index keys can fit on an 8K page and, all things being equal, the less logical reads required for an index seek.

For tables with a clustered index, it's even more important, as the index leaf pages contain the actual rows of data. The smaller the data rows, the more rows on an 8K page, the faster the access.

Best regards,

SteveR





John Rempel
John Rempel
SSC Eights!
SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)

Group: General Forum Members
Points: 997 Visits: 288
That may be true for _most_ programming languages, but the .NET framework supports SmallDateTime (v 2.0) in the System.Data.SqlTypes namespace.
Tatsu
Tatsu
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 307

You would only be worried about the SmallDateTime type in the data access code which, as I said, should be auto-generated. You re-run your code generator, rebuild the solution and you're good to go.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Amit Lohia
Amit Lohia
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
Points: 966 Visits: 174

What happen to my last reply ?




Kindest Regards,

Amit Lohia
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search