1

In SSMS, if I have a query like:

select * from dbo.students where name = 'jim'

Or 

select 1, 1000

What's the data type of 'jim', 1 and 1000? They could be varchar, nvarchar, char, nchar, tinyint, smallint, int or bigint. I want to know how to identify their types. Thanks in advance.

Just a learner
  • 26,690
  • 50
  • 155
  • 234
  • Look in information_schema.columns or sys.columns I can't remember which one. – S3S Dec 04 '16 at 22:04
  • I want to know the data type of the literals, not some table columns. – Just a learner Dec 04 '16 at 22:04
  • I have to ask, where are you going with this? The data type is driven by the data type of the column in the table. Not the literal. – R. Richards Dec 04 '16 at 22:05
  • Then use variant property. SQL_VARIANT_PROPERTY – S3S Dec 04 '16 at 22:05
  • @R.Richards, what if no table involved like my second example? – Just a learner Dec 04 '16 at 22:06
  • 1
    SQL Server will use the set order until one fits. https://msdn.microsoft.com/en-us/library/ms190309.aspx – S3S Dec 04 '16 at 22:07
  • Okay! Got it. Makes much more sense. I have always made assumptions about datatype literals. `1` -> `int`; `'string'` -> `varchar`. – R. Richards Dec 04 '16 at 22:08
  • 1
    @scsimon Data type precedence is about what happens when comparing two items of different types, not how literals are interpreted. – Martin Smith Dec 04 '16 at 22:21
  • 1
    Some examples of different formats here and how to see the types http://stackoverflow.com/a/7428636/73226 – Martin Smith Dec 04 '16 at 22:23
  • @MartinSmith so you are saying the same precedence isn't used for interpretation? Then what precedence is? – S3S Dec 04 '16 at 22:23
  • @scsimon no it isn't used for interpretation. SQL variant is top so pretty much everything would be interpreted as that if that was the case. And the string `'123'` is a varchar literal despite that being near the bottom with lots of other stuff it could cast to above it. It determines what happens if you compare different types. So `varcharcolumn = N'foo'` means the column gets converted to nvarchar to match the literal. – Martin Smith Dec 04 '16 at 22:27

0 Answers0