teaser datalength

[SQLDenis]

declare @Temp Table(data text)
Insert Into @Temp Values(replicate('1',5000))
select datalength(data) from @temp
go

this returns 5000


now if we replicate 50000 what does this return?

declare @Temp Table(data text)
Insert Into @Temp Values(replicate('1',50000))
select datalength(data) from @temp
go

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nice95gle]

Spoiler

16

Well Done is better than well said
- Ben Franklin

 

[SQLDenis]

No

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nice95gle]

if I look up what datalength means is that cheating

Well Done is better than well said
- Ben Franklin

 

[SQLDenis]

you can lookup datalength

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nice95gle]

I haven't worked with datalength in a while so I misunderstood the meaning. But I think I got it. If I'm correct I will say why I put the first answer of 16.

Spoiler

5

Well Done is better than well said
- Ben Franklin

 

[tran008]

Spoiler

hmmm...50000
Oh wait...declare @Temp Table(data text)
according to BOL 'Microsoft SQL Server 2005 stores character strings longer than 8,000 characters and binary data longer than 8,000 bytes in special data types named text and image'.

so answer is 8000?

 

[nice95gle]

I'm dumb

Well Done is better than well said
- Ben Franklin

 

[SQLDenis]

tran008, fair enough, let's make it a 2005 teaser

declare @Temp Table(data varchar(max))
Insert Into @Temp Values(replicate('1',50000))
select datalength(data) from @temp
go

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Spoiler

Replicate only returns 8000 characters, do Replicate('1',50000) will only return a string 8000 characters long.

The answer is 8000.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

George what about this?

declare @Temp Table(data text)
Insert Into @Temp Values(replicate('1',8000) + 'abcde')
select datalength(data) from @temp
go

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Denis,

Spoiler

Same thing. Replicate returns a varchar (with 8000 as the max). You then add a string, which converts to varchar. varchar concatenated with varchar still maxes out at 8000 characters.

Answer: 8000

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

I know, I know, I did this tease a long time ago with replicate and varchar(max)

the 2005 version has to be like this

Spoiler

declare @Temp Table(data text)
Insert Into @Temp Values(replicate(convert(varchar(max),'1'),8000) + 'abcde')
select datalength(data) from @temp
go

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

I'm confused.

 

[SQLDenis]

>>I'm confused.

I succeeded

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

What's the thing that you were trying to draw out for us?

 

[SQLDenis]

did you run the first 2 queries? as you can see the bottom one returns the same length as the first one. the problem lies with something else

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nice95gle]

I get it.

When running the first 2 queries the value replicated is the char value of '1'. This will cause replicate to truncate at 8000 charaters (via BOL). If we were to explicitly set the value to be replicated to varchar(max) or nvarchar(max) we would see the full datalength return.

Something like this

select datalength(replicate('1',50000))
--Results = 8000
--because '1' is a string or char(1)

--but if I run this
Declare @data varchar(max)
set @data='1'
select datalength(replicate(@data,50000))

--Results = 50000
--Because @data is varchar(max)

Well Done is better than well said
- Ben Franklin

 

[ESquared]

Yes I did. I guess I knew the teaser already and just missed the point for that reason.

What about this? What datalength?

create table #Temp (data text)
declare @string varchar(8000)
set @string = replicate('1', 8000)
EXEC ('Insert Into #Temp Values(''' + @string + @string + @string + @string + @string + @string + @string + @string + ''')')
select datalength(data) from #temp
drop table #temp

 

[SQLDenis]

my guess is that it will be the full 8 * 8K, no way to check unless you run it because print will be truncated even if you specify 8192 characters

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions