0

I’m receiving a truly bizarre response from a query in SSMS this afternoon, and I’m not sure how to begin troubleshooting.

I have a very wide table loaded into my data warehouse from a pipe delimited file. ALL of the values are loaded as variously sized varchar columns.

The query I’m running is this:

select 14C_CLINIC_VISIT_NUMBER, 13D_CLINIC_COPAY_AMOUNT from [Table]

The results I’m getting back makes no sense:

C_CLINIC_VISIT_NUMBER D_CLINIC_COPAY_AMOUNT
14 13
14 13
14 13
14 13

...and so on.

The expected result is a text string for each column. In this particular case they should all read ‘N/A’. Also note that the headers being returned are not the correct column names. The numeric characters at the head of each column name have been removed and seem to be returned in the result set.

I’ve confirmed that they are N/A in the pipe delimited file. They are set to D_STR with a length of 9 in SSIS, where they’re loaded in from. The table itself has the field as a varchar(9) datatype. I don’t have DBA level access and I’m unsure of how I could audit the values in the resulting SQL table outside of simply querying, which is what I’m doing.

Has anyone ever seen anything like this before? I’m unsure of where to even troubleshoot.

Salman A
  • 262,204
  • 82
  • 430
  • 521
B. Brightside
  • 1
  • 1
  • It appears that SQL Server assumes a token split after the "14" and "13". You are selecting the constant 14 aliased as C_CLINIC_VISIT_NUMBER, and the constant 13 aliased as D_CLINIC_COPAY_AMOUNT, repeated for how ever many records are selected out of [Table]. No values from [Table} are present in the results. If you actually have columns named 14C_CLINIC_VISIT_NUMBER and 13D_CLINIC_COPAY_AMOUNT, you need to put the names in brackets like `select [14C_CLINIC_VISIT_NUMBER], [13D_CLINIC_COPAY_AMOUNT] from [Table]` – T N Mar 08 '22 at 20:38
  • @TN It works that way without a missing space, too. `SELECT 12huh FROM sys.databases;` it inspect the leading integer and assumes you meant `SELECT 12 AS huh FROM sys.databases;` - this is why we tend to avoid using column names that defy the [rules for identifiers](https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver15#rules-for-regular-identifiers). – Aaron Bertrand Mar 08 '22 at 20:40
  • @AaronBertrand - Just discovered that and have revised my comment. (Somewhat surprising behavior. I would have expected a syntax error.) – T N Mar 08 '22 at 20:43
  • @AaronBertrand we certainly do but sometimes the business resources on your project give you a great big data file full of them and with a due date weeks away on an incomplete spec we just don’t argue. For myself, I simply forgot that leading numerals were an issue, temporarily! – B. Brightside Mar 08 '22 at 21:15
  • As an aside it's interesting to note that even the syntax parser on this site has highlighted the numbers in your identifiers separately. – Aaron Bertrand Mar 09 '22 at 12:35
  • @AaronBertrand if only SSMS were as considerate – B. Brightside Mar 09 '22 at 18:37
  • You've got an answer. At some point you've got to let go about how unintuitive and inconsiderate it is ... and actually I would suggest "the business" is more inconsiderate than SSMS - SSMS just assumes you've read the docs, the business doesn't seem to know there even are docs :-) – Aaron Bertrand Mar 09 '22 at 18:38
  • @AaronBertrand I let it go yesterday. :D I am amused though, that my question has been edited like 4 times despite being pretty much dead for the last 16 hours, and most heavily edited by the power user who gave the snarkiest responses. Stack is more full of hall monitors than Reddit. Reminded me why I don’t use it. – B. Brightside Mar 09 '22 at 19:06
  • While I think it's a stretch to call that "heavily edited" I think it is good that people are more concerned about quality than making sure nobody gets fee-fees hurt. It's one of the reasons this site stands out above all other chatty forums. – Aaron Bertrand Mar 09 '22 at 19:36

1 Answers1

2

It is not bizzarre. The string 14C_CLINIC_VISIT_NUMBER is parsed as two tokens - a number and an identifier*. So your query is processed as:

SELECT 14 AS C_CLINIC_VISIT_NUMBER, 13 AS D_CLINIC_COPAY_AMOUNT
FROM ...

You must quote the identifiers for correct parsing:

SELECT [14C_CLINIC_VISIT_NUMBER], [13D_CLINIC_COPAY_AMOUNT]
FROM ...

* In SQL Server the identifiers begin with a-z, _, @ and # (ref). The parser sees digits so assumes a number literal followed by an identifier.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Respectfully, that’s pretty bizarre. I’d expect a syntax error before that kind of behavior quite honestly. – B. Brightside Mar 08 '22 at 21:18
  • I've seen enough to not call it bizarre. Anyway, go-to astexplorer.net, choose sql, paste `select 1a from t` and check the syntax tree. It'll tell you the same thing... A decimal 1 and an alias a. – Salman A Mar 08 '22 at 21:36
  • I’m not arguing that the parser isn’t doing as you say. I’m arguing that it’s not reasonable for a user to expect that behavior. It’s entirely unintuitive. – B. Brightside Mar 08 '22 at 21:39
  • @B.Brightside That's definitely not the most unintuitive thing about SQL Server, but it does look strange at first glance. It's quite clear from the documentation either way that regular identifiers cannot start with a number and must be quoted. Any time you have weird identifiers you should question what the parser might do with them. A case to be made for more normal identifiers I think – Charlieface Mar 08 '22 at 22:20
  • @Charlieface I agree. These identifiers were, unfortunately, thrust upon me. – B. Brightside Mar 08 '22 at 22:24