Geek asks and answers

SQL Server Indexing Best Practices (SQL Server 2008)

I have some doubts about choosing the right index and some questions arise:

Clustered index

What is the best candidate?

This is usually a primary key, but if the primary key is not used in the search, for example, it is CustomerNoused to search clients, if the clustered index is placed on CustomerNo?

Views with SchemaBinding

If I have a view with indexes, I read that they are not used, but the ones on the tables.

Does it make no sense? Or am I missing a point? Would it make a difference using "NOExpand" to force an index to be read from a view, not a table?

Nonclustered Indexes

When adding a non-clustered index, is it good to include all possible columns until you reach the limit?

Thanks so much for your time. I am reading a massive database and speed is a must

+3
source share
4 answers

A clustered index is an index that (a) determines the storage location of your table (the data in the table is physically sorted using the clustering key), and (b) is used as a "string locator" in each nonclustered index of this table.

Therefore, a clustered index must be

  • narrow (4 bytes is ideal, 8 bytes are fine - everything else is too much)
  • ( , SQL Server 4- )
  • static ( )
  • -. Varchar(x)

INT IDENTITY , . , ( ), GUID ( - )

- , - ! SQL Server - ​​ !

. :

: ! , !

: - JOIN .

: ! CRUD ! - !

, - .

, - , , , .

+5

, , , .

: , . , , , .

SQL Server 2008

genral, , . , . , , . , , , , .

+1

marc_s , INT IDENTITY PK - Parent Child, . "" , . :

CREATE TABLE Invoice
(
   -- Use the default MS Approach on the parent, viz Clustered by Surrogate PK 
   InvoiceID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   -- Index Fields here
);


CREATE TABLE InvoiceLineItem
(
   -- Own Surrogate Key
   InvoiceLineItemID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
   InvoiceID INT NOT NULL FOREIGN KEY REFERENCES Invoice(InvoiceID),
   -- Line Item Fields Here
);

-- But Cluster on the Parent FK
CREATE CLUSTERED INDEX CL_InvoiceLineItem ON InvoiceLineItem(InvoiceID);

, - . , - , .

( ) , Nonclustered , .

+1

SchemaBinding

...

? ?

( , , schemabinding - , )

( ) . , , .

- , . , , . , , NOEXPAND, Enterprise Developer ( On/Dev) , , )

, , , , a unique , .

- , , T U, Default. :

CREATE VIEW DRI_T_OneDefault
WITH SCHEMABINDING
AS
    SELECT U
    FROM S.T
    WHERE Default = 1
GO
CREATE UNIQUE CLUSTERED INDEX IX_DRI_T_OneDefault on DRI_T_OneDefault (U)

, . ( ), . , - .

0

More articles:


All Articles