Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting spaces with datalength


Counting spaces with datalength

Author
Message
benkoskysa
benkoskysa
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 125
With the given SQL, @temp is an empty table, therefore count(1) will always return 0.
Peter Trast
Peter Trast
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 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
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 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.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
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.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2702 Visits: 601
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Not my lucky day.



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

Group: General Forum Members
Points: 10714 Visits: 12017
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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