March 20, 2015 at 1:14 pm
Eric M Russell (3/20/2015)
#5 Inappropriate use of datetime columns. For example SELECT ... WHERE SALESDATE BETWEEN '2015/03/01' AND '2015/03/31'.
This one is the one I found myself doing a lot on DATETIME in the past.
BETWEEN '2015-01-01' AND '2015-01-02'
As opposed to
>= '2015-01-01' AND ...
That's not really a problem with BETWEEN though. That's a problem with me incorrectly using it. :w00t:
March 20, 2015 at 1:37 pm
xsevensinzx (3/20/2015)
Eric M Russell (3/20/2015)
#5 Inappropriate use of datetime columns. For example SELECT ... WHERE SALESDATE BETWEEN '2015/03/01' AND '2015/03/31'.
This one is the one I found myself doing a lot on DATETIME in the past.
BETWEEN '2015-01-01' AND '2015-01-02'
As opposed to
>= '2015-01-01' AND ...
That's not really a problem with BETWEEN though. That's a problem with me incorrectly using it. :w00t:
One thing that SQL Server could use is function based indexes, so we can index on something like CAST(SALESDATE AS DATE) or even PRODUCTDESC LIKE '%ASSEMBLY%' (True | False), so the following would be sargable:
SELECT ... WHERE SALESDATE = '2015/03/15';
SELECT ... WHERE PRODUCTDESC LIKE '%ASSEMBLY%';
Oracle already sort of supports this, but I think Microsoft could do it even better when they get there.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 20, 2015 at 2:19 pm
Eric M Russell (3/20/2015)
One thing that SQL Server could use is function based indexes, so we can index on something like CAST(SALESDATE AS DATE) or even PRODUCTDESC LIKE '%ASSEMBLY%' (True | False), so the following would be sargable:SELECT ... WHERE SALESDATE = '2015/03/15';
SELECT ... WHERE PRODUCTDESC LIKE '%ASSEMBLY%';
Oracle already sort of supports this, but I think Microsoft could do it even better when they get there.
While you can't put a function in an index, you can create a computed column and index that:
https://msdn.microsoft.com/en-us/library/ms189292(v=sql.105).aspx
http://www.sqlteam.com/article/using-indexed-computed-columns-to-improve-performance
😎
March 20, 2015 at 3:15 pm
Chris Harshman (3/20/2015)
Eric M Russell (3/20/2015)
One thing that SQL Server could use is function based indexes, so we can index on something like CAST(SALESDATE AS DATE) or even PRODUCTDESC LIKE '%ASSEMBLY%' (True | False), so the following would be sargable:SELECT ... WHERE SALESDATE = '2015/03/15';
SELECT ... WHERE PRODUCTDESC LIKE '%ASSEMBLY%';
Oracle already sort of supports this, but I think Microsoft could do it even better when they get there.
While you can't put a function in an index, you can create a computed column and index that:
https://msdn.microsoft.com/en-us/library/ms189292(v=sql.105).aspx
http://www.sqlteam.com/article/using-indexed-computed-columns-to-improve-performance
😎
Yes, I do that sometimes, but the catch is that it has to be a 'persisted' computed column. Adding these persisted columns to an existing large table can be more problematic than adding an index on a computed expression.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 20, 2015 at 7:15 pm
Eric M Russell (3/20/2015)
Chris Harshman (3/20/2015)
Eric M Russell (3/20/2015)
One thing that SQL Server could use is function based indexes, so we can index on something like CAST(SALESDATE AS DATE) or even PRODUCTDESC LIKE '%ASSEMBLY%' (True | False), so the following would be sargable:SELECT ... WHERE SALESDATE = '2015/03/15';
SELECT ... WHERE PRODUCTDESC LIKE '%ASSEMBLY%';
Oracle already sort of supports this, but I think Microsoft could do it even better when they get there.
While you can't put a function in an index, you can create a computed column and index that:
https://msdn.microsoft.com/en-us/library/ms189292(v=sql.105).aspx
http://www.sqlteam.com/article/using-indexed-computed-columns-to-improve-performance
😎
Yes, I do that sometimes, but the catch is that it has to be a 'persisted' computed column. Adding these persisted columns to an existing large table can be more problematic than adding an index on a computed expression.
And that's a good point too.
For us, we have to compare records down to the second for deep insights. But for me, I assumed that if a more open-ended way of specifying dates worked one way, then BETWEEN should be the same right? Wrong 🙂
I ended up specifying BETWEEN '2015-01-01' AND '2015-01-01 23:59:59.999' etc.
Then there was the rounding 😛
So, '23:59:59.997'.... Please kill me already.
Okies, back to Time >= '2015-01-01' AND Time < '2015-01-02'
March 20, 2015 at 8:38 pm
Knut Boehnert (3/20/2015)
#1 No documentation...
#2 Incomplete documentation
...
#3 Inadequate documentation
...
I'm working on a tool to help with this right now. First release is targeted for April.
ATBCharles Kincaid
March 21, 2015 at 6:09 am
Charles Kincaid (3/20/2015)
Knut Boehnert (3/20/2015)
#1 No documentation...
#2 Incomplete documentation
...
#3 Inadequate documentation
...
I'm working on a tool to help with this right now. First release is targeted for April.
Don't forget the documentaton. 🙂
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 22, 2015 at 12:34 am
Using default left outer joins instead of inner joins
Using distinct, especially in views
Complex views, joining complex views
March 22, 2015 at 8:50 pm
Eric M Russell (3/20/2015)
Chris Harshman (3/20/2015)
Eric M Russell (3/20/2015)
One thing that SQL Server could use is function based indexes, so we can index on something like CAST(SALESDATE AS DATE) or even PRODUCTDESC LIKE '%ASSEMBLY%' (True | False), so the following would be sargable:SELECT ... WHERE SALESDATE = '2015/03/15';
SELECT ... WHERE PRODUCTDESC LIKE '%ASSEMBLY%';
Oracle already sort of supports this, but I think Microsoft could do it even better when they get there.
While you can't put a function in an index, you can create a computed column and index that:
https://msdn.microsoft.com/en-us/library/ms189292(v=sql.105).aspx
http://www.sqlteam.com/article/using-indexed-computed-columns-to-improve-performance
😎
Yes, I do that sometimes, but the catch is that it has to be a 'persisted' computed column. Adding these persisted columns to an existing large table can be more problematic than adding an index on a computed expression.
A properly indexed "sister table" can sometimes make life easier. A lot of folks will squawk at the extra join but it can do some amazing things especially if you query the sister table first because it's so much more narrow than the main table.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2015 at 7:08 am
What I often see is this. A programmer goes in to fix a problem, finds the source of the problem and fixes it. Done. But wait, what about other areas of the code that are similar, that might have the same sort of problem? Why not check them out too and see if there is the same sort of problem? But no, we just have to fix the task at hand and never take that next step. It drives me crazy.
March 24, 2015 at 7:17 am
Jeff Moden (3/22/2015)
Eric M Russell (3/20/2015)
Chris Harshman (3/20/2015)
Eric M Russell (3/20/2015)
One thing that SQL Server could use is function based indexes, so we can index on something like CAST(SALESDATE AS DATE) or even PRODUCTDESC LIKE '%ASSEMBLY%' (True | False), so the following would be sargable:SELECT ... WHERE SALESDATE = '2015/03/15';
SELECT ... WHERE PRODUCTDESC LIKE '%ASSEMBLY%';
Oracle already sort of supports this, but I think Microsoft could do it even better when they get there.
While you can't put a function in an index, you can create a computed column and index that:
https://msdn.microsoft.com/en-us/library/ms189292(v=sql.105).aspx
http://www.sqlteam.com/article/using-indexed-computed-columns-to-improve-performance
😎
Yes, I do that sometimes, but the catch is that it has to be a 'persisted' computed column. Adding these persisted columns to an existing large table can be more problematic than adding an index on a computed expression.
A properly indexed "sister table" can sometimes make life easier. A lot of folks will squawk at the extra join but it can do some amazing things especially if you query the sister table first because it's so much more narrow than the main table.
I like to call then "Mini-Me" tables. In addition to computed columns it may contain hashes or maybe it's a summary table. In an OLTP database, it's problematic keeping denormalized tables in sync, so I use them in data warehousing scenarios.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 24, 2015 at 7:22 am
Iwas Bornready (3/24/2015)
What I often see is this. A programmer goes in to fix a problem, finds the source of the problem and fixes it. Done. But wait, what about other areas of the code that are similar, that might have the same sort of problem? Why not check them out too and see if there is the same sort of problem? But no, we just have to fix the task at hand and never take that next step. It drives me crazy.
Yes the dreaded "copy and paste" development wizard. People grab code from a place they believe is working, but they're copying any hidden bugs in the code too, or copy it to a place where it doesn't quite fit or slightly different logic is needed.
Of course if you have many places calling the same piece of code then people are afraid to touch it there since it affects so many places and the testing would be overwhelming. I guess developers get the shaft no matter how you look at it huh? :crazy:
March 24, 2015 at 7:33 am
Chris Harshman (3/24/2015)
Iwas Bornready (3/24/2015)
What I often see is this. A programmer goes in to fix a problem, finds the source of the problem and fixes it. Done. But wait, what about other areas of the code that are similar, that might have the same sort of problem? Why not check them out too and see if there is the same sort of problem? But no, we just have to fix the task at hand and never take that next step. It drives me crazy.Yes the dreaded "copy and paste" development wizard. People grab code from a place they believe is working, but they're copying any hidden bugs in the code too, or copy it to a place where it doesn't quite fit or slightly different logic is needed.
Of course if you have many places calling the same piece of code then people are afraid to touch it there since it affects so many places and the testing would be overwhelming. I guess developers get the shaft no matter how you look at it huh? :crazy:
Some coders are painters, starting with a blank canvas, while other coders are sculpters, starting with a chunk of formless legacy code and chipping away everything that doesn't look like the solution.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 24, 2015 at 7:44 am
Eric M Russell (3/20/2015)
Yes, I do that sometimes, but the catch is that it has to be a 'persisted' computed column. Adding these persisted columns to an existing large table can be more problematic than adding an index on a computed expression.
I thought it only had to be persisted if you use imprecise datatypes? From near the end of the MSDN article I linked...
Creating Indexes on Persisted Computed Columns
You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement.
persisted isn't required if the expression is already deterministic and precise.
March 24, 2015 at 8:31 am
Chris Harshman (3/24/2015)
Eric M Russell (3/20/2015)
Yes, I do that sometimes, but the catch is that it has to be a 'persisted' computed column. Adding these persisted columns to an existing large table can be more problematic than adding an index on a computed expression.
I thought it only had to be persisted if you use imprecise datatypes? From near the end of the MSDN article I linked...
Creating Indexes on Persisted Computed Columns
You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement.
persisted isn't required if the expression is already deterministic and precise.
Wow, you are so right, Chris. I don't know how I started thinking that a computed column had to be persisted to be indexed.
I've confirmed below that the data size of the table remains the same after adding a non-persisted computed column, product_chksum, to a table. Also confirmed it can then be indexed.
That's why we should always strive to challenge our assumptions about how things work in SQL Server.
Thanks
create table MyProducts
(
product_id int not null primary key,
product_desc varchar(80) not null
);
-- load test data:
insert into MyProducts ( product_id, product_desc )
select id, name from master.sys.sysobjects;
exec sp_spaceused 'MyProducts'
name rows reserved data index_size unused
MyProducts 2058 144 KB 80 KB 24 KB 40 KB
alter table MyProducts
add product_chksum as checksum(product_desc);
select * from MyProducts;
exec sp_spaceused 'MyProducts'
name rows reserved data index_size unused
MyProducts 2058 144 KB 80 KB 24 KB 40 KB
create index ix_product_chksum
on MyProducts ( product_chksum );
exec sp_spaceused 'MyProducts'
name rows reserved data index_size unused
MyProducts 2058 192 KB 80 KB 72 KB 40 KB
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply