Varchar(max) problem in SQL server 2005

Question

Post reply

Varchar(max) problem in SQL server 2005

Viewing 7 posts - 16 through 21 (of 21 total)

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

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 1

Hi Dipak,

Check out the below link

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Good article... thanks for posting it. Just about anything by Adam is worth a look-see.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

And the example Jeff provided only handles a variable a couple of times.

I've added this bit, to get the data actualy in a couple of tables and

the effect only enlarges :w00t:

btw dbcc ind also has nice info ...

--== added by alzdba - begin

create table T_varchar(idnr int identity (1,1) not null primary key,

mystring varchar(8000) not null )

create table T_varcharMax(idnr int identity (1,1) not null primary key,

mystring varchar(max) not null )

create table T_varcharMaxOffRow(idnr int identity (1,1) not null primary key,

mystring varchar(max) not null )

EXEC sp_tableoption 'T_varcharMaxOffRow', 'large value types out of row', '1'

/*

1 = varchar(max), nvarchar(max), varbinary(max) and xml columns in the table are stored out of row,

with a 16-byte pointer to the root.

0 = varchar(max), nvarchar(max), varbinary(max) and xml values are stored directly in the data row,

up to a limit of 8000 bytes and as long as the value can fit in the record.

If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row

in the LOB storage space.

*/

insert into T_varchar (mystring) values (@CsvVARCHAR)

insert into T_varcharMax (mystring) values (@CsvVARCHAR)

insert into T_varcharMaxOffRow (mystring) values (@CsvVARCHAR)

--===== Split the VARCHAR variable and measure the time it takes

PRINT 'Splitting the T_varchar ...'

SET STATISTICS TIME ON

SELECT @BitBucketV = SUBSTRING(','+mystring, t.N+1, CHARINDEX(',', mystring+',', t.N+1)-t.N)

FROM #Tally t

, T_varchar v

WHERE SUBSTRING(','+mystring, t.N, 1) = ','

AND t.N < LEN(','+mystring)

and v.idnr = 1

SET STATISTICS TIME OFF

PRINT REPLICATE('=',100)

--===== Split the MAX variable and measure the time it takes

PRINT 'Splitting the T_varcharMax ...'

SET STATISTICS TIME ON

SELECT @BitBucketM = SUBSTRING(','+mystring, t.N+1, CHARINDEX(',', mystring+',', t.N+1)-t.N)

FROM #Tally t

, T_varcharMax v

WHERE SUBSTRING(','+mystring, t.N, 1) = ','

AND t.N < LEN(','+mystring)

and v.idnr = 1

SET STATISTICS TIME OFF

--===== Split the MAX variable and measure the time it takes

PRINT 'Splitting the T_varcharMaxOffRow ...'

SET STATISTICS TIME ON

SELECT @BitBucketM = SUBSTRING(','+mystring, t.N+1, CHARINDEX(',', mystring+',', t.N+1)-t.N)

FROM #Tally t

, T_varcharMaxOffRow v

WHERE SUBSTRING(','+mystring, t.N, 1) = ','

AND t.N < LEN(','+mystring)

and v.idnr = 1

SET STATISTICS TIME OFF

PRINT REPLICATE('=',100)

go

-- the still not documented in bol dbcc option IND

DBCC IND (0, 'T_varchar', 1);

DBCC IND (0, 'T_varcharMax', 1);

DBCC IND (0, 'T_varcharMaxOffRow', 1);

drop table T_varchar

drop table T_varcharMax

drop table T_varcharMaxOffRow

The results of the jury :

====================================================================================================
Splitting the VARCHAR variable...
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
====================================================================================================
Splitting the MAX variable...
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 22 ms.
====================================================================================================
Splitting the T_varchar ...
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 12 ms.
====================================================================================================
Splitting the T_varcharMax ...
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 308 ms.
Splitting the T_varcharMaxOffRow ...
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 163 ms.
====================================================================================================
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
3 38339 NULL NULL 1461580245 1 1 72057594041204736 In-row data 10 NULL 0 0 0 0
3 38338 3 38339 1461580245 1 1 72057594041204736 In-row data 1 0 0 0 0 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
3 38341 NULL NULL 1493580359 1 1 72057594041270272 In-row data 10 NULL 0 0 0 0
3 38340 3 38341 1493580359 1 1 72057594041270272 In-row data 1 0 0 0 0 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
3 38458 NULL NULL 1525580473 1 1 72057594041335808 In-row data 10 NULL 0 0 0 0
3 38457 3 38458 1525580473 1 1 72057594041335808 In-row data 1 0 0 0 0 0
3 38343 NULL NULL 1525580473 1 1 72057594041335808 LOB data 10 NULL 0 0 0 0
3 38342 3 38343 1525580473 1 1 72057594041335808 LOB data 3 0 0 0 0 0
3 38456 3 38343 1525580473 1 1 72057594041335808 LOB data 3 0 0 0 0 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

It appears that all the variable declarations and presets are missing from the code... could you include those, Johan?

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

I'm sorry I've forgotten to mention just to put my code

right before the housekeeping section (last line) of Jeffs.

:blush:

-- put my code overhere

--===== Housekeeping

DROP TABLE #Tally

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 6

Thanks for the link !

The more you are prepared, the less you need it.

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 7

DBCC IND

this link was referenced earlier, but if you are looking for info on the DBCC IND command, this one has it.

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/13/More-undocumented-fun_3A00_-DBCC-IND_2C00_-DBCC-PAGE_2C00_-and-off_2D00_row-columns.aspx

The more you are prepared, the less you need it.