Monday

Tag: interview

Technical Interview Questions

Technical interviews are an attempt by a hiring team to ask the correct questions of a candidate to determine if they would be a good technical fit for the open position.

These questions can sometimes uncover missing segments of knowledge that might identify opportunities for the candidate, or even disqualify the candidate for the open position. That is good information to know before you initiate the hiring process, but it can also help identify specific talents or abilities in a candidate that are above and beyond the minimum knowledge expected.

One of the obvious pitfalls of the technical interview is if the questioning turns into more of a trivia contest than a verification of expected knowledge.

One way to determine if a candidate knows how to solve a problem is to give them a problem and ask them to solve that problem during the interview. Sometimes the problem can be a specific technical issue, or a theoretical problem that is just to see if they can determine a simple solution using just the facts presented.

I have asked a simple question to candidates in the past that doesn’t really apply to the job opening they are applying for, but does provide insight into how they identify the issue, think through the possible answers, and provide them an opportunity to present their ideas.

Why is a manhole cover round?

You may never have thought of this question before, but why is a manhole cover round? You want the candidate to consider the possibilities and try to provide possible reasons for this design choice. It has nothing to do with the position they have applied for, but it will give a hiring manager an idea of how this person will respond to a problem that seems to come out of left field.

Do they think though the question or just respond with “I don’t know.” and quit? Have them speak about what they think about the question. Do they have an opinion about why they aren’t square, hexagon, or even oval in shape? Have they seen a manhole or know what they are used for in everyday life? Why do they think some manholes covers are not round?

Hopefully they can speak to possible reasons, which gives you the opportunity to ask how they would find a suitable response. If they just want to Google the answer, maybe ask them what else you might do to get a suitable answer if there isn’t a consensus on Google.

The possible correct answers are:

  • Manhole covers are round because it is the best shape to resist the compression of the surrounding soil.
  • Round manhole covers are easier to manufacture, move, and place than square or rectangular ones. The heavy covers can be easily rolled into position.
  • Manhole cover the size to fit the opening cannot fall through the circular opening, unlike other shapes. No one wants a 100-pound manhole cover dropping onto their head.
  • The cover doesn’t have to be aligned in any specific angle to be placed back onto the exposed manhole. Other shapes would require precise alignment.

Years ago, there was a candidate that guessed the covers are round because the men accessing the opening are also round. While this is funny, I don’t think that was a design consideration.

I hate interviews that turn into trivia contests, so I’d much rather be asked a tough question that allows me to show my ability to use my brain to find solutions instead of just demonstrating my ability to memorize technical trivia that anyone could easily look up.

Sources:
(1) Why Are Manhole Covers Round? | Mental Floss. https://www.mentalfloss.com/article/60929/why-are-manhole-covers-round.
(2) Why are manhole covers round? | Live Science. https://www.livescience.com/32441-why-are-manhole-covers-round.html.
(3) Why Are Manhole Covers Round? – ScienceABC. https://www.scienceabc.com/eyeopeners/why-manhole-covers-circular-not-triangular-square-rectangular.html.
(4) The Surprisingly Technical Reason That Manhole Covers Are Round. https://www.envirodesignproducts.com/blogs/news/the-surprisingly-technical-reason-that-manhole-covers-are-round.

How to Spot a Bad Boss During an Interview

In a Harvard Business Review article by Sara Stibitz, she outlines how to spot a terrible boss during the interview process. The process is a fairly well-known list of items to watch for during an interview, but it doesn’t hurt to remind you of those items you should be aware of during this important process.

You should know what kind of person you respond well to, and make sure your new boss meets those requirements. You might not have a choice when looking for that new job, but if you do have a choice, you should also interview that new boss to make sure he or she is someone you can spend a lot of time with.

You should also trust your instincts to make sure if it feels wrong, abort the process and look elsewhere for an open position. The interview process is a lot like dating in that everyone is on their best behavior during the interview process. People dress up and at least act like they care about you and the company. If you can’t stand them or if they appear to have habits that seem annoying or unprofessional, it probably won’t get better after the job starts.

Ask a few well-crafted questions to determine how the day-to-day assignments are handled and whether the overall management style will fit with your work style and personality. If you like a little extra flexibility in how to complete tasks and the description from your prospective manager indicates they like to exert a lot of strict controls, you might not be a good fit for this position.

Always do your research before you appear for your scheduled interview. Check for specific comments about the company or department, and also see what you can find out about the prospective manager. Most people start with LinkedIn and Facebook, then go to sites like Glassdoor to get the details on complaints or former employee reviews. If you have doubts about someone or a company, it doesn’t hurt to start asking questions to anyone who might have some answers.

Principles to Remember

 Do:

  • Pay attention to how the manager treats you throughout the interview process
  • Research the manager, and if possible, find former employees to ask for their perspective
  • Request to spend a half-day at the organization so you can interact with your potential colleagues and boss

Don’t:

  • Ignore your gut instincts about the manager as you go through the interview process.
  • Ask direct questions about leadership style — you’re unlikely to get an honest answer, and they might signal with their response that you don’t want the job. Feel out their style using simple questions to determine if they manage or lead their team.
  • Neglect to look up your potential boss’s social media profiles.

22 SQL Server DBA and Database Developer Interview Questions

Introduction

There have been several blog posts about SQL Server Database Administrator and SQL Server Developer interview questions. I have listed some examples here for your review, but the best thing you can do to prepare for an interview as someone looking for a job working with databases is to know about SQL Server databases. If you are performing the interview as someone in the hiring process, think about the specific skills you are looking for and target your questions to help identify the applicants strengths and weaknesses.

1. What is a four-part name?

If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:

Production.AdventureWorks.HumanResources.Employee

Continue reading “22 SQL Server DBA and Database Developer Interview Questions”

22 SQL Server DBA and Database Developer Interview Questions

Introduction

There have been several blog posts about SQL Server Database Administrator and SQL Server Developer interview questions. I have listed some examples here for your review, but the best thing you can do to prepare for an interview as someone looking for a job working with databases is to know about SQL Server databases. If you are performing the interview as someone in the hiring process, think about the specific skills you are looking for and target your questions to help identify the applicants strengths and weaknesses.

1. What is a four-part name?

If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:

Production.AdventureWorks.HumanResources.Employee

Continue reading “22 SQL Server DBA and Database Developer Interview Questions”

Stop Hiring Terrible Managers

Hiring is one of the most serious activities you can do that will have a direct impact on the long-term success of your company. If you are hiring people who will make poor managers, or are already bad managers, you are actively hurting your company. The next question is how do you avoid hiring someone who is going to be a poor manager. In his article by Lou Adler we get some basic advice:

Here are some of the indicators for less experienced people who have the potential for management:

  1. Forget the “I” or “we” shortcut, instead look for people who are more proud of their team accomplishments. As you ask candidates to describe their most significant accomplishments look for a bias towards getting people to achieve a team result rather than an emphasis on individual contributor skills.  
  2. Volunteers or asks to lead team projects. Find out what type of projects the person has volunteered to do. Those with management aspirations and ability want to be involved in team projects in a leading capacity. If the person has been assigned to lead team projects, it’s a clue that others in the company think the person is also worth developing as a manager.
  3. Assigned to bigger and more important team projects. A track record of being assigned to expanding project roles indicates not only previous success but also upside potential.
  4. Proactively coaches others. Get examples of the person coaching other people who are peers. If the list is endless it’s an important clue the person enjoys helping others become stronger. This is a key trait of the best “coaching” managers.
  5. Assigned to multi-functional teams soon after starting with a new company. Find out how soon after starting with a company the person was assigned to work on an important multifunctional team. The sooner the better and less than six months is a great sign, especially if the person is working with important leaders in other departments and company executives.
  6. Hired by a former boss to take over an important team project. This is great evidence that the person is promotable.

If you are interviewing someone who is already a manager at their previous place of employment, you can’t just assume they were a great manager at that position, so what questions and which behaviors indicate a person is a good manager? Lou Adler has some additional advice on what to look for when conducting the interview:

  1. Comparable leadership skills. I define leadership as the ability to both visualize a solution to a complex problem and execute a successful solution. One way to assess this is to first engage in a back and forth discussion about how the person would handle a complex management challenge likely to be faced on the job. Make the problem increasingly complex to determine the point where the person’s thinking goes from specific to general. This represents the person’s current level of understanding. To validate the problem-solving skills ask the person to describe a past comparable accomplishment to determine ability to actually eliminate the problem. Both the thinking and execution responses need to map closely to real job needs.
  2. The quality of the teams they’ve built. Have candidates rank the quality of the people in their department. If they’re not strong find out why. If they are all strong find out the person’s grading system and how the team was hired and developed. It’s a great sign if the person has been able to attract previous co-workers.
  3. The process used to coach and develop their staff. Be concerned if a candidate doesn’t have even a rough development plan for each person on the team. If the candidate has one, determine how good it is.
  4. The trend of growth of the size of teams they’ve managed. A manager who has been promoted into bigger management jobs is a great sign. If it happens at multiple companies it’s even greater.
  5. Whether the person is more proud of management or individual contributor accomplishments. When I ask managers to describe their most significant accomplishments, I get very concerned when they describe an individual accomplishment. You should be, too.

SQL Server Interview Preparation

Preparing for a successful job interview can be stressful and can take significant time. If you follow some basic steps, you can reduce the stress as well as abbreviate the amount of time required. The first thing to remember is that preparing for an interview is very important and you must allot enough time to prepare for the interview so that will be successful. Adequate preparation time will result in decreased anxiety, better performance, a more relaxed interview experience, and you will leave a more positive impression on your interviewer.

  1. Review the company website – A great interview requires a conversation between the interviewer and the candidate. The company representative will ask you questions, and you are expected to give good answers. The part that people often forget is the candidate is expected to ask intelligent questions that demonstrate a knowledge about the company and the industry they serve. You should read through the company’s website and understand their place in their market. Ask yourself  what makes them special in their industry or to their current customers. Make sure you understand what challenges exist for them today, and that will help you understand why hiring you might address some of those challenges.
  2. Review your resume – This is going to be challenging to some, but if you have ever hired someone before this might be slightly easier. Read you resume like you are a hiring manager and looking at your resume for the first time. What items on the resume are impressive, and which items would be something a hiring manager might be worried about? Items that might concern a hiring manager are gaps in your employment, short-term assignments or frequent job changes, changing from one industry to another, missing skills, etc. Now is the time to change your resume or think about how you might address questions about these items. Do not lie about what you can or have done to make yourself look better, because this will come back later and could cost you the new job.
  3. What is important – Think about what is important to you and what it would take to make you happy at your new job. Write down 5 relevant questions that will tell you if this new company will make you happy. What did you really enjoy about a past employer that you want to see in this new employer? Are their specific technologies or solutions that you feel you are important? Make sure you ask about their use of technology today or plans for the future that might bring solutions important to you into this new environment. If telecommuting is important to you, as about the opportunity during the interview.
  4. Close the gap – While you might be the best at many different technologies, you might still be missing one or more skills that are important to this new company. That doesn’t mean you aren’t a great candidate, especially if you can identify that gap and explain your plan for closing the gap. Explaining how you know you aren’t the idea candidate because you are missing the one skill, but you can explain what it will take to learn that one skill, how long you think that will take, and explain one or more of your other skills is above average might seal the deal. This shows self awareness, problem solving, and time management skills during the interview.
  5. Practice answering questions – Start with 10 questions you might be asked about your ability to perform the new job responsibilities. Write down the questions, and then write down your answer to each question. You will probably have to refine your answers until they are concise and perfectly worded. Once you have these 10 questions solved, start with the next 10, then 10 more, etc. Review these questions and answers before your next interview, practicing listening to the questions, answering the questions, and studying your body language in a mirror.

If you take the responsibility of a job search seriously, less stressed during the process and you will be more successful overall.

How to Spot a Bad Boss During an Interview

In Harvard Business Review article Sara Stibitz, she outlines how to spot a terrible boss during the interview process. The process is a fairly well known list of items to watch for during an interview, but it doesn’t hurt to remind you of those items you should be aware of during this important process.

You should know what kind of person you respond well to, and make sure your new boss meets those requirements. You might not have a choice when looking for that new job, but if you do have a choice you should also interview that new boss to make sure he or she is someone you have spend a lot of time with.

You should also trust your instincts to make sure if it feels wrong, abort the process and look elsewhere for an open position. The interview process is a lot like dating in that everyone is on their best behavior during the process. People dress up and at least act like they care about you and the company. If you can’t stand them or if they appear to have habits that seem annoying or unprofessional,it probably won’t get better after the job starts.

Ask a few well crafted questions to determine who the day-to-day assignments are handled and the overall management style will fit with our work style and personality. If you like a little extra flexibility in how to complete tasks and the description from your prospective manager indicates they like to exert a lot of strict controls, you might not be a good fit for this position.

Always do your research before you appear for your scheduled interview. Check for specific comments about the company or department, and also see what you can find out about the prospective manager. Most people start with LinkedIn and Facebook, then go to sites like Glassdoor to get the details on complaints or former employee reviews. If you have doubts about someone or a company, it doesn’t hurt to start asking questions to anyone who might have some answers.

Principles to Remember

 Do:

  • Pay attention to how the manager treats you throughout the interview process
  • Research the manager, and if possible find former employees to ask for their perspective
  • Request to spend a half-day at the organization so you can interact with your potential colleagues and boss

Don’t:

  • Ignore your gut instincts about the manager as you go through the interview process
  • Ask direct questions about leadership style — you’re unlikely to get an honest answer, and they might signal that you don’t want the job
  • Neglect to look up your potential boss’s social media profiles

10 SSIS Interview Questions

SQL Services Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the “Standard” and “Enterprise” editions.

If you are looking to hire someone to assist your company with SSIS development or maintenance, you might have some questions for them to help gauge their experience and abilities, even if you don’t have a great deal of knowledge on your own.

1. Which versions of SSIS have you used?

Differences between versions in 2005 and 2008 were not very big so experience in 2005, 2008 or 2008 R2 is very similar. The big difference is with SQL Server 2000 compared to later versions. SQL Server 2000 used DTS and it very different from the technology used in the versions used today.

2. Have you used SSIS Framework?

This is common term in the SSIS world which just means that you have templates that are set up to perform routine tasks like logging, error handling, etc. If they answer yes, this would usually indicate an experienced person. A no answer is still fine if your project is not very mission critical or less complicated.

3. Do you have experience working with data warehouses?

SSIS is usually used for supporting a data warehouse environment, so knowledge of Data Warehouse design is very useful.

4. How did you learn SSIS?

How did the candidate learn what they know about SSIS? If they took a few classes, then they probably understand the concepts and techniques, but might have only used the features easily demonstrated in a classroom setting. If they have read a book or two on the subject, then they probably know a lot on the subject but may never have used the product outside of a sample database environment. They might have also read additional material not usually covered in a classroom setting, and have a more complete understanding of the overall capabilities of the product. If the only source of knowledge is from blog entries or internet articles, since the quality of that information can vary in quality, you might have to really question the candidate to better gauge their knowledge level. Knowledge gained through conference sessions is also a good source of quality knowledge, but direct experience is always best.

5. Do you have any certifications?

While certification is not a reliable indication of knowledge, it can indicate the candidate has the motivation to learn and the desire to demonstrate that knowledge. It also helps you understand the level of knowledge they are promoting so you can target your questions to the correct level.

6. How many different source and destination types have you used?

Common answers are SQL Server, CSV files, TXT files, Access tables, DB2 databases, Oracle databases, MySQL databases, web form scrapping, etc. You are looking for someone with the types of experiences that either fit your expected projects, or someone with experience that would indicate they could learn what is needed for your expected project requirements.

7. What is your approach for loading data warehouses using an ETL?

This is a open question around the general process used to develop an ETL solution in a production environment. Do they use one ETL to completely load all data, or do they use multiple jobs to load data to a series of staging tables? Do they load each ETL as part of a separate job, then load everything later as part of one job that moves the data from several staging tables into FACT tables.

8. What types of transformations you have worked with in SSIS?

The most common are: Derived Column, Aggregate, Conditional Split, Sort, Lookup, Merge Join and Union All.

9. How can you deploy a new SSIS Package (or Project)?

In previous versions of SSIS it could done either manually (or from BIDS using BIDS Helper) or using scripts. You might prefer SSIS 2012 with Deployment Wizard although you have to deploy the entire project with ALL packages so an extra step to control which packages are changed is recommended.

10. What is the difference between Control Flow and Data Flow?

Control Flow manages the flow of tasks in a package, but Data Flow most frequently moves data from Point A to Point B.

You can find more information about SSIS interview questions here.

20 SQL Server DBA Interview Questions

Introduction

There have been several blog posts about SQL Server Database Administrator interview questions. I have listed some examples here for your review, but the best thing you can do to prepare for an interview as someone looking for a job is to know SQL Server databases. If you are performing the interview as someone in the hiring process, think about the specific skills you are looking for and target your questions to help identify the applicants strengths and weaknesses.

1. What is a four-part name?

If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:

Production.AdventureWorks.HumanResources.Employee

2. How do you trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

3. What is a Linked Server?

Another question that can tell you if the candidate has experience with distributed databases is “What is a Linked Server?” A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.

4. Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

5. What happens on checkpoint?

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database. One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

6. What are the dynamic management views and what value do they offer?

The DMV’s are a set of system views new to SQL Server 2005 and beyond to gain insights into particular portions of the engine

7. What are the primary differences between an index reorganization and an index rebuild?

  • A reorganization is an “online” operation by default; a rebuild is an “offline” operation by default
  • A reorganization only affects the leaf level of an index
  • A reorganization swaps data pages in-place by using only the pages already allocated to the index; a rebuild uses new pages/allocations
  • A reorganization is always a fully-logged operation; a rebuild can be a minimally-logged operation
  • A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transactional and must be completed in entirety to keep changes

8.How can you control the amount of free space in your index pages?

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.

9. Name 3 or more DBCC commands and their associated purpose.

  • DBCC CACHESTATS – Displays information about the objects currently in the buffer cache.
  • DBCC CHECKDB – This will check the allocation of all pages in the database as well as check for any integrity issues.
  • DBCC CHECKTABLE – This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
  • DBCC DBREINDEX – This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
  • DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used.
  • DBCC MEMORYSTATUS – Displays how the SQL Server buffer cache is divided up, including buffer activity.
  • DBCC SHOWCONTIG – This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
  • DBCC SHOW_STATISTICS – This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
  • DBCC SHRINKFILE – This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
  • DBCC SQLPERF – This command will show you much of the transaction logs are being used.
  • DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.

10. What authentication modes does SQL Server support?

SQL Server supports Windows Authentication, SQL Server Authentication, and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server.

11. Define candidate key, alternate key, composite key

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

12. What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which usually makes it makes it slow, really slow if there are a large number of rows in the target database. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

13. How do you determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit my SQL Server service packs and versions list.

14. What is ACID?

ACID stands for Atomicity, Consistency, Isolation, Durability. You can read more details here.

15. What is Normalization?

Wikipedia says “normalization involves decomposing a table into less redundant (and smaller) tables without losing information; defining foreign keys in the old table referencing the primary keys of the new ones.” You should also understand the reverse process of denormalization. Denormalization is the controlled introduction of redundancy in to the database design, primarily used to improve the performance of your query by reducing the required number of joins.

16. What are user-defined datatypes?

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name and format to the database. If you have an element that is used often in your database, you can create a user-definded datatype to make the reuse across multiple tables easier.

17. What are defaults?

A default is a value that will be used by a column in your table, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.

18. What’s the maximum size of a row?

8060 bytes. Don’t be surprised by questions around minimums or maximums. Read more here.

19. What is the syntax for SELECT statements, covering all the options?

SELECT column_list
[INTO new_table_name]
FROM source_table
[WHERE filter_conditions]
[GROUP BY group_expression]
[HAVING search_conditions]
[ORDER BY order_expression [ASC | DESC] ]

You should also understand the execution order, which you can learn more about here.

20. When And Where Does The Local User Group Meet?

This is a suggestion from Brent Ozar, and I really like the intent of the question. It isn’t important if the candidate goes to every meeting, but they should know that they exist and when they usually meet. It is even better if they attend  on some fairly regular schedule. They won’t know everything and this gives you an idea if they connect to the local community of professionals so if they do run into an issue they know there is somewhere they can go for help besides Google. This will work even for remote or relocating candidates, as they should answer this question about their group in their current city.

BONUS – Why is a manhole cover round?

Sometimes we get so focused to technical questions, we forget that there are questions that aren’t just about SQL Server. Simple questions that aren’t about databases or technology can reveal a lot of information about how a person thinks and reacts to questions they couldn’t have practiced or studied for in advance. There  are several questions you could ask, like the example above, or ask them how many gas stations are open 24 hours a day in the state of Idaho, or how much the Pacific ocean weighs in kilograms. It doesn’t matter so much what the correct answer should be, but how they react to the question, how they describe the process of calculating their response, and the logic they use to justify or defend their answer.

By the way, there are several reasons a manhole cover is round in shape, versus square or triangle:

  1. The heavy metal cover is round so the maintenance worker can easily roll the cover on the smooth edge instead of picking it up when it needs to be moved from place to place.
  2. The cover doesn’t have to be aligned in any specific angle to be placed back onto the exposed manhole. Other shapes would require precise alignment.
  3. A round shape is easily sized so the cover can’t be dropped though the manhole and possibly injure workers under the street. Safety is important.

I once had a job candidate think about the answer for several seconds and respond that the cover is round because the manhole is round, and the manhole is round because the men who use the manhole are also round. While that is a funny answer, it doesn’t indicate they spent much time trying to find the logic in the shape of the original design.

Conclusion

You can find scores of questions on the internet, as well as questions based on your own experiences. You can find some additional questions here, here, and here.

9 Tricky Brain Teaser Interview Questions

During a technical job interview, you might be asked to solve a tricky question to prove your ability to think about a seemly impossible problem and use logic to solve the brain teaser. In this article by Eugene Kim, we get a few examples of these types of questions.

Q: Software engineer at Facebook: “You have two lightbulbs at a 100-story building. You want to find the floor at which the bulbs will break when dropped. Find the floor using the least number of drops.”

A: “Start moving up in increments of 10 floors and dropping the bulb until it breaks (ie: drop from floor 10, if it doesn’t break, drop from floor 20, etc.). Once the bulb breaks, move down to the floor before it broke on and start moving up floors in increments of one until the second bulb breaks. This results in a worst case scenario of 19 drops.”

“19 drops is not the best worst case scenario… imagine trying floor 16, if it breaks, you try 1 – 15 and thats 16 tries. If it doesn’t break, then try floor 31 and if it breaks, then try 17 – 30 (so 16 tries, including the try on floor 16). And on and on (45, 58, 70, 81, 91, 100). If you reach 91, you’ll have tried 7 floors so far and if it doesn’t break, then there’s 9 more tries to get to 100 (thus 16 in the worst case).”

Q: Software engineer at Raytheon: “In front of you are three light switches. Only one does anything, and it turns on the light downstairs. From here you can’t see the light, and it makes no sound. You must determine which switch operates the light, BUT you can only go check it once. How do you figure out which switch is for the light?”

A: “Flip any switch you want. Wait for about 5-10 minutes to let the bulb heat up. Flip that same switch off, and another one on. Go check the light. If it’s off and hot, it was the first switch, if it’s on it was the second and if it’s cold and off, it was the last one.”

Insider Tip: How to ace your job interview

With the new year may come a new job. As you are performing your job interviews, here are some tips to help you do a better job of impressing your new boss and coworkers. In this article by Steven A. Lowe, we find he has several tips to help you perform better during your interview.

If you’re an IT pro, chances the job interview is at — or very near — the top of your list of personal hells. Why not? Tech job interviews can be grueling experiences, rife with esoteric puzzles, uncomfortable pauses, landmine questions, and the aching underlying feeling that maybe you don’t belong.

Throughout the process, you will be talking with strangers via phone and video, taking tests, answering challenging and often uncomfortable questions, traveling on sleepless red-eye flights, and enduring multiple anxious periods of not knowing what is going on, how you’ve been received, or what will happen next.

To really ace the interview and minimize your anxiety going in, you must be prepared to an almost otherworldly extent, on many fronts at once. This means deep research on the position and company to decode what to expect during the interview process and to understand the company dynamics at as near the level of a current employee as you can.

Still sure you want to pursue that new job?

1. Don’t be afraid to reach out early

2. Don’t believe everything you read on Glassdoor

3. Find employee blogs and read them in depth

4. Research social culture – it’s as critical as technical focus

5. Understanding the underlying principles of interview puzzles is the key to crushing them

6. Connect with current employees

7. Don’t tilt at windmills

8. Dress as if you already work there

9. Let your personality out

10. Beware the “interviewing the interviewer” trap

11. Help the interviewer imagine you in the position

12. Always speak favorably about former employers

13. Ask for the job

My personal technique to breezing through interviews and leaving a positive impression is asking the interviewer questions about themselves on the subjects they want to talk about. Interviews are almost like a date, and people want to talk about themselves when on a date. No one wants to hear someone talk about themselves for an hour. Give the interviewer an opportunity to talk about themselves. Ask them why they came to work at this company, how long have they worked here, what they like about the company, what projects they have recently been involved with, etc. That is not all you want to talk about, but make sure you ask these questions as part of the interview.

10 Job Interview Tips

We begin a job search with a well written interview, networking with your circle of friends and friends of friends, putting together a list of quality references, and attempting to schedule some interviews with some target companies. Some people are really good at talking to people in general, and conducting a good interview. Some people aren’t very good a talking to people, and are terrible at interviews. Poor planning and a lack of practice can make that interview even worse.

Instead of relying solely on your professional skills, you should rehearse for your next job interview. Get a friend or family member to pretend to be the person conducting the interview, and have them play the part of asking you a series of interview questions.

Not sure how to go about doing so? Start by enlisting a family member, friend or partner to play the role of interviewer, and ask that she stay in character from start to finish. Set up a space, such as a desk or table, where you can create a suitable setting. Then use these 10 tips to from corporate trainer Marlene Caroselli to make your interviews — both mock and real — successful.

1. Research

Do some basic research about the company you are trying to get a job with, and the people that work at that company. Find the history, recent acquisitions, recent management changes, investor news, product changes, analysis reports, etc. Mot of this information is easily found on the internet by searching the target companies internet site, looking at news and financial stories, looking at all existing job postings, and looking at information on sites like Glassdoor.com.

2. Focus

Think about what people see when they look at you and what they hear when they talk to you. Do you look interested in the position you are interviewing for, and appear professional, engaging, and do you say things people want to listen to then you speak? Use a video recorder (laptop webcam, cellphone, video camera, etc.) and record yourself answering sample interview questions. Do you maintain a professional attitude? Are you focused on answering the questions while having a friendly demeanor? While you can’t change who you are, you can change how to look to other people by acting the part for a few hours during the interview process.

3. Stay on message

While the target company is looking to hire someone with the skills for the posted position, they are also looking for someone that fits well into the team and company culture. Maybe they are also looking for someone who is interesting, friendly, polite, and maybe a little different. The interview is all about stating your professional qualifications, but also gives the interviewer a chance to get to know a little about you by having a brief conversation. Don’t waste that brief moment of connection by stating the obvious, but also discuss what might not be so obvious about you and your life experiences.

4. Don’t be in a box

Try an exercise in visualization. Imaging the person you want to be, with the job you wish you had. What will it take to get to that life? See the interview as a step toward that life, and position your thinking to believe you can make those dreams come true. If you can’t believe it can happen, it won’t come true.

5. Practice your lines

We all know that movies and television shows are written and rehearsed. Actors have lines and they practice those lines until they come out as natural statements that are believable and sound true to their character. You should have a few lines that you have memorized. These lines should be practiced and true to who you are, and are targeted at telling your story in a believable way. Maybe it is a story about a funny event that happened to you at work that will be handy when the interviewer asks you about your previous job experience. Instead of a 10 minute rambling story about technical details or procedures, it is a funny story about how your boss crashed the departmental server with a spilled can of soda and how you both worked on recovering the server overnight to keep anyone from finding out how the server crashed. Maybe that same story ends with him thanking you with a warm handshake and both becoming great friends.

6. Be Memorable

When the interviewer asks for you tell them a little about yourself, you should have a brief (5-10 minute) story that summarizes your story. If you have never had a “real” job, this story will focus on your education, volunteer work, etc. If this is your tenth job, this story will summarize your work history, hitting the high points that might include company names, positions, big projects or accomplishments, and specific events. Don’t be surprised by this type of question and have a rehearsed version of your life story that is accurate, interesting, and relevant to the interviewer.

7. Ask Yourself Tough Questions

When you are practicing your interview, make sure you are asking yourself tough questions. Have your friend or family members ask you very difficult questions about your work experience, gaps in your employment, lack of training, etc. If they ask you tough questions you will be forced to really think about your answers, and validate all of your life experiences to justify your qualifications for this new position. If you can make it though that process and still feel good about the interview, what is the worse thing that can happen? What question can they throw at you during the real interview that you haven’t already asked yourself? This will help you feel more confident before the interview, and help you project that confidence during the actual interview.

If possible, take notes during the actual interview. Write down the questions you are asked and even the answers you provided. What follow-up questions were you asked? This information might be helpful for any follow-up interviews, but also helpful at interviews at other companies. If the interviewer has some doubts about your ability to perform one or two aspects of the target job, other companies might have the same doubts. Use this information as feedback to improve your next interview rehearsal with your friends and family.

8. Body Language

When you are practicing your interview with your friends or family, review the video for more than a transcript of your responses. You have to pay attention to your body language as part of the package. Focus on your body posture, facial expressions, the movement of your hands, the way you look at everyone while you answer the question from just one person, and other basic presentation techniques. Do you have any nervous ticks like clearing your throat, tapping your foot, touching your face, or other things that might be distracting?

9. Listen

When you are playing back the video of your rehearsals, close your eyes and just listen to the audio. Do you sound like someone to knows what they are talking about, that sounds confident, and sounds honest? If you look at the research on why someone doesn’t get the position after a face-to-face interview, you will see answers like they didn’t sound believable, they didn’t seem confident, or they weren’t comfortable talking to the team. Maybe you aren’t confident. Maybe you aren’t comfortable telling your life story to a bunch of strangers. But like the rehearsal of your lines to make your words seem natural and believable, you have to practice the sounds to make them come out right all the time. Make sure you sound as confident as you look.

10. Serenity

Work on being relaxed before your big interview. Being prepared helps you feel confident, and being confident will help you relax. While waiting for your turn in the interview room, don’t focus on reviewing your answers or checking your outfit in the bathroom mirror. Sit calmly and let your brain wonder to games or puzzles. When you are called to enter the interview, take a few deep breaths, put a smile on your face, and walk confidently into the future.

BONUS: Know when to say when

If you are in the middle of conducting an interview and things have gone really badly, know when to excuse yourself from the process and make a quick exit. I’m not suggesting that you give up and leave the interview because they asked you some tough or unexpected questions. I’m saying you should be prepared to call it quits if the interview turns out to be hopeless, for a different position than you were told, or if they ask you questions that you are uncomfortable in answering. Just tell them you are sorry but that you have run out of time and you must leave.

10 Tips For A Phone Interview

You are doing a job search and you have gotten through to the interview stage – but it’s a phone interview! How do you master a phone interview where the can’t see your body language or they might not even hear you very well?

  1. Take Notes – Write down the questions you are asked and if you can’t answer the questions tell the interviewer thou will get the answer and call them back with the information. If nothing else, you can reference your notes to see what went well or not so well so you can be prepared for followup calls or the next interview.
  2. Provide Concise Answers – Use one sentence to answer the question, when possible. A long answer might sound like it is providing more context or information, but without the ability to see the caller, you might just be confusing them. Wait for them to ask for more information.
  3. Schedule Adequate Time – Don’t try to handle the call during the scheduled 30 minute session, only to find they have so many followup questions you run out of time and have to end the call. This is your future we are talking about. If you think it will take 30 minutes, schedule an hour. If you think it will take an hour, schedule 2 hours.
  4. Talk While Standing – The listener can hear the difference between you slouched over your desk and you are standing up and smiling.
  5. Let Speech Be Your Body Language – Project a confident and smiling professional over the telephone and that will often be transmitted straight into the listeners brain.
  6. Find a Quite Room – Don’t let the call be interrupted by noise from a busy street or cafe patrons. We also don’t want television noise or children talking in the background.
  7. Ask Great Questions – Have a few questions prepared in advanced. When asked if you have any questions, make sure you have one or two appropriate questions that show you are serious about the position.
  8. Verify the Telephone – Make sure you telephone is working before the call. If this is a video call, make sure you have tested the service and have any software installed and configured before the call starts. If you will be using your cell phone, make sure you have the device fully changed and  you are taking the call in an area with excellent service.
  9. Research – Understand the company and the position before you get on the call. This shows you are interested and intelligent.
  10. Take it Seriously – This is just as important as a face to face interview. Don’t laugh off a question, make jokes, or be offensive in any way. If you mess up this call, this may be your last shot at being considered for the position.

 

 

10 SSIS Interview Questions

SQL Services Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the “Standard” and “Enterprise” editions.

If you are looking to hire someone to assist your company with SSIS development or maintenance, you might have some questions for them to help gauge their experience and abilities, even if you don’t have a great deal of knowledge on your own.

1. Which versions of SSIS have you used?

Differences between versions in 2005 and 2008 were not very big so experience in 2005, 2008 or 2008 R2 is very similar. The big difference is with SQL Server 2000 compared to later versions. SQL Server 2000 used DTS and it very different from the technology used in the versions used today.

2. Have you used SSIS Framework?

This is common term in the SSIS world which just means that you have templates that are set up to perform routine tasks like logging, error handling, etc. If they answer yes, this would usually indicate an experienced person. A no answer is still fine if your project is not very mission critical or less complicated.

3. Do you have experience working with data warehouses?

SSIS is usually used for supporting a data warehouse environment, so knowledge of Data Warehouse design is very useful.

4. How did you learn SSIS?

How did the candidate learn what they know about SSIS? If they took a few classes, then they probably understand the concepts and techniques, but might have only used the features easily demonstrated in a classroom setting. If they have read a book or two on the subject, then they probably know a lot on the subject but may never have used the product outside of a sample database environment. They might have also read additional material not usually covered in a classroom setting, and have a more complete understanding of the overall capabilities of the product. If the only source of knowledge is from blog entries or internet articles, since the quality of that information can vary in quality, you might have to really question the candidate to better gauge their knowledge level. Knowledge gained through conference sessions is also a good source of quality knowledge, but direct experience is always best.

5. Do you have any certifications?

While certification is not a reliable indication of knowledge, it can indicate the candidate has the motivation to learn and the desire to demonstrate that knowledge. It also helps you understand the level of knowledge they are promoting so you can target your questions to the correct level.

6. How many different source and destination types have you used?

Common answers are SQL Server, CSV files, TXT files, Access tables, DB2 databases, Oracle databases, MySQL databases, web form scrapping, etc. You are looking for someone with the types of experiences that either fit your expected projects, or someone with experience that would indicate they could learn what is needed for your expected project requirements.

7. What is your approach for loading data warehouses using an ETL?

This is a open question around the general process used to develop an ETL solution in a production environment. Do they use one ETL to completely load all data, or do they use multiple jobs to load data to a series of staging tables? Do they load each ETL as part of a separate job, then load everything later as part of one job that moves the data from several staging tables into FACT tables.

8. What types of transformations you have worked with in SSIS?

The most common are: Derived Column, Aggregate, Conditional Split, Sort, Lookup, Merge Join and Union All.

9. How can you deploy a new SSIS Package (or Project)?

In previous versions of SSIS it could done either manually (or from BIDS using BIDS Helper) or using scripts. You might prefer SSIS 2012 with Deployment Wizard although you have to deploy the entire project with ALL packages so an extra step to control which packages are changed is recommended.

10. What is the difference between Control Flow and Data Flow?

Control Flow manages the flow of tasks in a package, but Data Flow most frequently moves data from Point A to Point B.

15 SQL Server DBA Interview Questions

[UPDATE: See the updated list of 20 questions here.]

Introduction

There have been several blog posts about SQL Server Database Administrator interview questions. I have listed some examples here for your review, but the best thing you can do to prepare for an interview as someone looking for a job is to know SQL Server databases. If you are performing the interview as someone in the hiring process, think about the specific skills you are looking for and target your questions to help identify the applicants strengths and weaknesses.

1. What is a four-part name?

If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:

Production.AdventureWorks.HumanResources.Employee

2. How do you trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

3. What is a Linked Server?

Another question that can tell you if the candidate has experience with distributed databases is “What is a Linked Server?” A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.

4. Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

5. What happens on checkpoint?

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database. One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

6. What are the dynamic management views and what value do they offer?

The DMV’s are a set of system views new to SQL Server 2005 and beyond to gain insights into particular portions of the engine

7. What are the primary differences between an index reorganization and an index rebuild?

  • A reorganization is an “online” operation by default; a rebuild is an “offline” operation by default
  • A reorganization only affects the leaf level of an index
  • A reorganization swaps data pages in-place by using only the pages already allocated to the index; a rebuild uses new pages/allocations
  • A reorganization is always a fully-logged operation; a rebuild can be a minimally-logged operation
  • A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transactional and must be completed in entirety to keep changes

8.How can you control the amount of free space in your index pages?

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.

9. Name 3 or more DBCC commands and their associated purpose.

  • DBCC CACHESTATS – Displays information about the objects currently in the buffer cache.
  • DBCC CHECKDB – This will check the allocation of all pages in the database as well as check for any integrity issues.
  • DBCC CHECKTABLE – This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
  • DBCC DBREINDEX – This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
  • DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used.
  • DBCC MEMORYSTATUS – Displays how the SQL Server buffer cache is divided up, including buffer activity.
  • DBCC SHOWCONTIG – This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
  • DBCC SHOW_STATISTICS – This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
  • DBCC SHRINKFILE – This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
  • DBCC SQLPERF – This command will show you much of the transaction logs are being used.
  • DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.

10. What authentication modes does SQL Server support?

SQL Server supports Windows Authentication, SQL Server Authentication, and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server.

11. Define candidate key, alternate key, composite key

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

12. What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which usually makes it makes it slow, really slow if there are a large number of rows in the target database. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

13. How do you determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit my SQL Server service packs and versions list.

14. What is ACID?

ACID stands for Atomicity, Consistency, Isolation, Durability. You can read more details here.

15. When And Where Does The Local User Group Meet?

This is a suggestion from Brent Ozar, and I really like the intent of the question. It isn’t important if the candidate goes to every meeting, but they should know that they exist and when they usually meet. It is even better if they attend  on some fairly regular schedule. They won’t know everything and this gives you an idea if they connect to the local community of professionals so if they do run into an issue they know there is somewhere they can go for help besides Google. This will work even for remote or relocating candidates, as they should answer this question about their group in their current city.

BONUS – Why is a manhole cover round?

Sometimes we get so focused to technical questions, we forget that there are questions that aren’t just about SQL Server. Simple questions that aren’t about databases or technology can reveal a lot of information about how a person thinks and reacts to questions they couldn’t have practiced or studied for in advance. There  are several questions you could ask, like the example above, or ask them how many gas stations are open 24 hours a day in the state of Idaho, or how much the Pacific ocean weighs in kilograms. It doesn’t matter so much what the correct answer should be, but how they react to the question, how they describe the process of calculating their response, and the logic they use to justify or defend their answer.

By the way, there are several reasons a manhole cover is round in shape, versus square or triangle:

  1. The heavy metal cover is round so the maintenance worker can easily roll the cover on the smooth edge instead of picking it up when it needs to be moved from place to place.
  2. The cover doesn’t have to be aligned in any specific angle to be placed back onto the exposed manhole. Other shapes would require precise alignment.
  3. A round shape is easily sized so the cover can’t be dropped though the manhole and possibly injure workers under the street. Safety is important.

I once had a job candidate think about the answer for several seconds and respond that the cover is round because the manhole is round, and the manhole is round because the men who use the manhole are also round. While that is a funny answer, it doesn’t indicate they spent much time trying to find the logic in the shape of the original design.

Conclusion

You can find scores of questions on the internet, as well as questions based on your own experiences. You can find some additional questions here, here, and here.

Know How Employers Use Technology to Hire People

So you are looking for a job. Do you understand how companies use technology today when you apply for that new position? Effective job search strategies began changing in the mid-1990s with the appearance of the Internet, and you should adjust your job search skills to match the available technology. In the last few years, the widespread use of search engines and the growth of social media have changed recruiting in ways that are transparent to most job seekers. Not understanding those recent changes will make job hunting more challenging.

Technology Used by Employers

Just as you can use technology to find available jobs, employers can use technology to find information about prospective employees. Employers are using technology in three major ways that are transparent to job seekers:

Social media

Employers compare the resumes and applications submitted by job seekers with what social media shows about them. Do the dates provided, employers listed, job titles shown, education listed, etc, match between your LinkedIn and Facebook pages and the application provided? Do the other social media activities (LinkedIn groups, etc.) support the expertise and accomplishments claimed on the resume? Applicants who lack online validation of the “facts” on their resumes have a handicap and start the process of questioning your qualifications. This is why LinkedIn and Google Plus Profiles can be a job seeker’s best friend in today’s job market.

Search engines background checks

A 2010 study by Microsoft revealed that 80% of employers used search engines to discover information about job applicants. Beyond “social proof” of the resume or application, this research is a quick and cheap version of a basic background check. Searching through social media can help a job seeker by impressing the employer with positive information about activities and accomplishments. It can also hurt the job seeker by uncovering potential problems, inaccurate data, and bad behavior.

Applicant tracking systems (ATS)

Resumes submitted to many employers, particularly large employers, are often stored in a database known as an applicant tracking system. Use of an ATS makes the keywords used in resumes even more important than in the past. A resume which doesn’t contain the “correct” keywords (those the recruiter is using to search through the ATS for qualified applicants) will not be displayed to the recruiter by the ATS. Consequently, without the appropriate keywords in your resume, your resume will not be seen, no matter how qualified you are for the job.

How Job Seekers Use Technology

Job seekers can improve the probability of landing the target job through their own use of technology. Employers expect job seekers to intelligently use current technology as a demonstration of technical savvy and also as a demonstration of appropriate technical skills for today’s workplace. Not leveraging these technologies makes a job seeker look out-of-date, lazy, or both.

Use LinkedIn and Google Plus

Be sure potential employers find good information about you when they do their research. This is where LinkedIn, Facebook, Twitter, and Google Plus can be your best friends. Employers search the Internet for qualified candidates, so create robust social media profiles to ensure that a search on terms like your job title or key skills finds you quickly.

Online Reputation

Be mindful of all public online activity. Job seekers regularly lose out on opportunities because of damaging posts they have made in social media. And, job seekers without their own positive visibility on social media sites are vulnerable to losing opportunities because they look out of date or because of mistaken online identity. Make sure you have a positive online identity and aren’t easily confused with other people with the same or similar names.

Research Employers

Research will make you a more effective candidate and will also hopefully keep you from wasting your time trying to land a job you would hate. What are their products or services? What is their latest news? Who are their officers? Are they doing well or in financial difficulty? Do you know any employees (or know anyone who knows a current employee)? Impress employers with how interested you are in them and their jobs by doing this basic research and including the results in your correspondence.

Customize Resumes

Demonstrate your technical capabilities by customizing your resume to the specific requirements of the job posting you are applying for. Use the employer’s name and job title in the resume – “Objective: [their job title] for [employer name]” for example. Then, trump the ATS by analyzing the keywords used on the job description, and being sure to include the keywords appropriate for you in the resume you submit. This should increase the likelihood that your resume will appear in the ATS search results for that job.

Practice The Interview

Maybe you aren’t the best peaked, or are uncomfortable taking about your self. You might not normally rely on your speaking skills in day-to-day technical activities, but you will need to prepare for the inevitable interview. You will have to answer questions about yourself, explain why you are looking for a new job, describe your technical abilities, make small talk, etc. You want to look confident, intelligent, comfortable, etc. Research the internet and collect some common interview questions, and practice your answers to those questions that make you sound like the best candidate. You can visit sites like GlassDoor to collect real-world examples for questions asked by that specific employer, which might help reduce the potential surprise question that you don’t have a good answer for in advance. Never lie, but you can construct the truth to favor your situation.

 

10 SQL Server DBA Interview Questions

[UPDATE: See the updated list of 20 questions here.]

Introduction

There have been several blog posts about SQL Server Database Administrator interview questions. I have listed some examples here for your review, but the best thing you can do to prepare for an interview as someone looking for a job is to know SQL Server databases. If you are performing the interview as someone in the hiring process, think about the specific skills you are looking for and target your questions to help identify the applicants strengths and weaknesses.

1. What is a four-part name?

If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:

Production.AdventureWorks.HumanResources.Employee

2. How do you trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

3. What is a Linked Server?

Another question that can tell you if the candidate has experience with distributed databases is “What is a Linked Server?” A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.

4. Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

5. What happens on checkpoint?

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database. One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

6. What are the dynamic management views and what value do they offer?

The DMV’s are a set of system views new to SQL Server 2005 and beyond to gain insights into particular portions of the engine

7. What are the primary differences between an index reorganization and an index rebuild?

  • A reorganization is an “online” operation by default; a rebuild is an “offline” operation by default
  • A reorganization only affects the leaf level of an index
  • A reorganization swaps data pages in-place by using only the pages already allocated to the index; a rebuild uses new pages/allocations
  • A reorganization is always a fully-logged operation; a rebuild can be a minimally-logged operation
  • A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transactional and must be completed in entirety to keep changes

8.How can you control the amount of free space in your index pages?

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.

9. Name 3 or more DBCC commands and their associated purpose.

  • DBCC CACHESTATS – Displays information about the objects currently in the buffer cache.
  • DBCC CHECKDB – This will check the allocation of all pages in the database as well as check for any integrity issues.
  • DBCC CHECKTABLE – This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
  • DBCC DBREINDEX – This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
  • DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used.
  • DBCC MEMORYSTATUS – Displays how the SQL Server buffer cache is divided up, including buffer activity.
  • DBCC SHOWCONTIG – This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
  • DBCC SHOW_STATISTICS – This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
  • DBCC SHRINKFILE – This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
  • DBCC SQLPERF – This command will show you much of the transaction logs are being used.
  • DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.

10. What authentication modes does SQL Server support?

SQL Server supports Windows Authentication, SQL Server Authentication, and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server.

Conclusion

You can find scores of questions on the internet, as well as questions based on your own experiences. You can find some additional questions here, here, and here.

%d bloggers like this: