@AarolamaBluenk, please don't leave useless comments. If you aren't willing to actually improve the answer, then you don't have a right to say anything at all. — Jonathan Allen5 mins ago
So, the question arises how SQL Server handles batch update in case of
the primary key data changes in the table.
SQL Server builds a query plan for the update statement that update both tables.
Create the tables:
create table T1
(
T1ID int primary key
);
create table T2
(
T2ID int ...
His problem though is correlating entries in inserted with deleted when the primary key changes in an INSTEAD OF UPDATE trigger. There's no reliable way to do that I'm aware of.
@PaulWhite I suggested in a comment that he could add a surrogate key. Perhaps a unique identity column that can be used to join inserted and deleted in the trigger.
@dezso It's a new user failing to leave a comment because he can't. How relevant it is I don't know, depends on what the Q means by "administrator account".
The database that backs our software product has a table design like the following:
Positions
ID Name Parts
1 One 12345
2 Two 12346
3 Three 12347
Collections
ID TableID Collection
12345 1;1 1;2
12346 1;1 3;4
12347 1;2;2 5;1;2
Parts
ID Na...
Thanks, I see I provoked you into going into even greater depth :) are you interested in a little project to help out Jake Feasel and SQLFiddle? He's currently creating a new database for each pg fiddle and I think creating a schema might be sufficient (among other thoughts)? If you have the time/inclination please ping me in The Heap — Jack Douglas48 secs ago
@dezso I thought you might be interested in this conversation too: I've invited Erwin in here...
@PaulWhite I agree it is a shame, but I usually think any improvement is better than none. There isn't a really obvious way to feed back to the editor encouraging them to go one step further: superpinging from in here is probably the only option (and I'm happy to do that on request)
@PaulWhite I mean that I am in the habit of retagging - however, if I see other points to be improved, I do that. Sometimes I forget the title, but usually go back and edit that as well.
@PaulWhite I think you could find me guilty doing this kind of thing (on SO), although I don't really understand the issue. Could you give an example? I'd really like to be more mindful next time about whether my re-tagging makes sense.
@AndriyM Example: removing a tag like sql when the question needs obvious improvements like code formatting/spellcheck... If I'm going to edit, I try to add as much value as I can in one go. Substantive being the operative word, I guess.
2
@dezso I have no problem with the tag edit, there's just some small tidy ups to do in the main text as well. Like I said, not a great example, just coincident timing.
@PaulWhite I see now, thanks. Retagging can be done separately from editing the body & title. Missing tags are easy to catch. And sometimes they are the only thing I do catch, simply because I may have not time to take a closer look at the body.
@dezso it is, and you fixed the blatant abuse of sql too in that case.
@PaulWhite something you may not have considered: fixing the tags makes the question visible to the users who only look at the new tag, who may be more qualified to fix the rest?
On SO when my rep was below 2,000, I would use the retag link. Retag is great because it's like a quick edit.
But now that I am over 2,000 I have full edit, not no retag link. So questions I come across don't need any edits, just the tags corrected. But now, to correct a tag, I need to change ...
As Jeff said on the blog,
With inline tagging, you can tag more stuff, faster. This is somewhat dangerous, so we prefer that more experienced users have access to it.
Inline tagging pretty much lacks any resistance; you can retag questions so easily and without any extra thinking that it's ...
@JackDouglas I like to think I'm being fair about it (and to stress again: dezso's example was not the one that prompted my vent). The "lazy retags" I am objecting to are by users with edit rights where the post is obviously in major and immediate need of improvement. Not talking deep technical stuff: grammar, spelling, links, easy stuff like that.
--- Update ---
Thanks for the comments and the help so far. And I apologize for not specifying the question further. I have updated the question below.
--- Update ---
Currently I am asked to develop a database structure for a pretty big amount of data. I am debating between implementing a graph...
I admit, this stumped me for a moment today. Was debugging a really nasty, hack fest, dirty TSQL, horror of a stored procedure and found something that logically looks like this:
Jake an SQLfiddle are first class citizens in my world. No time today, but I'll come back to that tomorrow or so and see what I can do to help. — Erwin Brandstetter38 mins ago
You can use information_schema.columns or something similar for your database engine.
For MySQL look at http://dev.mysql.com/doc/refman/5.0/en/columns-table.html
For example look at Select a dynamic set of columns from a table and get the sum for each
@dezso for your answer here could you use a query link this fiddle? sqlfiddle.com/#!1/17657/6 I was going to post it before you added your answer but your answer threw me
I have this table, Lineas (id, date, proyect_id, hours) and I want to select a sum of hours by date into a week. For example I need this output for dates between 01/01/2013 and 05/01/2013:
Proyect | 01/01/2013 | 02/01/2013 | 03/01/2013 | 04/01/2013 | 05/01/2013
Proyect1 | - | 5 ...
You provided absolutely no details about your current table structure or data but you can use a Y/N flag in your pivot. Here is a sample solution:
select employeename,
isnull(hr, 'N') HR,
isnull(IT, 'N') IT
from
(
select e.name EmployeeName,
d.name DeptName,
'Y' flag
from employ...
I am trying to install RLWrap into my mac os x (Snow Leopard), I have XCode and am able to run the ./configure command sucessfuly, however when I try the make command I get the following error:
make all-recursive Making all in doc make[2]: Nothing to be done for
`all'. Making all in src gc...
@swasheck people mix together the concept of a clustered index and a primary key. I think this is due to the fact that many tables place their primary key on their clustered index.
@JNK So you've created the AD group as a login for the SQL Server. You will need to create it as a user within the database and then assign it to the role.
That's odd. If you've created the user in the database, it should have connect access to the database. Are you able to connect to the database as the user using SSMS?
@JNK Sorry, I can't think of much else off the top of my head. You are able to at least connect to the instance using the proxy login, right? Just can't use the database?
I've always seen and written my column aliases as
SELECT 1 as ColumnName
but today came across a query that used
SELECT ColumnName = 1
Is there any difference in how these two queries get executed? Or is there a standard among DBAs about which one to use?
Personally I think the 2nd would b...
@JNK Hrmmmm....I was under the impression that by creating the user in the database, they got DB CONNECT by default. It makes sense that granting it would get it to work, meaning that it didn't have it before.
@AaronBertrand That was me :) I didn't upvote your answer because it didn't address my question about if there was a difference between the two - it only stated your preference
@JNK well you create a login at the server level, then create a user in the DB that's associated with that login. I will confess I have been able to escape AD groups my entire career, but there are certainly some strange limitations with those. If granting connect explicitly makes it work, why not just grant connect explicitly?
@Rachel I guess you skipped over the sentence "There is no true advantage to using one or the other except for subjective / taste reasons."
@JNK the same reason there are 50,000 other inconsistencies in SQL Server - AD groups just didn't get the full attention they deserved - they are still fixing holes there.
@AarolamaBluenk I wasn't fishing for up-votes - just found it funny that a comment containing a small part of my answer got an upvote while my answer did not.
Hii,
I have a .net application which is connecting to the SQL Server using windows authentication.
We cannot use SQL Server authentication in the application. We have lot of Active Directory users there for our project. So we have to create separate login account for each Active Directory user...
@AaronBertrand Sorry, I did read your answer along with the other one, but when I re-read it to see if it actually addressed my original question I skimmed over that paragraph and missed that sentence :) I can always add an upvote later, but I can't always remove one
@Rachel no worries, if you hadn't up-voted Thomas' "me too" comment, or if my answer already had up-votes, I would never have noticed. But with those two criteria (and then you saying that my answer still didn't actually address your question) it seemed quite weird to me. I feel like if I have to draw special attention to particular sentences within an answer because you don't see them, I'm not going to bother next time.
So, the question arises how SQL Server handles batch update in case of
the primary key data changes in the table.
SQL Server builds a query plan for the update statement that update both tables.
Create the tables:
create table T1
(
T1ID int primary key
);
create table T2
(
T2ID int ...
add user permission as "sysadmin" from mssql server
The database that backs our software product has a table design like the following:
Positions
ID Name Parts
1 One 12345
2 Two 12346
3 Three 12347
Collections
ID TableID Collection
12345 1;1 1;2
12346 1;1 3;4
12347 1;2;2 5;1;2
Parts
ID Na...
On SO when my rep was below 2,000, I would use the retag link. Retag is great because it's like a quick edit.
But now that I am over 2,000 I have full edit, not no retag link. So questions I come across don't need any edits, just the tags corrected. But now, to correct a tag, I need to change ...
As Jeff said on the blog,
With inline tagging, you can tag more stuff, faster. This is somewhat dangerous, so we prefer that more experienced users have access to it.
Inline tagging pretty much lacks any resistance; you can retag questions so easily and without any extra thinking that it's ...
--- Update ---
Thanks for the comments and the help so far. And I apologize for not specifying the question further. I have updated the question below.
--- Update ---
Currently I am asked to develop a database structure for a pretty big amount of data. I am debating between implementing a graph...
You can use information_schema.columns or something similar for your database engine.
For MySQL look at http://dev.mysql.com/doc/refman/5.0/en/columns-table.html
For example look at Select a dynamic set of columns from a table and get the sum for each
I have this table, Lineas (id, date, proyect_id, hours) and I want to select a sum of hours by date into a week. For example I need this output for dates between 01/01/2013 and 05/01/2013:
Proyect | 01/01/2013 | 02/01/2013 | 03/01/2013 | 04/01/2013 | 05/01/2013
Proyect1 | - | 5 ...
You provided absolutely no details about your current table structure or data but you can use a Y/N flag in your pivot. Here is a sample solution:
select employeename,
isnull(hr, 'N') HR,
isnull(IT, 'N') IT
from
(
select e.name EmployeeName,
d.name DeptName,
'Y' flag
from employ...
I am trying to install RLWrap into my mac os x (Snow Leopard), I have XCode and am able to run the ./configure command sucessfuly, however when I try the make command I get the following error:
make all-recursive Making all in doc make[2]: Nothing to be done for
`all'. Making all in src gc...
I've always seen and written my column aliases as
SELECT 1 as ColumnName
but today came across a query that used
SELECT ColumnName = 1
Is there any difference in how these two queries get executed? Or is there a standard among DBAs about which one to use?
Personally I think the 2nd would b...
Hii,
I have a .net application which is connecting to the SQL Server using windows authentication.
We cannot use SQL Server authentication in the application. We have lot of Active Directory users there for our project. So we have to create separate login account for each Active Directory user...
