quick question for the room - I was investigating a slow insert/update and found a bunch of check constraints on a table. A number of them are just validating values in single columns. Is there any advantage to this over making a lookup table and using a FK?
nevermind, found an answer from our own @ypercube addressing it
There are two problems here essentially: 1) how to return a column value conditionally and 2) how to calculate running totals, because a running total is what you'll need to check on in #1's condition. Apparently the first one is trivial, as for the second one, it's been asked here plenty of times. In light of all that, I'm voting to close this as a duplicate of a past running total question of my choice: stackoverflow.com/questions/11310877/… — Andriy M5 mins ago
This could work better:
Where FK.DT = cast(getdate() + 1 - datepart(day, getdate()) as date)
I encountered a similar issue before and this workaround helped me. The expression in the OP appears to be evaluated as though it was a date in the 18th century!
CREATE TABLE FK
(
ID INT IDENTITY PRIM...
@JNK If you were to then query for cola = 'c' it could just give a plan with a constant scan.
@JNK The advantages of the FK approach I think is that often an application needs to show a list of allowable items and if this is stored in another table then it is much easier to retrieve. Also easy to knock up an admin screen to allow additional items to be added, also if stored in another lookup table might be more compact as just storing surrogate key in main table. Regarding efficiency I would have guessed check constraints would be quicker to evaluate though.
@ConcernedOfTunbridgeWells Is it too late to express an interest in tomorrow?
@MartinSmith I wonder if it could have anything to do with determinism of a function.
Except it's DATEADD and DATEDIFF of the OP's solution that are always deterministic, and the answerer's DATEPART isn't categorised in any way with regard to determinism. So, if there is a connection, it is not very obvious
@JNK not following. You ran the query once with actual execution plan turned on, and it ran in 24 seconds, then you ran it again, and it took 2 seconds? Was it the first time you ever ran that query (so you would pay for the compilation too)? Was the data not in cache the first time (so account for physical I/O on first run vs. logical I/O on second)?
@MartinSmith this sounds like another surprises and assumptions blog post. This time the joke is on me.
@MartinSmith it's obscure and I'd hate people to think that any time they're suffering a parameter sniffing issue, changing the syntax will work because it's probably an optimizer bug.
In most cases changing the syntax will appear to work but only because a new plan was generated for the current parameters.
Isn't PowerShell supposed to simplify things? Compare these two scripts and you tell me which is simpler:
Having a couple of hours to play I created a SQL Server 2014 box on Azure and followed This SQLServerCentral Post to create a File Group and table with T-SQL. All good. I then decided to take a look at doing it with Powershell and I cannot create the table. I think it is to do with my Index creat...
@AaronBertrand Actually, frigging stuff through object models tends to be quite verbose. You're really interacting with an API designed for use with languages like C#.
@ConcernedOfTunbridgeWells understood, but people often pitch PowerShell as this amazing thing that simplifies everything. It does not always simplify things. In some cases it makes them extraordinarily and needlessly complex.
@AaronBertrand Powershell is Microsoft's third real attempt at a scripting language for Windows. CMD files suffer from lack of features to use any system APIs and lack of the sort of scripting support from system utilities as was found in Unix. The second attempt was WSH, which allowed you to poke at COM APIs through VBScript of JScript.
Powershell was an attempt to build a scripting language with pipelining capabilities and other scripting niceties. It's certainly better than VBScript.
@ConcernedOfTunbridgeWells yes, I understand. What I don't get is why people want to shoehorn the language into every possible scenario, where often it's not the right tool.
@AaronBertrand Not terribly complex, 3 separate queries (2 inserts then an update). I didn't try to save it. It was only in SSMS, not plan explorer yet
@JNK obviously collecting a plan is not free, but that seems excessive, so I suspect there is something else going on other than "it takes 22 seconds to collect a plan."
There is still a 'databases are evil and scary' meme out in the wild among the sysadmin and development community. Some people will go to considerable lengths to avoid having to code SQL.
It is much more powerful in 2012. Why do you need to use both? Get your employer to renew the maintenance on the Red Gate tools and just switch to 2012 already.
If you just want to download SSMS 2012, you can do so here (you want SQLManagementStudio_x64_enu.exe)
You still have to step through many of the setup steps, as if you were installing a new instance. But on the feature selection page you just pick the management tools.
all -- I know this is a long shot.......
I have a query that executes a view on another server, that I wish I could "redirect", to access data on the local server... (here's the big catch) without the network bandwidth.
WHY:
A) There's a single T-SQL query hanging up one of our websites.
B) In...
Is there a someplace where the rules SQL Server uses to parse malformed dates are documented? For example, if my language's dateformat is dmy and someone writes a query featuring the date '2013/08/13', is there someplace where I can read about the rules that result in it treating 13 as the month? (I do know this is a terrible way to write dates.)
@AaronBertrand I had a go but after reading and re-reading, it sounds like there is no local SQL Server (yet) but he wants to use (a new) one sometimes to cache this data which is too slow to pull across the network. I don't think he's going to have much success with that if his app doesn't architecturally support it.
Could you please highlight the differences more obviously, instead of posting a wall of code and telling us to spot the differences? You can also leave out chunks of code that remain unchanged. I attempted one edit but it was not successful. Thanks. — Aaron Bertrand3 mins ago
For a trigger that is tracking UPDATEs to a table, two temp tables may be referenced: deleted and inserted. Is there a way to cross-reference the two w/o using an INNER JOIN on their primary key?
I am trying to maintain referential integrity without foreign keys (don't ask), so I'm using trigge...
@Aaronbertrand Is it possible for us to set up a chat? I'm just having trouble finishing this and am trying to meet a deadline. — omar K58 secs ago
Your deadline is not the community's problem.
7
After seeing the droves of people who have jobs in IT they don't deserve, I have less and less sympathy for people "trying to meet a deadline" by scraping together crap they've been spoon-fed from strangers. There's still some sympathy there, but it's dwindling away rapidly.
To add to the above: No offense, but your comments to the answers below also show a lack of understanding, time pressure, and a "givemetehcodez" feel. I'll let you in on a little secret: we all have our own jobs to do and deadlines, and as far as I know nobody is getting paid to answer questions here on SO. It's a community of people helping people, and you're honestly not making yourself a good candidate to be helped. — lc.22 mins ago
@swasheck Yeah, he and I would be having words. At this point, I need SQL Sentry Plan Explorer, the latest version of SSMS, notepad++, and Powershell Pro
I'm aware of the financial sector. My point is that some things are a good idea, but let's not be silly about it. Denying me industry standard tools because your team hasn't "vetted" them is ridiculous.
@billinkc Heh, MY team is to lazy to do that. Hell, they apparently are so busy playing on their Macs that they need me to export reports to Excel for them because they don't have time to push the button in SSRS.
Well my plan right now is to fix the "easy" stuff like the udfs in check constraints or the check constraints that act like crappier FKs
once I have the major/glaring schema issues resolved I want to start refactoring a lot of this code
the problem is they have like 10 level nested sprocs and they all have cursors and string manipulation but I am confident that if I knew the BUSINESS NEED for that outer sproc I could do it with a couple of queries and no loops
most of the cursors seem to be used to call functions and sprocs that just return data, not to make changes
@AaronBertrand yeah. this guy's purpose in life is to hate microsoft. so we pushed back because we get sick of it. basically we said, "that's a good thing, right? i'd like to make sure that my dbms enforces referential integrity since that's slightly important to the relational data model." {{ now shut up and go back and play with mongo }}
I think some shops might just add all this in DB because "well, it's just a simple constraint, easier than writing additional validation code and tying it into our existing monster of a codebase"
Check constraint is more "local" if you will - it only has to look at each row at write time and pass or fail. Filtered index has more logic, and is used for both read and write.
The idea everyone in the world seems to have is that if they ever find a spot where something isn't good for that instance that object should never ever be used again.
well, the smiley just means I'm definitely not advocating IF. and the query optimizer might have gotten better since the last time I wrote such a condition.
Is there any custom SSIS data cleaning components which can be edited and used I tried the Konesans transformation component but I dont think it works good can anyone point me to a place which has custom regex components which can be used.
I have a large data pool which needs to data cleansed fo...
Anyone have a database with at least one partitioned table, with plenty of rows in sys.dm_db_index_usage_stats? I'm trying to validate a query re-write but don't have a partitioned table handy. I could create one but my test wouldn't be very realistic.
@swasheck Are you thinking of WHERE column = COALESCE(@param, column)? It's not a pattern I personally favour, even with OPTION (RECOMPILE). Cade's option works, also benefits from OPTION (RECOMPILE), but IF is my go-to option, assuming there are not too many combinations.