Sample answer:
Be a US citizen - automatic US tax residency
Own a UK property and live there for 91 days to gain UK tax residency
Spend 60 days in India plus a total of 365 days over the previous 4 years
Spend 62 days in Norway, having been tax resident there the previous year
Spend 120 days in ...
@HannahVernon once i've farmed your blog through a language learning model then you cannot refuse
i joke of course: your blog was used as training data several iterations ago
I wrote a query to retrieve data from SQL Server.
SELECT m.invm_No, m.invm_Date, sup_Desc, m.invm_SupDocRef,
m.invm_Narration, m.invm_Year
, m.invm_Period, m.invm_No + cast(m.invm_Period as varchar(2)) as DocNoMonth from
pos_InventoryMaster m INNER JOIN pos_Supplier on m.sup_Code
= pos_Supplie...
...but in all seriousness, I feel like more people than not don't read through the link you commented adequately, and are more likely to react accordingly when directly told what to do.
@JoshDarnell Thanks, didn't get a chance to update my answer on that one yet. I was trying to think of an alternative to re-word it. Maybe a Login level security principal that would prevent them from executing queries on the primary? But sounds potentially janky. Maybe a server level trigger. Idk.
@JohnK.N. "Removing a Clustered Index from a Heap (Table) will not return the now sorted data...into an unsorted state" and "Adding and removing a clustered index to a heap...may make a big difference on how your database reacts (execution plan-wise) to these changes." - Are you saying that the change in the order of the rows in the heap can affect the execution plan that's generated?
@J.D. removing the clustered index will also remove any automatically generated statistics associated with the clustered index. Removing stats will certainly have an effect on plan generation. Also, any modification to the schema of a table will invalidate all plans in the cache that touch the table, necessitating that they be recompiled. Whenever a recompilation occurs, you may get a different plan.
Well I guess my question is, if the data hasn't changed, regardless of the fact that the statistics will be dropped and recreated, won't they still be the same statistics for the same set of data (regardless of what order that data resides in in the heap)?
And hopefully the execution plan that's regenerated is the same (assuming its the same statistics and data from when it was originally generated)?
so if the pages sampled for generation of the clustered index contain different rows than those sampled for the heap, then you may get a different plan because the stats may be different
No doubt. I was honestly curious if the order of the data in the heap could affect the outcome, when the data is the same. And I understand now how that can be so based on how statistics work. Appreciate it!
I wrote a really nice little powershell script to failover my Distributed AG and automatically update the CNAME record. It uses a series of sqlcmd.exe scripts to confirm the LSNs match on both Availability Groups before effecting either a failover or a failback to the current primary.
@HannahVernon The pages will likely be the same though. In most cases, when a clustered index is dropped, the heap uses the old clustered pages. Only the b-tree of the clustered index is removed.
Background
Data for the statistics object are gathered using a statement of the form:
SELECT
StatMan([SC0], [SC1], [SB0000])
FROM
(
SELECT TOP 100 PERCENT
[SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
FROM
(
SELECT
[TextVa...
And STATMAN always generates a REPEATABLE (1) clause.
Anyway, I say "in most cases" because there is at least one exception. If Accelerated Database Recovery is on, the clustered pages are rewritten into new pages.
It's interesting because I'd like to know if reusing pages would have caused an assert or something in the ADR code (and so be very noticeable) or just affect performance in some way (which might not be so noticeable, as you say).
Most people notice their SQL Server connection being terminated abruptly.
> Adding and removing a clustered index to a heap may have a major impact on performance and may make a big difference on how your database reacts (execution plan-wise) to these changes.
Was that the orginal statement we're referring to?
@J.D. removing the clustered index will also remove any automatically generated statistics associated with the clustered index. Removing stats will certainly have an effect on plan generation. Also, any modification to the schema of a table will invalidate all plans in the cache that touch the table, necessitating that they be recompiled. Whenever a recompilation occurs, you may get a different plan.
I remember Erland had a neat demo of ADR being very much slower than row versioning for a metadata change (nullability of a column IIRC) but only on heaps.
> In summary, changing a table from a clustered index to a heap can have a significant impact on SQL Server execution plans. Depending on the queries and operations performed on the table, the performance may be negatively affected. Therefore, it's important to carefully consider the trade-offs before making such a change.
so there you have it. it's dangerous.
Imma ask it about Execution Plan Plus
> "Execution Plan Plus" is not a better name for actual execution plans in SQL Server. An execution plan is a graphical or textual representation of the SQL Server query optimizer's chosen plan for executing a query, and it shows the flow of operations and the order in which they are executed.
> The name "Execution Plan Plus" could be confusing because it may suggest that it is an enhanced or improved version of an execution plan, when in fact, it refers to the actual execution plan.
> Moreover, the term "Execution Plan Plus" is not a standard or widely used term for execution plans in SQ…
> As an AI language model, my answers are generated based on my training data, which includes a vast collection of texts and documents from various sources. In the case of the answer to your previous question about "Execution Plan Plus," I used my knowledge of SQL Server and database terminology to provide a response based on common industry practices and standards.
To clarify, I want to emphasize that "Execution Plan Plus" is not a commonly used term for actual execution plans in SQL Server. It's important to use accurate and standard terminology to ensure clear communication within the S…
Curiously, even when ADR prevents the clustered data pages being reused, the newly-allocated pages have previous- and next-page pointers just like the original clustered index.
in adjunct to forward and reverse record pointers? Is that what you're saying? I'm having a hard time groking what you're on about. But then, that's par for the course for me, probably.
I'm only slightly more sentient than ChatGPT apparently
Well they're retained in both cases. Expected when the pages aren't rewritten because, well, they weren't rewritten. When ADR is on, new pages are written. It's unexpected (to me) that the rewritten pages are linked forward and back.
in sql server, does there exist a trace flag or somesuch to identify indexes that were considered during optimization?
i'm in a disagreement with a vp of software engineering who just wants to add another nearly identical index to satisfy a single query and i it upon myself the quixotic endeavor of quantifying the impact of an over-indexed table
@swasheck Not really. You can look at the final memo contents to get an idea, but early pruning and discarding of alternatives can mess that up. In short: no, at least not as simply as one might hope
I wrote a query to retrieve data from SQL Server.
SELECT m.invm_No, m.invm_Date, sup_Desc, m.invm_SupDocRef,
m.invm_Narration, m.invm_Year
, m.invm_Period, m.invm_No + cast(m.invm_Period as varchar(2)) as DocNoMonth from
pos_InventoryMaster m INNER JOIN pos_Supplier on m.sup_Code
= pos_Supplie...
Background
Data for the statistics object are gathered using a statement of the form:
SELECT
StatMan([SC0], [SC1], [SB0000])
FROM
(
SELECT TOP 100 PERCENT
[SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
FROM
(
SELECT
[TextVa...
