主页 / user-36614

BanksySan's questions

Martin Hope
BanksySan
Asked: 2017-08-12 07:45:15 +0800 CST
  • 6

SqlDbType.Structured将数据发送到存储过程中的表参数时，类型的行数\总大小是否有上限？

我找不到任何建议，所以我假设如果发送了一个巨大的数据集，就会有一个超时。

我正在查看 2000 多行，所以没什么可怕的。我只是想抢占任何潜在的爆炸。

sql-server ado.net
Martin Hope
BanksySan
Asked: 2017-06-19 04:18:10 +0800 CST
  • 1

我有一系列行代表开始 ( S) 或 ( E) 是一个thing

+----+-------+-------+
| Id | Event | Thing |
+----+-------+-------+
|  1 | S     | A     |
|  2 | E     | A     |
|  3 | S     | B     |
|  4 | E     | B     |
|  5 | S     | C     |
|  6 | S     | D     |
|  7 | S     | E     |
+----+-------+-------+

我正在尝试获取没有结束事件的行。

SELECT [a].[Id] AS [ID a],
       [a].[Event] AS [Event a],
       [b].[Id] AS [ID b],
       [b].[Event] AS [Event b]
  FROM [dbo].[TimeSeries] AS [a]
  LEFT OUTER JOIN [dbo].[TimeSeries] AS [b]
    ON [b].[Thing] = [a].[Thing]
   WHERE [a].[Event] = 'S'
   AND [b].[Event] = 'E'

这返回

+------+---------+------+---------+
| ID a | Event a | ID b | Event b |
+------+---------+------+---------+
|    1 | S       |    2 | E       |
|    3 | S       |    4 | E       |
+------+---------+------+---------+

这就是我被困的地方。

我想要所有既不在ID a列中也不在ID b列中的 ID。然后我猜想LEFT JOIN找到所有没有结束事件的行。

注意：真实的表是一个包含数百万行的 SSIS 日志。

t-sql
Martin Hope
BanksySan
Asked: 2016-12-20 10:38:12 +0800 CST
  • 5

我有以下查询来获取与最新日期对应的值：

SELECT MAX(RowAddedDate), X, Y
FROM dbo.MyTable
GROUP BY X, Y

这很好，但我需要获取此查询中每一行的 ID。如果我添加 ID，我会得到所有内容，因为 ID 需要在GROUP BY.

我该如何解决这个问题？

sql-server t-sql
Martin Hope
BanksySan
Asked: 2016-12-20 09:49:52 +0800 CST
  • 0

我有以下查询：

SELECT 1,
       row_number() over (partition by lower(X), lower(Y) order by X DESC) AS rn
FROM dbo.MyTable
WHERE rn = 1

当我运行它时，出现错误Invalid column name 'rn'.。

我不明白为什么我会按照该部分rn中的定义得到这个。SELECT

我也试过用WHEREa代替HAVING。

sql-server t-sql
Martin Hope
BanksySan
Asked: 2016-11-22 05:41:06 +0800 CST
  • 1

我想看看在计算列上设置索引的效果，所以我创建了一个表，如下所示：

CREATE TABLE [Domain\UserName].[CompColIndexing](
    [a] [int] NOT NULL,
    [nonIndexedNonPersisted]  AS ([a]+(1)),
    [nonIndexedPersisted]  AS ([a]+(1)) PERSISTED,
    [IndexedNonPersisted]  AS ([a]+(1)),
    [IndexedPersisted]  AS ([a]+(1)) PERSISTED
) ON [DATA]

我已经800,000为此添加了行，其值为a循环0到9.

添加了以下索引：

CREATE NONCLUSTERED INDEX [IX_DJB_CompNonPersisted] ON [Domain\UserName].[CompColIndexing] 
(
    [IndexedNonPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_DJB_CompPersisted] ON [Domain\UserName].[CompColIndexing] 
(
    [IndexedPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

然后我运行了一些ORDER BY子句来查看我会得到什么性能差异，然后计划以查看不断变化的值a将如何影响事物。

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY a

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedPersisted

出乎意料的是，我发现我对每个查询都得到了完全相同的结果：

分析器输出

我至少期望SORT第一个查询的操作会变慢，因为它没有索引。

这里发生了什么事？

基数是故意低的，实际上，我实际上需要对三个不同的值进行排序。

我在用着Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

实际执行计划可在： https ://www.brentozar.com/pastetheplan/?id=S10MTuxGg

sql-server sql-server-2008-r2
Martin Hope
BanksySan
Asked: 2016-10-18 12:50:38 +0800 CST
  • 4

我试图用大量虚拟数据填充一个表，以便我可以进行优化等。

我有以下内容：

WHILE @RowCount < 3000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max)))

    SET @RowCount = @RowCount + 1
END

但是，这似乎很慢。

有没有更好的方法来自动将半随机行加载到数据库表中？

新脚本

这个似乎很快：

USE [Test]
GO

/****** Object:  Table [dbo].[Test]    Script Date: 17/10/2016 21:22:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [Test]
GO

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL,
    [OtherKey] [int] NOT NULL,
    [Description] [varchar](max) NOT NULL,
    [Time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-- Add sample data

DECLARE @RowCount INT
DECLARE @Random INT
DECLARE @Upper INT

SET @Upper = 1000
SET @RowCount = 0

WHILE @RowCount < 1000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description]
               ,[Time])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max))
               ,GETDATE())

    SET @RowCount = @RowCount + 1
END

GO

/****** Object:  Index [IX_ID]    Script Date: 17/10/2016 22:18:48 ******/
CREATE CLUSTERED INDEX [IX_ID] ON [dbo].[Test]
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO



/****** Object:  Index [IX_OtherKey]    Script Date: 17/10/2016 21:22:46 ******/
CREATE NONCLUSTERED INDEX [IX_OtherKey] ON [dbo].[Test]
(
    [OtherKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

如果有人有更快的方法，那么我会很高兴听到它。

sql-server performance
Martin Hope
BanksySan
Asked: 2016-10-18 07:41:03 +0800 CST
  • 4

我发现了一些exec(sql)隐藏在代码中的语句。它们存在是有充分理由的，因为这些语句不能直接编写，但它们是明显的攻击媒介。

有安全的替代方案exec(some sql)吗？可以正确参数化的东西，包括语句中的表名？

sql-server t-sql
Martin Hope
BanksySan
Asked: 2016-10-17 09:27:33 +0800 CST
  • 9

我有一个简单的SELECT声明。

USE [AdventureWorks2014]
GO

SELECT *
FROM Sales.SalesOrderDetail sod

执行计划有两个Compute Scalar。

显示计算标量的执行计划 为什么是这样？我期待得到Index Scan或者可能是一个Table Scan？

第一个（最正确的）有

 [[AdventureWorks2014].[Sales].[SalesOrderDetail].LineTotal] = Scalar Operator(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPrice] as [sod].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sod].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2014].[Sales].[SalesOrderDetail].[OrderQty] as [sod].[OrderQty],0),(0.000000)))

当第二个有：

[[sod].LineTotal] = Scalar Operator([AdventureWorks2014].[Sales].[SalesOrderDetail].[LineTotal] as [sod].[LineTotal])
sql-server performance
Martin Hope
BanksySan
Asked: 2016-10-16 11:45:53 +0800 CST
  • 0

我正在使用 AdventureWorks 数据库并运行以下查询：

SELECT ct.Name
FROM Person.ContactType ct
WHERE ct.Name LIKE 'Own%'

执行计划显示它正在使用一个Index Seek (NonClustered)，但是表上只有一个索引；那是 上的主键ContactTypeID。

在此处输入图像描述

该表定义如下：

/****** Object:  Table [Person].[ContactType]    Script Date: 15/10/2016 20:43:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Person].[ContactType](
    [ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED 
(
    [ContactTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [Person].[ContactType] ADD  CONSTRAINT [DF_ContactType_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for ContactType records.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ContactTypeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contact type description.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'CONSTRAINT',@level2name=N'DF_ContactType_ModifiedDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'CONSTRAINT',@level2name=N'PK_ContactType_ContactTypeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Lookup table containing the types of business entity contacts.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType'
GO
sql-server index