Len Function vs datalength function | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Len Function vs datalength function

Hi all,<br />Actually this is script,created to check the length of a SQL statement before and after the operations on it: <br /><br />————————————————————————<br />The code is:<br /><br />set nocount on<br />DECLARE @colName nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @colList varchar(max)<br />declare @tablename varchar(200)<br /><br />set @tablename = ‘tab1′<br />set @tablename = ‘tab2′<br /><br />SET @colList = ‘ ‘<br /><br />DECLARE sameColumnNamesCur CURSOR FOR<br /><br />SELECT scW.NAME<br />FROM SYSOBJECTS soW<br />JOIN SYSCOLUMNS scW ON soW.ID = scW.ID<br />JOIN SYSCOLUMNS scD ON scD.NAME = scW.NAME<br />JOIN SYSOBJECTS soD ON scD.ID = soD.ID<br />WHERE soD.NAME = @tablename<br />AND soW.NAME = @tablename<br /><br />– Loop thru all of the like columns<br />– between the data table and the work<br />– table and build a comma separated list<br />OPEN sameColumnNamesCur<br />FETCH sameColumnNamesCur INTO @colName<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />– IF @debug &gt; 0<br />– PRINT ‘@colName = ‘ + COALESCE(@colName, ‘NULL’)<br /><br />SET @colList = @colList + ‘[‘ + @colName + ‘], ‘<br /><br />FETCH sameColumnNamesCur INTO @colName<br />END<br /><br />CLOSE sameColumnNamesCur<br />DEALLOCATE sameColumnNamesCur<br /><br />print ‘After building the string'<br />print ‘len of statement string =’ + convert(varchar(10),len(@colList))<br />print ‘–&gt;’ + @colList + ‘&lt;–‘<br />print ”<br />print ‘After doing the substring/len function'<br /><b>set @colList = substring(@colList, 1, len(@colList) – 2)</b><br />print ‘len of statement string =’ + convert(varchar(10),len(@colList))<br />print ‘–&gt;’ + @colList + ‘&lt;–‘<br /><br /><br /><br /><br />(1) when i m executing on the local machine, i m getting this result (i.e.) 67<br /><br />Result:<br />After building the string<br />len of statement string =67<br />–&gt;[Config_Key], [Config_Code], [Config_Value], [Config_Description], &lt;–<br /><br />After doing the substring/len function<br />len of statement string =65<br />–&gt;[Config_Key], [Config_Code], [Config_Value], [Config_Description]&lt;–<br /><br />——————————————————————————–<br />(2) But, when i m executing on the server, i m getting differnt result (i.e.)lenght =66, there is missing bracked ],but when using datalength function(instead of len function) , we are getting length of string = 67. <br /><br />IS THERE ANY SERVER SETTING (OR) CHANGE IN THE CODE, I HAVE TO DO. Because,if we change len() to datalength(), then we have to change the logic of all Stored procedures.<br /><br />Result:<br />After building the string<br />len of statement string =66<br />–&gt;[Config_Key], [Config_Code], [Config_Value], [Config_Description], &lt;–<br /><br />After doing the substring/len function<br />len of statement string =64<br />–&gt;[Config_Key], [Config_Code], [Config_Value], [Config_Description&lt;–<br /><br />Thanks.<br /><br />Can anybody share his thoughts, regarding this.<br /><br />//Faisal<br />
You initialize the @colList variable with a blank space: SET @colList = ‘ ‘ When you concatenate other strings after it, the blank is preserved, and it is included in the results for both LEN() and DATALENGTH(). Are you perhaps doing an LTRIM after determining the length, before doing the SUBSTRING? Or does the execution on the server perhaps return only a single column? We often see these loops to add columns, where the comma is added each time after the column name. Then, after the loop is finished, the final comma is eliminated. I always prefer to add the comma only when necessary: SET @colList = ”
…..setup the cursor…..
WHILE……
BEGIN
SET @colList = @colList + CASE WHEN LEN(@colList) > 0 THEN ‘, ‘ ELSE ” END ‘[‘ + @colName + ‘]’
…….
END
And by the way, there is no need to do this in a cursor: SET @colList = ‘ ‘ SELECT @colList =
@colList + CASE WHEN LEN(@colList) > 1 THEN ‘, ‘ ELSE ” END + ‘[‘ + scW.NAME + ‘]’
FROM SYSOBJECTS soW
JOIN SYSCOLUMNS scW ON soW.ID = scW.ID
JOIN SYSCOLUMNS scD ON scD.NAME = scW.NAME
JOIN SYSOBJECTS soD ON scD.ID = soD.ID
WHERE soD.NAME = @tablename
AND soW.NAME = @tablename

.. also see what are the SET options on that SQL instance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>