The Heap™ – Consultancy ©® - 2017-02-16 (page 1 of 2)

Michael Green

12:58 AM

@MaxVernon You mean from here on your interest just gets less and less?

Max Vernon

LOL I guess I meant piqued.

Michael Green

:-)

Paul White

5:13 AM

@ypercubeᵀᴹ Not a constructive (or accurate) comment. Comments like that are frustrating: they can't be voted on or responded to (within the purpose of comments). Best to just remove it, so I did.

@JoeObbish There might well be 100 different examples though (and/or duplicates). Questions that ask for an unbounded list can be problematic for several reasons, so you'll need to think carefully about what the real question actually is, and how it can be asked within the constraints of Q & A format.

As Martin said, HJ basically comes down to there being an equality. Hash Agg requires a vector (group by) aggregate before 2016. Aside from that, the question of which applications/queries will and won't benefit from (clustered) columnstore is rather broad, and reasonably well documented in various places already.

There is an interesting question there, it just remains to be seen whether it can be made suitable for the site.

FWIW I can't think of any queries that would perform worse, necessarily, with a clustered columnstore than without. That's because CCI adds the possibility of columnstore access and batch mode execution - it doesn't take anything away (aside from a rowstore clustered index, but that can be simulated). There are other downsides to columnar storage of course, not related to querying.

Magisch

7:23 AM

Hey @PaulWhite just a heads up, there's a very suspicious user posting answer linking to a known spam website here and here. I raised a mod flag too.

It popped up on a spam detector bot so thats how I noticed it (We've had at least 18 confirmed instances of that website being used to spam in the past)

Paul White

7:55 AM

@Magisch Thanks.

@Magisch Good ol' Smokey. Nuked.

Magisch

@PaulWhite merci :)

ypercubeᵀᴹ

12:53 PM

dbareactions.com/post/155625776119/…

hot2use

1:04 PM

@ypercubeᵀᴹ Just one of those days, huh?

ypercubeᵀᴹ

@hot2use feels more like a week or month really ;)

Anwar

hi. good evening

I'm in a dilemma. I am working on a result processing software where the result for each semester is used to determine who will get promotion to the next semester. We have a list where only promoted students for a semester should appear. The problem is the calculation for the whole semester result is a bit long process. Should I store the end result in DB or use the calculation whenever I need the promoted students' list?

hot2use

@Anwar IMO I'd store the results in a history table, because one day you might have to prove that the student was allowed to be promoted.

Anwar

@hot2use Thanks. But I can prove from the entries from marks table. The problem was actually large queries. For example, I need to fetch marks for each subjects for a given Term which are also divided by mark types.

I think in that case, history table should be read-only. Right?

hot2use

1:28 PM

@Anwar I was thinking along the line of: A student can be promoted, but then an appeal results in the conclusion that the student should not have been promoted.

Anwar

OK. I got it. The proof will be there. I was concerned about the speed of the query

hot2use

You stored the relevant information in the history table along with what is required to reinforces the descision of the promotion

Depends on how often you will be running the statement.

If you'll be running it once or twice per end-of-semester then you could possibly go without the history table. If you'll be running it more and the results don't change, then you might be better off having a "Run Promotion Schedule" button and storing the data in a table for further reference.

I love this sentence: It depends.

Anwar

The result itself will be needed once or twice... But I need to maintain a student list who got promoted.

And this list will be used more frequently

hot2use

Will the query select all the data or just for a given semester? Will the queried data change? Could somebody modify the data after a promotion list has been created? It's not just about speed. You have to know what your requirements are. Speed can be optimized.

Anwar

1) For a semester only (at least for now). 2) Queried data might change (but normally after the result is published, it shouldn't).

Yes, An admin can modify the data if he wills

dezso

1:45 PM

@ypercubeᵀᴹ then do something else

read this, for example: scribd.com/document/339002050/Leslie-Ray-Charping-Obit

ypercubeᵀᴹ

@dezso quite funny

Max Vernon

2:42 PM

@PaulWhite what do you mean by simulated?

Joe Obbish

@MaxVernon Perhaps a nonclustered index with lots of include columns?

Max Vernon

@JoeObbish that was my initial thought. But, with Paul you never know.

Paul White

3:06 PM

Yes that or any index that covers everything needed by the queries that used the clustered index

Joe Obbish

3:24 PM

@PaulWhite The real question is what are some examples of queries against CCIs that require indexes to perform well (other than the obvious ones)? So far my go-to example is string aggregation. I would not ask a question like that here or anyone else, so trying to identify queries with hash join ineligibility seemed like a reasonable way to chip away at the question. It doesn't seem to fit the Q&A format here so I won't ask it. Thanks for your feedback.

McNets

Hi, can I have your advice? it is not a sql issue, it's about my professional skills.

Joe Obbish

3:41 PM

@McNets Are you asking someone in particular or the room in general?

McNets

All the room in general

@JoeObbish, all the room

Joe Obbish

I don't see why not? people seem to be helpful and friendly here

McNets

I am preparing the annual training plan.
Although my current job is IT Manager, I am still working on as a developer in some special projects, which also involves design database.

My company, despite its volume, (~ 250 employees), has no DBA team, and I would like to improve my knowledge of databases.
BI tasks are increasing lately, my first thought was to do an OLAP course , but I believe this is use a sledge-hammer to crack a nut. Maybe something related to db replication and data transformation.

Joe Obbish

3:55 PM

@McNets Technically there isn't a question there. You want to improve your knowledge of databases to accomplish what?

McNets

@JoeObbish Well that's the question, I'd like to learn about OLAP but I don't really think my company need it.

I know, I must take a decision.

It's in way over my head, and I cant't decide.

Max Vernon

4:12 PM

@McNets determining what you don't know is difficult because you don't know it (yet). I'd recommend watching an introductory video on a variety of database-related topics, such as OLAP, BI, cloud, etc, so you can get a better idea of what would be an appropriate area for further study.

Master Database

0

Q: Is There Anyway to Fix the Reputation Mechanics on Stack Overflow?

I have only tried answering questions on Stack Overflow for a few days. But I am troubled by the mechanics of the site. So far I've answered 36 questions. Of those 2 were accepted as the correct answer, 7 have received up-votes but were not accepted as the correct answer, 23 have received no vote...

discussion

McNets

Hi @MaxVernon, yes you're right, It's just I don't know DBA people, and I don't know who to ask and talk about it. And maybe, training companies are offering me more than I need, or worse, something that I don't need.

Joe Obbish

4:56 PM

@McNets Perhaps look at free resources on your own time to get a better idea as to if you should spend company resources on the topic?

ErikE

5:50 PM

Is there a way in SQL Server to add a column NOT NULL but without checking the constraint (WITH NOCHECK)?

Max Vernon

@ErikE you mean where the existing rows are allowed to be NULL ?

USE tempdb;
CREATE TABLE dbo.TestNull
(
	id INT NULL
);

INSERT INTO dbo.TestNull (id)
VALUES (NULL);

ALTER TABLE dbo.TestNull
WITH NOCHECK
ADD CONSTRAINT CK_TestNull_id
CHECK (id IS NOT NULL) ;

Subsequent inserts with NULL for id fail with:

Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the CHECK constraint "CK_TestNull_id". The conflict occurred in database "tempdb", table "dbo.TestNull", column 'id'.

ypercubeᵀᴹ

6:05 PM

@ErikE see the answer by Martin Smith:

6

Q: Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I execute the command ALTER TABLE MyTable ALTER COLUMN MyColumn BIGINT NOT NULL; the query takes fo...

sql-server-2008 null alter-table

and his comments about possible pitfalls of my solution/answer there. Which is what Max suggested above

ErikE

@MaxVernon Hmmm interesting I hadn't thought of using a CHECK constraint. Thanks!

I'm working in an environment where I have to use RedGate SQL Compare to generate change scripts to schema, and I only get to provide a "pre" script and a "post" script. In changing a column's data type from varchar to int, I ran into an issue where the final state of the column should be NOT NULL, but I need an interim period where it is nullable until I can update the data.

Master Database

0

Q: A fiddle designed for dba.se

SQLFiddle has been an essential tool and a great complement to dba.se for a very long time, but it has gone downhill a bit in the last year or two. I have also wondered for some time what a 'fiddle' designed specifically with dba.se and markdown in mind might look like, and over the last few day...

discussion

ErikE

Do you think my comments on these questions are appropriate?
http://dba.stackexchange.com/questions/164451/source-control-branching-strategy
http://dba.stackexchange.com/questions/164002/best-way-to-migrate-existing-production-database-to-new-physical-storage-structu
http://dba.stackexchange.com/questions/164066/which-sql-server-settings-should-always-be-set

Max Vernon

@ypercubeᵀᴹ I love having access to Martin's knowledge.

2

ErikE

6:20 PM

@MasterDatabase This is specifically about the person complaining about reputation mechanics

Max Vernon

@ErikE FYI, @MasterDatabase is a bot

@ErikE all those comments seem respectful, and well thought out, to me.

ErikE

@MaxVernon Ah, thanks.

@MaxVernon Appreciate the feedback.

Joe Obbish

@ErikE That operation is supposedly much faster than it used to be in some cases on SQL Server 2016

in previous versions sometimes modifying a column to be NOT NULL meant that SQL Server modified every data page in the table

Evan Carroll

0

Q: Best practice to do datbase management

I have an opinion that we should separate db management and thedashboard backend system which processes the data and provides the API. When data model is created during API development we observe that the focus is given on ease of access rather than following correct design pattern. That is one...

sql-server mysql

ypercubeᵀᴹ

7:09 PM

The only justified complaint I see in this question is the voting. Yes, there seems to be some lack of voting (up or down) in dba.se. Some people vote a lot and many people vote too few times. But I find it odd that you complain about that, having casted only 8 votes yourself ;) — ypercubeᵀᴹ 35 secs ago

2

@ErikE @MaxVernon ^^^, regarding your discussion about rep mechanics

Max Vernon

@ypercubeᵀᴹ nice observation!

I love that @AaronBertrand moved that question to Meta Stack Exchange

ypercubeᵀᴹ

7:36 PM

For fairness, I don't think your answers are all that good either. — Evan Carroll 16 mins ago

One of the rare occasions where Aaron and Evan agreed on something.

Discussion on answer by Brent Ozar: I…

Transcript for

The Heap™ – Consultancy ©®