Question on what actually the "Maintenance Cleanup Task" does. When the process executes I see xp_delete removing files but does the task also remove any entries from tables within MSDB?
The reason I ask is the backup appliance we use for SQL, and all other enterprise backups seems to be havin...
Another recent request pointed out an issue with the current system: answers are limited to only 30,000 characters in length.
While I personally do not feel that posting very large amounts of code in answers is particularly desirable, I do foresee other circumstances where it might be desirable...
I don't know how to get through to this guy how irrelevant and annoying his "observations" are.
One more time, the question where you were littering your comments wasn't about how to quickly generate 10,000 rows of sample data or any of the other nit-picks you were making. I strongly suggest you drop your antagonizing vendetta immediately. — Aaron Bertrand ♦41 secs ago
Congratulations, all bow to Dudu who took a generic "a CTE" comment and rammed it down my throat. Yes, you built the fastest approach to populating a new table with 10,000,000 integers. You are amazing. I will be sure to use your method in every single answer I ever post again. — Aaron Bertrand ♦26 secs ago
His original comment on this answer said something like "why sys.all_objects and not a CTE"? I replied that I had proven before that recursive CTEs, at least the kind you see in all of Itzik's posts, are not great at set generation.
But more importantly there were a bunch of nit-picks about my answer that had absolutely nothing to do with the question or the answer.
This immature nonsense is exactly why I stopped answering on Stack Overflow.
I thought I did a pretty good job here of demonstrating why you want to right-size varchar/nvarchar columns. And this clown starts attacking me for "teaching by a bad example"...
This comment is rich, given that his whole reason for attacking my answer was because populating from sys.all_objects is a couple seconds slower than his magical CTE
@MaxVernon - P.s. I don't care about differences of milliseconds but differences of a scale. — Dudu Markovitz3 mins ago
Here's another way to do it. Given this table:
CREATE TABLE #PartCosts
(
PartNo varchar(32) NOT NULL,
StartDate date NOT NULL,
EndDate date NOT NULL,
Cost decimal(18,2) NOT NULL
);
INSERT #PartCosts(PartNo, StartDate, EndDate, Cost)
VALUES('ABCD1','2014-01-...
ooooh, in my answer, I actually used the "slow" way of creating a numbers table. /slaps self.
sigh Actually, I have 101. But that's beside the point. Any moderator, anywhere on the network, is given powers to moderate chat, and they have not just the ability but the responsibility to use them. So yes, either of us is entirely qualified to exercise those powers here.
@EvanCarroll I'm aware, I just have a habit of being pedantic. Look, whether I or Thomas is a moderator or active community member here or not is irrelevant. We're chat moderators, and this is chat.
@EvanCarroll I think you should go cool off. (We also don't ban for swearing, nor would I have done so, my comment was simply that I've not seen such flags from this room before is all).
@ThomasWard I have never seen such a thing but she had a picture of the cereal box. General Mills I want to say made it. I (don't) need to follow up and see where I can get a box. FOR SCIENCE!
This accept makes me want to expand my answer a bit beyond a single sentence. I was sure @MartinSmith was going to get the glory on that one. His answer deserves it more than mine, I would say.
@swasheck Yes can talk about that. Polybase is basically a form of external table, requires highly structured files, eg.csv or my weapon of choice, pipe-separated. I like how you can point it at a directory (not just a specific file), then loading the table is just a matter of dropping new files in the directory, with same structure of course.
@swasheck but is undeniably powerful and external / Polybase tables are often a source for CTAS in my Azure SQL Data Warehouse. Pair it with partition switching and you have a recipe for fast-loads. Alternately use a "cold storage", eg rarely queried data held in flat files on blob storage, reducing overhead of store in db.
@JackDouglas Nah I understand how you didn't get that, lamak did but I also see why it could be misunderstood by people who weren't around at the time so the removal isn't disputed :)
Thought experiment - let's say you have a table with a date column of some description that you know you're going to be querying on frequently. Is it crazy talk to split out part of the date into their own columns for the purpose of indexing? So you'd have something like int EventYear, int EventMonth, DateTime EventDate. This doesn't really sit well with me but I can't tell if I'm being overly Pollyannaish.
@MaxVernon I'm still chewing it over but maybe I will. As far as calculated columns, I don't know off the top of my head if Cassandra supports them (probably but I don't know) but I didn't necessarily want to cloud the water with a specific (non)rdbms
I don't blame you. Since our team has been moving to various NoSQL databases the number of experts in any given topic is thin on the ground. And thin in the air. Really just plain thin.
In a data warehouse, the date dimension is universally broken out into parts like that. Don't know that I've seen specific indexing created to support it
Build out that table once with a value per day for the allowable range of dates and then simply reference it when you use it
So the general takeaway is that there is value to the idea, depending on circumstances. Although I think you could say that for almost any idea, with the possible exception allowing a sapient tangerine to run your country.
@PaulWhite Makes sense, My brother called his girlfriend/wife his "wife" before they were married, they were together for 10+ years so "girlfriend" started to sound ridiculous. they married later on in their lives mainly for tax/inheritance reasons for the kids
Yeah my wife and I were together for almost 9 years before we got married. It does get a little ridiculous after a while. Saying "partner" always felt weird to me.
TIGER is the Census public data. In the USA, Census.org collects all of the data, enough for us to create a FOSS geocoder with very competitive accuracy and to host it locally.
Something tells me libpostal is the way of the future.
So right now, we have three methods of address standardizations and at least one of them should be killed like soon. We have methods returns stdaddr, norm_addy, and then the libpostal system.
Question on what actually the "Maintenance Cleanup Task" does. When the process executes I see xp_delete removing files but does the task also remove any entries from tables within MSDB?
The reason I ask is the backup appliance we use for SQL, and all other enterprise backups seems to be havin...
What would be the fastest way to generate large number of rows (10M / 100M / 1G records) with a single column of integer type, preferably unique?
Here's another way to do it. Given this table:
CREATE TABLE #PartCosts
(
PartNo varchar(32) NOT NULL,
StartDate date NOT NULL,
EndDate date NOT NULL,
Cost decimal(18,2) NOT NULL
);
INSERT #PartCosts(PartNo, StartDate, EndDate, Cost)
VALUES('ABCD1','2014-01-...
please look the video which will help you to connect toad .
https://youtu.be/d7hDegNYzw0
thanks
