1

What's the difference between this two commands?

Refreshview is only for views and recompile only for stored procedures? or there are some other differences?

Icet
  • 678
  • 2
  • 13
  • 31
  • I'm voting to close this question as off-topic because it is answered by reading the documentation corresponding to each stored procedure. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql & https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-recompile-transact-sql – alroc Jun 16 '17 at 11:25

2 Answers2

2

Both of these system procedure can be used on a view or table. However, they do very different things.

sp_refreshview 'MyView' causes the metadata for a view to be refreshed. This means that any DDL changes (such as ALTER) to base tables will be reflected in the view. This is particularly important for views which use select *.

sp_recompile 'MyView' causes all procedures, triggers and functions which reference the view to be recompiled and get a new query plan. It does not update the metadata for the view: if the view's base tables have changed then the metadata will still be incorrect.

You can see the difference in this fiddle. Notice how the new column was shown only after sp_refreshview was executed.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
1

You should use sp_refreshview. sp_recompile is only for triggers and stored procedures.

Mariusz Adamczewski
  • 107
  • 1
  • 9
  • 2
    Why do you say "You should use sp_refreshview"? The question doesn't state that a view needs to be refreshed, only what the difference is. If a view needs to be refreshed then he should use sp_refreshview. If a proc needs to be recompiled then he should use sp_recompile. – GandRalph Jun 16 '17 at 11:54