The Heap™ – Consultancy ©®

DECLARE @T AS table (c1 sql_variant NULL);

INSERT @T
	(c1)
VALUES
	(CONVERT(sql_variant, 1)),
	(CONVERT(sql_variant, 'banana')),
	(CONVERT(sql_variant, $50));

SELECT * FROM @T;

JNK

ah ok

Andriy M

12:48 PM

sqlfiddle.com/#!3/aa9377/1 and sqlfiddle.com/#!3/aa9377/2

Paul White

If you wanted a particular base type inside the variant, as it were, you could nest another CONVERT.

@AndriyM Yeah. The second one is a little 'sloppy' because it relies of type precedence and an implicit conversion.

JNK

I was trying to use CAST

Andriy M

@PaulWhite I see, explicit conversion at all times is best, thanks.

Paul White

@JNK Well CAST would work too.

JNK

I must have had another issue, I was getting a weird syntax error

for an INSERT it looks like it only cares about the first row being cast as a sql_variant

Kermit

12:52 PM

boom

0

A: Export vertica query result to csv file

You're using a syntax that's not valid for Vertica. Please read the documentation. Vertica only supports exports locally. The client your using needs to have support for a file export. For example, in Excel, you could set up the ODBC connection and import the data.

JNK

DECLARE @T AS table (c1 sql_variant NULL);

INSERT @T
(c1)
VALUES
(CONVERT(sql_variant, 1)),
('banana'),
($50),
(1),
('foobarbaz');

SELECT * FROM @T;

Paul White

@JNK Yes but multi-row VALUES is just a UNION ALL of expressions, so type is determined initially by the first row. It's more typing to be explicit, but IMO it is better.

JNK

oh I'm fine with it

I'm just trying to find the point at which it breaks

bluefeet

@Kermit -1 BOOM

:)

Andriy M

So basically the rule is the VALUES should work without the INSERT, for instance in SELECT * FROM (VALUES ...)

I guess

Paul White

12:59 PM

Struggling with SQLFiddle today. Anyway: sqlfiddle.com/#!6/a2178/2

Kermit

@bluefeet you're funny

Zane

Yo.

Kermit

bluefeet

1:23 PM

@Kermit I try but mostly I fail

Andriy M

1:43 PM

Does UNION ALL guarantee the order of results? I mean, if we take a simple statement of SELECT * FROM a UNION ALL SELECT * FROM b, will rows from a always precede rows from b in SQL Server? This is not about whether rows from a or rows from b will always be sorted the same, it's just about the order of the groups of rows.

Zane

I don't know that I would trust that it's all of the time.

Andriy M

I asked someone recently about this, because they said in their answer that the order would be the same. They replied it's in the standard but admitted it was only their observation with regard to SQL Server specifically (as well as the many other database systems they worked with).

Max Vernon

@AndriyM Why wonder? Add a column to the result set that shows which result set the row comes from, then order by that. Voila.

ypercube

@AndriyM I don't think it's in the standard.

Andriy M

@MaxVernon Yes, that's what I would go with. I'm asking because I was questioning someone's claim that the order would be preserved and was told it's in the standard.

but possibly not in the SQL Server manual.

ypercube

1:49 PM

@AndriyM I'm guessing here but trying to force a parallel plan might get the rows in mixed order.

Transcript for

The Heap™ – Consultancy ©®