{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/9107","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/9107/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/9107/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/9107/events","html_url":"https://github.com/sequelize/sequelize/issues/9107","id":300045814,"node_id":"MDU6SXNzdWUzMDAwNDU4MTQ=","number":9107,"title":"MSSQL: Implicit conversion causes table scan -- Prepending N to strings precludes index use on varchar columns","user":{"login":"dbellandi","id":5230902,"node_id":"MDQ6VXNlcjUyMzA5MDI=","avatar_url":"https://avatars2.githubusercontent.com/u/5230902?v=4","gravatar_id":"","url":"https://api.github.com/users/dbellandi","html_url":"https://github.com/dbellandi","followers_url":"https://api.github.com/users/dbellandi/followers","following_url":"https://api.github.com/users/dbellandi/following{/other_user}","gists_url":"https://api.github.com/users/dbellandi/gists{/gist_id}","starred_url":"https://api.github.com/users/dbellandi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dbellandi/subscriptions","organizations_url":"https://api.github.com/users/dbellandi/orgs","repos_url":"https://api.github.com/users/dbellandi/repos","events_url":"https://api.github.com/users/dbellandi/events{/privacy}","received_events_url":"https://api.github.com/users/dbellandi/received_events","type":"User","site_admin":false},"labels":[{"id":28979733,"node_id":"MDU6TGFiZWwyODk3OTczMw==","url":"https://api.github.com/repos/sequelize/sequelize/labels/feature","name":"feature","color":"0b02e1","default":false},{"id":183663033,"node_id":"MDU6TGFiZWwxODM2NjMwMzM=","url":"https://api.github.com/repos/sequelize/sequelize/labels/mssql","name":"mssql","color":"bfdadc","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2018-02-25T17:47:50Z","updated_at":"2018-12-07T19:58:34Z","closed_at":null,"author_association":"NONE","body":"<!--\r\n\r\nPlease note this is an issue tracker, not a support forum.\r\nFor general questions, please use StackOverflow or Slack.\r\n\r\nFor bugs, please fill out the template below.\r\n\r\n-->\r\n\r\n\r\n## What are you doing?\r\nQuerying a table and filtering on a varchar column. The issue is most easily illustrated using raw query code although it applies to both raw queries and model-generated ones.\r\n\r\n```sql\r\ncreate table names (\r\n  name varchar(100) not null primary key clustered\r\n)\r\n```\r\n\r\n```js\r\nsequelize.query('select * from names where name=:name', { replacements: { name: 'hello' } });\r\n```\r\n\r\n## What do you expect to happen?\r\nSequelize to construct SQL without prepending N to :name\r\n\r\nSQL sent to server:\r\n```sql\r\nselect * from names where name='hello'\r\n```\r\n\r\nSQL Server performs an index seek using index defined above.\r\n\r\n## What is actually happening?\r\nN is prepended.\r\n\r\nSQL sent to server:\r\n```sql\r\nselect * from names where name=N'hello'\r\n```\r\n\r\nSQL Server performs a table scan after doing an implicit conversion on N'hello'\r\n\r\n## Notes\r\nI believe this problem was introduced in response to issue [#3752](https://github.com/sequelize/sequelize/issues/3752) to handle unicode strings in mssql. The problem is that automatically treating all strings as unicode precludes index use on varchar columns which can significantly hurt performance.\r\n\r\nThe code in question begins [here.](https://github.com/sequelize/sequelize/blob/master/lib/sql-string.js#L24)\r\n\r\n## Workaround\r\nA workaround is possible for raw queries but not model-generated ones.\r\n\r\nExample:\r\n```js\r\nsequelize.query('select * from names where name=cast(:name as varchar(100)), { replacements: { name: 'hello' } });\r\n```\r\n\r\n\r\n__Dialect:__ mssql\r\n__Database version:__ All (Tested SQL Server 2012/2016)\r\n__Sequelize version:__ 4.34.0\r\n__Tested with latest release:__ Yes\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/445348216","html_url":"https://github.com/sequelize/sequelize/issues/9107#issuecomment-445348216","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/9107","id":445348216,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0NTM0ODIxNg==","user":{"login":"rconstantine","id":8312587,"node_id":"MDQ6VXNlcjgzMTI1ODc=","avatar_url":"https://avatars1.githubusercontent.com/u/8312587?v=4","gravatar_id":"","url":"https://api.github.com/users/rconstantine","html_url":"https://github.com/rconstantine","followers_url":"https://api.github.com/users/rconstantine/followers","following_url":"https://api.github.com/users/rconstantine/following{/other_user}","gists_url":"https://api.github.com/users/rconstantine/gists{/gist_id}","starred_url":"https://api.github.com/users/rconstantine/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rconstantine/subscriptions","organizations_url":"https://api.github.com/users/rconstantine/orgs","repos_url":"https://api.github.com/users/rconstantine/repos","events_url":"https://api.github.com/users/rconstantine/events{/privacy}","received_events_url":"https://api.github.com/users/rconstantine/received_events","type":"User","site_admin":false},"created_at":"2018-12-07T19:58:34Z","updated_at":"2018-12-07T19:58:34Z","author_association":"NONE","body":"BTW, I got the following to work, which is shorter than the in-model solution presented above: I didn't need to use the escape function.\r\n\r\nlr_rslt_id: {\r\n        [Op.eq]: Sequelize.literal(`'WBC'`)\r\n      }"}}
{"actor":{"display_login":"rconstantine"}}