The Heap™ – Consultancy ©® - 2018-05-03 (page 1 of 2)

« first day (2676 days earlier) ← previous day next day → last day (2185 days later) »

12:08 AM

-4

Q: Create Random Word for SQL Column

Does anyone have any SQL code to automatically generate words (all letter, no text). Is there any inline SQL to perform this on a column? We are trying to obfuscate first/last name and other word columns in table. Thanks, This answer currently does not work on column: https://stackoverflow.c...

sql-server sql data-masking

You should be able to use the code in my previous answer around birth dates to generate some prototype code. edit your question to show anything you have tried, even if it doesn't work as desired. — Max Vernon 53 secs ago

6 hours later…

6:04 AM

Good morning

morning

6:20 AM

user image

7

@dezso Congratulations!

@PaulWhite thanks. It took a while. The next target is becoming legendary, I think it will happen in the next 100 years.

If SE/SO lasts that long.

@dezso btw I added a bounty to dba.stackexchange.com/q/184556

6:39 AM

Morning

Very nice, @dezso

It seems we have to trust dezso now

@PaulWhite ha. Let's hope my son is doing soon much better and I find the time to write it up. FYI earlier my 'problem' was being in the French Alps, not the sickness, so nothing serious.

@PaulWhite I will delete just about everything

@dezso Yep no worries, the bounty is to encourage everyone

@dezso Try to limit it to unsalvageable crap if you can :)

@dezso congrats

@dezso Congrats

A wise man that @PaulWhite

3 hours later…

9:28 AM

Morning.

morning

@dezso congrats! well done

@Marian @hot2use @TomV @AndriyM thanks, thanks, thanks, and tanks (sic!)

Is @deszo now a 20xer?

3

congrats @dezso.

Next in line - from the Heap - are @JoeObbish (16.7k), @MikaelEriksson (16.5) and @sp_BlitzErik (15.5). Who will cross the 20x line first?

2

@dezso I get tanks instead of thanks? Hmm, should I thank you or tank you? Ha! :)

But really, I think the normal response to that is probably to just be tankful

10:03 AM

@ypercubeᵀᴹ I think I could learn to trust MikaelEriksson

Anyone want to answer:

-4

Q: Tag rename [ansi-sql-standard] to [sql-standard]

The standard, though originally ANSI isn't really ANSI anymore. In fact, I imagine far more people develop it from the ISO/IEC outside of ANSI. ANSI is kind of a private cabal of American corporate and government interests that that sit united at ISO/IEC. ANSI calls it ISO/IEC 9075-4:2016, and t...

discussion support tagging

I see the -4, but no feedback at all as to why there is disagreement with the proposal?

10:18 AM

@PaulWhite My guess for the -4 (or partly) is the offensive language (cabal, afraid) and slightly irrelevant content about what ANSI is.

@ypercubeᵀᴹ Thanks.

ConcernedOfTunbridgeWells

@Taryn I used to live in New Zealand. There's a reason for that.

@ypercubeᵀᴹ depends on how many xml questions come up ;)

@dezso congrats, yo

@PaulWhite anything to add on this?

2

Q: In SQL Server is a FAST_FORWARD cursor DYNAMIC by default?

I've been reading the DECLARE CURSOR (Transact-SQL) Books Online article. It states: FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. and FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row. F...

sql-server cursors

@ConcernedOfTunbridgeWells Flagged!

ConcernedOfTunbridgeWells

@PaulWhite Could be worse. Could be Australia.

10:31 AM

PostgreSQL's fsync() surprise

@sp_BlitzErik I'll take a looky ... seems like an obv-o-dupe, no?

10:48 AM

@PaulWhite i wasn't sure. it would certainly end up with an almost duplicate answer... unless you have another q&a in mind

@sp_BlitzErik Nope. If I posted an answer it would say pretty much what I said on the dupe.

dupe hammer it then i guess

i won't be part of such a charade

@sp_BlitzErik Wield the Hammer of Thor!

@sp_BlitzErik Craig wrote a really interesting Q&A on Stack Overflow about that a while back: stackoverflow.com/questions/42434872/…

@jadarnel27 good for "craig"

if that is zxis real name

11:02 AM

@sp_BlitzErik Good save. I almost flagged you.

i would have welcomed your inclusion of a flag

I wonder how SQL Server on Linux deals with that

Feature request: ability to flag specific revisions of chat messages

@PaulWhite Ooh, I hadn't thought of that.

hekaton probably

@jadarnel27 status-no-repro

11:07 AM

I'm being shut down by power-mad moderators!

So typical of them

user image

careful how you flag revisions, comrade

someday revision may flag you

@sp_BlitzErik :o

I'm being subtly threatened by a cartoon crossfitter using MS Paint. halp.

3

1. that's snagit editor

2. i can't even go to the gym to do real work nevermind crosfit

3. YOUR [sic] A DEAD MAN

I like this room. It's fun.

Feature request: allow Erik to downvote my incorrect chat messages

11:16 AM

status-review

should this even be here?

0

Q: OPENROWSET prints blank lines (or no data) to spreadsheet when logged in as any user but myself

I am trying to export data to Excel from SQL Server via a c# winforms application. The application is running on a windows domain environment. Here is the query: string exportQuery = @"INSERT INTO OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\temp\ExportedCategoryTotals.xls; ...

sql-server c# excel openrowset

it's everywhere

0

Q: Excel inserts no data using OPENROWSET when logged in as any account but my local account

I am trying to export data to Excel from SQL Server via a c# winforms application. The application is running on a windows domain environment. Here is the query: string exportQuery = @"INSERT INTO OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\temp\ExportedCategoryTotals.xls; ...

c# sql-server winforms export openrowset

if only we had people to moderate such behavior

they're busy responding to chat messages

sucks

11:21 AM

@sp_BlitzErik So anyway are you going to wield your golden hammer or not?

@PaulWhite i saw your comment there so figured i'd check in later

@sp_BlitzErik Ok fair enough

@PaulWhite answered

0

A: Tag rename [ansi-sql-standard] to [sql-standard]

ANSI References in 2018 There are still strong references to the "ANSI" standard as can be seen in the following excerpt from a page in the Oracle 12c documentation: (Emphasis by me) ANSI Standards The following documents of the American National Standards Institute (ANSI) relate to SQ...

@hot2use not enough dmx quotes but you have my vote

@hot2use ooo thanks

11:29 AM

You are welcome

@sp_BlitzErik I don't walk around big headed. I'm not a superstar.

Thanks @AndriyM

@hot2use pretty tame for the dog ;)

I thought dmx was a bicycle

@billinkc Have you seen Conditional Connection in SSIS based on Availability Group status?

ssis should have it's own se

pollution

pure pollution

masochist.se?

11:48 AM

i mean i'd just lump it in with visio

12:02 PM

informal poll

ahem

what's the opposite of "thanks in advance"?

"shouldn't have bothered in retrospect"?

We launched a thing today - stackoverflow.com/teams

@AndriyM that's a little too opposite

@sp_BlitzErik Let me VtC then for the time being :) As unclear

not very welcoming

12:07 PM

@sp_BlitzErik exactly one absence of "thanks in advance"

@sp_BlitzErik I must admit, I'm also feeling a little bit exclusive at this point too

:O

It'll pass I'm sure

is there a way to search chat for posted images?

Stack Overflow for Teams is Now Available

Alex Miller on May 03, 2018

It’s here. After months of designing, building, testing, and beta-ing, we’re happy to announce that as of today, Stack Overflow for Teams has launched and is available to everyone!

It often feels like developers have to know everything, constantly juggling dozens of tools, languages, libraries, and frameworks. To do this, they have to be able to quickly find the information they need in order to get back to writing software. When that information is public, all you need is a a quick search on Stack Overflow to turn up an answer and be on your way. But when that information is internal and private, it’s a maze of stale wikis and lost emails. …

12:09 PM

@Taryn schema separated -_-

@sp_BlitzErik yup

oh I see I was late to the party

@sp_BlitzErik try class="user-image"

also:

4

Q: How can I search for images I've uploaded in chat?

I'm looking to find an image I posted in Super User Root Access a few weeks ago, when I first discovered a chat-specific easter egg. I've tried searching the chat transcripts for imgur.com*, but it didn't return any results. Is there a way to view a list of images that I've uploaded in chat?

support chat search images chat-search

you are the noun

just my guess. there's probably a better way that people skilled in the dark arts of html and the chat api would know

> Stack Overflow for Teams doesn’t just provide you with the existing feature set of Stack Overflow, it also has new features that make it easier for new users and entire teams to use it, like guided onboarding for getting started

rad

> there’s a 14 day free-trial and after that it’s only $10/month for your first 10 users (additional users are $5/month)

a low, low price

well

we found paul's new blogging platform

12:29 PM

> Customer data at rest is not uniformly encrypted

I wonder what that means.

guys, forgot to ask you, did you encounter any problems regarding Query Store system tables?

5

Q: Never ending Query Store search

I'll say from the beginning that my question/problem looks similar to this previous one, but since I'm not sure if the cause or the starting info is the same, I decided to post my question with some more details. Issue at hand: at a strange hour (near the end of business day) a production inst...

sql-server-2016 statistics system-tables query-store

this is how I spent some time last week troubleshooting production

@PaulWhite some is encrypted with uniforms

@MaxVernon thank you that is very helpful

I try.

Yes, yes you do ;)

12:37 PM

i guess if you think about it, partial encryption is even stronger than full encryption

2

keeps you on your toes

is it encrypted? is it not?

can these people just not type?

ha ha ha yeah good point

user image

that seems to get bigger every time

it just looks bigger on a tiny screen

when an answer by ypercube ends up in the low quality queue

0

A: Speeding Up Query on a Time Series Table. It's Slow to get (MAX - MIN) Aggregation Value

An index on (point_id, datetime, value) will likely speed up the query.

@sp_BlitzErik That's what happens when people are bullied to convert comments to answers ;)

12:50 PM

i was assured by blog posts that bullies were a thing of the past around here

maybe we need a hash tag

bring in the big guns

user image

@ypercubeᵀᴹ Are you accusing me of bullying you?

Hi! Thank you for your suggestion with changing the data type. I have accomplished that and now the query is taking much less time. But it is still quite long. I have added the query planner debug information. Would appreciate if you could take a look at it. — ccostel 36 mins ago

What a nice person

A+ would help if i knew postgres

I updated your answer with that note. I avoid editing answers unless I'm certain the author's intent. — Dan Guzman 55 mins ago

This makes me sad

i'm even more sad that dan hasn't hit 10k

The system does a poor job of communicating how edits should work

@sp_BlitzErik Too many comments not enough answers

1:01 PM

solid commentary on the current state of dba.se

sigh

1:12 PM

Ooo exciting - network moderators are getting their own Team to share Q & A about moderation.

are non-moderators welcome to read the q&a so they can be informed about the moderation process and feel included in the greater community?

asking for a friend.

silly person shush

@PaulWhite ha, sorry, I should have uses "bullied"

hmpf

@sp_BlitzErik just become a mod

1:16 PM

easy peasy

i'd be terrible at it

how could you be worse

you probably would :P

@Taryn hey so congrats on getting Teams out the door. I assume you had a bunch of stuff to do on that (beyond creating millions of schemas).

oh now it makes sense why you were trying to do that dynamically

1:18 PM

@PaulWhite thank you

@sp_BlitzErik yup

We need to create them on the fly with permissions, etc in place

hope it worked ;)

nice to have some good news

@sp_BlitzErik sorta

There were a few issues, so we're precreating x number of them

wait

nevermind, i don't wanna know

@PaulWhite schemas or databases? They could use Access (or was it Express?) for the latter ;)

1:21 PM

@sp_BlitzErik ok, but ask if you want

@ypercubeᵀᴹ Well if they'd hired me as a consultant... ;)

was a little curious about the naming convention. i thought GUIDs for a second.

@PaulWhite once you work for free they'll never hire you

@sp_BlitzErik guids, never!

@Taryn i hope you never see a server with ssrs agent jobs

0

Q: SQL Server problems

Hi I have made a program on my home computer but I need to show it to my lecturer in college. For some reason I can run the program but as soon as it tries to open a page that had a table (datagridview) with data on it crashes and show a system error. ( Cant tell what the error was but something ...

ribbit

@sp_BlitzErik ribbit?

1:33 PM

maybe i would make a good moderator

i didn't vote to migrate that to SO

MRW I have to work on SSIS

2

@sp_BlitzErik good call

waking up at 5am means i can eat lunch at 10am right?

yes, and start drinking at midday

"start"

wink wink

1:44 PM

If Erik started drinking at midday yesterday, and never stopped, does that mean that he started drinking at midnight today? Or that he hasn't started drinking yet today?

erik started drinking in 1992

That is the only correct answer to my word problem. Well done.

3

I disagree with closing this one

0

Q: "no join predicate" warning with outer join

I have a database schema that looks something like this: create table t_order (orderid int, customerid int) create unique clustered index ix_order on t_order(orderid) create index ix_order_1 on t_order(customerid) create table t_product (productid int) create unique clustered index ix_product o...

sql-server join

The reason is slightly different than the target duplicate.

No close vote so far. Perhaps it was only flagged

by someone who hasn't reached 3k yet

@ypercubeᵀᴹ i saw that and think op could get a satisfactory answer from the other answer

but hey

i'm not trustworthy

1:51 PM

yet

@sp_BlitzErik I understand that the two questions are related and they'll get useful info from the other answer. But - I think - that some more explanation is needed in this one.

never will be at this rate

@ypercubeᵀᴹ go for it

@AndriyM Do my eyes trick me? I'm pretty sure it showed "closed as duplicate"

it must be serious, aaron messaged me on slack

@ypercubeᵀᴹ There may be some trickery but probably not to do with your eyes. I only saw the question after you linked it five minutes ago, and it didn't show as closed to me.

Still isn't

1:56 PM

@AndriyM It shows the same for me now.

Oh, I see

You reopened it and then posted the link

@AndriyM Yeah, I voted to reopen, forgot the hammer thing

I thought you encountered it in a review into which it got because someone had flagged it

@AndriyM sp_BlitzErik - ypercube. hammer fight!

1 : 1

Or fifteen all

1:59 PM

tbh that was the first time i had autoclosed a dupe

i really just wanted to cast the vote

8 sleeps until new car. Thought I was going to get it this week :(

@sp_BlitzErik I will, in the evening, if anyone else hasn't answered by then.

@AndriyM dba.stackexchange.com/posts/205704/timeline

oh sorry you already have that info

@sp_BlitzErik wait til you get modded and everything you do becomes binding

you can always add a manual "possible duplicate" or "related" comment in the hope that someone will start a close review

@PaulWhite ooh like my feet

exactly like that yes

2:11 PM

Too bad the timeline link is never accessible from the question and the revisions link isn't accessible when the post hasn't been edited.

There's probably a reason for them not to be.

Yeah it's a bug

thanks @202_accepted and @Lamak

@PaulWhite Done

I have a performance question with MSSQL Server, and I'm wondering if anyone can point me in the right direction. Basically, we have a table with 400 columns (most of which are VARCHAR), and we're thinking about using event-sourcing, and turning it into one table of four columns, where the columns are (OrderId, DateTime, PropertyName, PropertyValue). I'm wondering how this will affect the SQL server, and was curious if anyone had info on it. (These are only CRUD applications.)

(Basically, this is event sourcing.)

the ol' EAV trick

@202_accepted EAV?

dammit

eav

2:22 PM

joe celko just loaded a gun

Perfect, thanks guys. I'm trying to put together a proposal to not do this, but it's looking more and more like we're moving towards it.

@sp_BlitzErik I assume that's no bueno?

@202_accepted It has its uses I think. The point is knowing what you are getting into

i think it might be preferable to a table with 400 columns for a number of reasons

but hey

Well my alternative is to break the 400 columns down and normalize them.

sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway

2:25 PM

As an example, we store the Client Name, Address, etc. in the orders table, even though we have the FK to clients by id.

oh yeah, heh

that's preferable to EAV

@202_accepted then normalizing the data sounds like the best thing to do here

poor developers will have to write joins and all sorts of non-agile things

3

We have at least 100 columns that are covered by FK's, and another 200 that are not even used most of the time, so I think should be extracted as a 1:1 relation to a new table.

Then we have things like DateOrdered smalldatetime and DateOrdered2 varchar(30).

@202_accepted then it sounds like the choices are: "how to store the data in a contrived redundant way or normalizing"

2

2:27 PM

You know what the difference is? We format the DateOrdered as M/d/yyyy for DateOrdered2.

;)

@Lamak I offered to create a VIEW that replaces the current table, if we start using SP's and such for insertions and deletions. Then minimal changes app-side.

doesn't really sound like the best way either

seems to me like a lot of redundant data....and in this scenario I don't think that EAV makes sense, because you have that data on other tables

just make the entire database an indexed view

@Lamak I don't think it makes sense either, the problem is someone heard the "buzz word" and now we're stuck looking into it. Personally, I think splitting this table out to more logical, smaller pieces is ideal. Then make a VIEW that joins them all together, that way the developer (me) can query the view, and ignore the raw storage situation. We are storing the redundant fields here because we need them a lot, so a view should help with that while eliminating the redundancy.

2:35 PM

a view helps your app logically, but from a performance perspective it may not

@sp_BlitzErik Old fashioned and wasteful. A single json cell is more modern.

5 mins ago, by sp_BlitzErik

just make the entire database an indexed view

That might not be good. I know we're having a lot of performance issues right now, and I'm having trouble nailing it down. That table seems to be having really slow reads.

> 2018

> not storing flat files

@Lamak You just can't trust anything Erik says

2:37 PM

It's only 600,000 rows, and 1.6GB, so it shouldn't be as slow as it is.

it depends if it was said by sober erik or drunk erik

I trust the second one

Not until he reaches 20k

@202_accepted probably locking

Might also be off-row storage

@sp_BlitzErik The table is holding a shared lock, so no one can insert or update for hours.

(Literally.)

2:38 PM

400 varchar(max) columns no doubt

@PaulWhite Only two TEXT columns, all the rest have actual maximums.

@PaulWhite buncha chatterboxes

@202_accepted text...shudder

@PaulWhite Any way I can look into that to investigate further? (About to search here soon as well.)

@202_accepted yes ok (I was joking) but even non-max columns can move off-row

@202_accepted Look for ROW_OVERFLOW_DATA allocation units

2:39 PM

@PaulWhite Looking at the table, the largest non-max column is 300 chars, and there are several from 100 to 300.

@202_accepted also mind your idea about pointing modifications at a view

Msg 4405, Level 16, State 1, Line 16
View or function 'dbo.GoodLuck' is not updatable because the modification affects multiple base tables.

@sp_BlitzErik I wanted to point them at an SP.

Fun with instead of triggers

type_desc	total_pages	used_pages	data_pages
ROW_OVERFLOW_DATA	0	0	0

@PaulWhite ^^ :(

SELECT type_desc, total_pages, used_pages,data_pages
FROM sys.allocation_units
WHERE container_id IN (SELECT partition_id FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('...'))

That was the query I used.

So most of the rows contain very few populated columns?

2:43 PM

type_desc	total_pages	used_pages	data_pages
LOB_DATA	55275	55198	0
IN_ROW_DATA	151123	151100	150645
ROW_OVERFLOW_DATA	0	0	0

Looks like there's a lot in LOB_DATA, is that hurting me?

of course, maybe even physically

Well that's the text data type for you. Defaults to off-row storage.

@PaulWhite They're all populated, just most of the VARCHAR types are ''. (Because why use NULL?)

;)

@202_accepted Good grief.

2:44 PM

@PaulWhite This was legacy, when it was built. In 2009.

@PaulWhite I had but skipped it since it was AG related. I think it's answerable though now that I look harder

@PaulWhite OK, so I want to move TEXT to VARCHAR(MAX)?

@202_accepted they had nulls in that year

@billinkc Cheers!

@Lamak Yeah, but the dev didn't know how to use them.

2:44 PM

@Lamak Yeah but not SPARSE columns, right? Or did they.

@202_accepted I doubt that's the biggest cause of your problems.

but yes you probably do.

Are you using locking read committed or RCSI?

I don't know what either of those mean. We're not doing anything special with regards to SELECT, INSERT or UPDATE.

Except in one query I just modified the other day to use WITH (NOLOCK) on everything because it was blowing up our app.

RCSI is "Radical CSI", when used by @PaulWhite

@PaulWhite I just read what those are, looking now to see what we're using.

@202_accepted if you don't know, and nolock helped, it's probably read committed

> helped

2:50 PM

HE'S DELICATE

Alright, so I have "Is Read Committed Snapshot On" set to "False" in the database, is that the right option?

it's not a right or wrong thing

I meant is that the right option to be looking for...lol

it's one of them

that and snapshot_isolation

That's also false.

2:52 PM

okay, so you're using regular read committed

except where you've added nolock hints

6

Q: Switching to RCSI

The company I work for currently uses SQL Server databases (latest Enterprise version usually) for a product we develop. I would describe it as an OLTP database that is somewhat equally write and read intensive with a lot of time critical apps. In addition to this, a lot of reporting and graphic...

sql-server isolation-level metrics

brentozar.com/go/isolation

I'm reading this one right now: littlekendra.com/2016/02/18/…

> Isolation Levels in SQL Server Want to learn more about isolation levels in SQL Server? We’re here to help! Kendra Little – Videos and Articles Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide – This blog post walks you through what to consider to use optimistic locking in SQL Server. CRAIG FREEDMAN’S…

> We don’t sell this anymore, but check out our other training options.

Seems like spam

i'm only saying this because everyone gets it wrong at first: nolock hints don't mean that your query doesn't take out locks. it still will. it will only not respect locks taken out by queries that are modifying data.

that's where the beef with it is, you can end up reading data from transactions in flight

2:54 PM

@sp_BlitzErik Oh crap, so other queries will respect the lock of the one I edited?

depends, there's a whole chart of compatible and incompatible lock types

Alright, well the issue we had was that updates were blocked, when I added WITH (NOLOCK) they don't seem to be anymore, but I have no actual proof yet.

updates may still block one another -- you can't use nolock with modifications

Ok, I'm not worried about updates blocking each other at the moment (because that doesn't seem to ever happen - we only update recent stuff and all that seems to move quick).

I'm doing a query now to see if I can create a reasonable VARCHAR(#) field for those TEXT columns.

NOLOCK is so much fun for money transactions. It's almost like eventual consistency, except without the consistency.

2:59 PM

I think my problem is the TEXT columns. Running this query is going forever even on this small data-set.

« first day (2676 days earlier) ← previous day next day → last day (2185 days later) »

Transcript for

May '183

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile