irimias's questions

我有一个表格来描述我的应用程序用户，其中包含一些详细信息，例如姓名、姓氏、出生日期、国籍、电子邮件等......

我想知道每个属性和每个用户类别的最常见值和出现百分比。

例如 ：

create table test ( userId int identity(1,1), 
                    categoryId int, 
                    name varchar(50), 
                    surname varchar(50))

insert into test(categoryId, name, surname)
values  (1, 'John', 'Locke'),
        (1, 'John', 'Millar'),
        (1, 'James', 'Mill'),
        (1, 'John Stuart', 'Mill'),
        (2, 'Thomas', 'Bayes'),
        (2, 'Laurent', 'Schwartz'),
        (2, 'Herrmann Amandus', 'Schwartz'),
        (2, 'Thomas', 'Simpson'),
        (2, 'Leonhard', 'Euler')

结果应该是：

+------------+-------+--------+---------+----------+------------+
| categoryId | total |  name  | namePct | surname  | surnamePct |
+------------+-------+--------+---------+----------+------------+
|          1 |     4 | John   |    0.50 | Mill     |       0.50 |
|          2 |     5 | Thomas |    0.40 | Schwartz |       0.40 |
+------------+-------+--------+---------+----------+------------+

对于这个简单的示例，我可以通过如下查询来计算如何实现这一点：

select  t.categoryId, 
        t.total, 
        n.name, 
        1. * n.total / t.total as namePct,
        sn.surname,
        1. * sn.total / t.total as surnamePct
from (
    select categoryId, count(*) as total
    from test
    group by categoryId
    ) t
join (
        select categoryId, name, total
        from (
            select categoryId, name, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, name, count(*) as total
                from test
                group by categoryId, name
                ) t
            ) t
        where rn = 1
        ) n on t.categoryId = n.categoryId
join (
        select categoryId, surname, total
        from (
            select categoryId, surname, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, surname, count(*) as total
                from test
                group by categoryId, surname
                ) t
            ) t
        where rn = 1
        ) sn on t.categoryId = sn.categoryId

但是，在我的实际用例中，我的表有数百万行、数百个类别和十几个属性。

有没有办法使查询更简单、更高效（即每个属性没有一堆子选择）？

我目前使用的是 SQL Server 2008，但欢迎使用更新版本的答案。

我正在测试快照复制过程（SQLServer 2008（是的，不幸的是......））。

一切都很好，但我无法在MS 文档中找到表是如何更新的。我看到发布过程删除表，然后重新创建它们，然后用数据填充它。

好的，很好。但是是否有一种安全措施（锁、事务……）可以防止在此同步期间进行查询（特别是在删除表和创建之间）？

让我们想象一个数据库A( publisher ) 有一个在另一个数据库( subscriberT )上复制的表。当分发代理正在运行并且表在服务器 B 上更新时（删除表 T + 创建表 T + bcp-in 数据？），在几毫秒内表将不存在。如果一个应用程序试图读取表，它会得到一个错误。BTT

当我尝试B在分发代理运行时在服务器上启动跟踪时，我看不到为此步骤进行的任何事务。事实上，我可以T在服务器上的复制表上进行选择B（并出现错误）。

它真的应该如何工作吗？

有什么地方可以避免这种行为吗？

在 SQL Server 2008 中（但也在 2014 年）。让我们考虑一个具有输出参数的过程。此过程可能会产生错误（将在以下示例中出现）。我注意到如果我们在TRY/CATCH块中调用过程，输出参数的行为是不一样的。

例子：

create procedure test_output @result varchar(10) output
as
begin
    set @result = 'hello'
    raiserror('This is an error', 16,1)
    set @result = 'error'
end

如果我们以简单的方式启动该程序：

declare @res1 varchar(10)
exec test_output @result = @res1 out
print 'Result is: '+ isnull(@res1, 'empty')

我们得到（我很好）：

Msg 50000, Level 16, State 1, Procedure test_output, Line 7 [Batch Start Line 12]
这是一个错误
结果是：错误

如果过程现在在 try/catch 块中：

declare @res2 varchar(10)
declare @error_message varchar(max)
begin try
    exec test_output @result = @res2 out
end try
begin catch
    set @error_message = error_message()
    raiserror(@error_message, 16,1)
    print 'Result is: '+ isnull(@res2, 'empty')
end catch

我们得到（我很沮丧）：

消息 50000，级别 16，状态 1，第 28 行
这是一个错误
结果是：空

错误消息正常，但输出参数现在为NULL。如果在TRY...CATCH上下文中，执行在 之后立即停止RAISERROR，我会期望输出值设置为hello。

为什么会这样？

我尝试针对 SQL Server 2008 服务器运行 SQL Server 升级顾问 2014。

连接到 2008 服务器时一切看起来都很好，我可以选择要分析的数据库。

但是一旦启动，它就会永远运行，什么都不做（停留在步骤Analyzing Rules: 0/112）。

发生了什么事，我该如何解决？

SQL Server 2008 和 2014 都是 Express 版本，安装在我的笔记本电脑上。不幸的是，这是我发现运行升级顾问的唯一方法（在真正的生产/开发数据库上运行它有太多限制）。

SQL 2008 实例根据需要带有 SP3 。

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) 

当 Advisor 被冻结时，我们可以看到 4 个休眠查询（没有活动查询）：

SELECT name,
       cmptlevel
FROM
  (SELECT name,
          dbid,
          cmptlevel,
          DATABASEPROPERTYEX(name, N'UserAccess') AS 'UserAccess',
          DATABASEPROPERTYEX(name, N'Status') AS 'Status',
          DATABASEPROPERTYEX(name, N'IsInStandBy') AS 'IsInStandBy'
   FROM master.dbo.sysdatabases) t
WHERE LOWER(name) NOT IN ('tempdb',
                          'master',
                          'model',
                          'msdb')
  AND HAS_DBACCESS(name) = 1
  AND dbid NOT IN (32767)
  AND UserAccess != 'SINGLE_USER'
  AND Status = 'ONLINE'
  AND IsInStandBy = 0
ORDER BY name;

和

(@dbname nvarchar(256))
SELECT COUNT(*)
FROM master.dbo.sysdatabases
WHERE name=@dbname

和

SELECT 'Edition'=SUBSTRING(@@VERSION,PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2,PATINDEX(N'% on %',@@VERSION)-(PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2))

和

SELECT N'Job.Step',
        j.name + N'.' + js.step_name
FROM msdb.dbo.syssubsystems ss
JOIN msdb.dbo.sysjobsteps js ON ss.subsystem = js.subsystem
JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE ss.description_id=14555
UNION
SELECT N'Proxy',
        p.name
FROM msdb.dbo.sysproxysubsystem ps
JOIN msdb.dbo.sysproxies p ON ps.proxy_id = p.proxy_id
JOIN msdb.dbo.syssubsystems ss ON ss.subsystem_id = ps.subsystem_id
WHERE ss.description_id=14555

当然，当我们手动启动它们时，它们会起作用。

当我在服务器上启动 SQL 跟踪时，没有什么可疑的……

我有一个像（SQLServer 2008）这样的表：

CREATE TABLE [dbo].[my_test_table](
    [productId] [int] NOT NULL,
    [purchaseId] [bigint] NOT NULL,
    (some other columns....),
 CONSTRAINT [PK_my_test_table] PRIMARY KEY CLUSTERED 
(
    [productId] ASC,
    [purchaseId] ASC
))

大约有 1000 万行。

我想要一个返回产品总行数的查询，或者如果产品未分类，则返回所有产品的总行数。就像是：

declare @productId int

set @productId = 320

select count(*)
from my_test_table t with(nolock)
where productId = @productId
or @productId is null

问题是查询比等效查询花费更多时间：

select count(*)
from my_test_table t with(nolock)
where productId = 320
or 320 is null

我们如何解释这种行为？

以下是执行计划：

让我们想象一个检索数据并进行某种分页的存储过程。这个过程有一些输入，描述了我们想要哪组数据以及我们如何对其进行排序。

这是一个非常简单的查询，但我们以它为例。

create table Persons(id int, firstName varchar(50), lastName varchar(50))
go
create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderBy+' '+@orderDir

exec(@sql)

它应该是这样使用的：

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id', @orderDir = 'desc'

但是一个聪明的人可以推出：

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id)a from Persons)t;delete from Persons;print''', @orderDir = ''

...并删除数据

这显然不是一个安全的情况。我们怎么能防止它呢？

注意：这个问题不是关于“这是一种进行分页的好方法吗？” 也不是“做动态sql是一件好事吗？”。问题是关于在动态构建 sql 查询时防止代码注入，以便在将来我们必须再次执行类似的存储过程时有一些指导方针使代码更干净。

一些基本的想法：

验证输入

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

if @orderDir not in ('asc', 'desc') or @orderBy not in ('id', 'firstName', 'lastName')
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderBy+' '+@orderDir

exec(@sql)

传递 id 而不是字符串作为输入

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy tinyint = 1, @orderDir bit = 0
as

declare @orderByName varchar(50)
set @orderByName =  case @orderBy when 1 then 'id'
                        when 2 then 'firstName'
                        when 3 then 'lastName'
                    end 
                +' '+case @orderDir 
                        when 0 then 'desc' 
                        else 'asc' 
                    end

if @orderByName is null
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderByName+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+' 
order by '+@orderByName

exec(@sql)

还有其他建议吗？

我对SQLServer 2014 (12.0.4439.1) 数据库有一个奇怪的问题。

几个月前，我创建了一个数据文件和表来使用内存中的基本功能，看看是否可以改进我们的一些流程。完成后，我想清理所有内容，但我不知道“一旦创建了内存优化文件组，就只能通过删除数据库来删除它”。

好吧，我不想删除我的数据库（因为它在现实生活中被真实的人使用）也不想重新创建/重新填充它（相当多的工作）。

所以一切都保持这样：一个普通的数据库，有一个空文件用于内存中的东西。一切进展顺利。

但是，有一次，这个数据库的日志文件变得疯狂：它的大小增加了，没有什么可以阻止它。当我尝试缩小它时，我经常（并非总是）得到“由于 XTP_CHECKPOINT 导致日志文件缩小失败”，这看起来与内存中的功能有关。并且在尝试备份/服务重启/检查点和其他深奥的命令后，可以正确操作和缩小日志文件。

这么简单：我不明白发生了什么。

有人经历过吗？和/或知道避免/修复这种奇怪行为的干净方法？

[编辑] ：该问题看起来与https://support.microsoft.com/en-us/kb/3090141非常相似，但在我的情况下，已经安装了修补程序并且建议的解决方法（离线/在线）不起作用

通常，在 SQLServer 日志中，每小时都会有一条HkHostLogCheckpointRecord()消息。当此消息停止出现时，日志文件开始增加。是原因还是结果？这就是问题所在....