@wBob same for me. Tried with selective indexes but that did not fly. Interesting about the redundant ordinals. One more thing to try when optimizing.

As it happens, that's my favourite method of zero padding, but you Paul? Relying on implicit conversion, really?

wired.com/2016/05/…

Underground beer pipeline ^^^

@AndriyM A lazy comment. I would normally be explicit yes.

@PaulWhite That was in jest, of course, only because it was mostly thanks to you that I've learnt to be careful and avoid abusing implicit conversions.

I still like the conciseness of that method, though, can't help it.

@AndriyM Ha yes of course no worries! I'm so glad you mentioned it.

@AndriyM The simplicity is seductive, and the implicit conversion is probably harmless. I have been known to skip the cast/convert to varchar(11).

@MasterDatabase Well that's annoying!

@bluefeet That genuinely freaked me out for a moment! Master Database has become sentient!?! :)

:)

couldn't resist

@bluefeet Yeah nah fair play to you. Nicely done.

@bluefeet Is it well known that chat rooms undeleted by a moderator can be re-deleted by system very shortly after if the conditions for the initial deletion still apply? That caught me very much by surprise.

@PaulWhite I'm going to say yes, it's probably by design

I believe the only way to keep a un-deleted chatroom alive is to post in it

if there is no activity the system can delete it again, the next time clean-up runs

@bluefeet I just want to prevent deletion, not freezing. Freezing is OK. I want the transcript to be available, for anyone interested in seeing it.

I will probably ask this on meta.SE at some stage, just wondering if you know much about it offhand.

It seems unusual for the system to override an explicit mod action.

@PaulWhite right, but rooms without activity for 7 days are deleted. Worth retaining means more than 15 messages by at least 2 users

do you have a link to the room?

I can poke a chatterer expert. :)

yes, I really meant chatterer

@bluefeet Linky - it's the room related to the meta question sentient M.D. was talking about originally.

11 messages by 2 users - means it'll be deleted

@bluefeet That much I knew before hand. What surprised me was the auto re-delete after a mod (me) undeleted.

Unfortunately, it still doesn't meet the requirements to stick around.

@bluefeet 18 messages by 3 users now though. Jack added some.

@PaulWhite that will probably make it stick

@bluefeet And this is what I'm getting at. For a mod to undelete a chat room, and make it stick, we need to add noise messages. Seems ... inefficient?

@PaulWhite I guess my response would be is a room with only 11 messages really needed to stick around? If something important took place in the chatroom that needed to be included in a question or answer, maybe it should be added.

I'd say throw a request on MSE

yeah, do that @PaulWhite

@bluefeet That particular room is not a great example, but that does rather come to my point: should the system override - applying the general logic - after a mod has reviewed and considered the room transcript worth keeping? Yes I will likely ask on meta.SE.

It's the lack of public transcript that bugs me.

@PaulWhite I'm not trying to be difficult @PaulWhite and I hope you don't take it that way. I can't say for sure if it's worth keeping all these rooms around if undeleted. Throw it up on MSE and see what folks think.

@bluefeet No not at all, I appreciate the discussion.

@bluefeet For context, the original thing that brought it up was not that room at all, but another room that was created by a mod move-to-chat action.

I mean if we're worried about losing info, then shouldn't it be in the post. I don't know, I'm on very little sleep right now. :)

Chat like comments isn't permanent and can easily be purged.

@bluefeet In general, I agree, and do a lot of work manually moving comments into Qs/As, but sometimes only the original authors could do that, and they don't.

Anyway, I have straight in my mind what I want to say now so thanks. Go get some sleep.

@Lamak Do what? Add noise messages (the linked message)? Or ask on meta.SE?

@PaulWhite sleep, what's that? I have to finish working today.

@bluefeet Get back to work!!!

yes, sir!

LOL

I'll stop tormenting you with my edits

That was funny.

@PaulWhite It was referencing the "ask in MSE" thing

@Lamak Yes I see. Chat "helpfully" auto-linked your message to my previous reply.

@PaulWhite ah, that's right, didn't notice

Today is off to a bit of a weird start.

got to be better than the start to my day

@bluefeet What happened?

it's starting to seem like the start of the end of today for me

@Lamak What time is it where you are? 9:30am Thursday here.

@PaulWhite 2:45am I woke up from dream with a possible solution to a problem I've had for about 1.5 months. I got up and starting working on it been working since

@bluefeet Oh! I can relate to that!

@PaulWhite 5:30pm wednesday

@Lamak Ah. Enjoy your evening then :)

@bluefeet hey, at least it was with a possible solution

@PaulWhite I'll try

first I have to get off work though

@Lamak true and it actually worked which made it better

ah, then it's all good

I've had "dream solutions" that got me up to work on, and turned out to be complete rubbish when fully awake.

My subconscious is not as smart as it thinks it is.

I think it was the first time I ever had a dream solution

I was so wired when I woke up I decided to start working on it

@AndriyM Added an explicit conversion to my comment :)

Neat.

The context is somewhat lost now that the message to which you replied is removed.

@AndriyM Oh that's true. Nothing I can do about that: he deleted it himself. I'll just edit the answer instead.

Done.

What I've only just realised is that the GETDATE() in that answer is used in the computed column's expression directly rather than as a default value of a different column which would then be used in the computed expression (which was what I actually envisioned that method to be).

Wouldn't their implementation cause all IDs to always consist of the current date (changing to a new date every day)?

@AndriyM I wrote a whole reply for that but binned it. Are you talking about SELECT FORMAT( GETDATE(), 'kyyMMdd' ) ?

I like how FORMAT takes date and string switches.

@wBob No, I mean specifically the fact that GETDATE() is used directly in the computed column's expression. This way the resulting ID should always be generated based on today – meaning that all IDs would always contain the today's date, every day. Or am I wrong?

The OP wants the IDs to be generated based on today but they shouldn't change, right? And since GETDATE() is in the computed expression, the ID that's based on today's date will be based on tomorrow's date tomorrow. I haven't verified that yet, I guess I should, just for myself.

@AndriyM I'm with you, you'll have to travel in time to test though right ; )

I did mine like this:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp
	(
	rowId INT IDENTITY(0,1) PRIMARY KEY,
	yourSpecialId AS FORMAT( uniqueConstraintP1, 'kyyMMdd' ) + ' ' + FORMAT( CAST( ( ( rowId ) % 100 ) AS INT ), '00'  ),

	uniqueConstraintP1 DATE DEFAULT GETDATE(),
	uniqueConstraintP2 AS ( rowId ) % 100,
	uniqueConstraintP3 AS DATEPART (dayOfYear, GETDATE() )
	)
GO

-- Column cannot be used in an index or statistics or as a partition key because it is non-deterministic.

but didn't post in the end as I thought it was too similar to existing answer.

and I didn't really do the daily reset properly