I never knew this, but stored procedures have versions.
I was browsing the CREATE PROCEDURE doc page, and stumbled upon this item. In the beginning part of a stored procedure definition, after the name, there is a section that starts with
; number
Hmmm, I read the entry and it says the number is an integer that is used to group procedures of the same name. There is a note this is a deprecated feature that may be dropped, should not be used for new work, and old work ought to be modified, but it was interesting, so I tried it.
First, create a proc:
CREATE PROCEDURE dbo.GetOne
AS
SELECT 1
GO
Once this is done, we can execute it and it works. Now, let’s modify this. I’ll use this code:
CREATE PROCEDURE GetOne;2
AS
SELECT 'One'
GO
You can see these will have different results. If I execute them, I do so with the name, including the optional integer.
In SSMS, there is only one object listed.
If I drop the procedure, both are gone.
I’m not completely sure where I’d use this feature, and I can see not investing in it, but I found this fascinating. All these years of writing stored procedures and I learned something new this week.
Voice of the DBA 