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 Thursday, June 23, 2005 9:26 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 12, 2007 6:32 AM
Points: 62, 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.

Post #193544
Posted Thursday, June 23, 2005 9:31 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

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
Post #193551
Posted Tuesday, June 6, 2006 11:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499

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

 

 

  

 

 

Post #285378
Posted Tuesday, June 6, 2006 5:03 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:38 PM
Points: 817, Visits: 613
Any ideas on code to determine when to use nchar vs. char and nvarchar vs. varchar?
Post #285454
Posted Friday, June 23, 2006 2:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 30, 2012 4:10 AM
Points: 48, 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...

Post #289862
Posted Wednesday, June 17, 2009 12:04 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
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!
Post #736862
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse