The Heap™ – Consultancy ©®

Morning

hot2use

morning

8:04 AM

> repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (database).

pff

Jack Douglas

Just for you @Evan dbfiddle.uk/…

Q: Where is Newid(), Rand(), and Sysutcdatetime() new value per query vs. row behavior documented?

8:20 AM

Morning

Within a single query, all current time functions like sysutcdatetime() return a single point in time that is re-used throughout the query for each instance (e.g. of sysutcdatetime()) no matter how complex (afaik). However NewId() and Rand() do not follow this behavior and diverge from each oth...

I think this is a very interesting question. Could be better formulated, but I'd like to see a good answer explaining the difference in behaviour between the various functions.

3 hours later…

ypercubeᵀᴹ

11:31 AM

oops

dba.stackexchange.com/posts/215519/revisions

@Colin'tHart does Paul White's answer in the "duplicate" cover it? Or you think we need reopen?

hot2use

11:48 AM

@ypercubeᵀᴹ Added explanation in comment as per duplicate.

See explanation in duplicate question: The GETDATE and SYSDATETIME functions are indeed non-deterministic, but they are treated as runtime constants for a particular query. Broadly, this means the function's value is cached when query execution starts, and the result re-used for all references within the query. and This behaviour is by design, ... for the NEWID function. — hot2use 34 secs ago

@ypercubeᵀᴹ Covers part of it, but would be nice to have an answer that explains the differences between the three functions named in this question. All three functions behave differently in different contexts

Scratch that.

Covers most of it; presumably rand() works the same at the date/time functions

We have a few similar answers in the same area. That's the one with the quotation from Connect (RIP) that I think comes closest to the idea of official documentation. I left the comment rather than closing as a dupe because it was already closed, so it makes sense to see how people vote, and how the OP responds.

Maybe someone else could write a better answer, but if I answered it, I would use the same supporting links and much of the same phrasing.

Oh I see it has been reopened and closed as a dupe since I last visited the page.

The system works it seems 🙂

ypercubeᵀᴹ

@PaulWhite I saw your comment and voted to close. Hadn't noticed the tags - and the gold hammer

12:03 PM

I think it's in the right state for the time being.

12:14 PM

Works for me

hot2use

@PaulWhite +1

One of the questions I just reviewed reminded me of a maxim Jonathan Lewis used in one of his presentations

Regarding performance

Working too hard versus doing too much work

Working too hard is when you're doing unnecessary queries, or returning all rows when you only need a few. Things like that

Doing too much work is when the query is doing extra unnecessary sorting etc

Maybe I got the terms mixed up a bit... should see if I can find the course notes :-)

Doing less work more efficiently is usually a win.

12:32 PM

Doing less work is often the way I try to start when performance tuning.

@MaxVernon Finally gotten round to it

@TomV sweet.... I'm going to take a look shortly!

@MaxVernon It's not great, I just padded the comment a bit because I thought it should be in an answer, whether it was added to yours or as another one. Definitely not a comment-answer

12:58 PM

yay

1:11 PM

@TomV its a nice clear answer and provides good detail.

plus you mention Kalen so it's gotta be good.

2 hours later…

3:07 PM

VICTORY!!

the optimistic concurrency error was rather unhelpful

the problem was that I was trying to update a ts "timestamp" column while the target table had a trigger on that

3:23 PM

wow that only took months and months

congrats!

thanks

@MathieuGuindon is that a timestamp datatype? Those can never be updated by user code in SQL Server.

good question

checking

yup, timestamp

it's a very misleading type name

it's actually a monotonically increasing integer that SQL Server increments for each data modification performed database-wide.

no, no - the table is on MySQL

3:27 PM

ahhh, that's different then. :-)

so the trigger was causing a problem?

if it wasn't the timestamp type, then it had to be the trigger

in any case, the errors returned by the provider were wholly unhelpful

misleading anyway

Andy K

cya

@MathieuGuindon gotta love misleading error messages

1 hour later…

Zane

4:49 PM

Anybody know a reason that Doing a create index with the drop existing setting on would return this error?

Cannot create more than one clustered index on table 'dbo.table. Drop the existing clustered index 'IX_index' before creating another.

Didn't have any troubles running this script on other tables yesterday. Very confused.

Evan Carroll

5:23 PM

@JackDouglas awesome. Thanks! now we can all use VALUES expressions in MariaDB. About time.

That my SQL syntax for VALUES statements is terrifying, fyi.

It alias the columns inside of the VALUES statement by the actual value of the column.

SELECT t.what FROM ( VALUES ('what'),('the'),('fuck') ) AS t;

1 hour later…

jadarnel27

6:49 PM

@Zane no. Is it reproducible? I think that would make an interesting question on the main site.

There are several limitations to the DROP_EXISTING setting mentioned in the docs, but they give different error messages (can't convert a clustered index to nonclustered, can't change the constraint enforced by a unique index, etc).

Evan Carroll

7:31 PM

@MaxVernon Congrats!