What SQL Server datatypes come through as CLOB
20 November, 2012
Our Yellowfin views are using SQL Server views as their source.
What SQL Server datatypes will come through in Yellowfin as CLOB?
What I want is to prevent CLOB fields in Yellowfin. How do I need to CAST in the SQL Server views to make sure they don't come through as CLOB in Yellowfin?
What SQL Server datatypes will come through in Yellowfin as CLOB?
What I want is to prevent CLOB fields in Yellowfin. How do I need to CAST in the SQL Server views to make sure they don't come through as CLOB in Yellowfin?
Hi,
The SQL Server datatypes that will come through as CLOB in Yellowfin are:
varchar(max)
nvarchar(max)
text
ntext
xml
Casting in Transact-SQL is done by using the CAST function:
[code]CAST ( expression AS data_type [ ( length ) ] )[/code]
Though keep in mind that your data may be truncated, for example when casting from varchar(max) down to varchar.
I hope that helps with your casting, if there are any other questions please don't hesitate to contact us.
Regards,
David
The SQL Server datatypes that will come through as CLOB in Yellowfin are:
varchar(max)
nvarchar(max)
text
ntext
xml
Casting in Transact-SQL is done by using the CAST function:
[code]CAST ( expression AS data_type [ ( length ) ] )[/code]
Though keep in mind that your data may be truncated, for example when casting from varchar(max) down to varchar.
I hope that helps with your casting, if there are any other questions please don't hesitate to contact us.
Regards,
David
Thanks David.
What is the largest varchar and nvarchar can I use without it coming through in Yellowfin as CLOB? nVarChar(8000)? nVarChar(4000)? nVarChar(2000)?
What is the largest varchar and nvarchar can I use without it coming through in Yellowfin as CLOB? nVarChar(8000)? nVarChar(4000)? nVarChar(2000)?
(I meant to post that as a comment, not an answer.)
Hi,
I just tested it on my system and it's 8000 for VarChar and 4000 for nVarChar.
Regards,
David
I just tested it on my system and it's 8000 for VarChar and 4000 for nVarChar.
Regards,
David
Thanks!
For the record, that's because under the hood, SQL server has an 8 KB row/page-limit. When using (n)varchar(max), the storage engine will save as much data in the row as possible, but in case of overflow will chuck a 16-bit pointer in there. The difference (4k vs 8k) between the varchar and nvarchar data types is due to the fact a unicode character takes twice the storage space as a regular one.
David, one question for you. Are you guys planning on removing the DISTINCT keyword limitation when varchar(max) fields are in use? The very reason they were introduced was to replace TEXT data types and allow grouping/sorting on long text. Obviously, I can't talk for other DBMS, but as far as MSSQL is concerned, treating a varchar(max) as a clob in yellowFin imposes limitations on the end-user that are not actually enforced by the database server itself.
Cheers,
Christophe.
David, one question for you. Are you guys planning on removing the DISTINCT keyword limitation when varchar(max) fields are in use? The very reason they were introduced was to replace TEXT data types and allow grouping/sorting on long text. Obviously, I can't talk for other DBMS, but as far as MSSQL is concerned, treating a varchar(max) as a clob in yellowFin imposes limitations on the end-user that are not actually enforced by the database server itself.
Cheers,
Christophe.
Any idea, why they came up with this 8kb thingy?
Why not 16kb?
Why not 4kb?
Or is it because on early 32-bit systems, 1st-level-cache of most CPUs was 8kb?
Regards
Flo
Founder / www.lelala.de
Why not 16kb?
Why not 4kb?
Or is it because on early 32-bit systems, 1st-level-cache of most CPUs was 8kb?
Regards
Flo
Founder / www.lelala.de