Sergiy (11/25/2015)

---

Welsh Corgi (11/25/2015)

---

The following is the structure of the table:

CREATE TABLE [dbo].[xactControlPoint](

[xactControlPointID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[xactControlPointTypeID] [int] NOT NULL,

[call_id] [int] NOT NULL,

[contr_id] [int] NULL,

[xactTransactionIDValue] [varchar](50) NULL,

[beginDate]...

It depends. In this case, I don't see begin date being queried, so I'd be very leery of starting the clustered index with that column. Is begin date...

Rushing this process more than normal right now, very busy on top of holiday fast approaching :-).

Hopefully this helps a little more:

Chg1) Add column contr_id as an INCLUDEd column on...

It is a lot easier if you use a spreadsheet to hold the stats so the columns stay intact ;-). But I've read these so much I can read...

Welsh Corgi (11/25/2015)

---

The following is the structure of the table:

CREATE TABLE [dbo].[xactControlPoint](

[xactControlPointID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[xactControlPointTypeID] [int] NOT NULL,

[call_id] [int] NOT NULL,

[contr_id] [int] NULL,

[xactTransactionIDValue] [varchar](50) NULL,

[beginDate] [datetime]...

Sergiy (11/24/2015)

---

Welsh Corgi (11/24/2015)

---

I have three tables that are very large and have too many indexes.

I can easily archive the tables without running into the risk of having to tune...

For us, most tables don't really need partitioned. Archiving and partitioned views handles most of them.

When I do partition, I align indexes because I want to be able to...

You could always archive old data first, then adjust the indexes as needed. But be sure to capture all index stats (missing, usage and operation) now before you delete...

I believe it may be due to the deferred resolution SQL uses for permanent tables that don't exist during parsing. Then again, maybe not :blink:

Make sure you have the best clustered index on every table. That is critical for best performance overall, and the wrong clustering key often leads to extra indexes. ...

As noted, msdb is the main concern here.

But it's possible you might need to do rebuilds in model if you add/adjust tables in there, especially since you'd want the model...

There are legitimate cases for using identity as a later key.

For example, in an OrderItems table, I might want to key/cluster the table on ( OrderNumber, $IDENTITY ) in that...

The bigger design error is likely that you have PK identities when the table contains a valid data key. Identities have become a crutch and are vastly over-used. ...

Here's code that will generate the code you need.

Press Ctrl-T before running the code to have it come out as text. Then copy that text to a new query...

I don't think a global temp table would necessarily be immediately dropped. But overall I agree, there's no great concern to drop temp tables in the code, unless perhaps:

1)...