0

I have put a question here

Select the field as Distinct having data type as Text. Sql Server

and I got two replies one is saying to use 

SELECT DISTINCT CONVERT(VARCHAR(MAX), Subjects) FROM T1

and the other is saying to use

select distinct cast(Subjects as varchar) from T1

I tried both and they are working fine. I am wondering whats the difference between both.

Thanks.

Community
  • 1
  • 1
Kamran
  • 4,010
  • 14
  • 60
  • 112
  • 1
    The second one casts to `varchar(30)` rather than `max` but that is just because no length is specified. Not due to `CAST` vs `CONVERT` – Martin Smith Dec 10 '13 at 12:40
  • They are only "working fine" because you didn't compare them on any strings greater than 30 characters. Did you see [my comment on the answer you accepted](http://stackoverflow.com/questions/20494116/select-the-field-as-distinct-having-data-type-as-text-sql-server#comment30632399_20494273)? – Aaron Bertrand Dec 10 '13 at 13:24

3 Answers3

1

No, performance is not a issue.(As far as your question describes both can be used and there is no difference between them but as martin said statement using CAST will cast to varchar(30) as it is it's default length)

CAST is an ANSI SQL-92

CONVERT is specific to SQL Server

CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers.

Try this 

SELECT DISTINCT CONVERT(VARCHAR(MAX), GETDATE(),108)
--and the other is saying to use

select distinct cast(GETDATE() as VARCHAR(MAX))
Suraj Singh
  • 4,041
  • 1
  • 21
  • 36
0

Performance wise, no difference. Just that CAST is ANSI-SQL(complying to the standards) so the query will be portable between server technologies.

Convert is specific to sql server but provides a richer set of options to convert.

Suggest using cast for portability unless you have to do otherwise.

King Regards, Sumit

Sumit Chaudhary
  • 118
  • 6
0

CONVERT more flexible when converting between date and time values, fractional numbers, and monetary signifiers. CAST is the more ANSI-standard.

thats means that while the cast format is set to standard u can use a lot of formats in convert for example

convert ( datetime , get date () , **format**)

in the format u can enter the number of the format.

101 mm/dd/yyyy

102 yyyy.mm.dd ANSI date with century

103 dd/mm/yyyy

104 dd.mm.yyyy

u can google for more formats.

there is no diff preformance wise or any other aspect

DnL
  • 141
  • 1
  • 2
  • 17