none
how to create index on non-deterministic computed column

    Question

  • Hi All,

    Thanks in advance.

    Please guide me to create index on the non-deterministic computed column in sql server 2008.

    Thanks

    Rajan.S

    Tuesday, April 02, 2013 10:27 AM

Answers

  • Hi All,

    Thanks,

    I have created the index on the non-deterministic computed column by including the keyword   'with schemabinding' in the function which is used for creating the computed column.

    example:

    alter function func1(@name varchar(50))
      returns varchar(50)
      with schemabinding
      begin
      return @name+'abc'
     
      end

    create table tbl_check
    ( id int,
      name varchar(50),
      nameincaps as dbo.func1(name))

    create index idx1 on tbl_check(nameincaps)

    Hence the index has been created successfully.

    Thanks

    Rajan.S

    • Marked as answer by pyarajan Tuesday, April 02, 2013 11:03 AM
    Tuesday, April 02, 2013 10:51 AM

All replies

  • Please refer to the below Link

    http://msdn.microsoft.com/en-us/library/ms189292.aspx


    Thanks and regards, Rishabh K

    Tuesday, April 02, 2013 10:31 AM
  • Please refer the below link:

    http://msdn.microsoft.com/en-in/library/ms189292.aspx#BKMK_persisted

    Section: "Creating Indexes on Persisted Computed Columns"


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, April 02, 2013 10:32 AM
  • Hi All,

    Thanks,

    I have created the index on the non-deterministic computed column by including the keyword   'with schemabinding' in the function which is used for creating the computed column.

    example:

    alter function func1(@name varchar(50))
      returns varchar(50)
      with schemabinding
      begin
      return @name+'abc'
     
      end

    create table tbl_check
    ( id int,
      name varchar(50),
      nameincaps as dbo.func1(name))

    create index idx1 on tbl_check(nameincaps)

    Hence the index has been created successfully.

    Thanks

    Rajan.S

    • Marked as answer by pyarajan Tuesday, April 02, 2013 11:03 AM
    Tuesday, April 02, 2013 10:51 AM
  • create

    function dbo.func1(@name varchar(50))

    returns varchar(50)

    with schemabinding

    begin

    return @name+'abc'

    end

     

    create

    table dbo.tbl_check

    (

    id int,

    name varchar(50),

    nameincaps as dbo.func1(name) PERSISTED)

     

    create

    index idx1 on dbo.tbl_check(nameincaps)

    Drop

    table dbo.tbl_check


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, April 02, 2013 10:55 AM