December 22, 2015 at 9:02 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.
SQL has to follow its own data precedence rules, as Jacob also noted.
Also, this is really a case of incorrect data typing. Unicode strings should not be compared to non-unicode strings in the table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 9:18 am
Jacob Wilkins (12/22/2015)
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!
I have run your test and here are my results
SELECT COUNT(*) FROM #test WHERE some_string='dbname';
SQL Server Execution Times: CPU time = 219 ms, elapsed time = 213 ms.
SELECT COUNT(*) FROM #test WHERE some_string=N'dbname';
SQL Server Execution Times: CPU time = 203 ms, elapsed time = 211 ms.
I see implicit conversion but no impact to CPU or IO.
They are close enough to each other that I will say that on my server, they use very very similar cpu cycles to solve the problem.
Also, for it to mimic the original poster's query, we should use
create table X(column sysname) as sysname is really a specific kind of NVARCHAR, and not varcharmax
December 22, 2015 at 9:39 am
MadAdmin (12/22/2015)
Jacob Wilkins (12/22/2015)
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!
I have run your test and here are my results
SELECT COUNT(*) FROM #test WHERE some_string='dbname';SQL Server Execution Times: CPU time = 219 ms, elapsed time = 213 ms.
SELECT COUNT(*) FROM #test WHERE some_string=N'dbname';SQL Server Execution Times: CPU time = 203 ms, elapsed time = 211 ms.
I see implicit conversion but no impact to CPU or IO.
They are close enough to each other that I will say that on my server, they use very very similar cpu cycles to solve the problem.
Also, for it to mimic the original poster's query, we should use
create table X(column sysname)as sysname is really a specific kind of NVARCHAR, and not varcharmax
That last bit is true, of course, but I wasn't saying that it caused implicit conversion or issues when the original column was nvarchar/sysname; that would be a strange claim 🙂 That is of course quite safe.
I was responding specifically to the exchange between Scott and Eirikur about comparing a unicode literal to non-unicode column (as indicated by my quoting that exchange).
That's an interesting result for the two queries on your machine, but I've not been able to reproduce it on any of my instances so far.
Cheers!
December 22, 2015 at 10:01 am
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
IF OBJECT_ID('tempdb.dbo.#tbl1') IS NOT NULL
DROP TABLE #tbl1
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 10:22 am
My bad in the post before on the conversion, sorry about that.
😎
My point is that incorrect typing and implicit conversions do cause problems, I've lost count of how many of those I've come across but I've never ever seen correct datatypes and the absence of a implicit conversion causing a problem.
On that note I believe it is irresponsible to give any advice that encourages ignoring the data type such as posted previously on this thread.
December 22, 2015 at 10:37 am
ScottPletcher (12/22/2015)
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
Of course there were more logical I/Os in the second query. You are using the wrong data type. You are comparing a literal NVARCHAR string to a value in a VARCHAR column which requires SQL Server to implicitly convert EACH AND EVERY varchar value to an nvarchar value before doing the compare.
If you change the data type in the table to nvarchar you get 3 logical reads in both queries. This is because SQL Server will do an implicit conversion of the literal varchar string to nvarchar ONCE, and then use the index to find the value.
The moral here is actually to use the correct data type in your queries.
December 22, 2015 at 10:49 am
Eirikur Eiriksson (12/22/2015)
My bad in the post before on the conversion, sorry about that.😎
My point is that incorrect typing and implicit conversions do cause problems, I've lost count of how many of those I've come across but I've never ever seen correct datatypes and the absence of a implicit conversion causing a problem.
On that note I believe it is irresponsible to give any advice that encourages ignoring the data type such as posted previously on this thread.
Ah, but data types can change. I change columns from nvarchar to varchar fairly frequently, mostly for internal codes. There seemed to have been a trend for a while to make (virtually) everything nvarchar. That just wastes space and processing time.
I don't believe it's irresponsible to use SQL knowledge to prevent code that could cause major performance issues later and force re-writes.
Similarly, that's why literal dates should be specified as simply 'yyyymmdd' and not forced to, say, datetime simply because the column is currently datetime. What happens if the column is later changed to date? (Remember, date was not available until SQL 2008, thus earlier code was forced to use datetime even if it only needed a date). What you're saying is that the type should be explicitly specified:
WHERE meeting_date = CAST('20151211' AS datetime)
Admittedly SQL itself has is now coded to neutralize at least some of these specific date / datetime cases, as it has been for earlier smallint / int conversions. But I still won't be careless with other situations just because of that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 10:52 am
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
Of course there were more logical I/Os in the second query. You are using the wrong data type. You are comparing a literal NVARCHAR string to a value in a VARCHAR column which requires SQL Server to implicitly convert EACH AND EVERY varchar value to an nvarchar value before doing the compare.
If you change the data type in the table to nvarchar you get 3 logical reads in both queries. This is because SQL Server will do an implicit conversion of the literal varchar string to nvarchar ONCE, and then use the index to find the value.
The moral here is actually to use the correct data type in your queries.
I stated exactly the first part earlier.
As to the moral, I strongly disagree in the case of unicode. Do not explicitly specify it unless you have to. SQL implicitly converting a few literals is no issue at all, but forcing unicode when you shouldn't can be a major performance killer.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 10:57 am
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
Of course there were more logical I/Os in the second query. You are using the wrong data type. You are comparing a literal NVARCHAR string to a value in a VARCHAR column which requires SQL Server to implicitly convert EACH AND EVERY varchar value to an nvarchar value before doing the compare.
If you change the data type in the table to nvarchar you get 3 logical reads in both queries. This is because SQL Server will do an implicit conversion of the literal varchar string to nvarchar ONCE, and then use the index to find the value.
The moral here is actually to use the correct data type in your queries.
I stated exactly the first part earlier.
As to the moral, I strongly disagree in the case of unicode. Do not explicitly specify it unless you have to. SQL implicitly converting a few literals is no issue at all, but forcing unicode when you shouldn't can be a major performance killer.
And that is where I will have to disagree with you. If the data type is declared as NVARCHAR or NCHAR then you should use N'astring' when specifying a Unicode literal string whether it contains actual Unicode values or not. If you don't want to that, that is your choice.
December 22, 2015 at 11:08 am
ScottPletcher (12/22/2015)
Eirikur Eiriksson (12/22/2015)
My bad in the post before on the conversion, sorry about that.😎
My point is that incorrect typing and implicit conversions do cause problems, I've lost count of how many of those I've come across but I've never ever seen correct datatypes and the absence of a implicit conversion causing a problem.
On that note I believe it is irresponsible to give any advice that encourages ignoring the data type such as posted previously on this thread.
Ah, but data types can change. I change columns from nvarchar to varchar fairly frequently, mostly for internal codes. There seemed to have been a trend for a while to make (virtually) everything nvarchar. That just wastes space and processing time.
I don't believe it's irresponsible to use SQL knowledge to prevent code that could cause major performance issues later and force re-writes.
Similarly, that's why literal dates should be specified as simply 'yyyymmdd' and not forced to, say, datetime simply because the column is currently datetime. What happens if the column is later changed to date? (Remember, date was not available until SQL 2008, thus earlier code was forced to use datetime even if it only needed a date). What you're saying is that the type should be explicitly specified:
WHERE meeting_date = CAST('20151211' AS datetime)
Admittedly SQL itself has is now coded to neutralize at least some of these specific date / datetime cases, as it has been for earlier smallint / int conversions. But I still won't be careless with other situations just because of that.
This is literally moving the goalposts, how frequently do the data types change compared to the numbers of time the queries on those table run? Where is the process of schema change impact analysis etc.
😎
December 22, 2015 at 11:10 am
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
Of course there were more logical I/Os in the second query. You are using the wrong data type. You are comparing a literal NVARCHAR string to a value in a VARCHAR column which requires SQL Server to implicitly convert EACH AND EVERY varchar value to an nvarchar value before doing the compare.
If you change the data type in the table to nvarchar you get 3 logical reads in both queries. This is because SQL Server will do an implicit conversion of the literal varchar string to nvarchar ONCE, and then use the index to find the value.
The moral here is actually to use the correct data type in your queries.
I stated exactly the first part earlier.
As to the moral, I strongly disagree in the case of unicode. Do not explicitly specify it unless you have to. SQL implicitly converting a few literals is no issue at all, but forcing unicode when you shouldn't can be a major performance killer.
And that is where I will have to disagree with you. If the data type is declared as NVARCHAR or NCHAR then you should use N'astring' when specifying a Unicode literal string whether it contains actual Unicode values or not. If you don't want to that, that is your choice.
But then if you change the column back to varchar, as we and many others are doing to save space where possible, you have to go back and change all the code. The potential gain from using N' is effectively zero, the potential loss is huge, in performance and in re-work. That is indeed your choice, but at this company, we don't have time for useless re-work, we really don't.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 11:14 am
Eirikur Eiriksson (12/22/2015)
ScottPletcher (12/22/2015)
Eirikur Eiriksson (12/22/2015)
My bad in the post before on the conversion, sorry about that.😎
My point is that incorrect typing and implicit conversions do cause problems, I've lost count of how many of those I've come across but I've never ever seen correct datatypes and the absence of a implicit conversion causing a problem.
On that note I believe it is irresponsible to give any advice that encourages ignoring the data type such as posted previously on this thread.
Ah, but data types can change. I change columns from nvarchar to varchar fairly frequently, mostly for internal codes. There seemed to have been a trend for a while to make (virtually) everything nvarchar. That just wastes space and processing time.
I don't believe it's irresponsible to use SQL knowledge to prevent code that could cause major performance issues later and force re-writes.
Similarly, that's why literal dates should be specified as simply 'yyyymmdd' and not forced to, say, datetime simply because the column is currently datetime. What happens if the column is later changed to date? (Remember, date was not available until SQL 2008, thus earlier code was forced to use datetime even if it only needed a date). What you're saying is that the type should be explicitly specified:
WHERE meeting_date = CAST('20151211' AS datetime)
Admittedly SQL itself has is now coded to neutralize at least some of these specific date / datetime cases, as it has been for earlier smallint / int conversions. But I still won't be careless with other situations just because of that.
This is literally moving the goalposts, how frequently do the data types change compared to the numbers of time the queries on those table run? Where is the process of schema change impact analysis etc.
😎
Edit: Not at all "moving the goalposts". The only reason for the rule is the issue it causes if you get the type wrong, as I stated many times above. You're trying to move the goalposts by saying changes almost never occur, so the re-work involved is not significant. Even that may not be true -- it's certainly possible that changing one heavily-used column could cause a lot of re-work. Let me guess, then your answer is "don't change it". Ok, but then all that disk space is still wasted.
We are changing many columns for nvar to var, as I noted above; at one time there seemed to be a trend to use nvarchar for many more columns.
But what does it cost me to use a varchar literal instead of an nvarchar literal? Nothing. But if i get it wrong, the cost is huge. Not worth the risk.
Not to mention, I also have to know for every column whether it is varchar or nvarchar so I can write the data-type-specific literals for it. That effort, too, for no gain whatsoever.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 11:14 am
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
Of course there were more logical I/Os in the second query. You are using the wrong data type. You are comparing a literal NVARCHAR string to a value in a VARCHAR column which requires SQL Server to implicitly convert EACH AND EVERY varchar value to an nvarchar value before doing the compare.
If you change the data type in the table to nvarchar you get 3 logical reads in both queries. This is because SQL Server will do an implicit conversion of the literal varchar string to nvarchar ONCE, and then use the index to find the value.
The moral here is actually to use the correct data type in your queries.
I stated exactly the first part earlier.
As to the moral, I strongly disagree in the case of unicode. Do not explicitly specify it unless you have to. SQL implicitly converting a few literals is no issue at all, but forcing unicode when you shouldn't can be a major performance killer.
And that is where I will have to disagree with you. If the data type is declared as NVARCHAR or NCHAR then you should use N'astring' when specifying a Unicode literal string whether it contains actual Unicode values or not. If you don't want to that, that is your choice.
But then if you change the column back to varchar, as we and many others are doing to save space where possible, you have to go back and change all the code. The potential gain from using N' is effectively zero, the potential loss is huge, in performance and in re-work. That is indeed your choice, but at this company, we don't have time for useless re-work, we really don't.
You know what, I don't give a rats tails *** what you do. I will use the appropriate data types. Don't tell me I am wrong when I do.
December 22, 2015 at 11:18 am
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
Of course there were more logical I/Os in the second query. You are using the wrong data type. You are comparing a literal NVARCHAR string to a value in a VARCHAR column which requires SQL Server to implicitly convert EACH AND EVERY varchar value to an nvarchar value before doing the compare.
If you change the data type in the table to nvarchar you get 3 logical reads in both queries. This is because SQL Server will do an implicit conversion of the literal varchar string to nvarchar ONCE, and then use the index to find the value.
The moral here is actually to use the correct data type in your queries.
I stated exactly the first part earlier.
As to the moral, I strongly disagree in the case of unicode. Do not explicitly specify it unless you have to. SQL implicitly converting a few literals is no issue at all, but forcing unicode when you shouldn't can be a major performance killer.
And that is where I will have to disagree with you. If the data type is declared as NVARCHAR or NCHAR then you should use N'astring' when specifying a Unicode literal string whether it contains actual Unicode values or not. If you don't want to that, that is your choice.
But then if you change the column back to varchar, as we and many others are doing to save space where possible, you have to go back and change all the code. The potential gain from using N' is effectively zero, the potential loss is huge, in performance and in re-work. That is indeed your choice, but at this company, we don't have time for useless re-work, we really don't.
You know what, I don't give a rats tails *** what you do. I will use the appropriate data types. Don't tell me I am wrong when I do.
Well, I guess it's not if you're a contractor, particularly a govt one. In that case, "standard practice" seems to be to build bugs into the code that you can correct later for an additional fee.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 11:52 am
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Lynn Pettis (12/22/2015)
ScottPletcher (12/22/2015)
Of course when scanning the whole table the difference may be rather minor. It's the prevention of seeks that is always the major concern with implicit column conversions. I had to use a permanent tally table in the code below, but naturally you can use an in-line one if you prefer:
SET STATISTICS IO OFF
CREATE TABLE #tbl1 ( col1 varchar(7) PRIMARY KEY WITH ( FILLFACTOR = 100 ) )
GO
INSERT INTO #tbl1
SELECT RIGHT(REPLICATE('0', 7) + CAST(t.tally AS varchar(7)), 7)
FROM dbo.tally t
WHERE t.tally BETWEEN 1 AND 1000000
GO
SET STATISTICS IO ON
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = '0987654' --3 logical I/Os
SELECT t1.* FROM #tbl1 t1 WHERE t1.col1 = N'0987654' --2484 logical I/Os
Of course there were more logical I/Os in the second query. You are using the wrong data type. You are comparing a literal NVARCHAR string to a value in a VARCHAR column which requires SQL Server to implicitly convert EACH AND EVERY varchar value to an nvarchar value before doing the compare.
If you change the data type in the table to nvarchar you get 3 logical reads in both queries. This is because SQL Server will do an implicit conversion of the literal varchar string to nvarchar ONCE, and then use the index to find the value.
The moral here is actually to use the correct data type in your queries.
I stated exactly the first part earlier.
As to the moral, I strongly disagree in the case of unicode. Do not explicitly specify it unless you have to. SQL implicitly converting a few literals is no issue at all, but forcing unicode when you shouldn't can be a major performance killer.
And that is where I will have to disagree with you. If the data type is declared as NVARCHAR or NCHAR then you should use N'astring' when specifying a Unicode literal string whether it contains actual Unicode values or not. If you don't want to that, that is your choice.
But then if you change the column back to varchar, as we and many others are doing to save space where possible, you have to go back and change all the code. The potential gain from using N' is effectively zero, the potential loss is huge, in performance and in re-work. That is indeed your choice, but at this company, we don't have time for useless re-work, we really don't.
You know what, I don't give a rats tails *** what you do. I will use the appropriate data types. Don't tell me I am wrong when I do.
Well, I guess it's not if you're a contractor, particularly a govt one. In that case, "standard practice" seems to be to build bugs into the code that you can correct later for an additional fee.
Scott, this kind of comment is totally uncalled for, try keep the conversation on a professional level.
😎
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply