Best way to implement an insert-only table to prevent updating huge number of fields in SQL Server I'm going to have a table that holds pricing data of a lot of hotels, and this data will be updated regularly, say every one hour. I have a terrible experience for such a scenario, that we have to update a lot of rows, that causes deadlocks and leads to bad performance, so I'm going to avoid updating at all.
I think a good way is to insert every newly received price record, and specify some kind of version for them. So the table becomes something like this:
================================
RoomId | Date  | Price | Created
================================
1      | 29Dec | $1000 | 12:00
1      | 29Dec | $1200 | 12:30
1      | 29Dec | $1300 | 12:40
...

So when I'm going to query the current price, the row with the largest version (grouped by RoomId and Date) will be the desired result. The query is probably something like this:
SELECT * FROM (
    SELECT RoomId, Date, Price, ROW_NUMBER() OVER (PARTITION BY RoomId, Date ORDER BY Created DESC) AS rn 
    FROM Prices
) s
WHERE rn = 1

I'm not sure about this way being the best way, and also not sure about the structure of table and the appropriate query to achieve that.
Any ideas would be appreciated.
So if you are using an ORM I'd suggest that in this particular case you point the actual operation to a stored procedure instead of allowing the ORM to create the insert/update/delete statements (although this is preferable in general).  If you're using Entity Framework, this is fairly simple.  If the ORM doesn't support using your own SQL for create/update/delete, don't use it.
So our requirements are:

For a given date, a room has one price.
Prices for a room/date can be changed

This leaves two options:

Create an audit table to store changes resulting from updates, set up our stored procedure to enforce the audit rules
Use the table to store the current a past values, set up our stored procedure to insert new rows only when necessary

The first option works, but if you need a lot of concurrency or need to query past prices as valid values, the second option works better.  It begins like this:
CREATE TABLE Room
(
  RoomId  INT NOT NULL
  /* Other stuff */
 ,CONSTRAINT PK_Room PRIMARY KEY (RoomId)
)
;

CREATE TABLE RoomPrice
(
  RoomId      INT           NOT NULL
 ,StartDt     DATE          NOT NULL
 ,Price       DECIMAL(9,2)  NOT NULL
 ,UpdateDtm   DATETIME2(2)  NOT NULL
 ,CONSTRAINT FK_RoomPrice_Charged_For_Room FOREIGN KEY (RoomId) REFERENCES Room (RoomId)
 ,CONSTRAINT PK_RoomPrice PRIMARY KEY (RoomId, UpdateDtm)
 ,CONSTRAINT CK_RoomPrice_Price_Is_GT_Zero CHECK (Price &gt; 0.00)
)
;

Now the tricky part, which is ensuring everything behaves how we expect it should.  This is actually embedded in the table design, but it's not obvious without some explanation.  The rules are:

When a new row is inserted, it is the most recent version.  That price applies to all dates on or after the StartDate.
When a price changes, if the new StartDate is greater than or equal to all prior StartDates and greater than or equal to the current date1, the insert is valid.
When a price changes, if the new StartDate is greater than or equal to the current date but not greater than all prior StartDates, the insert is still valid, but all rows with a StartDate greater than the new StartDate are now invalid.

This is all handled by the following query.  To get a price for room @room as of date @date and point in time @dtm, we would use the following query (which should be wrapped in a stored procedure):
SELECT
  RP.Price
FROM
  RoomPrice RP
WHERE
  RP.RoomId = @room
    AND RP.UpdateDtm =
      (
        SELECT
          MAX(UpdateDtm)
        FROM
          RoomPrice
        WHERE
          RoomId = RP.RoomId
            AND UpdateDtm &lt;= @dtm
            AND StartDt &lt;= @date
      )

To set/update a price we would create a procedure like this (h/t to Erland Sommarskog's excellent blog post on error handling):
CREATE PROCEDURE RoomPrice_Set
  @RoomId     INT
 ,@StartDt    DATE
 ,@Price      DECIMAL(9,2)
AS
  SET NOCOUNT, XACT_ABORT ON;
  
  /* Need to put some time zone logic into this */
  IF @StartDt &lt; SYSDATETIME() THROW 50001, 'Date must be greater than or equal to current date',1;
  ELSE
    BEGIN TRY
      BEGIN TRANSACTION
      INSERT INTO RoomPrice (RoomId,StartDt,Price,UpdateDtm)
      SELECT
        i.RoomId
       ,i.StartDt
       ,i.Price
       ,SYSDATETIME()
      FROM
        (VALUES (@RoomId, @StartDt, @Price)) AS i(RoomId, StartDt, Price)
      LEFT JOIN
        RoomPrice RP
          ON RP.RoomId = i.RoomId
              AND RP.UpdateDtm =
                (
                  SELECT
                    MAX(UpdateDtm)
                  FROM
                    RoomPrice
                  WHERE
                    RoomId = i.RoomId
                      AND StartDt &lt;= i.StartDt
                )
      WHERE
        i.Price &lt;&gt; ISNULL(RP.Price,-1)
        
      COMMIT TRANSACTION
    END TRY
    
    BEGIN CATCH
      IF @@TRANCOUNT &gt; 0 ROLLBACK TRANSACTION
      ;THROW
    END CATCH
GO

Fiddle here: http://sqlfiddle.com/#!18/c50573/8
1This should be the only scenario, it would be very frowned upon if prices changed after the fact.