CODEWITHSUNDEEP
sqlt-sql
user380432
user380432

Reputation: 4779

How would I create an index on this temp table?

I am trying to speed up a query and I think I am confused about indexes. How, and what index would I add to this table. The ID is Unique, would this be a primary index?

CREATE TABLE #OSP
    (
        [Id] UniqueIdentifier,
        [YearMonth] int,
        [Expenditure] decimal (7,2),
        [Permit] decimal (7,2)
    );

Upvotes: 4

Views: 2587

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

You can specify the primary key in your create table statement.

CREATE TABLE #OSP
    (
        [Id] UniqueIdentifier primary key,
        [YearMonth] int,
        [Expenditure] decimal (7,2),
        [Permit] decimal (7,2)
    );

Upvotes: 3

brendan
brendan

Reputation: 29986

If you're joining on id then creating an index on that would help.

I think this would work:

CREATE TABLE #OSP
    (
        [Id] UniqueIdentifier,
        [YearMonth] int,
        [Expenditure] decimal (7,2),
        [Permit] decimal (7,2)
    );


CREATE UNIQUE CLUSTERED INDEX [idx_id] ON #Osp ([Id] ASC)

Upvotes: 1

Related Questions