No, SQL Server does not do anything like this automatically. You can achieve some of what you're after with data compression, but its highest granularity is at the page level. So you'll get some good compression in the index pages if you have an index on only this column (or at least with this column as the leading key column), but assuming the clustered index is not on this column, you'll get much less benefit from it when the data and other index pages contain many variations of the string.
Data compression uses multiple algorithms, including dictionary, which would have the best impact if all of the values on a page are the same. Even at its best, though, this is not going to be extremely valuable on a large table - imagine you have a million pages and each page has two copies of one of those values. Sure, compression buys you some savings by storing the value only once instead of twice on each page (plus some negligible pointer overhead), but SQL Server still stores a million copies - one per page!
What I suggest, instead of storing the same dozen strings over and over again, is to create a lookup table with a TINYINT key, allowing you to only store each description string once, no matter how many times it's used. You can always retrieve the description at query time without ever having to store it with the data, and you can even create views to make this mostly transparent to queries, applications and users. This can also be a more attractive option if your workload is CPU-bound; while data compression saves you on storage and memory, there is some CPU overhead involved in compressing and decompressing each page.
For example:
CREATE TABLE dbo.TypeInfo
(
TypeInfoID TINYINT PRIMARY KEY,
Description VARCHAR(64) NOT NULL UNIQUE
);
INSERT dbo.TypeInfo(TypeInfoID, Description) VALUES
(1,'very_long_descriptor_someone_came_up_with_a_long_time_ago'),
(2,'this_desciptor_is_also_very_long'),
(3,'nobody_knows_why_this_descriptor_is_so_long');
-- ... 9 other values ...
Now you need to add the TypeInfoID column to the original table:
ALTER TABLE dbo.OtherTable
ADD TypeInfoID TINYINT;
Then you can update the existing data as follows:
UPDATE o
SET TypeInfoID = t.TypeInfoID
FROM dbo.OtherTable AS o
INNER JOIN dbo.TypeInfo AS t
ON o.TypeInfo = t.Description;
(And process this in batches, if the table is large, and you want to minimize impact to the log and blocked operations.)
Then you can add a foreign key constraint:
ALTER TABLE dbo.OtherTable
ADD CONSTRAINT fk_TypeInfo
FOREIGN KEY (TypeInfoID) REFERENCES dbo.TypeInfo(TypeInfoID);
Once all of the data is verified to be correct, you can drop the column (first drop any constraints and indexes that reference it):
ALTER TABLE dbo.OtherTable
DROP COLUMN TypeInfo;
Then you can create a view that your queries can use to keep the semantics the same:
CREATE VIEW dbo.vOtherTable
AS
SELECT /* o.columns */, TypeInfo = t.Description
FROM dbo.OtherTable AS o
INNER JOIN dbo.TypeInfo AS t
ON o.TypeInfoID = t.TypeInfoID;
(Or in some scenarios you could even rename the table, and give the view the name the table used to have, making it completely transparent.)
And finally, if you don't want to allow NULLs in this column, and if you are on SQL Server 2012 or above, you can alter the column to NOT NULL without incurring updates and logging:
ALTER TABLE dbo.OtherTable
ALTER COLUMN TypeInfo TINYINT NOT NULL;
If you are on an earlier version, you might consider adding a CHECK CONSTRAINT instead:
ALTER TABLE dbo.OtherTable
WITH NOCHECK ADD CONSTRAINT ck_TypeInfoNotNULL
CHECK (TypeInfoID IS NOT NULL);
But this may not be ideal, as an untrusted constraint can affect the ability of the optimizer to use it... in a lot of cases it is better to just take the one-time performance hit up front.
I think much of this is way beyond the scope of your original question, and you may not even consider any of this solution compelling, but if you do, you may want to do your own research and testing to ensure the least intrusive method of making this change. You can start with the following questions here and on Stack Overflow:
Best Answer
If I had to guess it's the object id for sys.objects. So
Specifically look at the name column (object name) and the type column (type of the object, for example U for user table, P for stored procedure etc).