Martin Riddar's questions

我正在尝试创建一个存储过程，但它给了我错误消息
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in the EXCEPT operation.

问题是数据库和服务器排序规则都是SQL_Latin1_General_CP1_CI_AS，我不知道Latin1_General_BIN2排序规则是从哪里来的。

create procedure [ETL_1.4.0].update_valve_event_type
(
    @data nvarchar(max)
)
as
    declare @mismatch_table table(id int, [name] varchar(50))

    if isjson(@data) = 0
    begin
        ;throw 50000,'Input argument @data is invalid JSON.', 1
    end

    insert @mismatch_table (id, [name])
    select * from
    (
        select
            value as id,
            [key] as [name]
        from openjson(@data)
        except
        select
            id,
            [name]
        from enum.valve_event
    )data

    --clear mismatches that are deemed ok, e.g. spelling corrections
    delete from @mismatch_table
    where id = 10 and [name] = 'FEEDBACK_FROM_USER'

    if exists(select * from @mismatch_table)
    begin
        ;throw 50000,'Terminal version of enum for valve events does not match the EDW version', 1
    end
go

那么有人可以解释我所缺少的吗？
如果重要的话，这个数据库（以及与此相关的服务器）还没有上线，所以从技术上讲，我可以随心所欲地更改排序规则。

标题说“无关”，因为那是他们应该是的，但显然不是……

我有两个导致死锁的存储过程（log_sequence_event和log_se_event），我不知道为什么。它们由同一个 Python 应用程序在不同线程中通过 PYODBC 调用，并关闭自动提交。线程在提交之前最多执行 2000 次调用。
sequence并且se不要相互混淆，它们是完全无关的。

这两个过程都对各自的表进行了插入（fact.sequence和fact.se），并且每个表都有一个单独的索引视图（v_daily_fact_sequence和v_daily_fact_se）。
该过程log_se_event在插入dim.se到. 在插入之前没有做任何事情。dim.networkfact.selog_sequence_event

如果我正确理解死锁报告，死锁会发生在log_sequence_event持有RangeS-U视图索引的锁v_daily_fact_se并log_se_event持有视图索引的锁，然后他们想要另一个索引的锁。这是（对我而言）wtf 部分，为什么要锁定索引，反之亦然？ 这是在我运行 SQL Server 2019 Developers Edition 的笔记本电脑上。RangeS-Uv_daily_fact_sequencelog_sequence_eventv_daily_fact_se

涉及的部分：

• 过程：

create procedure [ETL_1.7.1].log_sequence_event
(
    @terminal_id int,
    @terminal_row_id bigint,
    @timestamp datetime2(3),
    @id int,
    @event int,
    @energy int,
    @duration int,
    @weight int = null,
    @no_of_dvs int,
    @average_fill_degree int --not used
)
as
    if (@event < 100)
        insert into fact.sequence
        (
            terminal_id,
            terminal_row_id,
            [timestamp],
            sequence_id,
            event_type_id,
            pred_kwh,
            pred_dv_count,
            pred_duration,
            pred_collected_weight,
            actual_kwh,
            actual_dv_count,
            actual_duration,
            actual_collected_weight
        )
        values
        (
            @terminal_id,--terminal_id,
            @terminal_row_id,--terminal_row_id,
            @timestamp,--[timestamp],
            @id,--sequence_id,
            @event,--event_type_id,
            @energy,--pred_kwh,
            @no_of_dvs,--pred_dv_count,
            @duration,--pred_duration,
            @weight,--pred_collected_weight,
            0,--actual_kwh,
            0,--actual_dv_count,
            0,--actual_duration,
            0--actual_collected_weight
        )
    else
        insert into fact.sequence
        (
            terminal_id,
            terminal_row_id,
            [timestamp],
            sequence_id,
            event_type_id,
            pred_kwh,
            pred_dv_count,
            pred_duration,
            pred_collected_weight,
            actual_kwh,
            actual_dv_count,
            actual_duration,
            actual_collected_weight
        )
        values
        (
            @terminal_id,--terminal_id,
            @terminal_row_id,--terminal_row_id,
            @timestamp,--[timestamp],
            @id,--sequence_id,
            @event,--event_type_id,
            0,--pred_kwh,
            0,--pred_dv_count,
            0,--pred_duration,
            0,--pred_collected_weight,
            @energy,--actual_kwh,
            @no_of_dvs,--actual_dv_count,
            @duration,--actual_duration,
            @weight--actual_collected_weight
        )
go


create procedure [ETL_1.7.1].log_se_event
(
    @terminal_id int,
    @terminal_row_id bigint,
    @timestamp datetime2(3),
    @id int,
    @event int
)
as
    declare @vcm_id int = 0

    select
        @vcm_id = [address]
    from dim.se se
    join dim.network net
        on se.terminal_id = net.terminal_id and se.network = net.id
    where se.terminal_id = @terminal_id
        and se.se = @id
        and network_type = 6

    insert into fact.se
    (
        terminal_id,
        terminal_row_id,
        [timestamp],
        se,
        event_id,
        repeat_count,
        error_code,
        network,
        vcm_id
    )
    values
    (
        @terminal_id,
        @terminal_row_id,
        @timestamp,
        @id,
        @event,--event_id,
        0,--repeat_count,
        0,--error_code,
        (select d.network from dim.se d where d.terminal_id = @terminal_id and d.se = @id),--network,
        @vcm_id
    )
go

• 表和相关的索引视图定义：

create table fact.sequence
(
    terminal_id int not null,
    terminal_row_id bigint not null,
    [timestamp] datetime2(3) not null,

    sequence_id int not null,
    event_type_id int not null,
    pred_kwh int not null,
    pred_dv_count int not null,
    pred_duration int not null, --seconds
    pred_collected_weight int not null, --kg
    actual_kwh int not null,
    actual_dv_count int not null,
    actual_duration int not null, --seconds
    actual_collected_weight int not null, --kg

    constraint pk__fact_sequence primary key (terminal_id, terminal_row_id),
    constraint fk__fact_sequence__dim_sequence foreign key (terminal_id, sequence_id) references dim.sequence(terminal_id, id),
    constraint fk__fact_sequence__enum_sequence_event foreign key (event_type_id) references enum.sequence_event (id)
)

go
create view staging.v_daily_fact_sequence
with schemabinding
as
    SELECT
        cast(fs.[timestamp] as date) [date],
        fs.terminal_id,
        fs.sequence_id,
        fs.event_type_id,
        count_big(*) as [count],
        sum(fs.actual_kwh) sum_kwh,
        sum(fs.actual_dv_count) sum_dv_count,
        sum(fs.actual_duration) sum_duration,
        sum(fs.actual_collected_weight) sum_kg
    FROM [fact].[sequence] fs
    group by
        cast([timestamp] as date),
        fs.terminal_id,
        sequence_id,
        event_type_id
go
create unique clustered index uci__staging__fact_sequence on staging.v_daily_fact_sequence([date], terminal_id, sequence_id, event_type_id)
go



create table fact.se
(
    terminal_id int not null,
    terminal_row_id bigint not null,
    [timestamp] datetime2(3) not null,

    se int not null,
    event_id int not null,
    repeat_count int not null,
    error_code int not null,
    network int not null,
    vcm_id int not null,

    constraint pk__fact_se primary key (terminal_id, terminal_row_id),
    constraint fk__fact_se__dim_se foreign key (terminal_id, se) references dim.se(terminal_id, se),
    constraint fk__fact_se__enum_valve_event foreign key (event_id) references enum.valve_event (id)
)

go
create view staging.v_daily_fact_se
with schemabinding
as
    select
        cast([timestamp] as date) [date],
        terminal_id,
        se,
        event_id,
        count_big(*) as [count]
    from fact.se
    group by
        cast([timestamp] as date),
        terminal_id,
        se,
        event_id
go
create unique clustered index uci__staging__fact_se on staging.v_daily_fact_se([date], terminal_id, se, event_id)
go

<deadlock>
  <victim-list>
    <victimProcess id="process2742b6a5848" />
  </victim-list>
  <process-list>
    <process id="process2742b6a5848" taskpriority="0" logused="351972" waitresource="KEY: 5:72057594049855488 (1c280247b0f1)" waittime="2388" ownerId="8532120" transactionname="user_transaction" lasttranstarted="2022-11-02T11:48:16.683" XDES="0x273c207c428" lockMode="RangeS-U" schedulerid="11" kpid="30304" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-11-02T11:48:17.697" lastbatchcompleted="2022-11-02T11:48:17.697" lastattention="1900-01-01T00:00:00.697" clientapp="Python" hostname="ENE-5CD9245PJR" hostpid="30564" loginname="etl" isolationlevel="read committed (2)" xactid="8532120" currentdb="5" currentdbname="EDW" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="EDW.ETL_1.7.1.log_sequence_event" line="50" stmtstart="2510" stmtend="4352" sqlhandle="0x03000500e1ab652609eeba0041af000001000000000000000000000000000000000000000000000000000000">
insert into fact.sequence
        (
            terminal_id,
            terminal_row_id,
            [timestamp],
            sequence_id,
            event_type_id,
            pred_kwh,
            pred_dv_count,
            pred_duration,
            pred_collected_weight,
            actual_kwh,
            actual_dv_count,
            actual_duration,
            actual_collected_weight
        )
        values
        (
            @terminal_id,--terminal_id,
            @terminal_row_id,--terminal_row_id,
            @timestamp,--[timestamp],
            @id,--sequence_id,
            @event,--event_type_id,
            0,--pred_kwh,
            0,--pred_dv_count,
            0,--pred_duration,
            0,--pred_collected_weight,
            @energy,--actual_kwh,
            @no_of_dvs,--actual_dv_count,
            @duration,--actual_duration,
            @weight--actual_collected_weight    </frame>
        <frame procname="adhoc" line="1" stmtstart="218" stmtend="588" sqlhandle="0x0100050095e97700e09e97017402000000000000000000000000000000000000000000000000000000000000">
EXEC [ETL_1.7.1].log_sequence_event @timestamp=@P1,@id=@P2,@event=@P3,@energy=@P4,@duration=@P5,@weight=@P6,@no_of_dvs=@P7,@average_fill_degree=@P8,@terminal_id=@P9,@terminal_row_id=@P1    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P1 nvarchar(46),@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 bigint)set nocount on;EXEC [ETL_1.7.1].log_sequence_event @timestamp=@P1,@id=@P2,@event=@P3,@energy=@P4,@duration=@P5,@weight=@P6,@no_of_dvs=@P7,@average_fill_degree=@P8,@terminal_id=@P9,@terminal_row_id=@P10   </inputbuf>
    </process>
    <process id="process2742b06d468" taskpriority="0" logused="770436" waitresource="KEY: 5:72057594049658880 (ffffffffffff)" waittime="2426" ownerId="8529120" transactionname="user_transaction" lasttranstarted="2022-11-02T11:48:15.520" XDES="0x273c2164428" lockMode="RangeS-U" schedulerid="4" kpid="36140" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-11-02T11:48:17.657" lastbatchcompleted="2022-11-02T11:48:17.657" lastattention="1900-01-01T00:00:00.657" clientapp="Python" hostname="ENE-5CD9245PJR" hostpid="30564" loginname="etl" isolationlevel="read committed (2)" xactid="8529120" currentdb="5" currentdbname="EDW" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="EDW.ETL_1.7.1.log_se_event" line="22" stmtstart="872" stmtend="1872" sqlhandle="0x03000500c4f6a02105eeba0041af000001000000000000000000000000000000000000000000000000000000">
insert into fact.se
    (
        terminal_id,
        terminal_row_id,
        [timestamp],
        se,
        event_id,
        repeat_count,
        error_code,
        network,
        vcm_id
    )
    values
    (
        @terminal_id,
        @terminal_row_id,
        @timestamp,
        @id,
        @event,--event_id,
        0,--repeat_count,
        0,--error_code,
        (select d.network from dim.se d where d.terminal_id = @terminal_id and d.se = @id),--network,
        @vcm_id    </frame>
        <frame procname="adhoc" line="1" stmtstart="136" stmtend="336" sqlhandle="0x01000500b1ea553470ff35ee7302000000000000000000000000000000000000000000000000000000000000">
EXEC [ETL_1.7.1].log_se_event @timestamp=@P1,@id=@P2,@event=@P3,@terminal_id=@P4,@terminal_row_id=@P    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P1 nvarchar(46),@P2 int,@P3 int,@P4 int,@P5 bigint)set nocount on;EXEC [ETL_1.7.1].log_se_event @timestamp=@P1,@id=@P2,@event=@P3,@terminal_id=@P4,@terminal_row_id=@P5   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594049855488" dbid="5" objectname="EDW.staging.v_daily_fact_sequence" indexname="uci__staging__fact_sequence" id="lock2740ecc4e00" mode="X" associatedObjectId="72057594049855488">
      <owner-list>
        <owner id="process2742b06d468" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2742b6a5848" mode="RangeS-U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594049658880" dbid="5" objectname="EDW.staging.v_daily_fact_se" indexname="uci__staging__fact_se" id="lock27317245700" mode="RangeS-U" associatedObjectId="72057594049658880">
      <owner-list>
        <owner id="process2742b6a5848" mode="RangeS-U" />
      </owner-list>
      <waiter-list>
        <waiter id="process2742b06d468" mode="RangeS-U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

我可以通过查询视图来获取可用内存[master].[sys].[dm_os_sys_memory]，但是我没有找到任何类似的东西来获取有关 CPU 负载的信息。

我只找到有关通过 SQL 服务器本身查询 CPU 负载的信息，这不是我主要关心的，我需要系统上的全部负载。我查看了系统视图，但我找不到任何东西，或者至少不明白我是否找到了它......

我想通过 T-SQL 得到这个的原因是我们已经有一个监控应用程序，它正在查询我们所有的系统以获取其他信息，并且能够扩展它而不是设置一些全新的东西会很好。

目前感兴趣的系统正在运行 2014 和 2017 express，但它可能很快就会包括 2019/2022 express。

我正在尝试找到一种方法，允许应用程序在 SQL Server 2019 上创建表并将数据插入其中，同时防止注入攻击，以防应用程序凭据泄漏。在编写可以并行运行的代码和编写不受 sql 注入攻击的动态 sql 时，我的经验是有限的。
表名基于来自应用程序的输入，即如果输入为“nds”，则表名应为lake.nds_raw_log。
据我了解，没有办法通过直接授予此应用程序的角色权限来执行此操作，因为创建表与删除或更改它们没有分开。

我想出的是作为 dbo 执行存储过程。当然它不长，但我有两个问题：

• 感觉很做作，根据我的经验，有一种更简单的方法。
• 我相信如果我在查询新创建的表时检索到错误的表，我需要将它作为可序列化运行以避免孤立表。这实际上不应该是一个大问题，因为它不会在第一次开始生产后经常发生，所以也许我不应该关心它。

    create procedure [lake].[create_terminal_raw_log_table]
    (
        @terminal_name nvarchar(100)
    )
    with execute as 'dbo'
    as
        begin try
            set transaction isolation level serializable
            begin transaction
                --create table
                declare @dynamic_sql nvarchar(1000) =
                    'create table [lake].' + quotename(@terminal_name) + '
                    (
                        id bigint not null,
                        [timestamp] datetime2(3) not null,
                        cmd varbinary(max) not null
                    );'
                exec sp_executesql @dynamic_sql
    
                /*get name of new table, this is why I believe that I need serializable isolation
                since other tables can be created in parallel*/
                declare @table_name nvarchar(100) =
                (
                    select top 1
                        [name] as table_name
                    from sys.tables
                    order by create_date desc
                )
        
                --rename table
                declare
                    @old_name nvarchar(100) = '[lake].' + @table_name,
                    @new_name nvarchar(100) = @table_name + '_raw_log'
                begin try
                    exec sp_rename
                        @objname = @old_name,
                        @newname = @new_name
                end try
                begin catch
                    set @dynamic_sql = 'drop table ' + @old_name
                    exec sp_executesql @dynamic_sql
                    ;throw
                end catch
    
                --create primary key
                set @dynamic_sql = 'alter table [lake].' + @new_name + ' add constraint pk__' + @new_name + ' primary key(id)'
                exec sp_executesql @dynamic_sql
    
            commit transaction
        end try
        begin catch
            rollback --I thought a rollback would occur when I throw after dropping the table but that doesn't seem to be the case
            ;throw
        end catch

所以我想这可以归结为3个问题：

• 这个存储过程实际上可以免受 SQL 注入攻击吗？
• 有更简单的方法吗？
• 将事务级别设置为可序列化将保护代码在从 sys.tables 中选择时不会选择错误的表是否正确？

我第一次在 SO 上发布了这个但没有得到回复，这就是我现在在这里尝试的原因，我希望我这样做没有违反任何规则（手指交叉）。

我试图了解如何正确使用表分区和分区消除，以便我可以决定是否要在新数据库（sql server 17 express）中使用它。我倾向于不使用它，但这是一个不同的故事，所以这个问题更多的是我现在不知道它是如何工作的。在下面的脚本中，我创建了一个分区函数和方案，一个使用该方案的表，插入一些数据，然后以 3 种不同的方式查询它。

• 第一个查询正确地仅访问第一个分区（实际分区计数 = 1，实际访问的分区 = 1）。

• 我希望第二个查询也只能访问第一个分区，因为我已经在值 20200101 上创建了分区函数作为范围，但它正在访问分区 1 和 2（实际分区数 = 2，实际访问的分区 = 1. .2)。

• 我希望第三个查询只能访问分区 2，但它正在访问分区 2 和 3（实际分区计数 = 2，实际访问的分区 = 2..3）。

我知道我需要进行精确的类型匹配才能进行分区消除，但我想我在这里有吗？我错过了什么？

create partition function partfun (datetime2(0))
as range right for values ('20200101', '20210101')

create partition scheme ps
as partition partfun all to ([primary])

create table t
(
    id int not null identity(1,1),
    logdate datetime2(0)
) on ps(logdate)
insert into t values ('20190101'),('20200102')

select * from t where logdate < cast('20191231' as datetime2(0))
select * from t where logdate < cast('20200101' as datetime2(0))
select * from t where logdate >= cast('20200101' as datetime2(0)) and t.logdate < cast('20210101' as datetime2(0))