SQL server 2012 - 我应该如何创建一个具有相同id值的表，以便我可以快速查询它？

I have to create a table that will contain an IP address and a created date.

我必须创建一个包含IP地址和创建日期的表。

The table will have rows with the same IP address but likely different create dates. (unless they come in so fast that the create date is the same).

该表将具有相同IP地址但可能具有不同创建日期的行。 (除非它们进入的速度太快,以至于创建日期相同)。

I will then want to query it to determine if 5 entries have been added for the given IP address. This must me very fast - performance is crucial as it will accessed threw an API (WCF web service) used by customers.

然后,我将查询它以确定是否已为给定的IP地址添加了5个条目。这一定非常快 - 性能至关重要,因为它将访问客户使用的API(WCF Web服务)。

How should I design this for performance?

我应该如何设计性能?

I've started with this but not sure that it is the best way.

我从这开始,但不确定这是最好的方式。

CREATE TABLE [dbo].[AttemptedFailedLoginsToApi]
  (
      [AttemptedFailedLoginsToApi] [bigint] IDENTITY(1,1) NOT NULL,
      [IPAddress] [varchar](250) NOT NULL,
      [CreatedDate] [datetime] NOT NULL,
CONSTRAINT [AttemptedFailedLoginsToApiId_PK] PRIMARY KEY CLUSTERED 
  (
      [AttemptedFailedLoginsToApi] ASC,
      [IPAddress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Any ideas would be appreciated.

任何想法,将不胜感激。

Regards.

1 个解决方案

#1

Why do you need to create a table with the same id just keep id on auto increment, create a column for ip and a date column and whenever you have to query it just select your desired column.

为什么你需要创建一个具有相同id的表,只需将id保持为自动增量,为ip和日期列创建一列,每当你需要查询它时,只需选择你想要的列。