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 «««1234»»

Data Type and Length Expand / Collapse
Author
Message
Posted Wednesday, August 11, 2010 6:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Good question! Thanks.
Post #967358
Posted Monday, August 23, 2010 5:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
Hugo Kornelis (8/9/2010)
Good question! When I first saw it, I thought there would be a very high percentage of correct answers since the confusing default lengths for character strings have already been covered in a few recent QotD's. But the results say that at this time, only 53% of the respondents have given the right answer, so there is obviously still a lot of need to keep driving this point home. Good job, magasvs!


Nah... it just shows that 47% of the people are too lazy to run the code which is also why these are only worth 1 point.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #973345
Posted Monday, August 23, 2010 5:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
paul.knibbs (8/11/2010)
David Data (8/10/2010)
It would be nice to have a datatype like .NET's string which can carry strings of (almost) any length without worrying about what its contents' maximum length might happen to be. Fields in database tables may need to be specified precisely; local variables should not.


Isn't that pretty much what VARCHAR(MAX) is?


There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #973347
Posted Monday, August 23, 2010 5:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:05 AM
Points: 1,528, Visits: 5,172

There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.


Well, the person I was responding to never mentioned anything about performance, he just wanted a text field that can be any length in T-SQL, and I was pointing out that already exists.
Post #973348
Posted Monday, August 23, 2010 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
paul.knibbs (8/23/2010)

There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.


Well, the person I was responding to never mentioned anything about performance, he just wanted a text field that can be any length in T-SQL, and I was pointing out that already exists.


Heh... Well, everyone should always be concerned about performance so I was pointing out how bad it can actually be when you don't size stuff properly.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #973822
Posted Sunday, September 26, 2010 11:30 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Great Question it chew up my hour worth ups time.
Post #993433
Posted Monday, September 27, 2010 4:55 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:26 AM
Points: 115, Visits: 739
(As someone has re-awakened this subject...)
Jeff Moden (8/23/2010)

There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.

I was talking about local scalar variables, not arrays or database fields where extra microseconds can get multiplied by millions of records.

But I am curious as to why a VARCHAR(MAX) string that happens to contain say 100 chars is so much slower that a VARCHAR(100) that does? Its index and length values will have to be 32 bit numbers, but with 32/64 bit CPUs anything shorter tends to be less rather than more efficient anyway. As even a VARCHAR(100) is stored as a variable length string, I would not imagine the memory management issues are much different either. Or are they?

That said, I do always use the smallest VARCHAR I can - though I'm not sure whether it really makes much difference as SQL Server seems to allocate space for the actual content not the potential maximum content, as I tested by creating a large table of VARCHAR(4096) containing just 'x' (1 character) per record.
Post #994111
Posted Monday, September 27, 2010 7:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:30 PM
Points: 1,264, Visits: 721
The problem with wide columns is that even though SQL Server supports now larger row size the maximum capacity specification is still 8,060 bytes per row. If the row becomes wider - then row overflow occurs and data saved differently. This affects query performance (I/O performance degradation). More details are here http://msdn.microsoft.com/en-us/library/ms186981.aspx. This works differently with varchar(max), nvarchar(max), varbinary(max), text, image, or xml data types, but I just wanted to make point that columns width should be reasonable.

Another example of incorrect data type usage is using NVARCHAR or FLOAT data types when it's not required. Just a couple of days ago I have been testing large database migration and by changing NVARCHAR, DATETIME to VARCHAR and DATE/SMALLDATETIME (together with column width reduction) one of the tables reduced in size by 11 GB! There were columns like Year nvarchar (255), PostalCode nvarchar (255), Phone nvarchar (8000)...
Post #994145
Posted Tuesday, September 28, 2010 1:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:05 AM
Points: 1,528, Visits: 5,172
magasvs (9/27/2010)
There were columns like Year nvarchar (255), PostalCode nvarchar (255), Phone nvarchar (8000)...


One of our developers stored a TCP port number (which is, by definition, an unsigned 16-bit integer) in a VARCHAR(50) field...sometimes I despair.
Post #994225
Posted Tuesday, September 28, 2010 2:04 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:26 AM
Points: 115, Visits: 739
OTOH I designed our ETL system with [Postcode] [nvarchar](10) and then had to change it when I found records with postcodes like 'If out please leave by back door or with neighbour at No. 127'

magasvs (9/27/2010)
The problem with wide columns is that even though SQL Server supports now larger row size the maximum capacity specification is still 8,060 bytes per row. If the row becomes wider - then row overflow occurs and data is saved differently.


I understand the need to keep the actual data short, (especially for fields in the primary key, where I prefer to use INT or CHAR if possible), but does a record containing a couple of VARCHAR(8000)'s or even a VARCHAR(MAX) which happen to contain 50-char strings need row overflow, or does it only need 100 bytes (+overhead)? My tests suggest the latter.
Post #994236
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse