Depending on the amount of RAM your server has, it might just read the whole table into memory and keep it there - so your first query will load it, and any subsequent queries will just operate on those pages loaded in memory.
To get any meaningful data, you need to flush the cache before every run of your query! (run DBCC FREEPROCCACHE
and DBCC DROPCLEANBUFFERS
before any query execution!)
I tried your scenario with 100'000 dummy file names and almost the same table structure (I changed FileName
to be VARCHAR(260)
).
Without index:
Table 'TestFiles'. Scan count 1, logical reads 2137, physical reads 5, read-ahead reads 2136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query Subtree Cost 1.68884
With non-clustered index on filename:
Table 'TestFiles'. Scan count 1, logical reads 6, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query Subtree Cost 0.0065704
This was done with this statement:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SELECT *
FROM testfiles
WHERE FileName = 'File-D8584B44-518F-428A-86A1-7836E0B60502'
I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine.
#SQL 2008 R2
#add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#SQL 2012
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$InstanceName = "myservername"
$query = @"
CREATE TABLE dbo.authors(
au_id int NOT NULL,
au_lname varchar(40) NOT NULL,
au_fname varchar(20) NOT NULL
CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED
(
au_id ASC
)
)
"@
$InstanceObject = New-Object "Microsoft.SqlServer.Management.SMO.Server" "$InstanceName"
$db = $InstanceObject.Databases['tempdb']
$db.ExecuteNonQuery($query)
$MyScripter = New-Object "Microsoft.SqlServer.Management.SMO.Scripter"
$MyScripter.Server = $InstanceObject
$MyScripter.Options.DriAll = $true
#$MyScripter.Options.DriPrimaryKey = $false
#$MyScripter.Options.DriClustered = $false
$MyScripter.Script($InstanceObject.Databases['tempdb'].Tables['authors'])
Best Answer
You need to be sure that you prefix Unicode string literals with an N prefix. For example these will work differently if the underlying data type is
NVARCHAR
:Results:
For those on mobile devices or decrepit browsers that show box characters instead of actual Unicode characters, this is what it looks like: