@PaulWhite, I think your question, "why can't a null be equal to a null for the sake of join" has been more than adequately answered by my explanation. — jujiro48 mins ago
I didn't bother to downvote first but no. Not adequate.
Morning. Yesterday I found out that a table can only have one identity column ( and it does not yield an error if you add another column as identity, rather it replaces the existing (!) seed column to the new applied column). I think the MSSQL team could have dont better. Maybe an error or something else.
Anyway I don't see any problem why they didn't allow more than one identity column
ok , so after an insert statement , they could return all seeds or select scope_idenitty(1,2,3...) or something like that. I think it's a foolish limitation from MS.
*done better
I know I can use computed column , but that's not the point. They could allowed it.
(maybe in further version it is allowed. I'm using 2008R2)
It's after that I've added another column in production and set it to an identity , and went home. suddenly they called me about many errors. It was only then that I figure it out that a table can't have more than one.....
Well, I will never forget that limitation anymore.... that's for sure.
@RoyiNamir Try writing DDL to add a second IDENTITY column. You can't. The SSMS designer is probably scripting a DROP and CREATE behind the scenes, which is awful in its own way. You can see the script the designer will run IIRC.
Leaving that Trash room is not as easy as might seem. Every time I'm trying to do that, it asks me, "Do you want to leave Trash?" Well, of course I don't want to leave Trash (and with a capital T, too).
It's after that I've added another column in production and set it to an identity , and went home. suddenly they called me about many errors. It was only then that I figure it out that a table can't have more than one.....
I went tothe designer in SSMS and went to the new added column and right click , set it as an idenitity column and SAVE. The thing which I didn't know was that it moved (!!) the identity feature from the PK of the table
Scripts don't just let you see all relevant errors and warnings (which is already unlike designers). They also let you keep the history of changes. And of course you can use them to easily set up another copy of the same schema (provided you scripted everything and kept all the scripts, of course).
@RoyiNamir You want to get the Windows login credentials for someone connected using SQL Server Authentication? e.g. DOMAIN\UserName for someone connecting through the sa account?
I'm using sql server 2008 r2.
I logged in to ssms which in turn connects to a remote sql server machine.
im writing a query which writes a file.
I need to know - which windows permissions I should grant to c:\myfolder
is there any select query which can provide me the windows account who is a...
@PaulWhite do mods have special tools to retrieve relevant information that fast or do you just know you way around DBA.SE so good? Normal search? I'm always amazed how fast you retrieve Q&As.
Yes, but sadly without dbatools for the database part. Had to restore from backup, because I couldn't access the source server from the target server (firewall restrictions).
the only thing it didn't migrate were maintenance plans because they don't support SSIS yet, but it migrated the jobs, so there was a bit of cleanup to do
The function for getting Kerberos details is pretty useful. Also the ones for converting Powershell objects to a datatable, writing out a table and generating a schema etc. Lots of little things that I keep re-writing myself otherwise at each job
Did you know Korean has two different number systems or names? One is korean based and the other Chinese so you tell time of 9:30 as korean word for 9 and chinese for 30
I don't think using citext is enough[1]. The first question is what is an email address. Currently the most correct answer is specified in RFC5322. That spec is insanely complex[2], so much so that everything breaks it.
DOMAIN using HTML5 type=email spec
HTML5 contains a different spec for emai...
You assumed Aaron was thinking you were talking about him after you said: Definition of egoist: one who is arrogantly conceited, and who thinks everything is automatically about them.
@JackDouglas It would be really cool if there was a button or link to automatically upload the plan to paste the plan and to return that link. That sounds hard to do, so maybe if there's an easy way for the end user to copy the XML that's good enough?
Q: I’m a developer. Can I access the API directly to push plans to the web? Not yet, but that’s on our future roadmap. In the meantime, contact us with information about the project you’re working on, and we may be able to help."
@JoeObbish I was being facetious; although it doesn't come across well in "text"
@JoeObbish - I like the multiplicative CTE, by the way. Quite interesting.
although I must say, you have the commas separating the CTEs in the wrong spot. THEY SHOULD ALL BE AT THE START OF EACH LINE! (ducks the ensuing holy war)
@PaulWhite pretty much everywhere. But mostly at the start of a line when someone is indenting their code by 2 spaces. I mean thank god I can use find-and-replace with a regex to put the code back the way it should be.
@JackDouglas Funnily, I only mind for code.
I suppose I should try the "convert 4 tabs to spaces" thing, to see if I can get that to work without losing my mind.
I honestly don't care much how people format their code so long as it is readable. I do object to a mixture of tabs/spaces making the whole thing painful.
@PaulWhite that's the key for me (the readability bit). There a couple of DBAs here who insist on using a single space for indentation. For me, I can't read code like that.
@JoeObbish I'm not sure that's right, at least it isn't the way I'm thinking. My aim is to make it useful to Q&A on the site here, and that might include questions like 'why this plan?'. Of course it's never going to be a way of getting nice reproducible timings on a dedicated server, but it can still provide useful ballpark timings especially if you repeat a few times. This for example: dbfiddle.uk/…
I have two queries: SELECT col1 from dbo.table except select col1 from dbo.ref vs SELECT col1 from otherdb.dbo.table except select col1 from dbo.ref the former is snappy, 23 seconds versus killed after 20 minutes. No activity in the system, approximately same structure (second has partitioning in otherdb). Does the optimizer treat queries that go "outside" the current database differently?
@billinkc No the optimizer doesn't care which database the object is in (except for remote queries). The difference in schema (partitioning) is most likely to blame.
Apologies for the title but it's not easy to summarise what the issue i'm having is! I'm still a very beginner in SQL (literally 8 weeks of learning, and I'm completely stumped!)
I work for a law firm and have been asked to produce a report to go to the head of each department. This report will ...
I'm using sql server 2008 r2.
I logged in to ssms which in turn connects to a remote sql server machine.
im writing a query which writes a file.
I need to know - which windows permissions I should grant to c:\myfolder
is there any select query which can provide me the windows account who is a...
I don't think using citext is enough[1]. The first question is what is an email address. Currently the most correct answer is specified in RFC5322. That spec is insanely complex[2], so much so that everything breaks it.
DOMAIN using HTML5 type=email spec
HTML5 contains a different spec for emai...
