December 21, 2015 at 8:45 am
This query has issues due to the \.
If I use a Where = it works fine. How do I get around this small annoying issue.
SELECT name FROM sys.syslogins
WHERE name in ('Domain\USER','Domain\User1')
December 21, 2015 at 9:14 am
December 21, 2015 at 9:19 am
When the query above is executed it fails due to the \ in the string.
If the query is changed to select * from syslogins where name = 'DOMAIN\USER' it works fine.
Why doesn't it work for NOT IN ('DOMAIN\USER','DOMAIN\USER1') . This fails due to the \
December 21, 2015 at 9:21 am
Talib123 (12/21/2015)
This query has issues due to the \.If I use a Where = it works fine. How do I get around this small annoying issue.
SELECT name FROM sys.syslogins
WHERE name in ('Domain\USER','Domain\User1')
Nothing wrong with this query apart from the [name] being sysname or NVARCHAR(128), consider using the N prefix for the literals to avoid implicit conversion.
😎
SELECT
SSL.name
FROM sys.syslogins SSL
WHERE SSL.name IN (N'Domain\USER',N'Domain\USER1');
December 21, 2015 at 9:22 am
IF something is not working and you want to prove it to us you need a couple of things.
First, ask, how do I prove this to people who cant see my screen.
Give us steps to act out the test.
Give us expected results - I think it should be XYZ.
Give us you actual results -But I am getting ZXY.
'Tricky \. issue' is not a great expected result or step to reproduce or expected or actual result.
What is the error code and message i.e. the actual result.
December 21, 2015 at 9:22 am
Sorry now works fine
December 21, 2015 at 9:25 am
Talib123 (12/21/2015)
This query has issues due to the \.If I use a Where = it works fine. How do I get around this small annoying issue.
SELECT name FROM sys.syslogins
WHERE name in ('Domain\USER','Domain\User1')
Although I'm using 2012, I don't believe I would see any different results in 2008.
Trying your query with IN, NOT IN or = all produce expected results.
So, please show me the error that you are getting, or explain why you believe it is the '\' that is causing a problem.
We can't see your monitor, so we can't even begin to guess what you must be seeing.
December 21, 2015 at 9:29 am
BrainDonor (12/21/2015)
Talib123 (12/21/2015)
Sorry now works fineTypo, or something more interesting we might all want to learn from?
Or wrong server on a different domain:-P
😎
December 21, 2015 at 9:31 am
December 21, 2015 at 2:52 pm
Eirikur Eiriksson (12/21/2015)
Talib123 (12/21/2015)
This query has issues due to the \.If I use a Where = it works fine. How do I get around this small annoying issue.
SELECT name FROM sys.syslogins
WHERE name in ('Domain\USER','Domain\User1')
Nothing wrong with this query apart from the [name] being sysname or NVARCHAR(128), consider using the N prefix for the literals to avoid implicit conversion.
😎
SELECTSSL.name
FROM sys.syslogins SSL
WHERE SSL.name IN (N'Domain\USER',N'Domain\USER1');
No, never do that unless the statement won't run without you specifying a unicode literal. Implicitly converting a literal(s) is not a big deal.
But if you put the N' when the column is not unicode, you'll force SQL to implicitly convert the column itself and that could be a real performance killer.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 21, 2015 at 3:50 pm
ScottPletcher (12/21/2015)
Eirikur Eiriksson (12/21/2015)
Talib123 (12/21/2015)
This query has issues due to the \.If I use a Where = it works fine. How do I get around this small annoying issue.
SELECT name FROM sys.syslogins
WHERE name in ('Domain\USER','Domain\User1')
Nothing wrong with this query apart from the [name] being sysname or NVARCHAR(128), consider using the N prefix for the literals to avoid implicit conversion.
😎
SELECTSSL.name
FROM sys.syslogins SSL
WHERE SSL.name IN (N'Domain\USER',N'Domain\USER1');
No, never do that unless the statement won't run without you specifying a unicode literal. Implicitly converting a literal(s) is not a big deal.
But if you put the N' when the column is not unicode, you'll force SQL to implicitly convert the column itself and that could be a real performance killer.
Try to avoid giving wrong advices.
Especially on the matter you do not understand.
Here is a script for you to expand the boundaries of your education:
DECLARE @String NVARCHAR(10) SET @String = NCHAR(256+54)
SELECT @String, CONVERT(VARCHAR(10), @String), N'??????', '??????'
-- KK????????????
DECLARE @test-2 TABLE (
String NVARCHAR(50)
)
INSERT INTO @test-2
( String)
SELECT N'K'
SELECT * FROM @test-2 t
WHERE t.String = N'K'
--(0 row(s) affected)
SELECT * FROM @test-2 t
WHERE t.String = 'K'
--(1 row(s) affected)
Conclusion - implicit conversion from nvarchar to varchar may cause data loss.
Good advice would be - always use appropriate data types, avoid implicit conversions by all means.
In these terms the suggestion from Eirikur was a good one.
_____________
Code for TallyGenerator
December 22, 2015 at 2:43 am
ScottPletcher (12/21/2015)
But if you put the N' when the column is not unicode, you'll force SQL to implicitly convert the column itself and that could be a real performance killer.
This is incorrect, the value passed will be converted, not the whole column.
Edit: my bad.
😎
Long lost count of the times where implicit conversions cause performance problems, yet to find a single case where correct data typing does.
December 22, 2015 at 8:46 am
Eirikur Eiriksson (12/22/2015)
ScottPletcher (12/21/2015)
But if you put the N' when the column is not unicode, you'll force SQL to implicitly convert the column itself and that could be a real performance killer.This is incorrect, the value passed will be converted, not the whole column.
😎
Long lost count of the times where implicit conversions cause performance problems, yet to find a single case where correct data typing does.
Hmmm...really?
nvarchar is of a higher precedence than varchar, so if the literal is nvarchar and the column varchar, then the column should be converted.
That is confirmed with the following quick test:
CREATE TABLE #test (some_string varchar(max));
INSERT INTO #test
SELECT TOP 1000000 ac1.name
FROM sys.all_columns ac1
CROSS JOIN
sys.all_columns ac2;
SELECT COUNT(*)
FROM #test
WHERE some_string='dbname';
SELECT COUNT(*)
FROM #test
WHERE some_string=N'dbname';
DROP TABLE #test;
The second SELECT causes a conversion of the values in the column, a fact that is pointed out as a warning in the execution plan, and results in a noticeable increase in CPU for the second query.
I'm probably just misunderstanding what's being claimed 🙂
Cheers!
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply