You were so helpful with my last question, I thought id throw another one at you thats stumping me.
Basically I got a table, requirements from company was it was all supposed to be 1 table so I got myself 1000 columns, and 100million rows (... life sucks).
Anyway they want me to generate for th...
@MarkStorey-Smith I like that too. "Your data is relaitively small" would make a good title for a blog post - perhaps I should pull finger and write some more postings for the dba.se blog.
@MarkStorey-Smith OK. I had a couple of rotten ear infections around winter 2011-2012. They were some sort of antibiotic resistant bug and took three different ear drops and two sets of oral antibiotics to get rid of.
@MarkStorey-Smith ouch, so it's like you're drunk without actually drinking (I've got a swimmer's ear problem myself). I'd say you need some rest, cause pills will take some time to take effect.
Normally when someone (typically from accounts) moans about how sensitive their data is I say something along the lines of 'Is your data more sensitive than the identities of criminal case witnesses - including the ones with name supression?'
@ConcernedOfTunbridgeWells well..we do clean data when brought to dev/qa sites (sanitize emails and phones, at least), but in production there's complete access for the prod team DBA.
@Marian The particular gig in question was a data migration project. I also did the data model for the system. However, for data warehouse projects - at least those in finance companies - reconciliation is king, so we need to get the production financials in order to produce reconciliations that actually mean something to the business.
Some stuff (e.g. credit card or banking details) tends not to be relevant to M.I. and I normally argue to keep that out of scope for a DW project as it comes with a huge amount of baggage. Keep the bank reconciliation with the accounts where it belongs, but we do need cashflow and aged debt stats.
@Marian That's pretty much it. Production data set, the whole production data set and nothing but the production data set. The first thing the business will do if you produce any sort of output is to compare it to their existing figures. You'd better have the reconciliation sorted before it gets anywhere near UAT.
Fortunately, insurance companies (where I mostly work) tend to have relatively small data sets, so this isn't really a problem - except when they insist on overengineered infrastructure for all their development environments.
I've seen one site managed by a certain international I.T. services firm where they quoted us £30,000 to roll out a development environment.
@Marian Have to really see if the I/O is up to scratch for a data warehouse. Most cloud platforms have really crap I/O performance.
TBH a high spec PC with a SSD is good enough for the few tens or hundreds of GB you typically find in this industry. Occasionally the platforms get into the TB range but that's not normally the case.
@ConcernedOfTunbridgeWells you're right, for I/O they don't rule yet. But for a small shop, instead of killing their (weak) own machines, they could roll out few machines on azure, do the testing, then clean up everything.
I know at least one small shop that would benefit this kind of roll out.. but their boss is just too lazy and unaware that some poor old laptops are not proper environments :-).
@Marian @JackDouglas mignt be rolling one out, but most of the big players are using platforms based on spinnies as far as I can tell. For a web app the I/O is fine; for anything data heavy it's not really up to scratch. I think if you want an EC2 instance with faster I/O, you can get it - at a price.
For a DW, all the high-availability stuff is a solution in search of a problem. What you really need for that type of application is cheap, fast direct-attach storage and some sort of backup strategy - maybe a HBA on the server that allows backups to the SAN).
Well, let me start by saying that you work on small data - 10ns of millions are not large. The last DWH projet I had had 400 million rows added to the fact table. PER DAY. Storage for 5 years.
The problem is hardware, partially. As large joins may use a LOT of temporary space and there is only s...
New people new environment and new expectations. No. I've not yet left for work but I figured I'd pop in and say hi. Also: I GET TO WORK WITH SQL SENTRY IN PRODUCTION AND NOT THE EMPTY PROMISES OF A PURCHASING DEPT
@AaronBertrand I think it was an auto ban, because the song had far too much s p e r m in it.
Although, I am a little surprised that that is on an autoban list. Maybe f u c k has to be said like 5 times for an autoban and then s p e r m has to be said like 10 times for an autoban.
In my experience, SqlServer sometimes gets confused when you use aliases in updates that use the FROM syntax. Try rewriting it to use the actual table names. — Klaus Byskov Pedersen3 mins ago
@JNK I can get past the initial fine. I've quit for a year or two at a time. The problem is I talk myself back into it like 6 months later because I love smoking.
@JNK nope I can't do that. I always think I can do that about a year into quitting and then I'm fucked. 1 cigarette becomes 2 and becomes a pack then it's a pack a day.
I am getting an error that says "Incorrect syntax near 'COUNT' and I can't figure out what it is. Any ideas?
SELECT COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 4 AND Active_Age='0-30' THEN P_NUMBER END) as p_number1
COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 5 AND Activ...
I am getting an error that says "Incorrect syntax near 'COUNT' and I can't figure out what it is. Any ideas?
SELECT COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 4 AND Active_Age='0-30' THEN P_NUMBER END) as p_number1
COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 5 AND Activ...
Scenario
I have 4 front-end sql server.
All of them have the same db instances. Data are replicated from back-end databases.
Issue
In SQL Management Studio I have registered the servers and I have created a group (called front_end). The group can be inquired using the sql editor opening a con...
When Episode III is back in the theaters, I'm going to sit at the front and when Anakin turns into Darth Vader, I'm going to yell: Nooooooo! I can't believe Anakin becomes Darth Vader!
Trying adding an AS
select x.TeamProjectProjectNodeName,x.TestPlanName
from (select TeamProjectProjectNodeName,TestPlanName,ResultOutcome, count(ResultOutcome) from [Tfs_Warehouse].[dbo].[TestResultView]
where TestPlanName <> 'NULL'
GROUP BY TeamProjectProjectNodeName, TestPlanName, ResultOutco...
I haven't actually got to that point in the books. But because of the Ice and Fire Wiki and tracking the differences between the show and the books, I've gotten a lot of what happens already. I really didn't think it was going to be until the last episode, but we've still got a few more deaths next week.
@Lamak I don't need two pages worth of description for what food was on the table. That is irrelevant and now I've already put down the book and made a sandwich.
@FreshPrinceOfSO It's a candid camera trick where a kid gets a stranger to help him put together a puzzle and it's a picture of that person which was quickly made into a puzzle.
@FreshPrinceOfSO I checked out that app this morning. Not sure what all the fuss is about. Presumably you can still go to regular porn web sites. Just not an app which uses the API.
It was called titsandglass. I thought that was clever.
I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly requested data.
And at the scale when you would have difficulty managing your own indexes, the costs are so much higher it would be too risky to have it done automatically
I am trying to write a small blog engine. I would love to find a sample SQL Server schema to give me some ideas but have yet to find one.
I would like to have a blog table that allows me to store more than 8000 bytes of data. Can anyone tell me if a good way to do this would with two fields lik...
@Jharwood Database engines could add their own indexes. It's a hard problem though and even harder to fit into a commercial product. I know it's been tried with SQL Server, for example, but in the end generally better results were obtained with offline tools like DTA - examining a workload and suggesting indexes, partition schemes, indexed views etc etc
You can always run the profiler to capture a workload and see what gets suggested. But the fact that a lot of what is suggested needs to be thrown out probably points to the fact that either 1) the tuning wizard needs to be a lot smarter or 2) the tuning wizard really just can never be smart enough.
@Jharwood It's a similar question to asking why execution engines don't detect when the optimizer came up with a daft plan, provide feedback to the optimizer and ask it to try again (perhaps incorporating results so far).
@FreshPrinceOfSO it may be more accurate to compare it to the little display in most newer cars that tells you how many more miles you can drive on the gas in the tank
@CadeRoux It works surprisingly well (better than most DBAs) on suitable workloads. The problem is 'suitable'. It expects reasonably relational schemas, for example.
You were so helpful with my last question, I thought id throw another one at you thats stumping me.
Basically I got a table, requirements from company was it was all supposed to be 1 table so I got myself 1000 columns, and 100million rows (... life sucks).
Anyway they want me to generate for th...
I am getting an error that says "Incorrect syntax near 'COUNT' and I can't figure out what it is. Any ideas?
SELECT COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 4 AND Active_Age='0-30' THEN P_NUMBER END) as p_number1
COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 5 AND Activ...
I made a list with but after 10 the space between my sentence and the period didn't exist on IE because on Firefox it works. thanks.
Scenario
I have 4 front-end sql server.
All of them have the same db instances. Data are replicated from back-end databases.
Issue
In SQL Management Studio I have registered the servers and I have created a group (called front_end). The group can be inquired using the sql editor opening a con...
I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly requested data.
I am trying to write a small blog engine. I would love to find a sample SQL Server schema to give me some ideas but have yet to find one.
I would like to have a blog table that allows me to store more than 8000 bytes of data. Can anyone tell me if a good way to do this would with two fields lik...
