Royi Namir's questions

我有一张Images表，其中包含这些基本列 —

ImageId (int)
VotesUp (int)
VotesDown (int)

每张图片都可以获得赞成票（赞成票、反对票、撤回票）

upvotes 表有：

ImageId (int)
UserId (int)
Score (int )   [-1,0,+1]

因此，如果 -

• 然后用户插入 +1 分数（如果不存在）我向upvotes表中添加一个新行并更新表中的值Images（以便快速获取）

• 用户将其分数从 +1 更改为 0 然后降低votes up

• 用户将其分数从 +1 更改为 -1，然后减少votes up和增加 votes down
• 用户将其分数从 0 更改为 +1，然后增加votes up

• 用户将其分数从 0 更改为 -1，然后增加votes down

• 用户将其分数从 -1 更改为 0 然后减少votes down

• 用户将其分数从 -1 更改为 +1，然后减少votes down 和增加votes up

这是一个非常简单的逻辑。

这是upvotes桌子上的触发器：

alter TRIGGER [dbo].[UpvotesChanged]
ON [dbo].[Upvotes]
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS( SELECT 1  FROM   DELETED ) --update
BEGIN

UPDATE imgs
SET    VotesUp = CASE

                    WHEN deleted.Score = 1 AND INSERTED.score =0      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 0 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    ELSE ISNULL(VotesUp, 0)
                END  
                ,
    VotesDown = CASE 

                    WHEN deleted.Score = 0 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesDown, 0) -1
                    WHEN deleted.Score = -1 AND INSERTED.score =0      THEN ISNULL(VotesDown, 0) -1
                    ELSE ISNULL(VotesDown, 0)
                END   
FROM   Images imgs
    JOIN DELETED 
        ON  imgs.ImageId = deleted.ImageId
        JOIN INSERTED ON imgs.ImageId = INSERTED.ImageId


END
ELSE
--insert
BEGIN
UPDATE imgs
SET    VotesUp = CASE 
                    WHEN INSERTED.Score = 1 
                    THEN ISNULL(VotesUp, 0) + 1
                    ELSE VotesUp
                END,
    VotesDown = CASE 
                    WHEN INSERTED.Score = -1 
                        THEN ISNULL(VotesDown, 0) + 1
                    ELSE VotesDown
                END
FROM   Images imgs
    JOIN INSERTED
        ON  imgs.ImageId = INSERTED.ImageId
END
END

它按预期工作。

那么问题出在哪里呢？

问题 ：

我认为这个触发器可以在没有所有这些 if's/cases 的情况下得到简化。我只是看不到简化。

是否可以简化此触发器？

（问题从SO转移）

我有一个带有聚簇索引的表（虚拟数据）包含 2 列：

现在我运行这两个查询：

declare 
@productid int =1 , 
@priceid  int = 1




SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid OR @productid IS NULL)
       AND (priceid = @priceid OR @priceid IS NULL)  


SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid)
       AND (priceid = @priceid)

两个查询的实际执行计划是：

如您所见，第一个使用 SCAN，第二个使用 SEEK。

然而 - 添加OPTION (RECOMPILE)到第一个查询，使执行计划也使用 SEEK：

DBA 聊天室的朋友告诉我：

在您的查询中，@productid=1，这意味着 (productID=@productID OR @productID IS NULL) 可以简化为 (productID=@productID)。前者需要扫描才能使用@productID 的任何值，后者可以使用搜索。因此，当您使用 RECOMPILE 时，SQL Server 将查看您在 @productID 中实际拥有的值并为其制定最佳计划。对于@productID 中的非空值，搜索是最好的。如果@productID 的值未知，则计划必须适合@productID 中的任何可能值，这将需要扫描。请注意：OPTION (RECOMPILE) 将在您每次运行时强制重新编译计划，这将在每次执行时增加几毫秒。虽然这只是一个问题，如果查询运行非常频繁。

还 ：

如果@productID 为空，您会寻求什么值？答：无所求。所有值都符合条件。

我知道这会OPTION (RECOMPILE)强制 SQL Server 查看参数的实际值，并查看它是否可以使用它进行 SEEK。

但是现在我失去了提前编译的好处。

问题

恕我直言，扫描只会在参数为空时发生。
没关系——让 SQL SERVER 为 SCAN 创建一个执行计划。
但是，如果 SQL Server 发现我多次使用值运行此查询：1,1，那么为什么它不创建另一个执行计划并为此使用 SEEK？

AFAIK-SQL 为命中率最高的查询创建执行计划。

• 为什么 SQL SERVER 不保存执行计划：

  @productid int =1 , @priceid int = 1

（我用这些值运行了很多次）

• 是否可以强制 SQL 保留该执行计划（使用 SEEK）以供将来调用？

完整的创建表脚本+数据

我有这张代表层次结构的表：

childID  parentID          NAME          siblingOrder
1          1               a               0
212        1               ab              1
112        1               ac              2
412        1               ad              3
-912       112             aca             0

结构是：

a
+----ab
+----ac
     +---aca
+----ad

（该siblingOrder列用于控制ab, ac,的顺序ad ）

我已经有了这个解决方案，它使用 siblingOrder：

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)  AS VARCHAR(1024))  AS PATH
   FROM   @myTable
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)AS VARCHAR(1024) )
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

所以order by PATH实际上做的工作：

问题：

问题是我必须（！）将值放入siblingOrder以使其工作！

否则，例如：

如果我把0所有siblingOrder这些都放在结果中：

（是的，现在按路径排序 - 不起作用......）

我需要它aca永远在ac

（我添加的唯一原因siblingOrder是订购兄弟姐妹！）而且我不想siblingOrder在不需要时强制添加

问题 ：

是否可以增强查询以便siblingOrder只影响兄弟姐妹？

我的意思是，如果我不关心兄弟姐妹的顺序（通过放置0），我仍然aca希望ac

sqlonline : 与 siblingOrder

Sqlonline - 没有 siblingOrder

假设我有一个全球网站（我的意思是它有来自世界各地的用户）。

我有 1 台服务器在德国（在某个城市）。它永远不会从德国搬走——永远不会。

我的问题是：

当用户保存数据时，我需要保存操作日期：

我应该将它保存（我想我应该保存）UTC吗？

或者因为 - 服务器位于一个位置 - 可以保存所有日期，因为GetDATE() 相对性点是同一点。

我错了吗 ？如果我这样做，你能提供一个简单的失败场景吗？

我正在使用 SQL Server 2008 R2，并且我有这个伪查询 (SP)：

select ...
from ...
WHERE    @LinkMode IS NULL
     AND (myColumn IN (...very long-running query...))
     ...
     ...

问题是查询需要很长时间才能执行——即使我使用@LinkMode=2.

正如您所注意到的，只有当@LinkMode 为空时才应该执行长时间运行的查询，而这里的情况并非如此。在我的情况下 @LinkMode = 2 ！

但是，如果我将其更改为：

 select ...
    from ...
    WHERE    1=2
         AND (myColumn IN (...very long time exeted query...))
     ...
     ...

SP确实运行得很快。

我以前听说过，有时优化器可以优化标准的顺序。

所以我问：

• 即使优化器选择了不同的路线，还有什么比检查 if 更快=null？我的意思是，我认为检查if a==null比运行其他长查询要快得多......

• 如何强制SQL Server 运行我编写的查询（相同的顺序）？

我不明白 ：

我已经安装了 sql server 2008 R2。安装过程要求我提供所需的实例名称，所以我写道：MSSQLSERVER。

安装后，运行Setup Discovery Report会显示实例名称：

我还安装了所有的服务包。

此外，所有服务都已启动：

此外，所有端口都是开放的（tcp:1433 + udp:1434）

此外，命名管道在：

那么，问题出在哪里？

我无法通过实例名称连接：

也无法使用：

user\MSSQLSERVERuser计算机名称在哪里。

也无法使用：.\MSSQLSERVER

但是（奇怪）：

那些 3确实有效：

• user（“用户”是我的机器名）
• (local)
• .

问题 ：

为什么我不能使用我的实例名称连接到我的 sql 服务器？

相关资料：

通过三个工作人员之一连接后，我运行了一个有用的信息：

我有这段代码：

Select name, age , row_number() over (partition by name order by age desc) rn from my table

（目标：选择同名中年龄最大的人）

如果我想选择所有行编号的记录1，我想我必须这样做：

Select ... from ( above query) where rn=1

但这迫使我用另一个包裹它select。

有没有其他解决方案而不是包装select？（cte也像包装一样）

如果我在 SQL Server 中编写：

SELECT number FROM master..spt_values WHERE Type = 'P' ORDER BY Number

我得到了一个我已经可以使用的序号列表，而不是写一个带有序号的表。

这对于小型连接、行编号等非常有帮助。

我想知道是否还有其他像这样的隐藏表也可以帮助我做其他事情？例如，我可能想要select someNonImportantTextColmn from master..unknown并应用我的功能。

我在哪里可以找到这些“帮助表”的完整列表？

看着

update MyTable
set Status = 1
where Status = 0

当然 -where是在实际更新之前计算的。

但是这个过滤（where Status = 0）是否也在锁内？

我的意思是锁在哪里？

这里 ：update ...

或在这里： filter and update...

我只想监视 sp。

SQL Server Profiler 是否会监听所有事件然后过滤所需的数据（例如 sp 的数据）？

或者更确切地说，sql仅发出Profiler 预配置事件？

我正在使用 sql server 2008 r2。

我登录到 ssms，后者又连接到远程 sql server 机器。

我正在编写一个写入文件的查询。

我需要知道 - 我应该授予哪些 Windows 权限c:\myfolder

有没有什么select query可以为我提供最终写入文件的Windows帐户？

我在 Microsoft SQL Server 中有一个表。有时我需要更新，有时我需要插入。我可以写2个存储过程：

InsertNewPerson
UpdatePertsonById

但我正在考虑编写 1 个存储过程，而不是 ( SetPerson)，这两者都可以（如果有ID，它是一个update操作，否则insert）。

我应该创建一个存储过程（只维护一个）还是应该创建两个不同的存储过程？

问完这个我还有一个问题。

一家公司需要存储敏感数据。请让我们现在不要谈论证书。

DBA执行以下操作：

  CREATE SYMMETRIC KEY SecureSymmetricKey1 
  WITH ALGORITHM = DESX
  ENCRYPTION BY PASSWORD = N'blabla';

程序员想要加密数据并执行以下操作：

  -- open key
  OPEN SYMMETRIC KEY SecureSymmetricKey1
  DECRYPTION BY PASSWORD = N'blabla'

  -- actual encrpyt
  DECLARE @encrypted_str VARBINARY(MAX)
  SET @encrypted_str =  EncryptByKey(Key_GUID('SecureSymmetricKey1'),'my data');

另一个程序员想要读取数据，所以他这样做了：

  DECLARE @decrypted_str VARBINARY(MAX)
  SET @decrypted_str = DecryptByKey(...encrypted_str...) 

一切都好。

问题 ：

1. 当程序员打开对称密钥时，他必须知道密码。我认为程序员不应该知道密码。如何解决？

2. 如果 GOD 黑客获得了整个.BAK文件并将备份恢复到自己的机器上 - 他可以查看其中一位程序员编写的存储过程源，并查看密码。然后黑客可以做：

   打开对称密钥 SecureSymmetricKey1 密码解密 = N'blabla'

我错过了什么？

感谢您的帮助。

所以我在聊天中询问SQL Server 备份文件的安全性。

有人告诉我，如果攻击者可以访问未加密的 .bak 文件，他们就可以访问数据。

那么让我们看看这个场景：

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

注意 - 这里没有密码。

然后我们通过以下方式加密我们的表：

UPDATE tbl1
 SET namePAss = ENCRYPTBYKEY(KEY_GUID('MySymetricKey'),name)
GO

现在假设黑客拿到了我的bak文件。为了查看数据（在他自己的计算机和 sql-server 中），他所要做的就是：

SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM tbl1

他还能访问数据吗？

来自msdn：

与派生表不同，CTE 可以是自引用的，并且可以在同一查询中多次引用。

我经常使用 CTE，但我从未深入思考过使用它们的好处。

如果我在同一个查询中多次引用 CTE：

• 有任何性能优势吗？
• 如果我正在执行自联接，SQL Server 会扫描目标表两次吗？

我有一个.mdf250 mb 的 10 GB 文件.LDF。

我想缩小 10 GB .mdf（如果可能）

我阅读了有关 dbcc 命令的信息，但我在缩小.LDF（使用截断所需的任何大小）的上下文中阅读了它。

但是我想在.MDF不丢失数据的情况下缩小文件。

它的确切命令是什么？

1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

我正在尝试了解 SQL Server 加密。

• 一旦我执行了上面的代码，以后有什么方法可以找出 的密码值是SecureSymmetricKey多少？

2)

如果现在我正在这样做Certificates：我是管理员并创建了：

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

只有我知道密码。

后来我：

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'

CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

直到现在，一切都还好。

现在，当黑客进入计算机时，他所要做的就是：

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

接着 ：

SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM  tbl1

那么证书的保护在哪里呢？没有人问他密码（就像密码加密（如我的第一个问题）......？他只需要知道证书名称

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

找出证书名称是什么不是问题......那么黑客解密数据的保护在哪里？

1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

我试图弄清楚 SQL Server 加密。

• 一旦我执行了上面的代码，以后有什么方法可以找出密码的值是SecureSymmetricKey多少？

2)

如果现在我这样做Certificates：我（管理员）创建

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

只有我知道密码。

后来我：

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'


CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

到现在为止，一切正常。

现在，当黑客来到电脑前，他所要做的就是：

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

接着 ：

SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM  tbl1

那么证书的保护在哪里呢？没有人要求他输入密码（就像密码加密一样（就像我的第一个问题一样）......？他只需要知道证书名称

打开对称密钥 MySymetricKey 通过证书解密 MyCertificate

找出证书名称是什么也不是问题......那么黑客的解密数据保护在哪里？

3)

当我使用

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';


DECLARE  @str NVARCHAR(100)
SET @str = 'lala';

OPEN SYMMETRIC KEY SecureSymmetricKey
    DECRYPTION BY PASSWORD = N'StrongPassword';

我想保护谁的数据？ 从客户端发送到服务器的数据？（数据是通过纯文本发送的 - 在发送数据之前我无法激活 SQL 命令......）或有权访问 SQL 服务器的人？