主页 / user-10650

World Wide DBA's questions

Martin Hope
World Wide DBA
Asked: 2018-01-11 08:56:13 +0800 CST
  • 26

我习惯于在非常安全的环境中工作，因此我将权限设计得非常精细。我通常做的一件事是明确地让DENY用户能够访问UPDATE不应该更新的列。

例如：

create table dbo.something (
    created_by varchar(50) not null,
    created_on datetimeoffset not null
);

一旦设置了值，就不应更改这两列。DENY因此，我会明确UPDATE许可他们。

最近，在一次团队会议上，一位开发人员提出了确保字段永不更新的逻辑应该包含在应用程序层而不是数据库层中，以防“他们出于某种原因需要更新值”。对我来说，这听起来像是典型的开发者心态（我知道，我曾经是其中的一员！）

我是我公司的高级架构师，我一直遵循使应用程序运行所需的最少权限的原则。所有权限都会定期审核。

在这种情况下，最佳做法是什么？

sql-server database-design
Martin Hope
World Wide DBA
Asked: 2017-07-22 10:03:28 +0800 CST
  • 2

在下面的示例中，我需要根据日期组合获取包含最新数据的行。我不能简单地这样做MAX(insert_date), MAX(update_date)，因为它不会返回正确的数据。它现在的工作方式是让MAX(insert_date)then 执行自连接以获得MAX(update_date)then 自连接以返回行值。

有没有更好、更有效的方法来做到这一点？下面的示例仅包含 4 行，但在生产中我将每隔几分钟处理大约 100 万行。

例子：

create table #temp (
iud char(1) not null,
id int not null,
date date not null,
value decimal(9,2) not null,
insert_date datetimeoffset not null,
update_date datetime2 not null
);

insert #temp
values
('i', 1001, '2001-01-01', 2, '2001-01-01 00:00', '2001-01-01 00:00'),
('i', 1001, '2001-01-01', 9, '2001-01-01 00:00', '2001-01-01 01:00'),
('i', 1001, '2001-01-01', 7, '2001-01-02 00:00', '2001-01-01 00:30'),
('i', 1001, '2001-01-01', 4, '2001-01-02 00:00', '2001-01-01 00:00');

-- this is wrong as it returns no results
select t.*
from #temp as t
join (select iud, id, date, max(insert_date) as insert_date, max(update_date) as update_date
      from #temp
      group by iud, id, date) as x
on t.iud = x.iud
and t.id = x.id
and t.date = x.date
and t.insert_date = x.insert_date
and t.update_date = x.update_date;

-- this works, but can it be simplified?
select n.*
from #temp as n
join (
    select n.iud, n.id, n.date, n.insert_date, max(update_date) as update_date
      from #temp as n
      join (select iud, id, date, max(insert_date) as insert_date
              from #temp
          group by iud, id, date) as i
    on i.iud = n.iud
  and i.id = n.id
  and i.insert_date = n.insert_date
group by n.iud, n.id, n.date, n.insert_date) as x
on x.date = n.date
and x.insert_date = n.insert_date
and x.iud = n.iud
and x.id = n.id
and x.update_date = n.update_date
order by n.iud, n.id, n.date;

drop table #temp;
sql-server sql-server-2016
Martin Hope
World Wide DBA
Asked: 2016-06-10 11:13:48 +0800 CST
  • 24

我有以下MERGE针对数据库发出的声明：

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

但是，这会导致会话终止并出现以下错误：

消息 0，级别 11，状态 0，第 67 行当前命令发生严重错误。结果，如果有的话，应该被丢弃。

消息 0，级别 20，状态 0，第 67 行当前命令发生严重错误。结果，如果有的话，应该被丢弃。

我已经将一个简短的测试脚本放在一起，它会产生错误：

USE master;
GO
IF DB_ID('TEST') IS NOT NULL
DROP DATABASE "TEST";
GO
CREATE DATABASE "TEST";
GO
USE "TEST";
GO

SET NOCOUNT ON;

IF SCHEMA_ID('MySchema') IS NULL
EXECUTE('CREATE SCHEMA "MySchema"');
GO

IF OBJECT_ID('MySchema.Region', 'U') IS NULL
CREATE TABLE "MySchema"."Region" (
"Id" TINYINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Region" PRIMARY KEY,
"Name" VARCHAR(8) NOT NULL CONSTRAINT "UK_MySchema_Region" UNIQUE
);
GO

INSERT [MySchema].[Region] ([Name]) 
VALUES (N'A'), (N'B'), (N'C'), (N'D'), (N'E'), ( N'F'), (N'G');

IF OBJECT_ID('MySchema.Location', 'U') IS NULL
CREATE TABLE "MySchema"."Location" (
"Id" SMALLINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Location" PRIMARY KEY,
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Location_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
"Name" VARCHAR(128) NOT NULL,
CONSTRAINT "UK_MySchema_Location" UNIQUE ("Region", "Name") 
);
GO

IF OBJECT_ID('MySchema.Point', 'U') IS NULL
CREATE TABLE "MySchema"."Point" (
"ObjectId" BIGINT NOT NULL CONSTRAINT "PK_MySchema_Point" PRIMARY KEY,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL CONSTRAINT "FK_MySchema_Point_Location" FOREIGN KEY REFERENCES "MySchema"."Location"("Id"),
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Point_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
CONSTRAINT "UK_MySchema_Point" UNIQUE ("Name", "Region", "LocationId")
);
GO

-- CONTAINS HISTORIC Point DATA
IF OBJECT_ID('MySchema.PointHistory', 'U') IS NULL
CREATE TABLE "MySchema"."PointHistory" (
"Id" BIGINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_PointHistory" PRIMARY KEY,
"ObjectId" BIGINT NOT NULL,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL,
"Region" TINYINT NOT NULL
);
GO

CREATE TYPE "MySchema"."PointTable" AS TABLE (
"ObjectId"      BIGINT          NOT NULL PRIMARY KEY,
"PointName"     VARCHAR(64)     NOT NULL,
"Location"      VARCHAR(16)     NULL,
"Region"        VARCHAR(8)      NOT NULL,
UNIQUE ("PointName", "Region", "Location")
);
GO

DECLARE @p1 "MySchema"."PointTable";

insert into @p1 values(10001769996,N'ABCDEFGH',N'N/A',N'E')

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

如果我删除该OUTPUT子句，则不会发生错误。另外，如果我删除deleted引用，则不会发生错误。所以我查看了 MSDN 文档中的OUTPUT条款：

DELETED 不能与 INSERT 语句中的 OUTPUT 子句一起使用。

这对我来说很有意义，但是重点MERGE是您可能事先不知道。

此外，无论采取何种操作，以下脚本都可以正常工作：

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

此外，我还有其他查询以OUTPUT与抛出错误的查询相同的方式使用，并且它们工作得很好——它们之间的唯一区别是参与MERGE.

这给我们的生产造成了重大问题。我已经在具有 128GB RAM、12 x 2.2GHZ 内核、Windows Server 2012 R2 的虚拟机和物理机上的 SQL2014 和 SQL2016 中重现了这个错误。

从查询生成的估计执行计划可以在这里找到：

预计执行计划

sql-server sql-server-2014
Martin Hope
World Wide DBA
Asked: 2015-09-21 14:54:20 +0800 CST
  • 11

给定一个元素，在 XML 模式集合中定义如下：

<xsd:element name="xid">
    <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="32" />
        </xsd:restriction>
    </xsd:simpleType>
</xsd:element>

您将如何使用 XQuery 更新元素？

该元素位于模式集合的ns命名空间中。我一直在尝试更新以下查询的元素：

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com"; 
     replace value of (/ns:*/ns:xid)[1] with "X00011793" cast as element(ns{http://www.anon.com}:xid,#anonymous) ?') 
 where id = 11793

但这会产生以下错误：

消息 9301，级别 16，状态 1，行 2 XQuery [cm.item.data.modify()]：在此版本的服务器中，“cast as”不可用。请使用 'cast as ? 句法。

如果我完全删除演员表并使用此查询：

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com"; 
     replace value of (/ns:*/ns:xid)[1] with "X00011793"') 
 where id = 11793

我收到此错误：

消息 2247，级别 16，状态 1，第 2 行 XQuery [cm.item.data.modify()]：该值的类型为“xs:string”，它不是预期类型“<anonymous>”的子类型。

如果我发出此查询：

update cm.item
   set data.modify(
      'declare namespace ns="http://www.anon.com/"; 
       replace value of (/ns:*/ns:xid/text())[1] with "X00011793"')
 where id = 11793

我收到此错误：

Msg 9312, Level 16, State 1, Line 2 XQuery [cm.item.data.modify()]: 'text()' 不支持简单类型或' http://www.w3.org/2001/XMLSchema #anyType ' 元素，找到 '(element(ns{ http://www.anon.com/ }:xid,#anonymous) ?) *'。

我的目标是 SQL Server 2008 R2。

谢谢！

sql-server sql-server-2008-r2
Martin Hope
World Wide DBA
Asked: 2012-08-25 12:46:40 +0800 CST
  • 6

我有两个集群，每个集群都有两个配置为主动/被动的节点；这两个集群使用单独的服务器作为见证服务器参与高可用性镜像会话。

当主集群中的一个节点发生故障时，镜像故障转移似乎在主集群的辅助节点有时间上线之前就开始了。

无论如何告诉见证服务器让被动节点在启动到辅助集群的故障转移之前尝试上线？

sql-server sql-server-2008