1

Is there a difference between the two T-SQL commands below ?

  1. Column name with quotes

    SELECT SUM(DATALENGTH('ColumnName')) / 1048576.0 AS ColumnName 
    FROM DatabaseName.Schema.Tablename
    
  2. Column name without quotes

    SELECT SUM(DATALENGTH(ColumnName)) / 1048576.0 AS ColumnName  
    FROM DatabaseName.schema.Tablename
    
1

2 Answers 2

6

Yes, the first variant will give you the data length of the text 'ColumnName' whereas the second will most likely get you nearer the results you are looking for (which I assume is the total length of all data within the given column).

In SQL Server if you are looking for quoted identifiers you can use the standard " or square brackets. You only need to quote names that are reserved keywords like TABLE, INDEX etc... but as a general code-style tip I recommend picking a quoting scheme and using it consistently within your code. It makes it easier for other developers to read and pick up your scripts.

SQL Server: SET QUOTED_IDENTIFIER

2

The text enclosure causes your function to evaluate the text "ColumnName", where the second will evaluate the values in the hypothetical ColumnName. For example, the code below returns two table names: sysdac_history_internal and sysdac_instances_internal, and the result of your calculation on those two strings.

select SUM(DATALENGTH(Name)) / 1048576.0 AS DataLength_ex,name 
from master.sys.tables
group by name

SUM(DATALENGTH('sysdac_history_internal')) / 1048576.0 = 0.000043869

SUM(DATALENGTH('sysdac_instances_internal')) / 1048576.0 = 0.000047683

Whereas with the quotes, the query still returns two results, with the names of those two tables, but the calculation for each is actually based off the text 'Name'.

select SUM(DATALENGTH('Name')) / 1048576.0 AS ColumnName, name 
from master.sys.tables
group by name

SUM(DATALENGTH('name')) / 1048576.0 = 0.000003814

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.