{"id":9516,"date":"2022-04-15T06:28:00","date_gmt":"2022-04-15T04:28:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=9516"},"modified":"2022-08-08T17:56:57","modified_gmt":"2022-08-08T15:56:57","slug":"create-sql-server-index","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-index\/","title":{"rendered":"Create a SQL Server index"},"content":{"rendered":"\n

Create an index in SQL Server to improve the performance of queries on tables and in clustered views. Two types of indexes exists, the clustered and the nonclustered one. <\/p>\n\n\n\n

To create a non-clustered index, use this example and adapt it to the project requirements. Indexes are the first recommended step to performance optimization in SQL Server.<\/p>\n\n\n\n

Create a SQL Server non clustered index on a table<\/h2>\n\n\n\n

First, the how-to section below creates an index on the sales table created here<\/strong><\/a>.<\/p>\n\n\n\n

Let’s explain the context of the example. Firstly, it’s a very basic example to show the CREATE INDEX T-SQL command and how easy it is to optimize performance. <\/p>\n\n\n\n

Secondly the two indexed columns are the typical ones used in large queries execution, i.e., the Year and the month. Another very common one is the date.<\/p>\n\n\n\n

Why the large sales tables need to use some date related columns as indexed columns ? <\/strong><\/p>\n\n\n\n

Let’s consider any business, the managers need to see the figures in a context to know how good the sales are. To achieve this the time is the best dimension, because the time comparisons are the most common and because the business financial managers use fiscal year as references. <\/p>\n\n\n\n

Popular views are the yearly, quarterly, monthly, and daily ones. and as the data evolves along the fiscal year, indexes<\/a> evolves and follow the date.<\/p>\n\n\n\n

Step 1: Create the sales table to be indexed<\/h3>\n\n\n\n

For instance, the sales tables have this initial structure. Use the following SQL code to create the sales table as an example for the index creation.<\/p>\n\n\n\n

CREATE TABLE [dbo].[SALES] \n(\n\t[Year] TINYINT,    \t\n\t[MonthName] NVARCHAR(50),  \n\t[MonthCurrent]    BIT,     \t\n\t[NumberMonth]     TINYINT,   \t\n\t[EmployeeNumber]   SMALLINT,   \n\t[NumberOfClients]   INTEGER,   \n\t[NumberOfSales]   BIGINT,   \t\n\t[Amount_ET]     NUMERIC(15,5), \n\t[Amount_IT]     DECIMAL(15,5)  \n);\n<\/pre>\n\n\n
\n
\"Create
Create the SQL Server  table to be indexed in SSMS <\/figcaption><\/figure><\/div>\n\n\n
Step 2: Create the SQL Server index on the sales table using this syntax<\/h3>\n\n\n\n
Finally, let’s consider that on our Analytics system the main columns to filter data. It’s the year and the month name: Year and MonthName.<\/p>\n\n\n\n
CREATE INDEX indexYearMonth \n\tON [dbo].[SALES] ([Year], [MonthName]);\n<\/pre>\n\n\n
\n
\"Create
Create a SQL Server index and add it to the Sales table<\/figcaption><\/figure><\/div>\n\n\n
Step 3: Check the index presence and type under the table in SSMS<\/h3>\n\n\n\n
Expand the sales table properties and check the index presence. Note that per default the index is a non-unique and non-clustered index.<\/p>\n\n\n
\n
\"Check
Check the index presence and type under the table with SSMS<\/figcaption><\/figure><\/div>\n\n\n
Notes:<\/p>\n\n\n\n