April 23, 2009 at 9:55 am
GilaMonster (4/23/2009)
Alvin Ramard (4/23/2009)
Can't take the worst the postings from here as examples. Presentations have to be believable after all. 😛
So you're saying the bad post from here are unbelievable?
I'd agree.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 23, 2009 at 10:10 am
Greg Edwards (4/23/2009)
Bob Hovious (4/23/2009)
My topic would more likely be... Laughable SQLWhy isn't there an SQL equivalent to Iron Chef? That would make for an interesting competition. Instead of a mystery basket of ingredients, the competitors get an inherited database with really bizarre schema, and equally bizarre requirements... then given an hour to code. Judges would rate the solutions on speed, flexibility, innovation, etc.
That sounds like a good 'hands on' interviewing technique.:-D
Book smart or real life expreience would show pretty quickly.
Greg E
Like this? (Attached)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 10:35 am
Bob Hovious (4/23/2009)
Where would one look for examples of bad SQL?
I can think of a poster child. I'll bet Lynn can too.
Bob, how about: SQL 2008, the sequel?
Are we about to go back to movie titles? 😀
Actually, I think we could come up with several.
April 23, 2009 at 10:55 am
I see a Model of consistency - varchar(100).
Oh wait - now I can see why they did this.
It's flexible - you can put anything in a column.:hehe:
And I can see even with a only a few records, this is used.
So for the business requirements, what did we sell and how many might be the first one.
That wouldn't seem impossible.
Greg E
April 23, 2009 at 11:02 am
There's more to it than might meet the eye at a glance. There are the obvious violations of normal form, data typing, etc., but how many will even check, much less notice, that it's set to auto-shrink, or that the statistics are not going to auto-update? But wait! There's more! ... 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 11:15 am
Default autogrow on data and log
No indexes or constraints on Products. The cluster on Main is going to fragment quite badly, depending how often the inserts/updates are.
Product price is inconsistent in format, and is a string column. That'll make sums fun.
Badly unnormalised.
Non-date values in a 'date' column.
No users or roles, so I guess everyone is sysadmin
Oh, and a couple violations of the laws of physics
On the plus side, the page verification is correct. Something at least.
Did you make this up to torment potential employees?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 11:34 am
I made it up as a conversation-starter for interview situations. Hand someone a laptop with this on it, ask them what they would do. Make it clear that it's a test, not a real database.
If the person says, "Oh, okay, I see what you're doing here. That's pretty clever!", that's all I need to know.
If they notice the denormalization and the fact that it's all varchar(100) and suggest normalizing it to 3NF and fixing some data types, and moving "Not in Stock" to something other than the "ShipDate" column, but nothing else, that says they're probably at least up to a minimum standard.
If they dig in a bit, and mention that it probably shouldn't have auto-shrink turned on, that there is no primary key or clustered index on Products, that the clustered index on Main is badly designed (but a moot point anyway since Main should be split into multiple tables and normalized), question why it's in Simple recovery, note that the initial file size is 2 Meg and it's set on 10% auto-growth and the log file has a similar problem, and that Auto Update Stats is turned off, or that the MainProducts view is just plain junk by any normal standard, or any subset of those, then that says something else entirely. If they also note that there are no procs and ask about how the database is supposed to be accessed, that's also worth noting. And, as Gail noted, no users/roles, so either everyone is sysadmin, or nobody can access the database at all.
The data issues, including non-date data in a column that looks like it should be datetime, the salespeople being referenced only by first name, the address being all one field, the phone numbers having no reference to area code, the fact that OrderNumber has no constraints on it at all and allows duplicates, and the fact that the prices in Products don't necessarily add up to the total price in Main, are also all there for the testing.
The violations of the laws of physics are just for fun. So is the fact that someone appears to have actually ordered "Bait and Switch". Or maybe these are brand names for a new type of energy drink? (Come on, you could easily sell an energy drink called "Perpetual Motion" on ThinkGeek.com! 1000mg of caffein and a kilogram of sugar disolved in a half litre of water, with purple coloring and fake "mixed berry" flavor! Add some vitamin B12 and you wouldn't be able to keep it in stock!) Again, though, if anyone looks at it and starts making jokes about violations of the laws of physics, and notes that apparently these aren't in stock, they're probably worth having as a co-worker, as opposed to the person who starts talking about lawsuits for false advertising and takes it at all seriously. Another test, just not a technical one.
Anyone at all familiar with relational databases can spot the major situations at a glance. But being blinded by the obvious and missing the details says something about how someone will work. And the ability to spot what's not there (missing PK, missing clustered index, missing procs, missing security) is much more rare, and very valuable.
And now, of course, I've ruined the fun of anyone else who wants to dig into that particular test database. I have another, with similar levels of issues, but it starts out with a "one-true-lookup" table as the core of the database, instead of these violations of 1NF. Want a look at that one?
The whole point of either is to get an idea of how the person looks at a database. It's a conversation starter. And much more useful in finding out how a person thinks than questions about "you have a roomful of people, and two have different colored hats than the other ones" or silliness like that.
Edit: Fixed a typo.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 1:41 pm
That's an interesting technique. With VMWare/Virtual PC, you could easily set one of these up. Hmmm, I think I see an editorial here.
April 23, 2009 at 1:49 pm
Steve Jones - Editor (4/23/2009)
That's an interesting technique. With VMWare/Virtual PC, you could easily set one of these up. Hmmm, I think I see an editorial here.
If you're talking about my Test1 database, if you aren't just using the database (like I did here), but also have the server settings, etc., you can also set up a test that includes things like overkill/underkill maintenance plans, yearly full backups and log backups every 10 minutes, data files on partitions that are too small for any growth, and so on.
Different tests for admin vs dev vs architect skill sets.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 1:57 pm
I love these questions.
http://www.sqlservercentral.com/Forums/Topic703533-24-1.aspx?Update=1
Gives me a chance to get multiple posts in :w00t:
April 23, 2009 at 1:57 pm
GSquared, actually the whole technique is great. Give them a server in a VM, what's wrong?
A good way to see how people go about their jobs.
April 23, 2009 at 2:08 pm
Steve Jones - Editor (4/23/2009)
I love these questions.http://www.sqlservercentral.com/Forums/Topic703533-24-1.aspx?Update=1
Gives me a chance to get multiple posts in :w00t:
Yeah. Like you, of all people, need to pad your posting stats. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 2:11 pm
Normalisation?
http://www.sqlservercentral.com/Forums/Topic703573-149-1.aspx
It's all yours. I'm headed for bed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 2:25 pm
Steve Jones - Editor (4/23/2009)
GSquared, actually the whole technique is great. Give them a server in a VM, what's wrong?A good way to see how people go about their jobs.
Of course, it's not just "what's wrong?" The more important thing is how would they prioritize the issues, and how would they go about fixing them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 2:30 pm
I agree. I think it's an outstanding technique. You could watch them go through things, or leave them in a room, give them an hour, have them make notes and repeat back to you what was important and why.
Viewing 15 posts - 3,646 through 3,660 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply