Eloquent Typecasting to nvarchar(4000)

Here is a small article that might be helpful: https://timacdonald.me/automatically-casting-eloquent-model-foreign-keys/

See also https://laravel.com/docs/5.6/eloquent-mutators#attribute-casting

I think I have my columns cast correctly, but I think the issue I'm possibly running into is that in the SqlServerGrammar, it shows

protected function typeString(Fluent $column)
{
    return "nvarchar({$column->length})";
}

with no way to tell it to use a varchar instead.

I think you'd be better off using text datatype instead of varchar(4000).

You can also manually add raw queries to force using an index, but that seems a bit much, especially if you're doing it a lot.

Just me, but for sql server I'd use asp.net razor with c#

I had things like a varaible passed in querystring for example and in c# you have to cast it:

myvar = Convert.ToDouble(pg);

But I don't understand your situation, just try and use a datatype that will work correctly. Or work with a string (if possible) then cast.

I have always looked at life like:

c# goes with sql server 
php goes with mysql
java goes with any supported database, but mysql or sap is a good marriage.

Ok enough of my thoughts on this.

@jlrdw honestly, I've never had issues with PHP and SQL Server. SQL Server has enterprise features that I don't know of in MySQL (granted I'm not a DBA), but the other point is that in the business I'm in, I don't even have the option of choosing the database I'm using. The business was built on SQL Server long before I got here.

@Cronix I think I'm not explaining it clearly: my columns are small varchars (say 200 characters or so), but when I see the query run in the analyzer, I see it execute as:

(@P1 nvarchar(4000))select top 1 * from [table] where [column] = @P1

I think that initial casting of the bound parameter to an nvarchar(4000) is causing it to bypass my index, which is of my varchar(200) column. So the problem (I think) is that my column is appropriate, but my query is being compiled into something that is inefficient.

Perhaps I'm reading this wrong?

The only way I've seen to force Eloquent to use an index that isn't being used is to append the query.

Something like

// all of your normal stuff
$query = Model::where('this', 'that'); 

// force it to use an index
$query->getQuery()->from(\DB::raw('`table` FORCE INDEX (index_name)'));

// get the results
$results = $query->get();

I've done that with MySQL - it may be different syntax for MSSQL.

I haven't used MSSQL for a laravel project so can't really say what's going on. I know it doesn't do that with MySQL, or at least that I have seen.

And if regular PDO works just use regular PDO. Eloquent is not a cure-all for everything. You probably already know about laravels instance to PDO, getPdo (). Good luck.

It's all good, thanks for the discussion guys. I know this is kind of a deep quirk to work through (not a lot in our environment use MS over My). I like using Eloquent for building out these simple queries, and I mostly wanted to document this issue somewhere on the internet, as thus far I haven't really seen it called out much.

A php string should match the mssql column type, i think nvarchar is the correct type, but now it will not use the varchar index.

I have the same issue, have you found a way to fix it?