A question seen often around the forums at SQLServerCentral is: "Can you post me a list of possible interview questions?" You can edit that for various forms of ESL difficulties, and that will be a question that pops up at least once a week.

The concern that the majority of forum goers have in answering these questions is not that we can't provide answers, but we're concerned you're about to apply for a job where we are currently working. We don't want a "professional" working with us that came in unprepared on the material, and just crammed for the test. It's not going to do you, or us, any good. How many of you remember what a Gerund is from English class? All right, you two wise guys in the back put your hands down. The point is that cramming for some test or interview is not the same as learning knowledge. To work with SQL Server, you need to learn a few things.

What you'll need to do is figure out what you'll want to specialize in at the beginning, and get yourself a solid understanding of the basics of these. Below, we'll discuss what the different levels of positions are, and then what topics you'll want to do some heavy research in before going to your first interview.

If you're used to coding front end applications and calling procedures, you've barely scratched the surface of what SQL Server does. It's used for data storage, yes. It's purpose, however, is high speed data retrieval, organization, and upkeep. There are a number of aspects that keep it running well.

I cannot stress enough that you want to do research and take the time to understand what you've researched. "I want to get a job" should not be your goal in and of itself, no matter how tired you are of flipping burgers. Walking into an interview with the express purpose of getting the job, instead of providing evidence of what value you can bring to the company in trade for a paycheck, is going to end up shooting you in the foot in the long run. Simply put, except in a few specific geographic regions, the SQL Server community is not big enough to play that game for long. You will get caught, and you will fail.

What level of job should I be looking for?

In general, there are three classifications of positions for technical positions, and SQL Server is no exception. You'll see them listed as Junior, Intermediate / Experienced / <no adjective>, and Senior. They might also be listed as Level I / II / III.

Junior or Level I

A junior is the starting level of the industry. They're the newbies. This is where you should expect to fit in at first. Your best bet is to try to do this job at a company you're already working for, or get lucky finding an employer who's ready to train someone as a longer term investment.

A junior level SQL person is expected to have a reasonable amount of book learning on the subject, some familiarity with the product, and to have at least toyed around with their student or free development copy of SQL Server. They should have enough experience that creating a database, assigning a login, making some tables and indexes, creating a view, and writing a stored procedure aren't foreign to them. Depending on the position they may want more understanding in a few specific areas like execution plan reading or perhaps building VBScript for DTS.

A junior is normally expected to be working under or with a mentor and shouldn't be a junior for longer than a year or two. Just about everything they do will be double checked until their code is trusted by the mentor, and should be considered on the level of a well-trained, and reasonably paid, intern.

Intermediate or Level II

This is 80% of the field for SQL Server professionals, and covers a wide range of duties. A Level II is expected to be independent in their field(s) of specialization, and can work on a wide range of projects with little oversight. They can read a requirements document, translate it themselves into the technical methodology, and come up with, at the least, a plan of attack. Often they'll utilize other members of the same team if they get stuck on some esoteric issue, or perhaps while looking for a better way to produce a solution.

You'll know you've just started to reach this level when you know what you don't know. That sounds paradoxical, but it's a simple truth. SQL Server is (to borrow some Shrek) like an onion. It has many layers. The trick here is that you will have learned enough of the system to recognize when you're getting in over your head on a topic that you haven't researched well enough yet. Once you can do that, you know when you need to go to someone else or do more research to get it right. This is the kind of person you can trust to work independently, because they won't take your entire production system down with an accident.

Senior or Level III

There are two forms of Senior positions, when you see them listed in a job opportunity. The first is the Senior in ability, or a Level III. The second possibility is a Team Lead or mentoring position, which also has a tendency to be listed as a Senior. We'll review the differences below.

A Level III Senior is usually considered a guru in the field, and have either highly specialized in their forte, or have been generalists long enough that they can cover the field in most aspects. They know where to look to find most of their own answers for the deeper questions. They've dug deeply into the mechanics of the engine, know enough to thwart most disasters, and are usually incredibly grumpy. Okay, maybe not that last part. What they can do is keep incredibly large and busy systems at peak performance, and are expected to have a lot of experience and knowledge in whatever they're working on.

A Level III Senior's experience almost always let's them say "Been there, done that, here's our options" when asked a question in their field(s). They have worked through a number of projects, can estimate time and personal expectations well, and will rarely need assistance outside of calling Microsoft because of a bug they found.

The other version of a Senior can describe a level within a team. The Senior DBA/Developer usually is the go-to person for knowledge and research, and perhaps organizes the team in regards to projects and standards. This person is also usually involved in doing new technology research for a company, as well as mentoring the other members of the SQL team in necessary methods.

Quite often a Level II will get stuck, or perhaps even hired, as the Senior person for a team. Most of the times a person will grow into this role over time, taking over from the last person to hold the position when they move on in their career. Good candidates to fit these positions are hard to find, because of the combination of people skills and technical ability.

It is worth taking the time to investigate with the manager/contact that's presenting the job description which of these they're looking for. Quite often you can puzzle it out from the job requirements, but other times it can be difficult to decipher. The more information and understanding you have about the skills your employer is interested in, and your ability to provide those skills, the better off both the interviewer and you will be during the discussion.

What are the general specialties in the field of SQL Server?

None of these are exhaustive lists of what you'll need to know to become a Level III in any of these positions. This is what you'll want to focus on to become a Level I, and is merely getting your feet wet. Deep dives into these topics are what people write entire books about.

Database Architect

There are two sides of the fence when discussing an architect. The first is a SQL Server specific Database designer/architect. These specific architects are masters of their craft on a specific database engine (SQL Server in this case) and are well trained professionals in all aspects of the field. We won't be discussing this job, it is not something you prepare for in the course of a few months. This is an end goal for a career.

The other version of Architect is the theoretical architect. This is a person well versed in design theory and entity relationships, and are generic to all RDBMS. They will be able to take business requirements and turn them into technical requirements and documents. They create the paperwork, and possibly some of the basic technical design, of what everyone else will implement.

A person looking into this position will want to concentrate on:

• Theoretical design, normalization, and table keys
• Data integrity and cascades
• Data Flows.
• Schema methodology and warehousing methodology. Kimball vs. Inman, Star vs. Snowflake, etc.
• ERWin or another database ERD software
• Industry specific concepts: They'll be the ones organizing the widget tables for the company. If you don't know what their widget means, you'll need to learn fast.

Database Administrator

The Database Administrator, or the proper title of the DBA, handles the day to day upkeep of the servers. Their job is keeping the existing systems online, helping setup new systems, checking on the status of the servers, and dealing with day to day maintenance tasks such as re-indexing and backups.

The DBA is usually the catchall position for a number of companies. Many are called upon between administrative duties to perform the architecture duties listed in the previous section. Another thing they're regularly called on to do is optimize production level SQL code or deal with data issues as well. This usually occurs because they are the ones with the highest access to the production systems, and regularly work with both the hardware and software sides of the server. Optimization though usually involves another skill set, which usually is more involved at a developer level. We'll discuss that further below.

A note to the juniors reading this article, you probably won't be tasked with either architecture or optimization while being a DBA early in your career. There is an understanding by the senior staff, or at least one hopes there is, that it's too much to take on everything at once too quickly. Things become rote instead of truly understanding what you're doing, and that can be dangerous.

This person usually rotates into an on-call shift for 24/7 support of the servers and is expected to take the lead on the majority of business affecting issues.

A person looking into this position will want to concentrate on:

• Database monitoring, including package failures, server error logs, windows error logs, and PerfMon.
• Performance monitoring: You need to learn to how check for pressure on a slow system.
• Troubleshooting. Yes, this is a skill. Knowing how to research an issue you've never heard of before is a primary skill of a DBA.
• Backups, Restores, different methods of restoration and how to proceed. Differential backups.
• Maintenance tasks: Re-indexing, Reorganizations, log backups, and a number of CHECKDBs. Complete understanding every possible option in the maintenance plan wizard is a good start.
• RAID mechanics.
• SQL Server installation and setup.
• SQL Server security (NT and SQL) and Windows Security. You need to know enough about security, even in a big shop, to intelligently talk to the network admins for your server login setups and other issues that occasionally crop up.

Database Developer

The database developer is the majority of the work that will be available. They handle the building of new development and application changes as required. Usually part of a development team, they get a set of requirements and build on existing systems. They primarily build stored procedures, user defined functions, the occasional trigger, and some supporting tables when necessary. The primary job of these people is to get the data into and out of the database accurately, quickly, and efficiently. Preferably in that order.

A person looking into this position will want to concentrate on:

• T-SQL. You must know standard SQL syntax and a number of regularly used functions that are T-SQL specific. Open BOL (Books Online) and learn what the different date, string, and mathematical functions can do.
• Aggregation queries and how to make them behave, including sub-query, temp table, and table variable usage.
• Parameterization, especially in regards to procedures and dynamic SQL, to protect from SQL injections.
• User Defined Functions: Limitations, usefulness, and optimization impacts.
• Index building and usage. Clustered indexing, non clustered indexing, covering indexes. Understand a B-Tree and the leaf level, at the very least.
• Execution plans, understanding them at a generic level and learning when things are bad from an optimization standpoint. The more you understand about optimization, the more you understand about writing good queries, which is the lion's share of this position's tasks.

SSIS Developer or ETL designer

The SSIS developer and ETL designer is the communication between servers under most circumstances. They handle the data flow between multiple systems, including other database engines (Oracle, MySQL, etc). They are primarily involved with data validation, cleanup, and transport. ETL stands for Extract, Transform, Load. That's what this job does.

A person looking into this position will want to concentrate on:

• Business Integration Development and or Visual Studio with BIDS installed locally.
• An understanding of using the data flow, and all components therein. Of specific interest will be the transformation tasks. There is a lot of things you can do in there. Half the battle is knowing you can do it at all.
• A basic knowledge of the T-SQL language and creating procedures for staging table cleanups.
• An understanding of Visual Basic for Scripting tasks.

SSAS/SSRS Developer

A reporting specialist has become familiar with all the tools available in SQL Server Reporting Services, or perhaps Crystal Reports. They are also familiar with dealing with cubes in Analysis Services, if not to the point that they create their own. These jobs are relatively recent in the SQL Server community, and the striation of tasks are not yet clearly defined into how much an average person is expected to take on in these roles.

A person looking into this position will want to concentrate on:

• SSRS: Create a report, create running total/aggregation reports, deal with pagination, and many varieties of formatting.
• SSRS: Reporting Services security and report access.
• SSRS: Publication of reports.
• SSAS: Building of a cube from a star schema warehouse.
• SSAS: MDX language at a basic level. Know how to access it, and some general functionality.
• SSAS: Shared dimensions and virtual cubes.

But this job description lists a job that says DBA and wants me to do SSIS! What should I be studying?

This is where experience helps, and perhaps I'll be able to take a number of job postings off the Internet and help to interpret them in a different article. What you'll need to read between the lines in the job description, and see what they're concentrating on. A number of companies out there will spam keywords into the descriptions just to make sure they have the highest 'visibility' rate. Others are written by HR staff who get a generic description from the manager or CEO that reads: Get a SQL guy in here. They know about as much as what you're going to be doing as you do.

The world isn't perfect and if they had someone who could do our jobs, they probably wouldn't be hiring us. It's also possible the people who do know what they need are just too swamped to write up the perfect job description. You'll have to do what you can to prepare your knowledge for the interview, and then interview them back as to what they really need. Sometimes you'll go to an interview that you're just out of your depth in. That's okay.

Just be honest, answer "I don't know, but I would research it by doing ... " and see if that's good enough. It probably won't be, if they don't even know what they're looking for. I was hired once by writing down the questions in the tech interview I didn't know and asked if I could contact him the following day with my research results. I did it, this impressed him, and I was brought on board. Honesty means a lot in this industry.

However, in the worst case scenario, you'll be blown off. That will probably happen for the first few interviews, if you're still very new. However, now you know the next thing you need to do some deep research on, and that recruiting agent that sent you off to the interview won't be afraid to send you to the next one, either.