Professional Web Applications Themes

Partial Index - Microsoft SQL / MS SQL Server

Can someone tell if it's possible to partially index a varchar. For instance, I have a varchar(40) field, but I don't want to index all 40 characteres; I want to index the first 15 characters. Is this possible? And if it is, how might one write the SQL statement. I've tried using the Substring function, but it has failed to work. Thanks, Maggy...

  1. #1

    Default Partial Index

    Can someone tell if it's possible to partially index a
    varchar. For instance, I have a varchar(40) field, but I
    don't want to index all 40 characteres; I want to index
    the first 15 characters. Is this possible? And if it is,
    how might one write the SQL statement. I've tried using
    the Substring function, but it has failed to work.

    Thanks,

    Maggy
    Maggy Guest

  2. #2

    Default Re: Partial Index


    I thinks it is possible, but you have to change some things. Look this
    example:

    CREATE TABLE [dbo].[example] (
    [a] [varchar] (40) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
    [b] AS (left([a],15))
    ) ON [PRIMARY]
    GO

    set
    ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF
    IER,ANSI_NULLS on
    GO

    set NUMERIC_ROUNDABORT off
    GO

    CREATE INDEX [IX_example] ON [dbo].[example]([b]) ON [PRIMARY]
    GO

    set arithabort OFF
    GO

    set numeric_roundabort OFF
    GO

    set quoted_identifier OFF
    GO



    --
    --

    Un saludo

    --
    --
    ----------------------------------------------
    "Sólo sé que no sé nada. " (Sócrates)

    (Guía de netiquette del foro)
    [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
    [url]http://perso.wanadoo.es/rubenvigon/foro[/url]

    (FAQ's de SQL Server)
    [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
    [url]http://www.helpdna.net/bosqlfaq.htm[/url]

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> escribió en el mensaje
    news:enYUuaJQDHA.1584TK2MSFTNGP11.phx.gbl...
    > It is not possible to do that at this time.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Maggy" <rmagnussolutran.com> wrote in message
    > news:407601c34096$23da5890$a401280aphx.gbl...
    > > Can someone tell if it's possible to partially index a
    > > varchar. For instance, I have a varchar(40) field, but I
    > > don't want to index all 40 characteres; I want to index
    > > the first 15 characters. Is this possible? And if it is,
    > > how might one write the SQL statement. I've tried using
    > > the Substring function, but it has failed to work.
    > >
    > > Thanks,
    > >
    > > Maggy
    >
    >

    Carlos Sacristan Guest

  3. #3

    Default Re: Partial Index

    Well yes, if you want to create a new column then you can index it. But you
    can't use expressions in the index definition itself.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Carlos Sacristan" <csacristanARROBAocasoPUNTOes> wrote in message
    news:ONOhrjJQDHA.2256TK2MSFTNGP11.phx.gbl...
    >
    > I thinks it is possible, but you have to change some things. Look this
    > example:
    >
    > CREATE TABLE [dbo].[example] (
    > [a] [varchar] (40) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
    > [b] AS (left([a],15))
    > ) ON [PRIMARY]
    > GO
    >
    > set
    >
    ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF
    > IER,ANSI_NULLS on
    > GO
    >
    > set NUMERIC_ROUNDABORT off
    > GO
    >
    > CREATE INDEX [IX_example] ON [dbo].[example]([b]) ON [PRIMARY]
    > GO
    >
    > set arithabort OFF
    > GO
    >
    > set numeric_roundabort OFF
    > GO
    >
    > set quoted_identifier OFF
    > GO
    >
    >
    >
    > --
    > --
    >
    > Un saludo
    >
    > --
    > --
    > ----------------------------------------------
    > "Sólo sé que no sé nada. " (Sócrates)
    >
    > (Guía de netiquette del foro)
    > [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
    > [url]http://perso.wanadoo.es/rubenvigon/foro[/url]
    >
    > (FAQ's de SQL Server)
    > [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
    > [url]http://www.helpdna.net/bosqlfaq.htm[/url]
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> escribió en el mensaje
    > news:enYUuaJQDHA.1584TK2MSFTNGP11.phx.gbl...
    > > It is not possible to do that at this time.
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "Maggy" <rmagnussolutran.com> wrote in message
    > > news:407601c34096$23da5890$a401280aphx.gbl...
    > > > Can someone tell if it's possible to partially index a
    > > > varchar. For instance, I have a varchar(40) field, but I
    > > > don't want to index all 40 characteres; I want to index
    > > > the first 15 characters. Is this possible? And if it is,
    > > > how might one write the SQL statement. I've tried using
    > > > the Substring function, but it has failed to work.
    > > >
    > > > Thanks,
    > > >
    > > > Maggy
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

  4. #4

    Default Re: Partial Index


    That's because I said to Maggy 'you have to change some things' ;-)


    --
    --

    Un saludo

    --
    --
    ----------------------------------------------
    "Sólo sé que no sé nada. " (Sócrates)

    (Guía de netiquette del foro)
    [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
    [url]http://perso.wanadoo.es/rubenvigon/foro[/url]

    (FAQ's de SQL Server)
    [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
    [url]http://www.helpdna.net/bosqlfaq.htm[/url]

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> escribió en el mensaje
    news:ug$v$wJQDHA.3192TK2MSFTNGP10.phx.gbl...
    > Well yes, if you want to create a new column then you can index it. But
    you
    > can't use expressions in the index definition itself.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Carlos Sacristan" <csacristanARROBAocasoPUNTOes> wrote in message
    > news:ONOhrjJQDHA.2256TK2MSFTNGP11.phx.gbl...
    > >
    > > I thinks it is possible, but you have to change some things. Look
    this
    > > example:
    > >
    > > CREATE TABLE [dbo].[example] (
    > > [a] [varchar] (40) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
    > > [b] AS (left([a],15))
    > > ) ON [PRIMARY]
    > > GO
    > >
    > > set
    > >
    >
    ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF
    > > IER,ANSI_NULLS on
    > > GO
    > >
    > > set NUMERIC_ROUNDABORT off
    > > GO
    > >
    > > CREATE INDEX [IX_example] ON [dbo].[example]([b]) ON [PRIMARY]
    > > GO
    > >
    > > set arithabort OFF
    > > GO
    > >
    > > set numeric_roundabort OFF
    > > GO
    > >
    > > set quoted_identifier OFF
    > > GO
    > >
    > >
    > >
    > > --
    > > --
    > >
    > > Un saludo
    > >
    > > --
    > > --
    > > ----------------------------------------------
    > > "Sólo sé que no sé nada. " (Sócrates)
    > >
    > > (Guía de netiquette del foro)
    > > [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
    > > [url]http://perso.wanadoo.es/rubenvigon/foro[/url]
    > >
    > > (FAQ's de SQL Server)
    > > [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
    > > [url]http://www.helpdna.net/bosqlfaq.htm[/url]
    > >
    > > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> escribió en el mensaje
    > > news:enYUuaJQDHA.1584TK2MSFTNGP11.phx.gbl...
    > > > It is not possible to do that at this time.
    > > >
    > > > --
    > > >
    > > > Andrew J. Kelly
    > > > SQL Server MVP
    > > >
    > > >
    > > > "Maggy" <rmagnussolutran.com> wrote in message
    > > > news:407601c34096$23da5890$a401280aphx.gbl...
    > > > > Can someone tell if it's possible to partially index a
    > > > > varchar. For instance, I have a varchar(40) field, but I
    > > > > don't want to index all 40 characteres; I want to index
    > > > > the first 15 characters. Is this possible? And if it is,
    > > > > how might one write the SQL statement. I've tried using
    > > > > the Substring function, but it has failed to work.
    > > > >
    > > > > Thanks,
    > > > >
    > > > > Maggy
    > > >
    > > >
    > >
    > >
    >
    >

    Carlos Sacristan Guest

Similar Threads

  1. partial index on boolean, problem with v8.0.0rc1
    By Igor Shevchenko in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: December 13th, 06:59 PM
  2. Replies: 4
    Last Post: August 3rd, 03:11 PM
  3. Newb query: index.htm & index.php & the server default
    By Lab309 in forum PHP Development
    Replies: 7
    Last Post: September 22nd, 02:08 PM
  4. Partial Borders
    By David Lally in forum Adobe Photoshop Elements
    Replies: 1
    Last Post: August 30th, 06:42 PM
  5. Put partial text
    By Alvin Bakker in forum Macromedia Director Lingo
    Replies: 1
    Last Post: August 5th, 12:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139