Index on Varchar?

Question

We are experiencing serious problems with locking the table in production. I noticed that I created a stored procedure that gets a list of orders by order number. The order number is VARCHAR (150). There is no index of any type in this column.

This column currently has many NULL values. However, over time (this table has recently been published), the table will increase significantly. No more NULL values ​​will be added at this time.

My question is double. First, is the index useful here. Prok is actively used. And if so, should this be grouped or not? Data such things as CP123456, DR126512.

The second question, which probably affects the first question, would be useful to change the column to CHAR (10), since it "seems", the order number is always the same size. Is there a speed advantage when setting the index to a fixed CHAR length, unlike VARCHAR (150)?

(Different in size due to unknown requirements when creating a column).

SQL Server 2008

Answer 1

• Yes, absolutely! Go straight and add an index. Index clustering is probably not needed here, and in any case it will not be possible if you already have another clustered index (such as a primary key) in the table.

• Changing a column to CHAR(10) may have some advantages in terms of storage size, but it is unlikely to have a particularly strong effect on index performance. I missed it now.

Answer 2

I have no references to citing about this, only experience / anecdotal evidence.

First, queries can almost always be improved with indexes. The exact benefit depends on the request.
- If the query requires only specific records / a small part of the table, the index will help - If the query requires the entire table, but can be useful from the ordered data, the index will help

Clustered indexes typically provide performance advantages over nonclustered indexes. In a very simplified sense, using a non-clustered index is like using two tables and joining them together (the friendly search index is used first, which is then combined with the data itself - if the index does not contain all the data fields that you need).

However, it discusses the order in which data is added to your table. If your clustered index means that data is often inserted or deleted in the middle of the table, you will get fragmentation and other artifacts. However, in my experience, awareness and consideration of this is necessary only in extreme situations.

In short, DEFINITELY index your data. And a clustered index is usually best suited to meet your worst queries.

Regarding the difference between VARCHAR and CHAR? In the old days, it was important to keep variable-length fields at the end of your data to make identifying fixed-length fields easier. This meant that having the VARCHAR field as your first field and using it as a unique identifier was pretty low.

Currently, the difference in performance is negligible. Personally, I still save the unique identifiers as a fixed length. Variable-length data usually does not have a noticeable operating cost, but when you really do comparisons for join predicates, etc., it is much more appropriate, if possible, to have fixed-length fields.