August 28, 2017 at 7:41 am
I have a script that creates a table, SerialNo, and in the script there are some CONSTRAINTS ADDed.
CREATE TABLE [SerialNo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SerialNumber] [nvarchar](32) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[Status] [int] NOT NULL,
[MaskinID] [nvarchar](32) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[MetodID] [nvarchar](32) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[Artikelnummer] [nvarchar](32) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[Date] [date] NOT NULL,
[Sequence] [int] NOT NULL,
[BatchID] [nvarchar](12) COLLATE Finnish_Swedish_CI_AS NULL,
[rowCreatedDT] [datetime] NOT NULL,
[rowChangedDT] [datetime] NOT NULL,
[DetaljIndex] [nvarchar](32) COLLATE Finnish_Swedish_CI_AS NULL,
[NoTriggerExec] [nchar](1) COLLATE Finnish_Swedish_CI_AS NULL,
[Cavity] [int] NULL,
CONSTRAINT [PK_SerialNo] PRIMARY KEY CLUSTERED
(
[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
ALTER TABLE [SerialNo] ADD CONSTRAINT [DF_SerialNo_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [SerialNo] ADD CONSTRAINT [DF_SerialNo_rowCreatedDT] DEFAULT (getdate()) FOR [rowCreatedDT]
GO
ALTER TABLE [SerialNo] ADD CONSTRAINT [DF_SerialNo_rowChangedDT] DEFAULT (getdate()) FOR [rowChangedDT]
GO
I wonder
1. Is there an Index created for these constraints?
2. Would I get in trouble if I added an index on rowCreatedDT ?
3. Does it matter if I create the index ASC or DESC if it main use is in a MAX(rowCreatedDT) statement?
August 28, 2017 at 7:59 am
Indexes are automatically created for primary key and unique constraints. No other.
Without seeing the query, can't answer 2 or 3. If it's a straightforward SELECT MAX(...) ... with no JOINs, no WHERE, then a single column index will work fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2017 at 6:07 am
The problem I was anticipating was indexing and default value. But I guess that's not a problem...
Thanks for your answer!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply