I'd hand them a computer with a badly designed local database on it and ask them to identify some problems with it.
Make it something with 3 or 4 tables, maybe a customers table and a contacts table and an orders table. Name the tables Table01, Table02, Table03, give the columns similar names (Col01, etc.), use a trigger on the orders table to make sure the customer ID is in Table01 in Col00, instead of a foreign key. Have dozens of triggers instead of check constraints. Have all the columns be Char(100). Maybe 10 or 15 rows of data per table. Have a blank password for the sa login. Have a stored procedure that has a char(8000) input parameter, which uses "exec (@param)" as the only line of code in the proc; name the proc MainProc.
If the applicant takes one look at the database, shudders in horror and asks nervously if this is what your real databases look like, odds are good that he's going to be okay as a DBA. If he instead says something like, "Well, you should add some indexes so it's faster", politely tell him you'll call him and that he shouldn't call you.
Come up with a few variations, one of which violates every standard practice you can think of, one which is merely bad without being an abomination, another that has some simple-to-fix-but-obvious problems, and another which is a little bit tricky to spot the problems in and which generally follows best practices (maybe index too many columns per table and otherwise have a good database). If you take the time to weed people out based on which scenarios they can spot and handle, you'll do well. Again, keep the databases simple, with only a few tables and a couple of stored procedures/views/UDFs, so they can be analyzed quickly and easily.
Asking simple questions like a list of database objects is okay if what you're looking for is someone who skimmed through SQL for Dummies and memorized a few basics, but doesn't know Varchar(100) from Text. Having a laptop with some messed up databases on it as a test is much more thorough and will also help you spot the guys who, like many in IT, aren't excellent at being interviewed, but who actually know what they're doing.
Discussion on:
View:
Show:
This article was only in regards to the sit-down portion of the interview. If you design a test for them (which you definetly should), incorporate the questions you mentioned above into that portion. I agree with you...NEVER hire someone just based on asking them technical questions.
Backup and recovery strategies
Strategies for performance optimistation both in SQL and in terms of the low level file organisation.
Security considerations.
Most imporantly you can't BS them without exposing yourself badly.
Communication wise I'll help people out at interviews by restating the question, if I think I've communicated badly. I don't expect jargon and exact syntax, but I do expect the concepts and they should be able to get across their understanding with examples, if nothing else.
To be honest, I'd rather have someone less techy and more talky.
The key thing when looking for a dba is to distinguish between someone who's familiar with SQl and some one who can design and maintain a database.
It's amazing how many sql 'experts' there are out there who write subqueries instead of joins, like two pass sorts, think unions are bad things to join that will annoy management ans alias is a tv show with a nice looking bint in it.
Strategies for performance optimistation both in SQL and in terms of the low level file organisation.
Security considerations.
Most imporantly you can't BS them without exposing yourself badly.
Communication wise I'll help people out at interviews by restating the question, if I think I've communicated badly. I don't expect jargon and exact syntax, but I do expect the concepts and they should be able to get across their understanding with examples, if nothing else.
To be honest, I'd rather have someone less techy and more talky.
The key thing when looking for a dba is to distinguish between someone who's familiar with SQl and some one who can design and maintain a database.
It's amazing how many sql 'experts' there are out there who write subqueries instead of joins, like two pass sorts, think unions are bad things to join that will annoy management ans alias is a tv show with a nice looking bint in it.
The article you're commenting about is regarding questions to ask potential database developers, not potential DBAs (that will likely be my next article). Also, if you notice at the end of this article, I mention that if the candidate performs well I will ask them to take my programming test, which is essentially what you are talking about here.
In addition, they must understand how to set up a relational database. And this is not only for architects. Anyone doing this work must understand basic design strategies. In smaller companies, you will find yourself as the architect, dba and developer. They must understand why they might consider using an index and what to watch out for in over use of indexes. I've seen this happen a lot, where data manipulation is made to tables without thought to unique values and proper indexing. This alone can bring your database to a halt. Basically, columns you join on or use in your 'where' clause, generally, should be indexed. It's through using indexes one sees the value!!!!
sure, you should weed out people who don't have a clue so you don't spend money or waste time figuring out if they fit. But there are commercial IT testing places, like brainbench. They do most of their biz with employers looking to verify skills. As an applicant you can also take their tests to see how you do in an area.
I would expect questions about what is an inner join, outer join , and the efficiency related to them
particularly in multi user scenarios, if I was limited to one question, it would be
What is a transaction ?
For senior developer, what is transisolation.
What is a transaction ?
For senior developer, what is transisolation.
Excellent questions but should add a couple others:
Any developer above entry level should be able to handle cursors blindfolded. What code elements are required to utilize a generic boiler plate cursor loop?
In a stored procedure based back end, how would you provide the identity of an inserted row to the front end or mid tier application?
Any complicated application will require the use of temp tables. What are temp tables, why are they used, how are they created?
What is a transaction?
Front end developers managing transactions is a recipe for disaster. I'm maintaining a GUI app that inserts into serveral tables on one button click written by a guy who must have read the 'Learn T-SQL In 15 Minutes' book. Logic goes something like this:
1. Insert table 1, get identity
2. Insert table 2, get identity
If fails, delete table 1
3. Insert table 3, get identity
If fails, delete table 2 then
delete table 1
... repeat and expand as needed
Referential integrity is important as a concept but would most likely be something set up at the architecture level that a developer just needs to know is there.
Any developer above entry level should be able to handle cursors blindfolded. What code elements are required to utilize a generic boiler plate cursor loop?
In a stored procedure based back end, how would you provide the identity of an inserted row to the front end or mid tier application?
Any complicated application will require the use of temp tables. What are temp tables, why are they used, how are they created?
What is a transaction?
Front end developers managing transactions is a recipe for disaster. I'm maintaining a GUI app that inserts into serveral tables on one button click written by a guy who must have read the 'Learn T-SQL In 15 Minutes' book. Logic goes something like this:
1. Insert table 1, get identity
2. Insert table 2, get identity
If fails, delete table 1
3. Insert table 3, get identity
If fails, delete table 2 then
delete table 1
... repeat and expand as needed
Referential integrity is important as a concept but would most likely be something set up at the architecture level that a developer just needs to know is there.
A lot of places don't have DBAs. Many software houses don't want to tie them themselves to stored procs which they see as proprietary.
Personally I think you should tie the integrity of the data into the DBMS, it's what they were invented for in the first place.
The facility to do that though relies on specific architectural or business constraints.
I've worn both hats, database and application developer for a long time now, given a free reign I prefer to put the functionality in the 'best' place, I'm not always given that option though.
I have seen your example many times.
One of my favourites was .
Load up all details lines into memory
Allow user to add, chnage and delete them inside the app.
Delete all existing detail lines from the db
Insert new ones
No transaction round the delete and insert.
Help desk, 'I got an error adding a new line and now all my data has gone'
No sh** Sherlock
It's a falling down point in this test, enough SQL knowledge to write a query is not enough to make you a database developer or a database application developer.
I'd rather have someone who struggles with a Having clause but understands referential integrity and transactions any day.
Personally I think you should tie the integrity of the data into the DBMS, it's what they were invented for in the first place.
The facility to do that though relies on specific architectural or business constraints.
I've worn both hats, database and application developer for a long time now, given a free reign I prefer to put the functionality in the 'best' place, I'm not always given that option though.
I have seen your example many times.
One of my favourites was .
Load up all details lines into memory
Allow user to add, chnage and delete them inside the app.
Delete all existing detail lines from the db
Insert new ones
No transaction round the delete and insert.
Help desk, 'I got an error adding a new line and now all my data has gone'
No sh** Sherlock
It's a falling down point in this test, enough SQL knowledge to write a query is not enough to make you a database developer or a database application developer.
I'd rather have someone who struggles with a Having clause but understands referential integrity and transactions any day.
I've got to disagree about cursors and temp tables. I'll give you that complicated applications might need temp tables, but most likely derived tables will work (they are faster).
Cursors though, are a different story. If someone knew off the top of their head all the syntax of declaring and using a cursor, I actually wouldn't hire them! Cursors should never be used except as a last resort. And even then, there is probably a better way.
Cursors though, are a different story. If someone knew off the top of their head all the syntax of declaring and using a cursor, I actually wouldn't hire them! Cursors should never be used except as a last resort. And even then, there is probably a better way.
Sorry about that, but considering some of the conversations I've had recently, you have to wonder...
I think instead of just a definition of a trigger I might ask "when would one use a trigger?" The answer given would imply the definition is known (or not) and give some insight in to their actual knowledge of triggers.
I was asking for what people knew about optimisation, the ones who said not a lot and didn't BS me, did understand indexes, using joins instead of sub queries etc. They just thought that was common sense.
It's amazing how many times you see massive levels of nested triggers and things like calling mail directly from them not tied to "er it's a bit slow isn't it".
It's amazing how many times you see massive levels of nested triggers and things like calling mail directly from them not tied to "er it's a bit slow isn't it".
The further a DBA wants to run from triggers the better I like it. Of course that presumes Deny all standard SQL to start with, and Stored Procs are the only access method allowed. Funny how T-SQL that doesnt need to recompile runs fast, has few if any bugs or lock contentions, and zero possibility of SQL injection attacks. It may be a more rigid design, but templated developement using a tool like ERWin makes this sort of thing maintainable, auditable, predictable and fast.
The key to avoiding lock contentions is simple, use a standard join path to navigate through the database, and always use that same path whenever possible. It may look ugly, but a few slow ugly queries in the system, esp if they are compiled and maintained in a stored proc are well worth it. People often say that thousands of tables and tens of thousands of stored procs will make this kind of thing impossible. On the other hand I have done it successfully a few times over, and it can be done. For example I did a thing for US nation wide real estate a few years back that has zero lock contentions, and zero recompiles in the whole system. It has audits, security, and lots of esoteric niceties in it. My point being look for developers with both creative energies, and a fierce commitment to investigating and using best practices. A developer who hates innovation for the sake of recreation, but loves it for the sake of application to the problem is a rare and valuable treasure. If he takes specifications seriously and wants to document when and where use cases are implemented, has a passion for reading, understanding, questioning, and validating those use cases, then putting the exact specifications into his code chances are he wont be a developer much longer, that guy is on his way to becoming an architect.
Michael Rempel
The key to avoiding lock contentions is simple, use a standard join path to navigate through the database, and always use that same path whenever possible. It may look ugly, but a few slow ugly queries in the system, esp if they are compiled and maintained in a stored proc are well worth it. People often say that thousands of tables and tens of thousands of stored procs will make this kind of thing impossible. On the other hand I have done it successfully a few times over, and it can be done. For example I did a thing for US nation wide real estate a few years back that has zero lock contentions, and zero recompiles in the whole system. It has audits, security, and lots of esoteric niceties in it. My point being look for developers with both creative energies, and a fierce commitment to investigating and using best practices. A developer who hates innovation for the sake of recreation, but loves it for the sake of application to the problem is a rare and valuable treasure. If he takes specifications seriously and wants to document when and where use cases are implemented, has a passion for reading, understanding, questioning, and validating those use cases, then putting the exact specifications into his code chances are he wont be a developer much longer, that guy is on his way to becoming an architect.
Michael Rempel
I may sound out of topic but i cant
find a topic related to this one.
it would be
nice if someone could provide this
article in techrepublic.
thanks!
find a topic related to this one.
it would be
nice if someone could provide this
article in techrepublic.
thanks!
stuff.
They key thing though is to tie it in to what they are going to be developing.
I ask questions that test the limits of their application of the language. It's not so much a pass or fail test, but tests where the knowledge and their experience tie in
Like what is a transaction, if you don't know that you've never done any serious database development. If they don't cover it in their answer then but they got it right, ask them when they would use it.
Pointer manipulation, unions, structs, pre and post decrement are good ones for C.
If you haven't done these and can't describe situations where they've come in handy or that they've caused problems then they've turned up after reading the "Idiots guide to C"
In other words your tests should be specific to your environment.
They key thing though is to tie it in to what they are going to be developing.
I ask questions that test the limits of their application of the language. It's not so much a pass or fail test, but tests where the knowledge and their experience tie in
Like what is a transaction, if you don't know that you've never done any serious database development. If they don't cover it in their answer then but they got it right, ask them when they would use it.
Pointer manipulation, unions, structs, pre and post decrement are good ones for C.
If you haven't done these and can't describe situations where they've come in handy or that they've caused problems then they've turned up after reading the "Idiots guide to C"
In other words your tests should be specific to your environment.
At the top of every C# file there are typically items such as "using System;". Name as many different libraries as you can remember....
What you want to note is that the ones that roll off the tongue are those they constantly use/have to add. As they slow down, it becomes occasional... If you are looking for someone to do heavy XML work and they exclude System.Xml.Schema; System.Xml.Xpath; System.Xml.Schema.Xsl; -- be warned.
If they claim to have used SQL, then they should be able to name System.Data.SqlClient at least. This simple question reveals a lot about their past experience and familarity (One part of the decision to hire).
If the person has only been doing MAINTENANCE, they will often be unable to name any of them -- Why? because they do not have to add them to the files they are working on --- they are already there.
What you want to note is that the ones that roll off the tongue are those they constantly use/have to add. As they slow down, it becomes occasional... If you are looking for someone to do heavy XML work and they exclude System.Xml.Schema; System.Xml.Xpath; System.Xml.Schema.Xsl; -- be warned.
If they claim to have used SQL, then they should be able to name System.Data.SqlClient at least. This simple question reveals a lot about their past experience and familarity (One part of the decision to hire).
If the person has only been doing MAINTENANCE, they will often be unable to name any of them -- Why? because they do not have to add them to the files they are working on --- they are already there.
I'm interviewing for a Jr SQL Developer job next week. Do you think i will see question like this or something a little easier? I really enjoyed reading this. Thanks!!
from this table i have to take those records that have common gridcode and then from these records i have to store the records having maximum area in another table...
gridcode state area
13013 rajasthan 50000
13013 gujarat 45000
13014 rajasthan 25000
13014 gujarat 25000
gridcode state area
13013 rajasthan 50000
13013 gujarat 45000
13014 rajasthan 25000
13014 gujarat 25000
I am using vb.net with sql client. i have on problm. i created one table with fields author1, author2, author3. Now i want to combine these three fields items into new table field Author.
all datas in author1,2,3 are will be listed one by one under author field.
all datas in author1,2,3 are will be listed one by one under author field.
There's a few ways of doing this and you haven't said how the table is to be keyed
Quick one
Select Author1 as Author from Authors
Union
Select Author2 from Authors
Union
Select Author3 Author from Authors
Above assumes each author occurs only once in the entire table
Given you probably have book / document ID in there and not all books have three authors.
Select DocumentID, Author1 as Author from Authors Where Author1 is not Null
Union
Select DocumentID, Author2 as Author from Authors Where Author2 is not Null
Union
Select DocumentID, Author3 as Author from Authors Where Author3 is not Null
Or some such is likey to work.
Classic newbie DB design error by the way, always start normalised.
If you create you new table, then prefix the above with
Insert into NewAuthors, you are off and running
HtHs
Quick one
Select Author1 as Author from Authors
Union
Select Author2 from Authors
Union
Select Author3 Author from Authors
Above assumes each author occurs only once in the entire table
Given you probably have book / document ID in there and not all books have three authors.
Select DocumentID, Author1 as Author from Authors Where Author1 is not Null
Union
Select DocumentID, Author2 as Author from Authors Where Author2 is not Null
Union
Select DocumentID, Author3 as Author from Authors Where Author3 is not Null
Or some such is likey to work.
Classic newbie DB design error by the way, always start normalised.
If you create you new table, then prefix the above with
Insert into NewAuthors, you are off and running
HtHs
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
