@Marian I thought we established earlier that the answer was completely wrong? And I don't see how he deserves credit for someone else's desnarkification effort.
I have setup a mysql server on unbuntu. The server hosts websites for my various clients. Hence, I want all of them(and any new clients) to have remote access to the mysql server. How do I configure mysql for the same?
I know about the GRANT ALL PRIVILEGES command, but that would require me to i...
Hello. is it possible that triggers action wont be included if a transaction ?
for example ; Im inserting into TableA. which has onInsert trigger which insert to TableB. Is it possible that transaction will "respect" Only insertion to TableA ?
one more question please. when Im writing RowNumber over Partition.... - does the numbers are being added after (!) the table has been fully scanned ? or is it added as the result are generated?
what im trying to understand if using "rownumber" is causing the yielded rows to be SCANNED AGAIN
I suppose Brendan is right and the comments related to the Singular/Plural debate can be removed?
@JNK and user44098: Please exchange emails and have your debate elsewhere or create your own site called debateexchange.com and let's keep to the topic :) — Brendan Vogt3 hours ago
@RoyiNamir There is no guarantee that a table will be scanned. But I don't think it needs to be scanned again for the row numbers to be filled.
It probably depends on the actual expression (what's inside the OVER), indexes, etc.
@RoyiNamir don't think so, either. Surely a compute scalar operator would be used, but it should use the existing table/index scan. But only @PaulWhite could give you a definitive answer.
@RoyiNamir not an identical question, but I believe @Martin's answer will help.
An insert is always within a transaction.
If you don't have an explicit BEGIN TRAN ... COMMIT or SET IMPLICIT_TRANSACTIONS ON then the statement runs as a self contained auto commit transaction.
The trigger is always part of the transaction for the action that fires the trigger. If an error occ...
@RoyiNamir In normal situations, no. The row numbers are added row-by-row to the stream by the Sequence Project iterator. If you are accessing a CTE multiple times (a common pattern with row number solutions) that could cause multiple accesses. Need to see the query & plan to give a definitive answer.
@RoyiNamir this is the logical execution order. Any implementation can do follow any order it prefers, as long it produces the same result. So, there is no answer to this.
@RoyiNamir re-read the upper comments. That sequence of steps is just for the logical processing of a query. What @PaulWhite said it's about the physical processing.
I use them a lot but i dont know to answer : " Hey , Royi , does it first got to have the rows and THEN add those rowNumber values , it hurt performance...no?"
well , I dont have answer to that. it's pretty not-professional for me to use it without knowing the +/-.
@user44098 I won't point out the irony in calling us "sensitive tulips" for your rude language and then saying "Fine, I am out of here" when you get called on it. You're certainly more than welcome to stay, but we do have rules about this sort of thing and moderators to enforce them. — JNK ♦3 mins ago
@ypercube I agree too, but at the time it was widely perceived (in here) to be snark too far. It attracted an edit and a comment, but no flags afaik, so nothing terribly bad happened.
@PaulWhite a comment is more public than an edit or flag (or anything in here). I think a lot of people are sensitive to being called out in public (I know I am)
Every step of this process is like pulling teeth. To add the ability to create SSIS packages for my existing 2014 installation, I have to download SSDT-BI and choose to install a new instance of 2014. It doesn't install a new instance of course, but if you choose "add to existing" it fails on the x86/x64 architecture check. Brillant!!!
@dezso I'd like you to know in case you don't already that while I think the comment wasn't helpful on balance, a) I am happy to accept I may be wrong, it's just my opinion, and b) you are a top moderating user on the site: I look out for your name in flags as they tend to be particularly high quality
...along with a few others in here, mentioning no names
As a comment on its own it might have been less than completely helpful. Given that it accompanied a very helpful edit, seemed ok with me. Though it could have been a comment on the edit itself, I suppose. To avoid hurting feelings ;)
You could test this using something like:
USE tempdb;
CREATE TABLE xc
(
x INT NULL
);
INSERT INTO xc (x) VALUES (CASE WHEN (RAND() * 10) < 5 THEN NULL ELSE (RAND() * 10) END);
GO 10000
SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
INNER JOIN sys.part...
I am making one pivot table from the existing table with the help of T-SQl coding. However, there are possibilities that the columns after pivoting will be more than 1024 which is existing limit for any table as of my knowledge in SQL. Moreover,I have studied that we can make up to 30000 but I do...
List right now is: Package Protection levels, Diff between package, Flat File Connection Managers PITA to create, Invalid Column Mapper in 2012 good step but very version 1.0. SSDT-BI being free to develop but not free to run outside of VS fouls people up. Useful errors in Agent Job doesn't exist in project deployment model. Reports in SSMS are handy until you try to copy elements out
@Lamak What are some of the top unhelpful error messages that made you think of that
@billinkc i think that ssis could stand to give a bit more info with the error messages. like where should i look to resolve this D_STR -> D_WSTR conversion issue. dont just tell me the problem, at which point in your guts should i make this change? reading the source? etc.
@billinkc Resolving invalid columns when you change the workflow a tiny bit hasn't improved much since the last time I opened SSIS about six years ago.
like there are still times i've had to completely delete the task and start all over again when i add a column to a destination table. the metadata just gets "stuck" at what it was when i started ---- even after refreshing. sometimes i can't re-map in a simple data flow task.
See, these are things I've grown accustomed to dealing with. In cases like that, I swap out my source query with SELECT 1 AS foo which blows all the downstream components up and then revert to my original query.
But if you've used asynchronous components, then you get the joy of fixing all that or in the case of adding new fields, having to go back in and add them
has brent always been this surly, or is he just getting grumpy in his old age?
@PaulWhite ... off the top of your head, is there any reason you can think of that would cause an otherwise sql server (save for the tests i'm running) would be demonstrably faster with cold cache and dropped buffers than with a warm cache/buffers?
Can I add a key wish list item for SSIS? I would like to see a screen where you can open and review the logging for all of the tasks at once. That way you can review a whole package and make sure logging is set up correctly for everything in the package without having to click through individual tasks.
Actually, at-a-glance views for a few other things - for example showing the container/task hierarchy and what variables and expressions live in each scope.
Maybe a better templating capability for generating SQL.
Maybe a series of high profile MVP blog posts along the theme: 'Overengineered ETL frameworks considered harmful'. *No, your wonderful SSIS package configuration/logging/data quality really isn't better than the one shipped by the vendor.*
Is there a way to extract SQL Server files (.mdf & .ldf) from a backup file (.bak) to a physical location, without restore database?
I need a restore query or script to restore files from bak file only, no attaching to server.
Please don't speak about detaching query after restore.
I mean that an answer like "No, no script or query can do that. There are specific products that can do such conversions, bla bla..." might be more appropriate than having one answer for every one of them.
@billinkc You could also tell your little bird that some of these requests come from a 6'2" tall psychopath who hasn't been brought up on GBH charges. Yet.
@ConcernedOfTunbridgeWells some of them can't differentiate between operating system, office suite, database... and some of them think Macs are just sexier hardware
@ypercube well ... GPA is an aggregation ... not sure i'd see the benefit. if it was an indexed view i think it'd require some refreshing at each change, right?
Indexed views for aggregation are only really useful if they reduce the number of rows, IMHO. If you're computing "aggregations" across columns, but still producing the same number of rows, they're not going to provide much benefit over a persisted computed column.
I should probably blog about that. Too many people think, "oh, my view's slow, I'll put an index on it" then complain that it doesn't help.
Whatever the implications on performance (I guess we'll read about that in @Aaron's blog), your idea sounds much more better than using triggers and MERGE.
@JNK yes, it is stupid, but I learned a fun trick about its use from Itzik while at Bits. Going to blog about that too.
Short version: I would never use it as a column data type or parameter type but it can resolve conversion issues when used in the ELSE of a CASE expression.
I like the Star-Trek-esque beedeebeeboop as a chat notification on http://chat.meta.stackoverflow.com/ far more so than the fart/raspberry noise that http://chat.stackexchange.com/ uses. It is much more audible over my music and occasional gastric distress.
Can StackExchange just rip off MSO (or...
The fart scares the crap out of me sometimes, and when I'm on a call it can be really annoying, because I can't control volume for the browser separately from GoToMeeting/Skype etc. One of the reasons you might see me disappear for hours at a time.
@Lamak yeah and I always remember that after it happens. I want the noise most of the time, and when I don't, I don't realize I've got a tab open somewhere
@PaulWhite unfortunately i've not found a way to capture that data given our test harness (submit a batch of jobs to a Cognos dispatcher). i have captured the queries, though so i'll check that out next.
it's very strange. across multiple tests on the same configuration (with the only difference being 5 with DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS; and 5 without) the times i run the DBCC commands (statements? expressions? i'm so confused) end up being faster.
i'm sure it's something that i'm doing incorrectly
@PaulWhite there's not a dmv that would track read-aheads, right? i can get physical reads from query_stats but there's no differentiation in read-aheads, right?
You could test this using something like:
USE tempdb;
CREATE TABLE xc
(
x INT NULL
);
INSERT INTO xc (x) VALUES (CASE WHEN (RAND() * 10) < 5 THEN NULL ELSE (RAND() * 10) END);
GO 10000
SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
INNER JOIN sys.part...
Is there a way to extract SQL Server files (.mdf & .ldf) from a backup file (.bak) to a physical location, without restore database?
I need a restore query or script to restore files from bak file only, no attaching to server.
Please don't speak about detaching query after restore.
Tools --> Options --> Designers node --> Uncheck " Prevent saving changes that require table recreation ".
