The Tomb Raider series is pretty fun.

You're going to get the same tired old list of games that you can find anywhere on the internet:
Planescape Torment
VAmpire Masquerade
Blah Blah Blah

But there's only one game you really need to play:
Shadowman

It's like the old Tomb Raider games. But better.

Plus it has the best not-really-The Doors-(probably because they couldn't get the license)-but-sounds-like-The Doors soundtracked psychedelic Viet Nam flashback boss fight ever.

I'm going to recommend some of my favourites. Hope you find some games that you like:

Legacy of Kain: Soul Reaver
Soul Reaver 2
Blood Omen 2
Legacy of Kain: Defiance

Prince of Persia: The Sands of Time
Prince of Persia: Warrior Within
Prince of Persia: The Two Thrones

Soldier of Fortune: Platinum Edition
Soldier of Fortune II: Double Helix - Gold Edition

The Suffering
The Suffering: Ties That Bind

Mafia

Shadow Man

Medal of Honor: Allied Assault War Chest

Return to Castle Wolfenstein

Clive Barker's Undying

Tron 2.0

F.E.A.R. Platinum

Baldur's Gate 1-2, Blood, Commandos Ammo Pack, Day Of The Tentacle, Deus Ex, Diablo, Duke Nukem, Doom 1-2, FEAR, Giants Citizens Kabuto, Gothic, Grim Fandango, Heretic, Hexen, Icewind Dale, Jagged Alliance 2, Medal Of Honor Allied Assault, Monkey Island (series), Morrowind, Myst, Neverwinter Nights, Planescape Torment, Psychonauts, Return To Castle Wolfenstein, Riven, Quake, Sam & Max Hit The Road, Serious Sam First / Second Encounter, Syberia 1-2, System Shock 1-2, The 7th Guest, The Dig, The Longest Journey, Thief 1-3, Tropico, Unreal, Zeus.

^ That should keep you occupied for a couple of hours... ;-)

I make you a list:
- FEAR.
- SWAT 4.
- Silent Hill 4.
- Heroes of Might and Magic 3.
- Fallout 1 & 2.
- Hitman 1, 2 and Contracts.
- Prince of Persia Sands of Time.
- Prince of Persia Warrior Within.
- Brother in Arms Road to Hill 30 and Earn in Blood.
- The Suffering.
- Call of Cthuluh.
- Undying.
- Star Wars Jedi Academy.
- Star Wars Knights of the Old Republic.
- Vampire The Masquerade Bloodlines.

That's a pretty wide net, even if you only want to play the best classics.

If I could only pick, say 3, then :
Dungeon Keeper, Thief 2 and Monkey Island

More generally:

RTS - Warcraft 3, Starcraft, Dawn of War, Red Alert 2, Populous: The Beginning, Age of Empires 2
TBS - Heroes of Might and Magic 3, Disciples 2, Age of Wonders 2, OpenXCom
FPS - Half-life 2, Thief 2 (though not really a shooter)
FlightSim - Freelancer, Wing Commander IV
Platformer - Too many insanely good classics to list: from Sonic to Earthworm Jim, to Prince of Persia, to Abe's Oddysee, to Mario, to the Disney film adaptions, to the various metroids, to the Metal Slug series, to goodness knows how many standalone releases not part of a bigger series.
Adventure/Point n Click - Again, too many great ones. Monkey Island is a good start, or even the free Beneath a Steel Sky on gog.
Sim/Management - OpenTTD, OpenRCT2, Startopia, Evil Genius, Simcity 4, The Impression Games city builders.

* and if I tried to remember and include everything I forgot to mention then the list would at least double...

So what if I do what other people here will do: post a list of my favorite games from that time period. What would that mean to you?
Given enough time, ALL games from 1992-2006 will be listed here (maybe an exaggeration, but do you get the point?).

most of the shooters pre-2006 that were great do not have enough playerbase to relive the experience. Tribes and older UT and day of defeat do not have a singleplayer experience that matches the online play from that time. I also want to suggest wolfenstein because it does have a singleplayer campaign. For racing games I thought Midnight Club 2 was great, and can be tracked down on pc dvd. Legacy of Kain: Blood Omen 2. Alot of console games were unique and cannot be enjoyed on PC, such a bloodwake or twisted metal.

Actually one game that is great from 2006 is Prey (2006). This can be gotten on steam and using a launcher update patch is DRM free.

Albion.
Baldur's Gate 2, Icewind Dale, Planescape Torment.
Freespace 1&2.
Commandos 1 and expansion.
Thief 1-3.
Outcast.
Jedi Knight, Jedi Outcast, Jedi academy.

All really great games, mid-1990s to early 2000s was a fantastic time for gaming.

All of them. '95 to '05 were the golden decade of gaming. Great games in general.
Check out your preferred genres, as my recommendations can only be personal.

1992 - 2006 is a MASSIVE time span. To put it in perspective, I went through games from 2005-2006 to choose ones I found memorable, excluding sport/expansions. And even then, it's quite long...

2005
Age of Empires III
Ankh
Battlefield 2
Black & White 2
Blitzkrieg 2
Boiling Point: Road to Hell
Call of Duty 2
Civilization IV
Cossacks II: Napoleonic Wars
Darwinia
Dead or Alive 4
Destroy All Humans!
Devil May Cry 3
Drakengard 2
Dungeon Siege II
Earth 2160
Empire Earth II
Fahrenheit
Fate
F.E.A.R.
God of War
Grandia III
Guild Wars
Hearts of Iron II
Myst V: End of Ages
N
Pariah
Pathologic
Prince of Persia: The Two Thrones
Psychonauts
Resident Evil 4
Shadow of the Colossus
Silent Hunter III
Soulcalibur III
Tom Clancy's Splinter Cell: Chaos Theory
Stubbs the Zombie in Rebel Without a Pulse
SWAT 4
UFO: Aftershock
We Love Katamari
X3: Reunion
True Crime: New York City

2006
Anno 1701
ArmA: Armed Assault
Battlefield 2142
Broken Sword: The Angel of Death
Caesar IV
Call of Duty 3
Call of Juarez
Company of Heroes
Dark Messiah of Might and Magic
DarkStar One
Dead Rising
DEFCON
Destroy All Humans! 2
Disgaea 2
Dwarf Fortress
Dreamfall: The Longest Journey
Elder Scrolls IV: Oblivion
Godfather: The Game
Gothic 3
GTR 2
Guild Wars Factions
Guild Wars Nightfall
Gumboy: Crazy Adventures
Half-Life 2: Episode One
Heroes of Might and Magic V
IL-2 Sturmovik: 1946
Just Cause
Legend of Heroes: Trails in the Sky SC
LocoRoco
Medieval II: Total War
Monster Hunter 2
Mortal Kombat: Armageddon
Need for Speed: Carbon
Neverwinter Nights 2
Ōkami
ParaWorld
Persona 3
Pokémon Diamond and Pearl
Tom Clancy's Rainbow Six: Vegas
Rayman Raving Rabbids
Red Orchestra: Ostfront 41-45
Runaway 2
Saints Row
Sam & Max Save the World
Sid Meier's Railroads!
Test Drive Unlimited
Titan Quest
Toribash
ÜberSoldier
Virtua Fighter 5
Viva Piñata
Yakuza 2
Ys Origin

2000:
Final Fantasy IX (PS1)
Baldur's Gate II: Shadows of Amn (PC)
Tony Hawk's Pro Skater 2 (DC/PC/PS1)
The Typing of the Dead (ARC, 1999/DC, 2000/PC, )
Command & Conquer: Red Alert 2 (PC, 2000)
​Phantasy Star Online (DC)
Deus Ex (PC)
SimCity 3000 Unlimited (PC)

2001:
Castlevania: Circle of the Moon (GBA)
Baldur's Gate II: Throne of Bhaal (PC)
Rez (DC/PS2)
Advance Wars (GBA)
Tactics Ogre: The Knight of Lodis (GBA)
Final Fantasy X (PS2)
Super Monkey Ball (ARC, /GC, 2001)

2002:
​Metroid Fusion (GBA)
Warcraft III: Reign of Chaos (PC)
TimeSplitters 2 (PS2/XB/GC)
Mega Man Zero (GBA)
The Legend of Zelda: The Wind Waker (GC)
Metroid Prime (GC)

2003.
WarioWare, Inc.: Mega Microgame$! (GBA)
Astro Boy: Omega Factor (GBA)
Warcraft III: The Frozen Throne (PC)
Fire Emblem (GBA)
Mega Man Zero 2 (GBA)
Star Wars: Knights of the Old Republic (PC/XB)
Castlevania: Aria of Sorrow (GBA)
Prince of Persia: The Sands of Time (XB/PS2/GC/PC)
Ninja Five-O (GBA)

2004:
Warhammer 40,000: Dawn of War (PC)
Cave Story (PC)
Half-Life 2 (PC)
Mega Man Zero 3 (GBA)
The Legend of Zelda: The Minish Cap (GBA)
WarioWare: Twisted! (GBA)
Metroid: Zero Mission (GBA)(Metroid 1 Remake)
Sid Meier's Pirates! (PC)

2005:
Resident Evil 4 (GC/PS2, 2005)
Phoenix Wright: Ace Attorney (DS)
Osu! Tatakae! Ouendan (DS)
Psychonauts (PC/XB/PS2)
Castlevania: Dawn of Sorrow (DS)
Shadow of the Colossus (PS2)
Mega Man Zero 4 (GBA)
God of War (PS2)

It would mean to me...

... that I respect both the time and thought put into offering personal suggestions and seriously look at the games listed.

Does it mean redundancy?

Certainly... but not all. There's room for personal taste. I'm hoping to get more than simply a "top 40"... but if I do, that's how it goes.

And there's no obligation to write anything. If you don't want to respond, that's fine. You don't have to.

Deus Ex is a super classic that holds up well even today with the DX10 .exe mod and "new vision" textures or whatever. It has some random old school aspects but if you can play modern FPS/RPG hybrids I think you can play and enjoy Deus Ex. If you like that one, then try stuff like Thief and System Shock 2.

If you like Bethesda's modern stuff you might like Morrowind, though the lack of quest markers or voices might get you down. It's a wonderful world to explore though.

Splinter Cell Chaos Theory still feels modern to me and is an excellent third-person stealth game.