Post reply

Speed question for computed columns

  • June 13, 2012 at 1:45 am

    #392506

    I have a zip code table that stores Latitude and Longitude. All my calculations use radians.

    I am thinking of adding Computed Columns that contain the radian values. This would make my code simpler and easier to read, but will these be calculated at run time anyway? I don't update the zip code table very often I could also run a script after updates and populate them that way. I don't join on the radian fields so I don't see a need for indexes.

  • June 13, 2012 at 4:29 am

    #1500247

    If the formulae for the computed columns are deterministic (in SQL Server's opinion), then you can persist the values, and even index them if you want to.

    Here's a sample of what that looks like:

    CREATE TABLE dbo.DropMe (

    ID INT IDENTITY PRIMARY KEY,

    CompCol AS ID+1 PERSISTED);

    If the column is persisted, the value in it will be calculated when the row is inserted/updated. If it isn't persisted, it will be calculated at runtime.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply