@CadeRoux I find it pretty amazing how SE manages to connect all the dots - thanks for posting the question on CS. I don't understand the maths at all but I'm assuming if it was wrong someone else would have shot it down :-)
Exactly, if $fl(N) > N$, then $fl(R\times fl(N))$ may be larger than $RN$. — Pedro10 hours ago
Setup
I am building on @Jack's setup, firstly because that saves time (kudos to Jack) and secondly to make it easier for people to follow and compare. Tested with PostgreSQL 9.1.4.
CREATE SCHEMA x;
SET search_path = x;
CREATE TABLE lexikon (
id serial
,word text
,frequency int
...
now when I am to choose a rep amount, 100 is the lowest
As noted by Robert, this has come up recently. Posting a bounty is a perfectly reasonably thing, a good thing (with badges too!). However, when applied repeatedly to a question it does start to represent abuse.
To help minimise this, but while leaving open the option of offering a subsequent bou...
maybe someone else could do it - probably not me on this one for obvious reasons :-)
@dezso have you had a good look at the answer? It really is superb
also - as Erwin's answer is clearly more complete than mine and includes everything I did, it would be useful to switch the accepted answer to his. I may revisit mine and try and improve it at some point when I have more time but I might not get round to it...
Why don't we default to <!-- language-all: sql --> everywhere on this site - is there a good reason?
6
@JackDouglas: Thanks, added it. Certainly improves the format. Normally, SE sites pick the default syntax highlighting from the tags, AFAIK, so I didn't think of it. — Erwin Brandstetter5 mins ago
@bluefeet sure, his comment shouldn't have been directed at you anyway. You were rightfully pointing out that the query needs an ORDER BY, and he thought you were saying an ORDER BY would make it (or prevent? I'm not sure now) short circuit.
(I agree wuth blueefeet, TOP without ORDER BY is meaningless) I have no idea when an if such a short-circuit is applied but an `ORDER BY`would have prevented it, wouldn't it?
I mean: TOP without ORDER BY is like TOP with ORDER BY NULL. If you specify another ordering, then no short-circuit can be applied as the rows have to be ordered
If you add an order by on a non indexed column you will have a Top N Sort in the query plan. Also if you do a top query the table scan (or what it is using) will terminate when there are enough rows found.
@ypercube no, YEAR() is not sargable. Forces a scan.
@MikaelEriksson thanks Mikael, I really haven't done any testing here. But the short circuiting wasn't really the point of bluefeet's comment - she wasn't trying to answer the question.
In this case the short circuiting (if it happens) may not really help depending on how far into the scan it took SQL Server to find 10 rows that matched the where clause. I haven't done any real experimentation on this.
So even in cases where it does short circuit, that query can still take a long, long time.
@AaronBertrand True. You will only benefit from the short circuit if you find your rows. If you use a where clause that will give you no rows you will have a full table scan before you get nothing back.
I've got this in crontab: * * * * * root mpop -aqC /etc/mpop.conf - any linux guru know what I need to do if I want mpop to run only if another instance isn't still going?
@Phil emergency work? in the middle of the night? there was an incident a couple of years back when we wished we had someone to call. What would you charge for that sort of thing?
@Phil It would be good to have your number - not sure I'll ever need to call it, but I'm sure the client would have paid a multiple of that amount without blinking if we'd had someone to call. No need for any formal arrangement - I'll just keep your number handy just in case and make you an offer you can't refuse if the need arises :-) Would your mobile wake you up at 3am? If you are happy with that please text me your number:
@Phil i can see how that would be the case. i guess i just don't feel enough of an expert to be able to authoritatively declare something is the root cause ... or how to truly resolve it.
@swasheck stuff like writing a function that takes a table type as a parameter, to encapsulate some logic on several fields ..and then when I call the function, it inlines it and shows as such in the explain
just simple stuff but it works the way that I think it should
I mean can we have <!-- language-all: sql --> applied by default to all posts where no highlighting is specified of course.
I note this would make us different from the rest of SE, but on a site like this I think a SQL default makes sense.
The alternative is we go through and set the defa...
We were very grateful for your answer on the CLR question - there was some argument about whether it should have stayed on SO so we wanted to show we could give it a better home.
No not at all - plus my company won't pay for it:(
To be honest I am wondering whether it is worth continuing on at this point as I am doing the 2008 MCM but it retires next year so I was thinking of just doing the upgrade exams and then sit the 2012 MCM.
I only know the little I remember Martin discussing once. One of these guys might be able to give good advice if they are around: @MarkStorey-Smith, @gbn, @AaronBertrand, @ConcernedOfTunbridgeWells
@Mr.Brownstone I imagine it is expensive if you are paying your own way - do you mind me asking what your motivation is?
yes, we are tiny in comparison - but I think we have much higher quality for database contributions over here - particularly SQL Server (and I'm an Oracle guy btw)
@Phil I see you are an Oracle DBA too! I must admit my only foray into Oracle (which was a home installation) was a complete disaster, I have a lot of respect for DBA's who work with Oracle.
@Mr.Brownstone not all of us are experts ... but the quality and collective weight of the experts here dwarfs the number of people like me. this is a great place to spend some time for different reasons ... 1) if you're a gamma (like me) then you can learn from a wide variety of awesomness, 2) if you're an alpha then you have a chance to rub shoulders with people as smart as yourself
I guess the big difference is that SQL Server is presented to you using Windows-style tools from the get-go, making it appear easier
5
@swasheck you don't need to even configure it these days. DBs auto-register. It's only if you're using DHCP or need ot do something crazy that you run into problems
@swasheck thank you for the compliment, however I would never consider myself an expert. I am learning all of the time, the first thing I ever learnt was this: You learn by failing!!!
@swasheck we have all been there. Like I said in an answer that I posted, one of our data centers was flooded when we had all that rain in the UK; I spent 72 hours (no break) rebuilding servers and restoring databases. I would prefer not to do it again but at least it was an experience ;-)
not out of the woods yet. i still have a crippled cluster that is almost un-salvageable (without tremendous downtime). the decision was made to recreate it with new hardware and updated OS and SQL server edition. also, we're going to shed some of its varied workload. all good stuff (and stuff i've been wanting to do since i got here), but it's become a project unto itself and i also run the risk of failure on the primary (read: only) node
@Phil as far as I remember (it was back in 2001) I had no problem with the listener - when I did everything properly. The bigger concern was that I could not swap install CDs, the system never wanted to eject the first volume :)
@JackDouglas TPTB told me that there's no law that says i only have to work 40 hours in a week. they are also pushing for full restoration of HA (understandably so). too many things happened all at once and my head still hurts ... and it was already 2 weeks ago. but yes, we're all catching our collective breath. the real problem is that i dont have a team to whom i may farm out my service requests so that i can focus on this project
is IOPS too localized to SQL Server for this question
@Mr.Brownstone SAN migration. data moving along nicely. i moved all of my system dbs through the appropriate methods. i even did them one at a time to be extra careful. there was a problem with model.
our backup system didnt have an actual file so i couldnt restore the sysdbs from a backup because i could only start SQL in single-user mode and that user wasnt the one owned by CommVault (backup solution)
@MarkStorey-Smith I'm not sure Martin has been in here recently enough to hear you - I hope he won't mind me pinging him. @martinc @Mr.Brownstone is working towards his MCM and had a question about whether to skip from 2008 to 2012 if you have a minute?
@Mr.Brownstone Cursing the lazy arse good for nothing "programmers" that designed what would be best described as a form of reverse history/archive table to accommodate name changes. Other than that, grand cheers :)
@JackDouglas Spoke to COTW yesterday, we might try grab a beer next week time permitting
@MarkStorey-Smith the joys of being a DBA, can never say it gets boring! I had a web-admin come to me today and complain that he got an "Out of Memory" error on his web server. What that had to do with me I do not know :-)
@swasheck I am of the opinion that OS Admins should not go anywhere near anything that has a DB on it - I don't care what they say. Our company has the policy of separation of duties though and I have to fight hard to keep my OS Admin rights, unfortunately I think I am slowly losing....
Got system with Postgres broken (a RAID is the reason) , without any backups.
Trying to put data to another comptuter with Postgres (and make however backup).
But always when I set up data directory and run postgres I've got message
GET FATAL: database files are incompatible with server
2012-...
@JackDouglas I'm a certification-free zone, I'm afraid. Never done one in my entire life. In fact, I've never been on any product-specific training courses as far as I recall (although I did teach some Unix sysadmin courses around 1991 ;).
@ConcernedOfTunbridgeWells it's auto-generated and it's absurd length doesn't really harm anything fortunately. Just annoying because it's dumped to the bottom of the page in my test enviornment
takes up the full page in 1680x1050 (stupid monitor)
fortunately it's a generic select *. Anything too complex I dump into a sproc since the auto-generated ORM crap gets ugly fast if you're doing more than simple CRUD
and flat out can't do a bunch of stuff, cursors, ctes, etc
@JakeFeasel Maybe you could ask @AaronBertrand's employer SqlSentry. If you put up an ad for them on the site they might come to the party for $200/month (or more).
I saw the conversation. I will bring it up. They may only be interested in the SQL Server portion though. You know, because that's our entire customer base. :-)
@ConcernedOfTunbridgeWells I know BCP will not work with temp tables, but I have a stored proc that uses dynamic sql to insert into a table. BCP keeps throwing an error on that part of my proc
Also can it stay where it is or does it have to be moved? Will you continue to manage it or is "might be interested in hosting sql fiddle" equivalent to "might be interested in managing"?
@JakeFeasel Just kidding - they take a cut - you'd be better off running your own, like join.app.net. I mean you already have a site AND people visiting the site daily.
flattr is nice for tips, but Kickstarter has momentum, excitement, clear goals, rewards, gamification. You need a project but I bet you could bring in a lot more
@JakeFeasel But what happens after I put money in - it says something about the money being divided up amongst your clicks at the end of the month? I'd like to just send you $50. I mean, I've supported albums and projects on Kickstarter and Pledge months ago to the tune of hundreds of bucks which are still yet to ship.
@CadeRoux Wow, well flattr works by taking a pool of money that you fund, which you then distribute as micropayments to anything you want to contribute to
@JakeFeasel I use SQL fiddle every few days and it lets me answer SQL Server questions when I'm away from home office, from a Chromebook, etc. It's something I should support.
We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind clustering generally, but with partitioning included, do things change?
I agree that one sentence answers usually aren't helpful, but in this case, how would you make it better? A diatribe on how the indexing works? Guy says he already knows the mechanics, just not sure if it's highly applicable in this circumstance.
He doesn't really want to know why that's not needed.
@MarkStorey-Smith then send an email to team+dba@SE or hit @shog9 and mention it to a team member directly. And for what it's worth, in cases that I'm not sure about @Shog9 is one of three people who I ask what to do on the network.
@Mark down-vote it. When you see an answer you think is insufficient, unhelpful, or otherwise bad - down-vote it. Doesn't matter if it was posted by a new user, a moderator, or Zombie Codd - down-vote it.
You can also comment, flag, rant on meta, or ping the author in chat... But first, foremost, and if you do nothing else - down-vote it.
Setup
I am building on @Jack's setup, firstly because that saves time (kudos to Jack) and secondly to make it easier for people to follow and compare. Tested with PostgreSQL 9.1.4.
CREATE SCHEMA x;
SET search_path = x;
CREATE TABLE lexikon (
id serial
,word text
,frequency int
...
I have a fulltext index on a table. Is it possible to retrieve the set of terms used in the index (gist or gin)? With weights if possible?
I mean can we have <!-- language-all: sql --> applied by default to all posts where no highlighting is specified of course.
I note this would make us different from the rest of SE, but on a site like this I think a SQL default makes sense.
The alternative is we go through and set the defa...
What are the most commonly used terms and acronyms among database administrators and what are their correlated meanings?
Got system with Postgres broken (a RAID is the reason) , without any backups.
Trying to put data to another comptuter with Postgres (and make however backup).
But always when I set up data directory and run postgres I've got message
GET FATAL: database files are incompatible with server
2012-...
We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind clustering generally, but with partitioning included, do things change?
