Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - index on a computed column?

I'm joining to a table dozens of different times, and every time, I join (or filter) based on the results of a SUBSTRING of one of the columns (it's a string, but left-padded with zeros, and I don't care about the last four digits). As a result, even though this column is indexed and my query would use the index, it does a table scan because the SUBSTRING itself isn't indexed, so SQL Server has to calculate it for every row before as it's joining.

I'm looking for any ideas on how to speed up this process. Currently, there's a view on the table (It's a "SELECT * FROM", just to give the table a friendly name), and I'm considering adding a column to the view that's computed, and then indexing that. I'm open to other suggestions, though - any thoughts?

MORE DETAIL: I should have shared this to begin with. The table receives replication from our billing system, so editing the underlying table to add a computed column is not an option. Any computed column would have to be added to the view on the table. Also, the leading zeros aren't always leading zeros - they're sometimes other data that I'm not interested in. I suppose the real question is "How can I join to data in the middle of a VARCHAR column while also making use of an index? Full-text Search?"

Clarifying my example I'm simplifying, but essentially, let's say I'm trying to look up values in a column with the following values:

00000012345MoreStuff
00000012345Whatever
19834212345
Houses12345837443GGD
00000023456MoreStuff

I'm interested in rows where SUBSTRING(7,5)="12345", so I'd want rows 1-4, but not row 5. What I'm proposing is adding a column to my "SELECT *" view that has this substring in it, and then indexing based on that. Does that make more sense?

like image 601
SqlRyan Avatar asked Aug 24 '09 15:08

SqlRyan


People also ask

Can I create index on computed column SQL Server?

Create indexes on persisted computed columnsYou can do this when the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated.

What keyword will create an index on a computed column?

To create a persisted computed column, you need to add the PERSISTED keyword to your column definition, as shown in the following CREATE TABLE statement.

Can we create index on CTE?

You cannot index a CTE, but the approach is that the CTE can make use of the underlying indexes. In the above query, a JOIN b cannot make use of an index on t. myname because of the GROUP BY .

Are there any disadvantages of using computed column?

Some Limitations. You can not reference columns from other tables for a computed column expression directly. You can not apply insert or update statements on computed columns.


2 Answers

Assuming you have your fields in this format:

00Data0007
000000Data0011
0000Data0015

, you can do the following:

  • Create a computed column: ndata AS RIGHT(REVERSE(data), LEN(data) - 4)

    This will transform your columns into the following:

    ataD00
    ataD000000
    ataD0000
    
  • Create an index on that column

  • Issue this query to search for the string Data:

    SELECT  *
    FROM    mytable
    WHERE   ndata LIKE N'ataD%'
            AND SUBSTRING(ndata, LEN(N'ataD') + 1, LEN(ndata)) = REPLICATE('0', LEN(ndata) - LEN('ataD'))
    

    The first condition will use an index for coarse filtering.

    The second will make sure that all leading characters (that became the trailing characters in the computed column) are nothing but zeros.

See this entry in my blog for performance detail:

  • SQL Server: leading wildcard match using an index

Update

If you just want an index on SUBSTRING without changing your schema, creating a view is an option.

CREATE VIEW v_substring75
WITH SCHEMABINDING
AS
SELECT  s.id, s.data, SUBSTRING(data, 7, 5) AS substring75
FROM    mytable

CREATE UNIQUE CLUSTERED INDEX UX_substring75_substring_id ON (substring75, id)

SELECT  id, data
FROM    v_substring75
WHERE   substring75 = '12345'
like image 199
Quassnoi Avatar answered Sep 28 '22 04:09

Quassnoi


Add a calculated column to your table and create an index on this column.

ALTER TABLE MyTable
Add Column CodeHead As LEFT(Code,Len(Code)-4)

Then create an index on this.

CREATE INDEX CodeHeadIdx ON MyTable.CodeHead
like image 38
pjp Avatar answered Sep 28 '22 03:09

pjp