Sql-server – What are all these databases with funny names

Other points (as noted, "How do I index?" is a topic for a book not a single post - also, so many of the answers come down to "it depends on your database and your workload"):

• Index selectivity is key. If you try and index a field that doesn't have many distinct values (i.e. a true/false bit field) then using that index is actually slower than just doing a table scan (but it still has to be maintained, thereby slowing DML calls [insert/update/delete] down to no benefit).
• In general, you're right about indexing narrow fields, but I'd rephrase it to "be very careful about indexing wide fields". If the index field is too narrow, you run into the selectivity problem above, but the wider the field the bigger the index is ("how much bigger?" depends on which DBMS you use).
• Indexing updated fields - If you have an index on a frequently-updated field, then yes that index will slow down updates to that field. However, if you're using that field quite often in query criteria it may still be worth it to index it. (see above: "it depends")
• Multi-column indexes: This is a tricky one:
  • Covering indexes (an index that contains all the fields for a query) can speed up queries (because then the query only has to look at the index - it doesn't have to refer to the base table).
  • Multi-column indexes generally have higher selectivity.
  • Multi-column indexes require more space.
  • They're only useful if the query filters on either the full index, or a leading subset. I.e. if you have an index on (State, County, ZIP), then queries filtering on (State), (State, County) or (State, County, ZIP) can use that index. Queries that filter on (County, ZIP), (County) or (ZIP) cannot use that index.
  • Corollary: The order of columns in a multi-column index is very important.
  • If you have a multi-column index (State, County, ZIP), then a single-column index on (State) would be redundant (since State is the first column, the multi-column index can be used for that). Note that SQL Server and Sybase (not sure about other RDBMS systems) don't prevent you from creating completely redundant indexes.

As always, the best indexing strategy is to analyze the workload your database is under and index to suit that. If you're indexing a data warehouse your indexes are going to be radically different from the indexes on an audit history database.

There's no way to find those files with a detached database .mdf. As Aaron suggests, you can create the database with ATTACH_REBUILD_LOG. Another option is if you have an "old" backup file of the database, you can use RESTORE FILELISTONLY to interrogate the backup file for the state of the database files at the time of that backup. This will give you a starting point to track down your files.

Edit Because I like Powershell, here's a script that will read through all the full backup files in a directory and build an attachdbs.sql in your Documents folder:

param([parameter(Mandatory=$true)][string] $dir,
        [parameter(Mandatory=$true)][string] $server)


#load assemblies
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server


#get backup files
$files = gci $dir | where {$_.name -like "*.bak"}

$output=([Environment]::GetFolderPath("MyDocuments")) + "\attachdbs.sql"

"/*****************************************" > $output
"Attach script based off of backup files" >> $output
"*****************************************/" >> $output
foreach($file in $files){
    $rs=new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $rs.Devices.AddDevice($file.FullName, "File")
    $hd=$rs.ReadBackupHeader($smosrv)
    $dbname=$hd.Rows[0].DatabaseName

    $dbfiles=$rs.ReadFileList($smosrv)

    "CREATE DATABASE $dbname ON" >> $output
    $filewrite=@()
    foreach($dbfile in $dbfiles){
        $filewrite+="(FILENAME='"+$dbfile.PhysicalName+"')"
    }

    $filewrite -join ",`n" >> $output

    "FOR ATTACH; `n--------------------------" >> $output
    }