Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2015-02-04 : 15:26:17
|
| I have these 2 statements for the Datatype but i was confused with the storage they have internally.declare @str1 nvarchar(max)SELECT @str1 = REPLICATE('A', 4001)SELECT LEN(@str1)DECLARE @str3 nvarchar(4001)SELECT @str3 = REPLICATE('A', 4001)Why the second statement is having error, if you justify with storage mechanism will be greatful.How do we get to know how the storage structure for these 2 if they are storing in In-page memory and blob storage ?? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-04 : 15:36:59
|
nvarchar(max) is special. For nvarchar(n), the max is 4000. nvarchar uses double the amount of storage as varchar, so this works fine:DECLARE @str4 varchar(4001)SELECT @str4 = REPLICATE('A', 4001)As for the justification, it has to do with overhead. 8096 is the maximum for varchar (divide by 2 for nvarchar), but there's like 96 bytes of overhead. I forget the details on it and don't care to look it up. So you'll see varchar(8000) or nvarchar(4000) being defined for long strings. And remember nvarchar/varchar(max) is special. It uses a different storage mechanism and doesn't have the 8000/4000 limit. Again too lazy too lazy to look it up for you.  Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2015-02-04 : 16:09:37
|
| Hi Tara, Thank you so much for your info.If you could please give me some reference where i can look for the storage type meaning any system table or DMV which indicates this to prove it, will be great help, i know this is too much to ask for you in your busy schedule,but when you have time please respond.Much appreciated for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-04 : 16:12:13
|
| https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=varchar%20storageTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-02-04 : 19:17:05
|
Books Online
Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824) |
|
|
|
|
|
|
|
|
Data type storage understanding