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


Suggestions for Datatypes


Suggestions for Datatypes

Author
Message
JT Lovell
JT Lovell
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 3

Just to weigh in a little late on this one..

Developmental speed can often outweight a fully-optimized data source. I.e. if all dates are "datetime" and all strings are "varchar" then it can be faster and simpler to design the apps that sit on top of them. I'll admit it's not as optimized as it could be, but there's a huge cost savings in knocking a month out of a development cycle that could easily buy a whole rack of SCSI drives.

I would personally only use this type of optimization on something where bandwidth was absolutely critical such as a wireless app with very limited bandwidth.


Amit Lohia
Amit Lohia
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1186 Visits: 174

JT Lovell

You bring the most important point. We all do the same to increase the speed of development because most of the project has a deadline of yesterday. This is the reason after development and use of the application for several month we can run this type of routine to fine tune the database and it will not take a month and we will save the whole rack of SCSI drives and do not forgot the cost of backup also.

Thanks
Amit




Kindest Regards,

Amit Lohia
dbaforever
dbaforever
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 539

Thank you Amit.

I use S-S 2000.

I ran your entire set of code, but nothing was written to the ouput table. Is your step for 2005 or 2000?

But on the subject of data types, my biggest pet peeve is the over-use of the nvarchar type here at my office.

Oh my, it is so wasteful. For some reason, when developers bring over a table from Access, the SQL table will always have several nvarchar columns which I then have to "clean-up" by changing each to varchar.

A 10-character value stored in an nvarchar column actually uses/occupies 20 bytes of disk space and memory/RAM.

I have recovered several Gigabytes of disk space by simply changing data types in several tables.

**If anyone knows how to change the DEFAULT data type for Access' Text columns when importing them into a SQL table, I would sure like to know that.**

I know there are times when nvarchar is required but in my environment - almost never.

Thanks. John


Matthew Bates
Matthew Bates
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 662
Any ideas on code to determine when to use nchar vs. char and nvarchar vs. varchar?
Stephen Turner
Stephen Turner
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 14
The quick and dirty way is to cast from nvarchar to varchar then back to nvarchar, then compare with the original...

cast(cast(myfield as varchar) as nvarchar) <> myfield

Should be fine upto a couple of thousand records!

A better way might be to get the binary and check every orther byte for a nonzero (need to check the encoding)

Someone with more knowledge of unicode will be along in a mo and tell us that even a simple accented character could be encoded differently in language A, language B and ASCII which of course would blow both methods out of the water...
YSLGuru
YSLGuru
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2424 Visits: 1665
The real answer to thsi issue of should I go with the smallest data type possible (for space & badnwidth reasons) or to go with what is portable and or last greater then 100 years (in the case of the DATETIME data type) is that "It depends". For some portability is more important then space conservation.

That being said there are still a few cases where no matter your situation I think we can all agree that changing the data type to something smaller is a good idea. In our environment the system we use has every single Boolean filed in every table set to use the INT data type or is set to use Numeric(18,0). Talk about a pooor choice and a waste of space! You know how this happens? You let procedural progarmmers with no proper DB Training making design decissions about the database. Every one of these Boolean fields should have been set to a BIT data type or at least to the smallest integer value in the system like TINYINT.

Kindest Regards,

Just say No to Facebook!
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