• Sql Server Interview Questions By Shivprasad Koirala

The first installment of this TechRepublic series focused on C# developer interview questions. It generated a lively discussion on the merits of such questions and the different approaches to measuring technical aptitude during an interview process.

• SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31. In this very first blog post – various aspect of the.
• How can the answer be improved?

This week we take a look at SQL Server. These questions (which all apply to SQL Server 2008) are not meant to be the only way to ascertain a candidate's technical expertise during an interview, but rather one piece of the puzzle that may spawn more thorough discussions. The depth of the questions may vary according to the level of the open position; for instance, you would expect more from a senior level candidate than from a junior level candidate.

SQL Server Interview Questions and answers. SQL Server Interview Questions and answers for beginners and experts. List of frequently asked SQL Server Interview Questions with answers by Besant Technologies. We hope these SQL Server Interview Questions and answers are useful and will help you to get the best job in the networking industry.

Note: This content is also available as a downloadable PDF.

Whereas most bands of the late ’70s embraced either punk/new wave or hard rock, The Cars were one of the first bands to do the unthinkable — merge the two styles together. Shake it up dublado mp4 torrent gratis. Add to it bandleader/songwriter Ric Ocasek’s supreme pop sensibilities, and you had an album that appealed to new wavers, rockers, and Top 40 fans. The band jokingly referred to the album as their “true greatest-hits album,” but it’s no exaggeration — all nine tracks are new wave/rock classics, still in rotation on rock radio. Albums in this collection are: The Cars (1978), Candy-O (1979), Panorama (1980), Shake It Up (1981), Heartbeat City (1984), Door To Door (1987).The Cars – The Cars (1978/2014)FLAC (tracks) 24 bit/192 kHz Time – 35:18 minutes 1,34 GBOfficial Digital Download – Source: HDTracks.com Front coverThe Cars’ 1978 self-titled debut, issued on the Elektra label, is a genuine rock masterpiece.

Sql Server Interview Questions By Shivprasad Koirala

What are DMVs?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

Another example is examining current sessions, much like the sp_who2 command:

What are temp tables? What is the difference between global and local temp tables?

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.

Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.

A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.

What are DBCC commands?

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:

DBCC CHECKALLOC — Check disk allocation consistency.

DBCC OPENTRAN — Display information about recent transactions.

DBCC HELP — Display Help for DBCC commands.

What is the difference between truncate and delete?

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.

What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What does the SQL Server Agent Windows service do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

The list of possible questions is endless. I am sure these questions will spawn debate and discussion.