I had a question from one of my students in my MCIS-4423 class at University College at Denver University about the various index options that you can choose when you create a relational index. Using the SQL Server Management Studio (SSMS) GUI, you can easily specify most options for index creation, and then you can either just create the index or click on the Script button to generate a T-SQL script that you can check and then run. I prefer the latter.
The common naming standard for non-clustered indexes is to start with an upper case IX_, then the table name, then an underscore, and the column name(s). So for example, we have IX_PersonContact_EmailAddress shown below. The order of the columns in the index is very important. You want the more selective columns first, and columns that are used in WHERE clauses generally should be first.
On the Options Page, you may want to set a non-default fill factor (depending on how volatile the data is). If your data is frequently updated, you may want a lower fill factor, to help avoid page splits and to minimize your index maintenance, (at the cost of large indexes). If you have Enterprise or Developer Edition, you should consider checking “Allow online processing of DML statements while building the index”, which allows SQL Server to build the index without locking up the table during the index creation. This is an extremely valuable option if you are in a 24×7 production environment.
If you have multiple processors or processor cores, you may want to set the maximum degree of parallelism to a value of about 25% of the number of processors that are seen by the operating system. For example, on a two socket, quad-core (eight total cores), you might set this value to 2 or 3. This prevents the index creation from using all of your processor cores and possibly impacting the overall performance of the database server while the index is being created. Of course, this may make the index take longer to create, but it is usually safer.
Here is what the T-SQL would look like for this.
USE [AdventureWorks] GO CREATE NONCLUSTERED INDEX [IX_PersonContact_EmailAddress] ON [Person].[Contact] ( [EmailAddress] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, MAXDOP = 2, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO