The other consulting team has to deal with a dumb database design. Identity property assigned on various tables. But, if they need to create "unknown" entities, they have to do an explicit insert into the negative spectrum on the table.
Dumb, dumb, dumb
But they didn't fight hard enough so this is the bed they must lie in.
So, they are running into a permission hatefest where the DBAs have cracked down on permissions in environments and so any time they want to run their ETL, they have to have the DBAs do it since they deny us ALTER permission
I haven't found it all that valuable. Primarily because only a small subset of facets fall into the "preventable" category, and prevent is not really prevent - it's actually allow but then rollback.
So, let's say you have a policy that says index names need to start with ix_. A user creates an index named foo, it takes 12 hours to build, then PBM steps in and says, "hold up!" Rollback. 12+ hours later you're back where you started.
DDL trigger can't solve that problem either, but a DDL trigger is advertised as an after trigger. PBM "on change prevent" is extremely misleading in most cases.
In most other cases that's not even a valid option.
Sanity check: I have a non unique clustered key on an integer and I have a primary key on another integer identity column. Should I add the primary key as a second column in the clustered key or not? Using both columns in the clustered key gives 4 + 4 bytes. Using a non unique key gives 4 byte plus a 4 byte uniquifier and an extra 2 bytes because the uniquifier is variable length. 10 bytes for the non unique clustered key and 8 bytes for the two column unique clustered key plus that the primary key values will be part of every non clustered key seams like a no-brainer but I might have misse…
The table has (will soon have) about 800,000,000 rows ever increasing and there are about 1,000,000 unique values in the clustered key column and there are 4 more non clustered keys. So size matters here.
@PaulWhite Half way through the video I see that my calculations is not really accurate. There are 2 more bytes added by the uniquifier for "end of row". So I think it is time for a change here. And long due since I designed it like this from the start, 12 years ago.
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.
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...
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...
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...
