September 1, 2015 at 12:51 pm
ScottPletcher (9/1/2015)
Jacob Wilkins (9/1/2015)
I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.SELECT queries against temporary tables typically only take a shared object lock anyway, so it's really just the difference between taking an S lock on the object and a Sch-S lock on the object. There's really no additional overhead in terms of the number of locks taken, as that is quite low in both cases.
Aaron Bertrand took a quick look at that here: http://blogs.sqlsentry.com/aaronbertrand/nolock-temp-tables/.
The first time you run them you'll see a bunch of locks on metadata and such for the query to compile (you see that in Aaron's piece above), but afterwards on a local temporary table that is a heap you'll see an S lock an an IS lock for the object without the NOLOCK hint (or directive, if you prefer), and with NOLOCK you'll see a Sch-S lock on the object and an S lock on the HOBT. With a clustered index, the results are the same except that you won't see the S lock on the HOBT for the NOLOCK case. I'm not sure that the difference between two locks and one lock when you have a clustered index on the temp table justifies the use of NOLOCK here 🙂
Basically, it just doesn't really make a difference with local temporary tables, so while you're technically not doing much other than wasting a few keystrokes, I can see how it might have some negative effects in terms of perpetuating some bad practices and misconceptions.
Cheers!
SQL does take many fewer locks in tempdb for SQL 2012 forward. AFAIK, SQL did not do that in SQL 2008 and before. (Not sure about 2008 R2). I thought this forum was for SQL 2008 (or perhaps before). In older versions, SQL acquires shared locks row by row for SELECTs, in tempdb as with any other db. Naturally it must also release those locks: admittedly, that is so little overhead it can probably be ignored. I.e., a 100K rows SELECTed = a 100K locks. AFAIK, if you run the code in the article yourself on a SQL 2008 box, you should be able to see the row locks.
For local temporary tables, I always remember seeing object level S locks for SELECTs, even in 2008. I'll have to track down a 2008 instance (fortunately getting harder and harder to do!) and test to make sure I'm not suffering from a poor memory 🙂
September 1, 2015 at 12:52 pm
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
drop table #tempCREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
Why in the world are you using NOLOCK here? There is no possibility that table can be locked by any other process. Are you familiar with that hint and everything it brings to the table? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Since it's impossible that anyone else needs to use that table, why on earth would you not use NOLOCK to reduce overhead?
Edit: Corrected one typo.
Scott last I remembered in the thread where you and Jeff were discussing this you were not able to provide significant proof that it made much, if any, difference.
You have been around this technology long enough to know that when you see that hint it is very frequently an indication that the person doesn't understand the nuances of that hint. As you well know that hint is not a magic go faster button but you seem to not only condone its usage, you seem to promote using it.
Only because you seem to blindly disparage it no matter what the context. NOLOCK wouldn't exist in SQL if it didn't have an effect. Any lock requires resources to accomplish: that's axiomatic. Thus avoiding any lock saves resources: that's axiomatic as well. Are those resources significant? Impossible to say without a lot of details.
In this specific case, how could NOLOCK ever cause a consistency problem? I guess I just don't see how.
Perhaps I tend to assume the worst with that hint more frequently than others. That is based on two things. First is a real world experience that costs hundreds of thousands of dollars and nearly took down a major player in the health care industry because of the mandate to use that hint on every single query no matter what despite the arguments from myself and a few other consultants.
The second reason is that I see it on the forums over and over misused/abused by people who don't have anywhere near the technical understanding of what really is happening like you do. Those people continue to blindly think it makes their queries faster while being totally oblivious to what might actually be happening.
Yes query hints have their place but they should be used by people who really understand the data and the hint. They should usually be a matter of last resort when all other attempts at performance improvement have been made.
I would feel like I was doing anyone an injustice by not suggesting they read up about the hint and understand it. Anything else would not be fulfilling my desire to help people understand and improve their knowledge with sql server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 1, 2015 at 1:08 pm
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
drop table #tempCREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
Why in the world are you using NOLOCK here? There is no possibility that table can be locked by any other process. Are you familiar with that hint and everything it brings to the table? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Since it's impossible that anyone else needs to use that table, why on earth would you not use NOLOCK to reduce overhead?
Edit: Corrected one typo.
Scott last I remembered in the thread where you and Jeff were discussing this you were not able to provide significant proof that it made much, if any, difference.
You have been around this technology long enough to know that when you see that hint it is very frequently an indication that the person doesn't understand the nuances of that hint. As you well know that hint is not a magic go faster button but you seem to not only condone its usage, you seem to promote using it.
Only because you seem to blindly disparage it no matter what the context. NOLOCK wouldn't exist in SQL if it didn't have an effect. Any lock requires resources to accomplish: that's axiomatic. Thus avoiding any lock saves resources: that's axiomatic as well. Are those resources significant? Impossible to say without a lot of details.
In this specific case, how could NOLOCK ever cause a consistency problem? I guess I just don't see how.
Perhaps I tend to assume the worst with that hint more frequently than others. That is based on two things. First is a real world experience that costs hundreds of thousands of dollars and nearly took down a major player in the health care industry because of the mandate to use that hint on every single query no matter what despite the arguments from myself and a few other consultants.
The second reason is that I see it on the forums over and over misused/abused by people who don't have anywhere near the technical understanding of what really is happening like you do. Those people continue to blindly think it makes their queries faster while being totally oblivious to what might actually be happening.
Yes query hints have their place but they should be used by people who really understand the data and the hint. They should usually be a matter of last resort when all other attempts at performance improvement have been made.
I would feel like I was doing anyone an injustice by not suggesting they read up about the hint and understand it. Anything else would not be fulfilling my desire to help people understand and improve their knowledge with sql server.
I can accept that. And, yes, insisting on NOLOCK on every query, no matter how critical the result is, is clearly insane.
I admit, also, that way too many people vastly overuse and abuse NOLOCK. But I won't use that problem to lie and say "it does not speed up queries". It could indeed speed queries up ... at the cost of possibly wrong results, which can be unacceptable for certain queries and cause great damage to a business. But it still might indeed have made the query run faster.
Honestly, though, I still think that overall the "use identity as the 'default' clustering key" myth is even more damaging than an "almost always use NOLOCK" myth. Improper clustering causes huge performance problems, including an explosion of extra indexes, with all the associated issues. Moreover, poor performance from bad clustering undoubtedly contributes greatly to NOLOCK being used in the first place, so in that sense it is indeed more damaging since it causes the subsequent NOLOCK damage as well.
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".
September 1, 2015 at 1:10 pm
Jacob Wilkins (9/1/2015)
ScottPletcher (9/1/2015)
Jacob Wilkins (9/1/2015)
I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.SELECT queries against temporary tables typically only take a shared object lock anyway, so it's really just the difference between taking an S lock on the object and a Sch-S lock on the object. There's really no additional overhead in terms of the number of locks taken, as that is quite low in both cases.
Aaron Bertrand took a quick look at that here: http://blogs.sqlsentry.com/aaronbertrand/nolock-temp-tables/.
The first time you run them you'll see a bunch of locks on metadata and such for the query to compile (you see that in Aaron's piece above), but afterwards on a local temporary table that is a heap you'll see an S lock an an IS lock for the object without the NOLOCK hint (or directive, if you prefer), and with NOLOCK you'll see a Sch-S lock on the object and an S lock on the HOBT. With a clustered index, the results are the same except that you won't see the S lock on the HOBT for the NOLOCK case. I'm not sure that the difference between two locks and one lock when you have a clustered index on the temp table justifies the use of NOLOCK here 🙂
Basically, it just doesn't really make a difference with local temporary tables, so while you're technically not doing much other than wasting a few keystrokes, I can see how it might have some negative effects in terms of perpetuating some bad practices and misconceptions.
Cheers!
SQL does take many fewer locks in tempdb for SQL 2012 forward. AFAIK, SQL did not do that in SQL 2008 and before. (Not sure about 2008 R2). I thought this forum was for SQL 2008 (or perhaps before). In older versions, SQL acquires shared locks row by row for SELECTs, in tempdb as with any other db. Naturally it must also release those locks: admittedly, that is so little overhead it can probably be ignored. I.e., a 100K rows SELECTed = a 100K locks. AFAIK, if you run the code in the article yourself on a SQL 2008 box, you should be able to see the row locks.
For local temporary tables, I always remember seeing object level S locks for SELECTs, even in 2008. I'll have to track down a 2008 instance (fortunately getting harder and harder to do!) and test to make sure I'm not suffering from a poor memory 🙂
Yes, you will always see object level locks, even today. But you also saw the row-level locks in SQL for #tables back then. SQL hadn't figured out the (seemingly obvious!) fact that it could forgo row-level shared locks on a temp 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".
September 1, 2015 at 1:13 pm
ScottPletcher (9/1/2015)
Jacob Wilkins (9/1/2015)
ScottPletcher (9/1/2015)
Jacob Wilkins (9/1/2015)
I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.SELECT queries against temporary tables typically only take a shared object lock anyway, so it's really just the difference between taking an S lock on the object and a Sch-S lock on the object. There's really no additional overhead in terms of the number of locks taken, as that is quite low in both cases.
Aaron Bertrand took a quick look at that here: http://blogs.sqlsentry.com/aaronbertrand/nolock-temp-tables/.
The first time you run them you'll see a bunch of locks on metadata and such for the query to compile (you see that in Aaron's piece above), but afterwards on a local temporary table that is a heap you'll see an S lock an an IS lock for the object without the NOLOCK hint (or directive, if you prefer), and with NOLOCK you'll see a Sch-S lock on the object and an S lock on the HOBT. With a clustered index, the results are the same except that you won't see the S lock on the HOBT for the NOLOCK case. I'm not sure that the difference between two locks and one lock when you have a clustered index on the temp table justifies the use of NOLOCK here 🙂
Basically, it just doesn't really make a difference with local temporary tables, so while you're technically not doing much other than wasting a few keystrokes, I can see how it might have some negative effects in terms of perpetuating some bad practices and misconceptions.
Cheers!
SQL does take many fewer locks in tempdb for SQL 2012 forward. AFAIK, SQL did not do that in SQL 2008 and before. (Not sure about 2008 R2). I thought this forum was for SQL 2008 (or perhaps before). In older versions, SQL acquires shared locks row by row for SELECTs, in tempdb as with any other db. Naturally it must also release those locks: admittedly, that is so little overhead it can probably be ignored. I.e., a 100K rows SELECTed = a 100K locks. AFAIK, if you run the code in the article yourself on a SQL 2008 box, you should be able to see the row locks.
For local temporary tables, I always remember seeing object level S locks for SELECTs, even in 2008. I'll have to track down a 2008 instance (fortunately getting harder and harder to do!) and test to make sure I'm not suffering from a poor memory 🙂
Yes, you will always see object level locks, even today. But you also saw the row-level locks in SQL for #tables back then. SQL hadn't figured out the (seemingly obvious!) fact that it could forgo row-level shared locks on a temp table.
I understand that you will always see object level locks because of intent locks. I did mistype and left out a word, but I would think it might be clear from context anyway; apparently it was not. Even in 2008, I remember seeing only object level locks for SELECTs against local temporary tables. With that bit of clarification out of the way, obviously it just needs be tested. I should be able to track down a 2008 server later and post some results.
Cheers!
EDIT: Technically, I just realized, there was nothing wrong with my initial statement of what I remembered, because I did specify object level S locks, which you wouldn't see if the S locks were being taken out at the row level; you'd see an IS lock at the object level instead. That aside, at least now it should be ultra-clear what I was saying. I'll post those test results later today, hopefully. 🙂
September 1, 2015 at 1:15 pm
ScottPletcher (9/1/2015)
Honestly, though, I still think that overall the "use identity as the 'default' clustering key" myth is even more damaging than an "almost always use NOLOCK" myth. Improper clustering causes huge performance problems, including an explosion of extra indexes, with all the associated issues. Moreover, poor performance from bad clustering undoubtedly contributes greatly to NOLOCK being used in the first place, so in that sense it is indeed more damaging since it causes the subsequent NOLOCK damage as well.
That is highly likely to be true. I totally agree that identity is probably more abused as the clustering key than NOLOCK. This not only is an interesting observation it would make a great editorial. You should write that one up, would be an interesting read I am sure. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 1, 2015 at 1:41 pm
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Honestly, though, I still think that overall the "use identity as the 'default' clustering key" myth is even more damaging than an "almost always use NOLOCK" myth. Improper clustering causes huge performance problems, including an explosion of extra indexes, with all the associated issues. Moreover, poor performance from bad clustering undoubtedly contributes greatly to NOLOCK being used in the first place, so in that sense it is indeed more damaging since it causes the subsequent NOLOCK damage as well.That is highly likely to be true. I totally agree that identity is probably more abused as the clustering key than NOLOCK. This not only is an interesting observation it would make a great editorial. You should write that one up, would be an interesting read I am sure. 😀
And yet, Scott, you as vehemently argue that using an identity column as a clustering index is wrong and should not be used as Sean argues against NOLOCK. I support Sean and his quest to educate people about NOLOCK. It has its place, we use it for the queries that generate data for dashboards for our application. We know the issues but we also know that it doesn't need to be 100% accurate, just close enough.
Using an identity column for a clustered index also has its uses. You need to be sure what the implications are and be able to defend why you are using it.
Guess what, it comes down to "it depends" in both cases.
Now, please take your horse back to the stables.
September 1, 2015 at 1:51 pm
Lynn Pettis (9/1/2015)
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Honestly, though, I still think that overall the "use identity as the 'default' clustering key" myth is even more damaging than an "almost always use NOLOCK" myth. Improper clustering causes huge performance problems, including an explosion of extra indexes, with all the associated issues. Moreover, poor performance from bad clustering undoubtedly contributes greatly to NOLOCK being used in the first place, so in that sense it is indeed more damaging since it causes the subsequent NOLOCK damage as well.That is highly likely to be true. I totally agree that identity is probably more abused as the clustering key than NOLOCK. This not only is an interesting observation it would make a great editorial. You should write that one up, would be an interesting read I am sure. 😀
And yet, Scott, you as vehemently argue that using an identity column as a clustering index is wrong and should not be used as Sean argues against NOLOCK. I support Sean and his quest to educate people about NOLOCK. It has its place, we use it for the queries that generate data for dashboards for our application. We know the issues but we also know that it doesn't need to be 100% accurate, just close enough.
Using an identity column for a clustered index also has its uses. You need to be sure what the implications are and be able to defend why you are using it.
Guess what, it comes down to "it depends" in both cases.
Now, please take your horse back to the stables.
Why do you insist on falsifying my position. Show me once when I've ever said identity should not be used as a clustering key. Identity should not be a default clustering key, because there should never be a "default" clustering key.
But you and other identity-obsessives have people automatically adding identity pks to every table. That's a horrible disservice to everyone believing it and to data modeling and table design in general.
No, I won't quit fighting the terrible idea of a default clustering key, that should be considered good for "every" table. There are far more important factors than page splits to consider when determining a clustering key.
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".
September 1, 2015 at 1:58 pm
Lynn Pettis (9/1/2015)
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Honestly, though, I still think that overall the "use identity as the 'default' clustering key" myth is even more damaging than an "almost always use NOLOCK" myth. Improper clustering causes huge performance problems, including an explosion of extra indexes, with all the associated issues. Moreover, poor performance from bad clustering undoubtedly contributes greatly to NOLOCK being used in the first place, so in that sense it is indeed more damaging since it causes the subsequent NOLOCK damage as well.That is highly likely to be true. I totally agree that identity is probably more abused as the clustering key than NOLOCK. This not only is an interesting observation it would make a great editorial. You should write that one up, would be an interesting read I am sure. 😀
And yet, Scott, you as vehemently argue that using an identity column as a clustering index is wrong and should not be used as Sean argues against NOLOCK. I support Sean and his quest to educate people about NOLOCK. It has its place, we use it for the queries that generate data for dashboards for our application. We know the issues but we also know that it doesn't need to be 100% accurate, just close enough.
Using an identity column for a clustered index also has its uses. You need to be sure what the implications are and be able to defend why you are using it.
Guess what, it comes down to "it depends" in both cases.
Now, please take your horse back to the stables.
Some of my comments yesterday from another q:
http://www.sqlservercentral.com/Forums/Topic1715492-391-2.aspx
"Customer" table is fine. CustomerId, like OrderId, is a natural "master" table where identity often is the best clustering key. [See, unlike Celko, I don't actually hate identities just for the sake of hating them.]
Notice, too, that in the original poster's DDL, every table has "identity primary key". Oh the humanity!
Edit:
"CustomerProductScore" table. I don't see a reason for this to be a separate table with only the columns that are shown. Why can't this be stored in the "CustomerProduct"/"Product"? Are there multiple values? If so, what key values distinguishes each Score value?
Btw, this points out a very major issue with slapping "magic" identity columns on every table just to, allegedly, give you non-fragmenting clustering (falsely claimed to be the holy grail of table/key design). Without an identity, you can easily see that you don't have any key value(s) to distinguish multiple rows. That makes you realize that (a) the detail column(s) should go back into its main table, since they are 1:1 values or (b) you need to identify and use another distinguishing key value for this relationship/table. By delaying adding an identity unless/until it's actually required, you must first identify natural keys, which points out possible data relationship issues.
I personally haven't seen that many critical issues with NOLOCK. Again, I've never advocated for its use in every query or even in most queries. But I still believe it's not as damaging as the lack of data design being caused by automatically adding a clustering identity key to every table.
You say that's now what you intended? Again, my support for specific uses of NOLOCK doesn't mean that I want it everywhere either. Yes, some do go that far. But more go too far with identity. I've seen some successes on the NOLOCK fron -- that is, a reduction in its use -- but nothing but regression on the identity front. I know of no one pushing NOLOCK on (virtually) every table, but lots of people effectively advocating identity, and even clustering on it, on every 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".
September 2, 2015 at 9:00 am
Finally, there's the cost to fix each issue.
Once people are properly convinced that NOLOCK is a bad idea in many, many cases, they simply remove those specific bytes -- "WITH (NOLOCK)" -- from the code. That's the sum total of rework effort.
But for a bad identity clustering key? Massive rework could be required, especially if tables have to be redesigned/restructured. Even the simplest case of just changing the clustered index to something else requires lots of work.
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".
September 2, 2015 at 9:42 am
ScottPletcher (9/2/2015)
Finally, there's the cost to fix each issue.Once people are properly convinced that NOLOCK is a bad idea in many, many cases, they simply remove those specific bytes -- "WITH (NOLOCK)" -- from the code. That's the sum total of rework effort.
But for a bad identity clustering key? Massive rework could be required, especially if tables have to be redesigned/restructured. Even the simplest case of just changing the clustered index to something else requires lots of work.
You are right and I am wrong. There is absolutely NO REASON ON EARTH that you should EVER use an identity column as a clustered index. Happy now???
September 2, 2015 at 9:46 am
Lynn Pettis (9/2/2015)
ScottPletcher (9/2/2015)
Finally, there's the cost to fix each issue.Once people are properly convinced that NOLOCK is a bad idea in many, many cases, they simply remove those specific bytes -- "WITH (NOLOCK)" -- from the code. That's the sum total of rework effort.
But for a bad identity clustering key? Massive rework could be required, especially if tables have to be redesigned/restructured. Even the simplest case of just changing the clustered index to something else requires lots of work.
You are right and I am wrong. There is absolutely NO REASON ON EARTH that you should EVER use an identity column as a clustered index. Happy now???
No, that's the same lie about me. I never stated one should never use an identity column. Contrarily, when I suggest anything but an identity column as a clustering key, you refer me to a video to remove my ignorance of the absolute necessity of using only identities as clustering keys.
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".
September 2, 2015 at 9:49 am
ScottPletcher (9/2/2015)
Lynn Pettis (9/2/2015)
ScottPletcher (9/2/2015)
Finally, there's the cost to fix each issue.Once people are properly convinced that NOLOCK is a bad idea in many, many cases, they simply remove those specific bytes -- "WITH (NOLOCK)" -- from the code. That's the sum total of rework effort.
But for a bad identity clustering key? Massive rework could be required, especially if tables have to be redesigned/restructured. Even the simplest case of just changing the clustered index to something else requires lots of work.
You are right and I am wrong. There is absolutely NO REASON ON EARTH that you should EVER use an identity column as a clustered index. Happy now???
No, that's the same lie about me. I never stated one should never use an identity column. Contrarily, when I suggest anything but an identity column as a clustering key, you refer me to a video to remove my ignorance of the absolute necessity of using only identities as clustering keys.
Then why do you keep arguing with me when I say that there are times it is appropriate? The only reason I can come to is that I am wrong and you are right.
And I have NEVER referred you to any video.
September 2, 2015 at 9:58 am
Lynn Pettis (9/2/2015)
ScottPletcher (9/2/2015)
Lynn Pettis (9/2/2015)
ScottPletcher (9/2/2015)
Finally, there's the cost to fix each issue.Once people are properly convinced that NOLOCK is a bad idea in many, many cases, they simply remove those specific bytes -- "WITH (NOLOCK)" -- from the code. That's the sum total of rework effort.
But for a bad identity clustering key? Massive rework could be required, especially if tables have to be redesigned/restructured. Even the simplest case of just changing the clustered index to something else requires lots of work.
You are right and I am wrong. There is absolutely NO REASON ON EARTH that you should EVER use an identity column as a clustered index. Happy now???
No, that's the same lie about me. I never stated one should never use an identity column. Contrarily, when I suggest anything but an identity column as a clustering key, you refer me to a video to remove my ignorance of the absolute necessity of using only identities as clustering keys.
Then why do you keep arguing with me when I say that there are times it is appropriate? The only reason I can come to is that I am wrong and you are right.
And I have NEVER referred you to any video.
Again, show me any time I've ever said an identity for clustering was never right, or not right in certain cases. Indeed, I posted a link above where I explicitly endorsed an identity clustering key.
My disgust is of the promotion of the idea that identity should be the de facto "default" clustering index. This concept has been so heavily promoted it's now deeply embedded in many places. For some people, every -- and I mean every -- table starts with an identity column, and often with "primary key" included. This is a disaster for proper data design, particularly given that so many people skip the logical design phase and go straight to actual tables.
Edit: I apologize if I mixed you up with someone else regarding the video.
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".
September 2, 2015 at 10:30 am
ScottPletcher (9/2/2015)
Lynn Pettis (9/2/2015)
ScottPletcher (9/2/2015)
Lynn Pettis (9/2/2015)
ScottPletcher (9/2/2015)
Finally, there's the cost to fix each issue.Once people are properly convinced that NOLOCK is a bad idea in many, many cases, they simply remove those specific bytes -- "WITH (NOLOCK)" -- from the code. That's the sum total of rework effort.
But for a bad identity clustering key? Massive rework could be required, especially if tables have to be redesigned/restructured. Even the simplest case of just changing the clustered index to something else requires lots of work.
You are right and I am wrong. There is absolutely NO REASON ON EARTH that you should EVER use an identity column as a clustered index. Happy now???
No, that's the same lie about me. I never stated one should never use an identity column. Contrarily, when I suggest anything but an identity column as a clustering key, you refer me to a video to remove my ignorance of the absolute necessity of using only identities as clustering keys.
Then why do you keep arguing with me when I say that there are times it is appropriate? The only reason I can come to is that I am wrong and you are right.
And I have NEVER referred you to any video.
Again, show me any time I've ever said an identity for clustering was never right, or not right in certain cases. Indeed, I posted a link above where I explicitly endorsed an identity clustering key.
My disgust is of the promotion of the idea that identity should be the de facto "default" clustering index. This concept has been so heavily promoted it's now deeply embedded in many places. For some people, every -- and I mean every -- table starts with an identity column, and often with "primary key" included. This is a disaster for proper data design, particularly given that so many people skip the logical design phase and go straight to actual tables.
Edit: I apologize if I mixed you up with someone else regarding the video.
I have never said it should be the de facto "default" clustering index. I have said that at times it is the appropriate column for the clustered index. I have always erred on the side of "it depends" in making recommendations (except maybe using uniqueidentifiers as the clustered index). So why do you keep arguing with me when I say that? That is the frustrating part.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply