For SQL Server 2005 and 2008 R2, the only way to get the Integration Services development environment, Business Intelligence Design Studio a.k.a. BIDS, was through the installation media aka you already bought Developer, Standard or Enterprise Edition. However, if you're looking to purchase SQL S...
@PaulWhite Must be out of habit, selfie-answers on Code Review are very often posted as CW, unless the OP has posted their own thorough Code Review with explanations and all (which happens but is fairly rare)
@Phrancis Well I think it's good you took the time to share the solution with the community. Nothing wrong in getting a bit of rep (and the green checkmark) for that. Would you like the CW removed?
For a table with PK on an identity column, it will be clustered by default. Could it better be non-clustered?
The reason is other indexes will be created for queries. A query which uses a non-clustered index and returned columns are not covered by the index will use less LIO because there is no ...
@swasheck Yep. Just thought it would be nice if we could have a single point where the pros and cons are listed. If the bounty expires with lack of interest, it'll probably end up closed for good. I might answer it myself though.
@MikaelEriksson I don't think you did. It's probably the word order that adds the unnecessary ambiguity and spoils the good joke. Ideally, you would want it to read, "Canonical answer by Paul White♦ required" :)
@Vérace No, but there's really no point delaying the inevitable. I'd be as quick to correct an error or reopen.
And it's only "on hold". No answers, but comments and question edits are unaffected.
It certainly helped that your question was interesting, well formatted and clear, which puts it above the majority of first-time posts. But the social climate on dba is more cooperative than on the bigger sites even for less useful questions - which may contribute to better questions in turn... — Erwin Brandstetter17 hours ago
@AndriyM Re: this It's either a tutorial question (it which case it would be nice if he had a go), or a misunderstanding that CASE is somehow required to solve the problem. Personally, if I knew Oracle, I would just write a good way to get the required result as an answer and hope for the best.
And if the presentation is important, replace the final query with:
SELECT
U.Eid,
U.Counts
FROM
(
SELECT
[Number] = COUNT(E.Eid),
[Null] = COUNT(*) - COUNT(E.Eid)
FROM @Example AS E
) AS R
UNPIVOT
(
Counts FOR Eid IN ([Number], [Null])
) AS U;
Ugh, sorry about the wall of code. I thought it would auto-limit.
@PaulWhite I have no problem helping people learn simple techniques, but some things are too simple, to the point that I would just be reciting the manual. I just don't see much point in that.
I mean, nothing else but reciting the manual
The post in question could be either from a lazy student or from someone who can't put together the simple things they have learnt to solve a (relatively) complicated problem – that I'm perfectly fine with, hence my comment trying to establish what case that is.
@PaulWhite There was a smiley in there - and the point about delaying is well made - From Marcel Pagnol - Jean de Florettte (my favourite author - my tranlsation) - "when you've started to strangle the cat, you have to finish it!"
The 10K tools are pretty cool... You get a birds-eye view of activity on the site, a "dashboard" view of what's happening. Some of the individual tools haven't scaled particularly well with Stack Overflow's growth, but the concept behind them is still sound: we trust you to enough to be a bee wat...
@PaulWhite Yes, it's the same syntactical differences as before. Change the column alias style (to expr AS alias), remove AS from dataset aliasing – and it works in Oracle.
@swasheck I went off that Q when he said in one comment " the query I listed is the common one" and 5 minutes later he made another comment and edited the question to include more "common" cases.
@ypercube Thanks! All done. Sorry I was watching the 200th SG-1
This is more of a comment than an answer. Could you please flag it for moderators to change it into a comment. You can comment on all posts when you have enough reputation. P. S. Welcome to the forum! — Vérace2 hours ago
As far as I can tell it is an attempt to answer the question. It might not be a very good answer (I don't know), but I wouldn't covert it to a comment.
@ypercube Well I don't want to wear it out :) Besides, doesn't super-ping forcibly drag people back to their computers, even if they're out of the house, or in the shower? I couldn't do that to him. ;)
So, poor answers: Down vote, comment to ask for more info & suggest improvements, maybe even flag as VLQ if you feel an answer is that bad, but don't ask the answerer to flag for a conversion to a comment if it shouldn't be.
Why does this limit exist?
We realize that new users may have valuable comments, and that we may lose some of those contributions by requiring 50 reputation points to unlock the feature.
However, history and experience have shown that the downsides of allowing everyone to comment are far great...
(related)
So 99 times/100 if a user can't comment, and posts an answer instead I'll either (a) leave it because it is at least an attempt to (partially) answer; or (b) delete it.
@PaulWhite "Couple of things about this ^^^" Is it the obvious spam that I missed? It's so rare that I wasn't even aware of it. Will keep an eye out in future.
Why is it when you put text between angle brackets (aka > - greater than symbol and < - less than symbol) that the text disappears? I've been caught by that a few times.
This is more of a comment than an answer. Could you please flag it for moderators to change it into a comment. You can comment on all posts when you have enough reputation. P. S. Welcome to the forum! — Vérace2 hours ago
@PaulWhite I'm not sure I get you. What did I write and what was wrong with it? And no, I'm not on the phone - maybe tired - not sleeping so well. Was my error egregious?
@Vérace This. No, not egregious. As far as I can tell it is an attempt to answer the question. It might not be a very good answer (I don't know), but it shouldn't be a comment.
The rest of the reasoning is just back up the Heap a page or so. Take a look when you've had some sleep :)
@PaulWhite OK - an attempt at an answer - even short and lacking in explanation, it doesn't become a comment. If it's NAA, then I can comment about flagging. Got it.
@PaulWhite "If it turns out he's asking the question while in an interview I will scream.". dba.se's (and StackOverflow's) reputation has travelled far and wide. I'm a mature student and we were specifically told not to use these sites even in open-book exams! :-)
@PaulWhite Maybe I didn't express myself well. I mean, there's nothing stopping us using them day to day - just that for exams, they're not allowed, even for open-book exams. Kinda makes sense - just as IM isn't allowed or email. You are allowed Google though - so if Google leads one to dba.se - I think it's more the communication with 3rd parties during exams that they're on about, so you could read dba.se, but just not post during tests.
@PaulWhite Do you find it odd that the optimizer knows how to simplify things like P.BusinessEntityID >= 1 AND (P.BusinessEntityID <= 64 OR P.BusinessEntityID < 20 OR P.BusinessEntityID < 5) to a single range predicate, but (apparently) doesn't do the same with something like WHERE P.BusinessEntityID IN (1,2,3,n,..,64)?
One is domain simplification over a complete range, the other would require knowledge of discreteness as well. TF 2301 enabled some trickery in that area, but only for statistics, not q.o. per se.
@PaulWhite - quick question; is it correct to state that rows in a heap that are deleted or truncated are not returned as free space as efficiently as rows that are removed from a clustered index?
@MaxVernon The main difference I am aware of is that empty page deallocation requires a TABLOCK hint (or equivalent) for DELETE, and even then it's not guaranteed in all circumstances. TRUNCATE TABLE does not suffer from the same issue IIRC.
we have a couple hundred staging tables that are heaps. We're seeing that space is not being freed in the database resulting in very large database sizes. Another DBA here is thinking converting those staging table heaps into clustered indexes would resolve that particular issue. So, we converted all those staging heaps into clustered indexes with what I thought was a pretty good key - QA is reporting load times are 3 times greater.
(all this happens in the test environment, of course)
@PaulWhite Thanks. Yes, I see your point now. I guess there would have to be some additional (and potentially tricky) reasoning about types there. And it might not be worth it.
@JNK So yeah I'm not going to bore you by listing every design I prefer, but the Kyle Lockwoood Silver Fern ones are very good; Land of the Long White Cloud by Mike Archer, Embrace by Denise Fung, and finally Manawa by Otis Frizzell.
I have recently added a SQL Server JOB on SERVER-A that goes to SERVER-B and backups all the SSAS databases.
This seems to be fine.
However when I want to delete backups older than 3 days, using the function below, I get the error message on the picture below.
It says that the path is wrong,...
-- This print raises an error:
-- Conversion failed when converting the varchar value 'An error was raised\nSEVERITY: ' to data type int.
PRINT 'An error was raised'
+ '\nSEVERITY: ' + @ErrSeverity
+ '\nSTATE: ' + @ErrState
+ '\nMESSAGE: ' + @ErrMsg
;
@PaulWhite Coming as I do from a country where flags (and displays of same) can arouse (ahem...) considerable controversy, I do like the koru - is it relatively neutral? Personally, I'd prefer a different design for my own country's flag - rather than just the standard three blocks of colour.
@MaxVernon I have not yet, still working on the specific quirks first for data validation before I start deciding how to propagate this query onto multiple servers
The phpMyAdmin support page points to stackoverflow.com and serverfault.com as the sole (non-bug related) support mechanism:
Getting support
Effective September 1st, 2013, support for questions directly related to phpMyAdmin has moved to stackoverflow. All prior support mechanisms have ...
@PaulWhite Would these "somebodys" be diehard "God, Queen, motherland (i..e. England) and only then country (i.e. NZ)"? The sort of person would regard the word "Aotearoa" as offensive?
In the short time that I've been a member of the community I've noticed that the OP sometimes doesn't tag their question with the RDBMS (go figure :)). Community members often ask for this information in the comments and/or the OP leaks the data via their question or comments. I have edited a q...
@PaulWhite Just like the web - if there's a perversion that you can even imagine in your most horrible nightmare, there's probably a website for devotees of the act in question. If you want a good laugh on the flag issue, try the "discussion" here.
I have an existing database with everything in schema dbo. I have an SSDT project with objects I am adding to it with schema foo
I have a table that looks like this in the project:
CREATE table foo.a (
id INT NOT NULL
CONSTRAINT [PK_foo_a] PRIMARY KEY CLUSTERED
CONSTRAINT [F...
Quick question on best practices - I'm refactoring some ad-hoc queries that have things like this...
select *
from supply_orders with(nolock)
Join CLIENT_EPISODES with(nolock) On epi_id = so_epiid
My initial thought was to just set isolation to read uncommitted (that's the same right?) since all tables are marked that way in the from clauses, and remove the (nolock) from the tables themselves
The MSDN online article "Snapshot Isolation in SQL Server" states:
"An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a conn...
I believe that is fixed now. Probably not in older versions. It's best practice to set isolation level explicitly at connection time anyway <-- all AFAIK
@swasheck Do you know what version of Purity OS you were running?
We found a nasty deadlock condition bug on large reads over iSCSI during our POC kit tests, but they fixed it last year and it was supposedly an iSCSI-only problem.
@PaulWhite Congratulations on getting the moderator position, may this be an opportunity for personal growth for you. I am sure you will do a great job. Cheers!
I read something at the beginning of the year saying they only had about 1500 installations at the time. So we are probably all testing their stuff for them to some extent.
Why does this limit exist?
We realize that new users may have valuable comments, and that we may lose some of those contributions by requiring 50 reputation points to unlock the feature.
However, history and experience have shown that the downsides of allowing everyone to comment are far great...
The role you created is password protected. Therefore it is not enabled by default, when alice logs in.
I have recently added a SQL Server JOB on SERVER-A that goes to SERVER-B and backups all the SSAS databases.
This seems to be fine.
However when I want to delete backups older than 3 days, using the function below, I get the error message on the picture below.
It says that the path is wrong,...
The phpMyAdmin support page points to stackoverflow.com and serverfault.com as the sole (non-bug related) support mechanism:
Getting support
Effective September 1st, 2013, support for questions directly related to phpMyAdmin has moved to stackoverflow. All prior support mechanisms have ...
In the short time that I've been a member of the community I've noticed that the OP sometimes doesn't tag their question with the RDBMS (go figure :)). Community members often ask for this information in the comments and/or the OP leaks the data via their question or comments. I have edited a q...
I have an existing database with everything in schema dbo. I have an SSDT project with objects I am adding to it with schema foo
I have a table that looks like this in the project:
CREATE table foo.a (
id INT NOT NULL
CONSTRAINT [PK_foo_a] PRIMARY KEY CLUSTERED
CONSTRAINT [F...
The MSDN online article "Snapshot Isolation in SQL Server" states:
"An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a conn...
