# MS SQL GUID type string represntation

**URL:** <https://community.dremio.com/t/ms-sql-guid-type-string-represntation/12577>\
**Category:** Uncategorized\
**Created:** [December 6, 2024, 5:57pm UTC](https://community.dremio.com/t/ms-sql-guid-type-string-represntation/12577 "2024-12-06T17:57:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vladislav-stolyarov](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vladislav-stolyarov/32/2351_2.png) [@vladislav-stolyarov](https://community.dremio.com/u/vladislav-stolyarov)\
**Post date:** [December 6, 2024, 5:57pm UTC](https://community.dremio.com/t/ms-sql-guid-type-string-represntation/12577/1 "2024-12-06T17:57:43Z")

</div>

Hello.

We use ms sql server 12 and have GUID(16 byte) type columns there that are obviously treated as binary columns by dremio. We want to use string representatin of those columns in dremio.  
The problem is that Dremio can not convert it to proper string using` CAST(GUID as varchar)` BUT only when CAST function is pushed down to sql server query. In this case it outputs proper guid:

```auto
select CAST(GUID as varchar)
FROM MsSqlTable WHERE ID = 8336754

```

Outputs: `56B15BAF-CF49-4708-81CF-F6EF5B9B437C`

Dremio also has its own convertion function that is executed on dremio side `CONVERT_FROM(GUID , 'UTF8')`. It outputs such text as it has no clue about guid binary format:

```auto
select CONVERT_FROM(GUID , 'UTF8')
FROM MsSqlTable WHERE ID = 8336754

```

Outputs: `�[�VI�G����[�C|`

My problem is that unfortunatelly Dremio does not push down CAST function to sql always, but quite often decides to pull GUID as binary column and try to convert it locally as an optimization. For example such query will output unexpectedly two broken text values not just second one.

```auto
select CAST(GUID as varchar), CONVERT_FROM(GUID , 'UTF8')
FROM MsSqlTable WHERE ID = 8336754

```

Outputs `�[�VI�G����[�C| ; �[�VI�G����[�C|`

How can we safely cast guid to proper text representation? Is there dremio native workaround or a way to force CAST function propogate down always to sql?

Build  
25.0.7-202407181421320869-2632b04f  
Edition  
AWS Edition (activated)

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [December 9, 2024, 1:28am UTC](https://community.dremio.com/t/ms-sql-guid-type-string-represntation/12577/2 "2024-12-09T01:28:03Z")

</div>

@vladislav-stolyarov Can you try External query instead?

> **[SELECT | Dremio Documentation](https://docs.dremio.com/current/reference/sql/commands/SELECT-statements/)**
>
> Dremio supports querying using standard SELECT statements. You can query tables and views that are contained in Dremio's space entity (including your home space) and in the sources that are connected to Dremio (such as Amazon S3 and Oracle).

Also, please send the profile of the incorrect query as we would like to review the push down, also the create table DDL from MySQL

---

<div class="post-metadata">

**Author:** ![vladislav-stolyarov](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/vladislav-stolyarov/32/2351_2.png) [@vladislav-stolyarov](https://community.dremio.com/u/vladislav-stolyarov)\
**Post date:** [December 9, 2024, 9:40am UTC](https://community.dremio.com/t/ms-sql-guid-type-string-represntation/12577/3 "2024-12-09T09:40:47Z")

</div>

Yes external query worked fine as expected.  
Btw extenral query is executed once? if used in CTE it will be called just once? Also it is not possible to pass dynamic sql to it (ie LATERAL), it has to be a literal?

Here are two profiles:  
[6c26af98-d1f3-4f26-a8de-151d14a47421.zip](https://community.dremio.com/uploads/short-url/pWxnjVUYITaRIA1GCFMQi8Qv1Yj.zip) (24,6 КБ)  
[7ea2f741-2bc3-487f-8c9b-40029d0a1371.zip](https://community.dremio.com/uploads/short-url/2uqZTQCWaMdsFZYemYSe2P0Ch82.zip) (24,1 КБ)

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [December 9, 2024, 4:18pm UTC](https://community.dremio.com/t/ms-sql-guid-type-string-represntation/12577/4 "2024-12-09T16:18:14Z")

</div>

@vladislav-stolyarov External query just pushes SQL to backend without parsing, so executed once. Current;y, Dremio does not support dynamic SQL