SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting spaces with datalength


Counting spaces with datalength

Author
Message
benkoskysa
benkoskysa
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 125
With the given SQL, @temp is an empty table, therefore count(1) will always return 0.
Peter Trast
Peter Trast
Say Hey Kid
Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)

Group: General Forum Members
Points: 686 Visits: 655
benkoskysa (3/3/2010)
With the given SQL, @temp is an empty table, therefore count(1) will always return 0.


Not if you replace DATALENGTH with LEN in this query ;-) I have to try everything myself, take NOBODY's word for anything... yes, I am a trusting soul...

--Mileage may vary--

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
benkoskysa
benkoskysa
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 125
Good catch, thank you.
It turns out that @table was empty because I used a database where the sys.master_files table was empty; therefore, no rows for the cte...

Live and learn

:-)
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1803
benkoskysa (3/3/2010)
... I used a database where the sys.master_files table was empty...


Well, it can't be empty. At the very least you can expect 8 rows from a brand-spankin' new install, 2 rows for each of master, tempdb, msdb and model. It's likely you don't have the right permissions to this view.

Although, this suggests 2 things: 1) that there is now another reason that people who get a QotD wrong can claim it is invalid - coz the required permissions weren't specified; and 2) that many cut-n-pasters will get the correct answer for the wrong reason.


S.
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 1813
Fal (3/3/2010)
benkoskysa (3/3/2010)
... I used a database where the sys.master_files table was empty...


Well, it can't be empty. At the very least you can expect 8 rows from a brand-spankin' new install, 2 rows for each of master, tempdb, msdb and model. It's likely you don't have the right permissions to this view.

Although, this suggests 2 things: 1) that there is now another reason that people who get a QotD wrong can claim it is invalid - coz the required permissions weren't specified; and 2) that many cut-n-pasters will get the correct answer for the wrong reason.


S.

I just should have opted to use a different view to populate the table with 10 records. I chose the sys.master_files for 3 reasons:

1. It has 2 rows for each of the visible system databases (no rows are visible for resource), making it 8 in total
2. Usually we have at least one non-system database (if we have a job :-)), which adds 2 more records, making it at least 10 in total, which satisfies the test requirement to have 10 records needed for QoD
3. The total number of records is still pretty small so the cte part is not heavy.

I guess the better choice would be to opt for something not heavy but less restrictive, for example

   select
top 10
row_number() over (order by table_name) record_number
from information_schema.tables



for the cte part. Thank you for pointing out the flaws in my question. I will try to be more accurate next time.

Oleg
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2826 Visits: 605
Thanks for your QotD! I answered too quick and didn't think of the difference between Len and DATALENGT().

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15498 Visits: 11354
Not my lucky day.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14166 Visits: 12197
Paul White NZ (3/30/2010)
Not my lucky day.

That sounds as if you got it wrong. I can't resist asking which error did you make:
a) 11 is an even number; or
b) datalength is the same as len
Hehe?

Tom

Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15498 Visits: 11354
Tom.Thomson (3/31/2010)
Paul White NZ (3/30/2010)
Not my lucky day.

That sounds as if you got it wrong. I can't resist asking which error did you make:
a) 11 is an even number; or
b) datalength is the same as len
Hehe?

Neither really - I just looked at it quickly, decided the answer was probably 'one', and went for it.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search