Azure 数据仓库中的表变量 [英] Table Variables in Azure Data Warehouse

问题描述

在 SQL Server 数据库中，可以像这样使用表变量:

In a SQL Server database, one can use table variables like this:

declare @table as table (a int)

在 Azure 数据仓库中，这会引发错误.

In an Azure Data Warehouse, that throws an error.

第 1 行解析错误，第 19 列:'table' 附近的语法不正确

Parse error at line: 1, column: 19: Incorrect syntax near 'table'

在 Azure 数据仓库中，您可以使用临时表:

In an Azure Data Warehouse, you can use temporary tables:

create table #table (a int)

但不是在函数内部.

消息 2772，级别 16，状态 1，第 6 行无法访问临时表从函数内部.

Msg 2772, Level 16, State 1, Line 6 Cannot access temporary tables from within a function.

这份来自微软的文档说，

◦必须分两步声明(而不是内联):◾CREATE TYPEmy_type AS TABLE ...;, 然后 ◾DECLARE @mytablevariable my_type;.

◦Must be declared in two steps (rather than inline): ◾CREATE TYPE my_type AS TABLE ...; , then ◾DECLARE @mytablevariable my_type;.

但是当我尝试这个时:

create type t as table (a int);
drop type t;

我明白了:

Msg 103010，级别 16，状态 1，第 1 行解析错误:第 1 行，列:8:'type' 附近的语法不正确.

Msg 103010, Level 16, State 1, Line 1 Parse error at line: 1, column: 8: Incorrect syntax near 'type'.

我的目标是在 Azure 数据仓库中创建一个使用临时表的函数.可以实现吗?

My objective is to have a function in an Azure Data Warehouse which uses a temporary table. Is it achievable?

编辑从这里开始

请注意，我不是在寻找其他方法来创建一个特定的函数.我实际上已经做到了并继续前进.我是一名资深程序员，但也是 Azure 数据仓库新手.我想知道是否可以在 Azure 数据仓库功能中加入一些临时表的概念.

Note that I am not looking for other ways to create one specific function. I have actually done that and moved on. I'm a veteran programmer but an Azure Data Warehouse rookie. I want to know if it's possible to incorporate some concept of temporary tables in an Azure Data Warehouse function.

推荐答案

好的，我相信这就是您所追求的.

Ok, I believe this is what you are after.

首先，它使用表值函数，它比标量或多语句表值函数快得多.

Firstly, this uses a Table Value Function, which are significantly faster than Scalar or Multi-statement Table value Functions.

其次，表变量或临时表没有用处，只有一些很好的奇怪字符串操作、一些数学和 CTE.绝对没有昂贵的 WHILE 循环.

Secondly, there was no use for a Table Variable, or Temporary Table, just some good odd string manipulation, a bit of maths, and a CTE. Definitely no expensive WHILE loop.

我已经针对链接中的示例对此进行了测试，它们都返回了预期值.

I've tested this against the examples in the link, and they all return the expected values.

USE Sandbox;
GO
CREATE FUNCTION ValidateHealthNumber (@HealthNumber varchar(10))
RETURNS TABLE
AS
RETURN

    WITH Doubles AS(
        SELECT CONVERT(tinyint,SUBSTRING(V.HN,O.P,1)) AS HNDigit,
               CONVERT(tinyint,SUBSTRING(V.HN,O.P,1)) * CASE WHEN O.P % 2 = 0 THEN 1 ELSE 2 END ToAdd
        FROM (VALUES(@HealthNumber)) V(HN)
              CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) O(P)),
    Parts AS (
        SELECT CONVERT(tinyint,SUBSTRING(CONVERT(varchar(2),ToAdd),1,1)) AS FirstDigit, --We know that the highest value can be 18 (2*9)
               CONVERT(tinyint,SUBSTRING(CONVERT(varchar(2),ToAdd),2,1)) AS SecondDigit --so no need for more than 2 digits.
        FROM Doubles)
    SELECT CASE RIGHT(@HealthNumber, 1) WHEN 10 - RIGHT(SUM(FirstDigit + SecondDigit),1) THEN 1 ELSE 0 END AS IsValid
    FROM Parts;

GO

CREATE TABLE #Sample(HealthNumber varchar(10));
INSERT INTO #Sample
VALUES ('9876543217'), --Sample
       ('5322369835'), --Valid 
       ('7089771195'), --Valid
       ('8108876957'), --Valid
       ('4395667779'), --Valid
       ('6983806917'), --Valid
       ('2790412845'), --not Valid
       ('5762696912'); --not Valid

SELECT *
FROM #Sample S
     CROSS APPLY ValidateHealthNumber(HealthNumber) VHN;
GO
DROP TABLE #Sample
DROP FUNCTION ValidateHealthNumber;

如果你不明白这些，请尽管问.

If you don't understand any of this, please do ask.

这篇关于Azure 数据仓库中的表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！