@dezso The OP's 2nd design suggests he wants that (what you say, a composite unique constraint). The 1st design has 2 unique constraints, separately in each column. I'm only asking for clarification
@dezso And you can define multiple FKs on the same column. (but not on an expression)
The source which has date in dd-MMM-yy format , have to convert it yyyy-mm-dd hh:mm:ss:SS and insert/update into the Database using stored procedure.New to stored procedure I am managed to write a stored procedure but I dont where or how to convert the data format.
ALTER PROCEDURE [dbo].[uspInse...
The source which has date in dd-MMM-yy format, have to convert it to yyyy-mm-dd hh:mm:ss:SS and insert/update into the database using stored procedure. New to stored procedure I managed to write a stored procedure but I don't where or how to convert the date format.
ALTER PROCEDURE [dbo].[uspIn...
But the DBA.SE account is linked to a different SO account.
Not sure what could be the best course of action. I mean, our copy could just be closed. Alternatively, though, it could be migrated to SO and closed there as a duplicate.
I have a question that is probably too simple for the website, I hope this is the right place: When using Ola Hallengren's db maintenace script (https://ola.hallengren.com/), I noticed it is writing log files to a certain directory
But I found no sql command that generates these files in the scripts, and no table where the path to the files could be stored.
How is this done? What command creates these files?
I'm looking for a database refactoring tool, preferably supporting Sql Server, that allows me to do operations like the following without hand-writing sql:
Change the primary key of a table even if it's used as a foreign key. The foreign keys should change accordingly.
Factor out a string colum...
I have Apache, PHP, and Postgres all installed on one CentOS 7.3. I have a .PHP script that does not use interactive user input that successfully connects to the Postgres database. So I know PHP and Postgres work. I now want to create new .PHP files for front end web development. I want a web pa...
Should I always treat my mod close vote as binding and reserve it for extreme situations, or should I treat my close vote as "normal" when it's the fifth and final vote anyway?
Neither. It isn't a vote. Ever. Don't treat it like one.
When you see a post you think should be closed, close it....
@McNets I just like the answers from some users in DBA just too much. So I feel discouraged to answer myself. But I'm scared of losing my room owner status now, so I actually posted an answer today (almost a whole year after my last one)
@PaulWhite In some cases you can just put the UDF in the WHERE clause and it works. I'm used to that not working because we don't use SCHEMABINDING here for anything.
WITH cte (UDF_VALUE) AS
(
SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
FROM dbo.X_ONE_ROW_TABLE
GROUP BY () -- The only change
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;
A disaster when the function is schema bound.
The bottom line (in answer to the question) is you can't force it to be evaluated once, you can just hack at it and hope for the best.
@JoeObbish Exactly. There's no documented way to force the number of times a scalar is evaluated or when (aside from CASE, and that only mostly works) in an execution plan. It's one of the fundamental freedoms of the QO.
Short version
I have to add a fixed number of additional properties to each pair in an existing many-to-many join. Skipping to the diagrams below, which of Options 1-4 is the best way, in terms of advantages and disadvantages, to accomplish this by extending the Base Case? Or, is there a better ...
Option 1
*This doesn't seem like a great idea to me, because it complicates the SQL to select all properties applied to a feature…
It does not necessarily complicate query SQL (see conclusion below).
…and doesn't readily scale to more conditions…
It scales readily to more conditions,...
@McNets it was a good answer. it's the traditional answer. you got it. it wouldn't be the answer I employ w/ PostgreSQL but it's a valid way to get the job done.
Ahh, gotcha. That matches what I see. Quick answers came from his site when I was starting out, but explanations to build understanding come from other people like Aaron.
> As a Visual Studio champ, Simon contributed to fixing a few stored procedures in Microsoft Release Management and resolved critical blocking and performance issues.
I don't know why, but that sentence sounds so weird to me
@Lamak Depends on your philosophy I guess. Let's say that you think you're really sure of something. You could be eager to hear evidence to the contrary because it would be very high value to you to be proven wrong.
On the other hand, it's more likely to be a waste of your time.
@MaxVernon Correct. But then not even PASS summit is reviewed for actual content before presentation. Aside from the slide deck confirming to the rules.
@PaulWhite interesting. You'd think that would be mandatory at PASS. Also, I guess that would be a great selling point if you presenting. "peer-reviewed by Paul White" would probably go a long way.
whereas "peer reviewed by Max Vernon" might get you laughed out of the room. ;-)
@JoeObbish After Adam's talk you'll be rewriting your entire code base to use parallel nested loops, apply, unsafe clr functions with begin thread affinity, and doing crazy things with dynamic SQL. Crazy, unsafe stuff that might break with the next CU, but somehow never does, and can perform extremely well. He's also entertaining to watch.
@MaxVernon regarding your answer about the fractional seconds. So time is stored as (kind of) an integer that denotes how many 1/300ths of a second it has?
I'm at a loss to explain why that is the accuracy limit. I would guess that when sybase was developing the datetime data type 3 milliseconds was about as much accuracy as you could get from a 25Mhz processor, or some such thing.
SQLServer datetime format is stored as 8 bytes where the first four bytes are number of days since Jan 1, 1900 and the other four bytes are number of ticks since midnight. And the tick is 1/300 of the second.
I'm wondering why is that? Where is that 1/300 came from? There must be some historic r...
No, First, I added the CTAS before. I never change DDL for CTAS, and I don't think we should be doing the opposite either. Second, the CTAS I had was arguably not right as it wasn't a timestamp, but it wasn't a date either. Yours makes the question impossible. Sure it's just a typo, but why did you make the edit. The edit also mislead Erwin in his answer. lastly, your preference over tmp is a preference that I'm ok with in the future, but it breaks my answer -- we were already using foo.
(actually I'd prefer foo slightly as tmp for a non-TEMPORARY table is kind of weird).
Meta-syntactic variables abound, I don't care about the variable name though I don't think it's feature to call it temporary when it's not temporary. And, I do think we should slightly prefer CTAS as it's the standard method of creating demo-tables, but we shouldn't fight about this or pick one of the other. When you beat me to the punch, I'm glad to accept DDL.
It seems, on a quick look, that multiple people were trying to make helpful edits at roughly the same time. I have locked the post while we all agree what should be done.
Well quite, but the timings don't really matter in the end. I'm surely not the only person to take a few minutes to write an answer an not notice that the question has been edited in the meantime.
So what do you all think should happen here? @ypercubeᵀᴹ @EvanCarroll
Given that the original question gave no table name, data types, or nullability information.
I'm cool with whatever. I was wrong about both the edit having the wrong type, AND the tmp. It was in fact called tmp in the OP's query, so I'm cool with my name of foo getting renamed to tmp, in fact @ypercubeᵀᴹ did the right thing there. I don't much care about the NOT NULL dispute, between Erwin and @ypercubeᵀᴹ either. I have no idea why that matters.
The original question had column names - without types (although we could guess). The table's name was in a query but not very visible as it wasn't formatted.
Yeah, i should have probably not added the NOT NULL in the first place.
How about we revert the question to its original form, just with the code formatted. Then, the answers can make whatever assumptions they like about types, names, and nulls.
To be clear: I'm strongly of the view that all the edits here were helpful
We just need to resolve the situation given the overlaps.
The source which has date in dd-MMM-yy format, have to convert it to yyyy-mm-dd hh:mm:ss:SS and insert/update into the database using stored procedure. New to stored procedure I managed to write a stored procedure but I don't where or how to convert the date format.
ALTER PROCEDURE [dbo].[uspIn...
I'm looking for a database refactoring tool, preferably supporting Sql Server, that allows me to do operations like the following without hand-writing sql:
Change the primary key of a table even if it's used as a foreign key. The foreign keys should change accordingly.
Factor out a string colum...
Should I always treat my mod close vote as binding and reserve it for extreme situations, or should I treat my close vote as "normal" when it's the fifth and final vote anyway?
Neither. It isn't a vote. Ever. Don't treat it like one.
When you see a post you think should be closed, close it....
Option 1
*This doesn't seem like a great idea to me, because it complicates the SQL to select all properties applied to a feature…
It does not necessarily complicate query SQL (see conclusion below).
…and doesn't readily scale to more conditions…
It scales readily to more conditions,...
