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 12»»

Suggestions for Datatypes Expand / Collapse
Author
Message
Posted Friday, May 27, 2005 5:56 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, 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
Post #185853
Posted Sunday, June 5, 2005 7:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:20 AM
Points: 2,555, Visits: 607
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 !!!**
Post #187696
Posted Sunday, June 5, 2005 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 8, 2007 8:53 PM
Points: 2, 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.

Post #187704
Posted Sunday, June 5, 2005 9:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:20 AM
Points: 2,555, Visits: 607
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 !!!**
Post #187709
Posted Monday, June 6, 2005 5:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,923, Visits: 1,874
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
Post #187772
Posted Monday, June 6, 2005 6:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:01 PM
Points: 295, Visits: 287

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
Post #187793
Posted Monday, June 6, 2005 8:38 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:09 PM
Points: 91, Visits: 198

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




Post #187873
Posted Monday, June 6, 2005 9:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 945, Visits: 266
That may be true for _most_ programming languages, but the .NET framework supports SmallDateTime (v 2.0) in the System.Data.SqlTypes namespace.
Post #187903
Posted Monday, June 6, 2005 10:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:01 PM
Points: 295, Visits: 287

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
Post #187909
Posted Thursday, June 9, 2005 10:57 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174

What happen to my last reply ?

 




Kindest Regards,

Amit Lohia
Post #189160
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse