Product:
Microsoft SQL server 2016
Issue:
Should i make a index?
Solution:
Yes, all table should have a index – at least a clustered index.
But a data-ware house table, should maybe not have a index when you load a lot of data.
Index can be created after you have loaded a lot of data. This to speed up the process.
Correct made index make the SELECT faster. Any index in the target table, makes the INSERT slower.
You should have not too few, or too many index on a table to get the best performance.
To create a index:
CREATE TABLE dbo.TestTable (TestCol1 int NOT NULL, TestCol2 nchar(10) NULL, TestCol3 nvarchar(50) NULL); CREATE CLUSTERED INDEX IX_TestTable_TestCol1 ON dbo.TestTable (TestCol1);
You can also create index by right-click on table name – indexes – New Index.
Check if index exist, before you drop it;
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_ProductVendor_VendorID')
DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (BusinessEntityID); Use ONLINE=ON to make the table readable when you create the index:
CREATE NONCLUSTERED INDEX [IX_NonClusteredIndexDemo_StudentName] ON [dbo].[NonClusteredIndexDemo] ([StudentName],[STDAddress]) WITH ( ONLINE=ON, FILLFACTOR=90)
To get more information of a query process use:
SET STATISTICS TIME ON SET STATISTICS IO ON SELECT * FROM [dbo].[DimAccount] WHERE [AccountType] ='Assets'
More Information:
https://www.sqlshack.com/tracing-and-tuning-queries-using-sql-server-indexes/
https://dataschool.com/sql-optimization/how-indexing-works/
- Columns with text, image, ntext, varchar(max), nvarchar(max) and varbinary(max) cannot be used in the index key columns.
- It is recommended to use an integer data type in the index key column. It has a low space requirement and works efficiently. Because of this, you’ll want to create the primary key column, usually on an integer data type.
- You should consider creating a primary key for the column with unique values. If a table does not have any unique value columns, you might define an identity column for an integer data type. A primary key also creates a clustered index for the row distribution.
- You can consider a column with the Unique and Not NULL values as a useful index key candidate.
- You should build an index based on the predicates in the Where clause. For example, you can consider columns used in the Where clause, SQL joins, like, order by, group by predicates, and so on.
- You should join tables in a way that reduces the number of rows for the rest of the query. This will help query optimizer prepare the execution plan with minimum system resources.
- If you use multiple columns for an index key, it is also essential to consider their position in the index key.
- You should also consider using included columns in your indexes.
https://blog.quest.com/11-sql-server-index-best-practices-for-improved-performance-tuning/
https://hungdoan.com/2017/04/13/nvarcharn-vs-nvarcharmax-performance-in-ms-sql-server/
The clustered index defines the order in which the table data will be sorted and stored. As mentioned before, a table without indexes will be stored in an unordered structure. When you define a clustered index on a column, it will sort data based on that column values and store it. Thus, it helps in faster retrieval of the data.
There can be only one clustered index on a table because the data rows can be stored in only one order.
When you create a Primary Key constraint on a table, a unique clustered index is automatically created on the table.
The non-clustered index does not sort the data rows physically. It creates a separate key-value structure from the table data where the key contains the column values (on which a non-clustered index is declared) and each value contains a pointer to the data row that contains the actual value. It is similar to a textbook having an index at the back of the book with page numbers pointing to the actual information.
https://www.tutorialsteacher.com/sqlserver/indexes
https://www.sqlshack.com/designing-effective-sql-server-clustered-indexes/
https://developer.ibm.com/articles/i-sql-indexs-and-native-io/
https://www.brentozar.com/archive/2021/06/learn-fundamentals-of-index-tuning-for-1/
https://www.sqlshack.com/designing-effective-sql-server-non-clustered-indexes/