July 25, 2017 at 9:05 am
Is there any advantages, disadvantages, system overhead etc if let's say I use a '543' vs 543 in a where clause? Just curious.
July 25, 2017 at 9:13 am
newdba2017 - Tuesday, July 25, 2017 9:05 AMIs there any advantages, disadvantages, system overhead etc if let's say I use a '543' vs 543 in a where clause? Just curious.
Depends on the data type, one may introduce an implicit cast and the other may or may not do so.
😎
If you post the DDL (create table) script, then we can tell!
July 25, 2017 at 9:18 am
Eirikur Eiriksson - Tuesday, July 25, 2017 9:13 AMnewdba2017 - Tuesday, July 25, 2017 9:05 AMIs there any advantages, disadvantages, system overhead etc if let's say I use a '543' vs 543 in a where clause? Just curious.Depends on the data type, one may introduce an implicit cast and the other may or may not do so.
😎
If you post the DDL (create table) script, then we can tell!
CREATE TABLE [Contracts](
[ContractID] [int] IDENTITY(1,1) NOT NULL,
[CustomerAddressId] [int] NOT NULL,
[RenewalDate] [smalldatetime] NULL,
[RenewAutomatically] [bit] NULL,
[ContractValue] [money] NOT NULL,
[DaysUntilRenewal] AS (datediff(day,getdate(),[RenewalDate])),
Difference between
Select * from Contracts where ContractID = 400
vs
Select * from Contracts where ContractID = '400'
July 25, 2017 at 10:52 am
newdba2017 - Tuesday, July 25, 2017 9:18 AMEirikur Eiriksson - Tuesday, July 25, 2017 9:13 AMnewdba2017 - Tuesday, July 25, 2017 9:05 AMIs there any advantages, disadvantages, system overhead etc if let's say I use a '543' vs 543 in a where clause? Just curious.Depends on the data type, one may introduce an implicit cast and the other may or may not do so.
😎
If you post the DDL (create table) script, then we can tell!
CREATE TABLE [Contracts](
[ContractID] [int] IDENTITY(1,1) NOT NULL,
[CustomerAddressId] [int] NOT NULL,
[RenewalDate] [smalldatetime] NULL,
[RenewAutomatically] [bit] NULL,
[ContractValue] [money] NOT NULL,
[DaysUntilRenewal] AS (datediff(day,getdate(),[RenewalDate])),Difference between
Select * from Contracts where ContractID = 400
vs
Select * from Contracts where ContractID = '400'
Other than an implicit data conversion from '400' to 400 in the second, not much difference. The main thing is using correct data types. The first query is the way I would go knowing that ContractID is defined as an integer.
July 25, 2017 at 12:46 pm
I have 45000 hours of experience with SQL Server, ALL of it with at least some focus (most with extreme focus) on performance tuning. I tell you this so you and others that read this will REALLY pay attention to what I am about to say:
THE SINGLE WORST PERFORMANCE ISSUE I SEE IN AGGREGATE IN SQL SERVER APPLICATIONS IS NOT USING THE CORRECT DATA TYPE
I note that there is NEVER an excuse to do this either because they data type of a column is known when the query is written!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 25, 2017 at 12:53 pm
TheSQLGuru - Tuesday, July 25, 2017 12:46 PMI have 45000 hours of experience with SQL Server, ALL of it with at least some focus (most with extreme focus) on performance tuning. I tell you this so you and others that read this will REALLY pay attention to what I am about to say:THE SINGLE WORST PERFORMANCE ISSUE I SEE IN AGGREGATE IN SQL SERVER APPLICATIONS IS NOT USING THE CORRECT DATA TYPE
I note that there is NEVER an excuse to do this either because they data type of a column is known when the query is written!
Agreed. Too bad I work with developers who don't understand this and only think of the database as a data store that should just work.
July 25, 2017 at 1:03 pm
Lynn Pettis - Tuesday, July 25, 2017 12:53 PMTheSQLGuru - Tuesday, July 25, 2017 12:46 PMI have 45000 hours of experience with SQL Server, ALL of it with at least some focus (most with extreme focus) on performance tuning. I tell you this so you and others that read this will REALLY pay attention to what I am about to say:THE SINGLE WORST PERFORMANCE ISSUE I SEE IN AGGREGATE IN SQL SERVER APPLICATIONS IS NOT USING THE CORRECT DATA TYPE
I note that there is NEVER an excuse to do this either because they data type of a column is known when the query is written!Agreed. Too bad I work with developers who don't understand this and only think of the database as a data store that should just work.
I've never had a problem with developers in my 20+ years of consulting, at least ones that are rational/sane. There is just no leg to stand on when the dev's query does an index/table scan (with associated HASH joins, extended blocking, buffer pool flush, CPU burn, etc, etc, etc) and mine with the correct data type does an index seek/nested loop join that is 4-6 ORDERS OF MAGNITUDE more efficient AND has NONE of those other negative consequences!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 25, 2017 at 1:09 pm
TheSQLGuru - Tuesday, July 25, 2017 1:03 PMLynn Pettis - Tuesday, July 25, 2017 12:53 PMTheSQLGuru - Tuesday, July 25, 2017 12:46 PMI have 45000 hours of experience with SQL Server, ALL of it with at least some focus (most with extreme focus) on performance tuning. I tell you this so you and others that read this will REALLY pay attention to what I am about to say:THE SINGLE WORST PERFORMANCE ISSUE I SEE IN AGGREGATE IN SQL SERVER APPLICATIONS IS NOT USING THE CORRECT DATA TYPE
I note that there is NEVER an excuse to do this either because they data type of a column is known when the query is written!Agreed. Too bad I work with developers who don't understand this and only think of the database as a data store that should just work.
I've never had a problem with developers in my 20+ years of consulting, at least ones that are rational/sane. There is just no leg to stand on when the dev's query does an index/table scan (with associated HASH joins, extended blocking, buffer pool flush, CPU burn, etc, etc, etc) and mine with the correct data type does an index seek/nested loop join that is 4-6 ORDERS OF MAGNITUDE more efficient AND has NONE of those other negative consequences!! 😀
Consider yourself lucky. I tried to encourage a developer while I was deployed to use proper data types, indexing, etc. All he would say is that the app he was developing would be used by a single customer with few users and a small amount of data. That is how many applications seem to start.
July 26, 2017 at 1:44 pm
Consider yourself lucky. I tried to encourage a developer while I was deployed to use proper data types, indexing, etc. All he would say is that the app he was developing would be used by a single customer with few users and a small amount of data. That is how many applications seem to start.
And this, dear readers, is one of the main reasons why there is an infinite amount of work out there for people like me!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply