Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Counting spaces with datalength Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 33, Visits: 117
With the given SQL, @temp is an empty table, therefore count(1) will always return 0.
Post #875947
Posted Wednesday, March 3, 2010 8:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, 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
Post #876053
Posted Wednesday, March 3, 2010 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 33, Visits: 117
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

Post #876097
Posted Wednesday, March 3, 2010 11:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, November 2, 2014 10:02 PM
Points: 506, Visits: 1,695
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.
Post #876579
Posted Thursday, March 4, 2010 10:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #876978
Posted Monday, March 15, 2010 3:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 2:56 AM
Points: 2,624, Visits: 581
Thanks for your QotD! I answered too quick and didn't think of the difference between Len and DATALENGT().

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #882775
Posted Tuesday, March 30, 2010 8:26 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
Not my lucky day.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892827
Posted Wednesday, March 31, 2010 4:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 7,855, Visits: 9,603
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
?


Tom
Post #893618
Posted Wednesday, March 31, 2010 6:05 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
?

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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893674
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse