varbinary(max)

Question

Post reply

varbinary(max)

Rainmaker097

SSC Eights!

Points: 866
More actions
August 20, 2012 at 11:42 am

#262968

Hi,
What is the maximum of max in varbinary type?
I tried
select Col1,len(CONVERT(VARCHAR(MAX),col2)) from tablename where Col1='CALCBUILDDATAMART'
I get Col1, 9187.
How is this even possible?
Thanks.
Regards,
RM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Details on varbinary(max) can be found here: http://msdn.microsoft.com/en-us/library/ms188362.aspx

Basically, 2 Gig is the largest you can store in any of the "max" data types.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Rainmaker097 SSC Eights! Points: 866 More actions · Answer 2

Rainmaker097

SSC Eights!

Points: 866

August 20, 2012 at 11:53 am

#1527765

Great. Thank you

Rainmaker097 SSC Eights! Points: 866 More actions · Answer 3

Hi,

I am able to insert more than 8000 characters. but when I select after converting to varchar, it chops the values.

For example, if you run the blow, you will see the output is chopped after 8000 chars. Do you know why is this?

Thanks,

RM

declare @DbServerA varchar(50)

set @DbServerA = @@servername

declare @ConsolePwd as varchar(50)

set @ConsolePwd = 'blah'

declare @XML as VARBINARY(max)

set @XML = CONVERT(VARBINARY(MAX),'<sections>

<comment>Database Connection String</comment>

<section name="DBConnectAttrib">

<item key="ConnectionString" value="Server=' + @DbServerA + ';Database=DBBLAH;User ID=userblah;Trusted_Connection=False;Pwd=' + @ConsolePwd + ';Pooling=true;Min Pool Size=0; Max Pool Size=50;" />

<item key="Provider" value="System.Data.SQLClient" />

</section>

<comment>Calculator Build Data Mart Configuration</comment>

<section name="CalcBuildDataMartConfig">

<BuildCalculatorDataMart dataMartName="MyTable" path="C:\" clientId="0" currency="0" securityType="2" priorPeriodsToBuild="6" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0" mapClientIdTo="1">

<Clients>

<Client id="1" percentToInclude="100" />

<Client id="3" percentToInclude="100" />

<Client id="4" percentToInclude="100" />

<Client id="8" percentToInclude="100" />

<Client id="9" percentToInclude="100" />

<Client id="10" percentToInclude="100" />

<Client id="15" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_CAD_OPT" path="C:\" clientId="0" currency="0" securityType="3" priorPeriodsToBuild="6" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0" mapClientIdTo="1">

<Clients>

<Client id="1" percentToInclude="100" />

<Client id="3" percentToInclude="100" />

<Client id="4" percentToInclude="100" />

<Client id="8" percentToInclude="100" />

<Client id="9" percentToInclude="100" />

<Client id="10" percentToInclude="100" />

<Client id="15" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_USD_EQ" path="C:\" clientId="0" currency="2" securityType="2" priorPeriodsToBuild="6" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0" mapClientIdTo="1">

<Clients>

<Client id="1" percentToInclude="100" />

<Client id="3" percentToInclude="100" />

<Client id="4" percentToInclude="100" />

<Client id="8" percentToInclude="100" />

<Client id="9" percentToInclude="100" />

<Client id="10" percentToInclude="100" />

<Client id="15" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_USD_OPT" path="C:\" clientId="0" currency="2" securityType="3" priorPeriodsToBuild="6" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0" mapClientIdTo="1">

<Clients>

<Client id="1" percentToInclude="100" />

<Client id="3" percentToInclude="100" />

<Client id="4" percentToInclude="100" />

<Client id="8" percentToInclude="100" />

<Client id="9" percentToInclude="100" />

<Client id="10" percentToInclude="100" />

<Client id="15" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_USD_EQ" path="C:\" clientId="0" currency="2" securityType="2" priorPeriodsToBuild="6" mapClientIdTo="1" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="2" percentToInclude="100" />

<Client id="6" percentToInclude="100" />

<Client id="7" percentToInclude="100" />

<Client id="11" percentToInclude="100" />

<Client id="13" percentToInclude="100" />

<Client id="14" percentToInclude="100" />

<Client id="16" percentToInclude="20" />

<Client id="17" percentToInclude="100" />

<Client id="19" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_USD_OPT" path="C:\" clientId="0" currency="2" securityType="3" priorPeriodsToBuild="6" mapClientIdTo="1" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="2" percentToInclude="100" />

<Client id="6" percentToInclude="100" />

<Client id="7" percentToInclude="100" />

<Client id="11" percentToInclude="100" />

<Client id="13" percentToInclude="100" />

<Client id="14" percentToInclude="100" />

<Client id="17" percentToInclude="100" />

<Client id="19" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_CAD_EQ" path="C:\" clientId="0" currency="0" securityType="2" priorPeriodsToBuild="6" mapClientIdTo="1" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="2" percentToInclude="100" />

<Client id="6" percentToInclude="100" />

<Client id="7" percentToInclude="100" />

<Client id="11" percentToInclude="100" />

<Client id="13" percentToInclude="100" />

<Client id="14" percentToInclude="100" />

<Client id="17" percentToInclude="100" />

<Client id="19" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_CAD_OPT" path="C:\" clientId="0" currency="0" securityType="3" priorPeriodsToBuild="6" mapClientIdTo="1" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="2" percentToInclude="100" />

<Client id="6" percentToInclude="100" />

<Client id="7" percentToInclude="100" />

<Client id="11" percentToInclude="100" />

<Client id="13" percentToInclude="100" />

<Client id="14" percentToInclude="100" />

<Client id="17" percentToInclude="100" />

<Client id="19" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_Demo_USD_EQ" path="C:\" clientId="0" currency="2" securityType="2" priorPeriodsToBuild="6" mapClientIdTo="3" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="2" percentToInclude="100" />

<Client id="6" percentToInclude="100" />

<Client id="7" percentToInclude="100" />

<Client id="11" percentToInclude="100" />

<Client id="13" percentToInclude="100" />

<Client id="14" percentToInclude="100" />

<Client id="17" percentToInclude="100" />

<Client id="19" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_Demo_USD_OPT" path="C:\" clientId="0" currency="2" securityType="3" priorPeriodsToBuild="6" mapClientIdTo="3" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="2" percentToInclude="100" />

<Client id="6" percentToInclude="100" />

<Client id="7" percentToInclude="100" />

<Client id="11" percentToInclude="100" />

<Client id="13" percentToInclude="100" />

<Client id="14" percentToInclude="100" />

<Client id="17" percentToInclude="100" />

<Client id="19" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_MorganStanley_USD_EQ" path="C:\" clientId="0" currency="2" securityType="2" priorPeriodsToBuild="6" mapClientIdTo="4" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="16" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_MorganStanley_USD_OPT" path="C:\" clientId="0" currency="2" securityType="3" priorPeriodsToBuild="6" mapClientIdTo="4" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="16" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

<BuildCalculatorDataMart dataMartName="MyTable_USA_USD_EQ" path="C:\" clientId="95" currency="2" securityType="2" priorPeriodsToBuild="6" mapClientIdTo="2" nnPrincipalWeightFactor="5.0" nnRepresentativeSample="100" nnMaximumAreaLimitFactor="1.0">

<Clients>

<Client id="2" percentToInclude="100" />

<Client id="6" percentToInclude="100" />

<Client id="7" percentToInclude="100" />

<Client id="11" percentToInclude="100" />

<Client id="13" percentToInclude="100" />

<Client id="14" percentToInclude="100" />

<Client id="17" percentToInclude="100" />

<Client id="19" percentToInclude="100" />

<Client id="10001" percentToInclude="100" />

</Clients>

</BuildCalculatorDataMart>

</section>

</sections>')

SELECT (CONVERT(VARCHAR(MAX),@XML))

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Rainmaker097 (8/20/2012)
Hi,
I am able to insert more than 8000 characters. but when I select after converting to varchar, it chops the values.
For example, if you run the blow, you will see the output is chopped after 8000 chars. Do you know why is this?
Thanks,
RM
[/code]

The data is in the table, but the client you are using (probably SSMS) with only display a maximum of 8000 characters.

Rainmaker097 SSC Eights! Points: 866 More actions · Answer 5

Hi,

if it is only going to display 8000 chars, what is the point of using a column that can have more than 8000 chars?

Am I "select"ing it wrong?

RM

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Rainmaker097 (8/20/2012)
Hi,
if it is only going to display 8000 chars, what is the point of using a column that can have more than 8000 chars?
Am I "select"ing it wrong?
RM

The data is there. The display in SSMS is limited to 8,000 characters. It is assumed that you are not going to use SSMS as your application that will consume this data. In other words, the full amount of text is available when you query the data but SSMS will not show it all.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jared SSC Guru Points: 61793 More actions · Answer 7

Rainmaker097 (8/20/2012)
Hi,
if it is only going to display 8000 chars, what is the point of using a column that can have more than 8000 chars?
Am I "select"ing it wrong?
RM

Are you really ever planning on "select"ing more than 8000 characters in a GUI window? I would ask what the point of that is... The point of the GUI displaying only 8000 characters is because a GUI is not part of an application and should not be used as such. The "point" of storing that data is that you are storing the actual data, not viewing it in a GUI.

Jared
CE - Microsoft

Rainmaker097 SSC Eights! Points: 866 More actions · Answer 8

I see what you mean thanks. These are config info for several applications and you are right, they don't have to displayed.

But is there any way I can display it or output this value, so I can prove to the developers it actually has more than 8000 chars in the column?

Jared SSC Guru Points: 61793 More actions · Answer 9

Rainmaker097 (8/20/2012)
I see what you mean thanks. These are config info for several applications and you are right, they don't have to displayed.
But is there any way I can display it or output this value, so I can prove to the developers it actually has more than 8000 chars in the column?

Have you tried exporting the data to file instead of just displaying it in the window? Not sure, but have you tried changing the output to text instead of grid?

Jared
CE - Microsoft

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

SQLKnowItAll (8/20/2012)
Rainmaker097 (8/20/2012)
I see what you mean thanks. These are config info for several applications and you are right, they don't have to displayed.
But is there any way I can display it or output this value, so I can prove to the developers it actually has more than 8000 chars in the column?
Have you tried exporting the data to file instead of just displaying it in the window? Not sure, but have you tried changing the output to text instead of grid?

It will still only show 8,000 characters. It is however trivial to export it to a file. Just click Results to file at the top. Open it with notepad or whatever and check the character count.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Rainmaker097 SSC Eights! Points: 866 More actions · Answer 11

Output to file or text doesn't work. But I will dig some more. Thanks a lot for explaining this to me. Much appreciated.

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Rainmaker097 (8/20/2012)
Output to file or text doesn't work. But I will dig some more. Thanks a lot for explaining this to me. Much appreciated.

What do you mean it doesn't work? Does it truncate the output when going to file? I have never tried that. You could always throw together a quick app to display it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 13

If you just need to show the amount of data, without worrying about what it actually has in it, you can use Len() or DataLength() to get that.

If you need to show the whole value, would it be okay to show it piecemeal?

select substring(MyBigColumn, 1, 8000), substring(MyBigColumn, 8001, 16000), ...

That'll show it 8k characters at a time.

You can append those on top of each other instead of side-by-side if you want.

USE ProofOfConcept;

GO

CREATE TABLE dbo.MyBigText (BigText VARCHAR(MAX));

GO

INSERT INTO dbo.MyBigText

(BigText)

VALUES (REPLICATE('1', 8000));

UPDATE dbo.MyBigText

SET BigText = BigText + CAST(REPLICATE('2', 8000) AS VARCHAR(MAX));

UPDATE dbo.MyBigText

SET BigText = BigText + CAST(REPLICATE('3', 8000) AS VARCHAR(MAX));

SELECT *

FROM Common.dbo.Numbers

CROSS APPLY (SELECT SUBSTRING(BigText, Number * 8000 + 1, (Number + 1) * 8000) AS Piece

FROM dbo.MyBigText) AS BT

WHERE Number BETWEEN 0 AND 3;

I have a Numbers table that goes from 0 to 10,000. Just a table of integer values. Useful for things like this.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon