Microsoft SQL Server Interview Questions And Answers (SET -8)

Microsoft SQL Server Interview Questions And Answers (SET -8)



What What does the term ACID refer to?
An acronym that describes concurrency support in a database
The answer is an acronym that describes concurrency support in a database. ACID is an acronym for the following four terms: * Atomicity - Every operation within a transaction is treated as a singular operation; either all of its data modifications are performed, or none of them is performed.
* Consistency - Once the transaction is completed, the system must be left in a consistent state.
* Isolation - It means that the operations within a transaction must be suitably isolated from other transactions. In other words, no other transactions should see data in the intermediate state, within the transaction, until it is finalized. This is done by using locks.
* Durability - Once a transaction is competed, all changes must be persisted as requested. The modifications should persist in total even in the event of a system failure.

What does this return?
SELECT USER_NAME() -- Returns Andy
EXECUTE AS TechPreparation
GO
EXECUTE AS Steve
GO
REVERT
GO
SELECT USER_NAME()
TechPreparation
The execution context switches can be nested, so changing to TechPreparation, then Steve, then issuing a Revert will return you to the context of TechPreparation. This works in the same manner as a stack.

In building a PDF report in Reporting Services 2005, images that are originally stored in jpg format are rendered in jpg format. What format are images rendered in if they were stored originally in another format?
png
Images stored originally in formats other than jpg are rendered in png format.

What does the tablediff utility do?
This utility will report the differences in data between two tables.
This utility will report the data differences between two tables. They must be alike in structure and give back the T-SQL to bring the data in the tables to be the same for most datatypes.

Which severity levels of errors can be corrected by a user (as opposed to those that an administrator or Microsoft must work to fix.)
11-16
Severity levels 11-16 are correctable by users. Below 11, these are informational warnings and not raised as system errors. Errors above 16 must have an administrator or Microsoft correct.

What is the granularity in seconds of the timestamp datatype?
The timestamp datatype does not store time values.
The answer is the timestamp datatype does not store time values. The better name for this datatype is rowversion, and it is actually a database-wide unique varbinary value. When you have a rowversion column in a table, the value of the rowversion column changes for each modification to each row. The value in the rowversion column is guaranteed to be unique across all tables in the datatype.

You are setting up the backup scheme for your SQL Server 2005 server and want to setup nightly full backups and hourly log backups in the Maintenance Plans subsystem. How many plans must you setup?
2
Since you have two separate schedules for the maintenance operations, you will need to have two separate plans to handle this need. Each plan can only be executed on one schedule, so one is needed for a single daily execution, the nightly full backups, and another for the hourly log backups.

You have installed one new assembly on your SQL Server 2005 server and are wondering if it is being used on the production server. How can you easily monitor to see if this assembly is being used?
You can monitor the Assembly Load event in a trace.
While you could scan trace results for the names of functions and procedures using the assembly, it is possible that you may not know all the places the assembly is being called from. There is a CLR Load event class that will capture an event when a CLR class is executed.

You wish to ensure you can recover your SQL Server 2005 database to a point in time if necessary. Which recovery models can you use?
Full
Only the Full recovery model supports point in time restoration of your database.

Does a root element in an XML document necessarily contain all the content for a well-formed document?
Yes
By definition, the root element is required in a well-formed XML document and it contains all other content nested inside it.

Which of the following datatypes can be represented in a SQL_VARIANT datatype?
None of the above.
The SQL Variant type can store all datatypes except varchar(max), varbinary(max), xml, text, ntext, rowversion/timestamp (thought the data of a rowversion can be stored in a binary(8), which can be stored in a sql_variant) and sql_variant (it may seem strange that you can’t store a variant in a variant, but all this is saying is that the sql_variant data type doesn’t actually exist as such—SQL Server chooses the best type of storage to store the value you give to it).

What's the difference between a server login and a database user?
A server login connects an account to the server. A database user the link from the server login to a defined database .
You need to create a server login using CREATE LOGIN before you can then link it to a database using a database user with CREATE USER. The server login logs into the server and grants access to any server wide permissions. The server login is then mapped to a database user for access to database objects.

For regulatory reasons, you must maintain an exact text copy of your XML documents in your SQL Server 2005 database. What datatype of storage should you choose?
varchar(max)
The XML data type changes the representation of your XML document into an internal, optimized version that has all the content, but may not maintain the original text. The varchar(max) or nvarchar(max) represenations should be used in this case.

What is a bookmark lookup?
An operation where the row in the heap or clustered index is found from the bookmark in the index.
A bookmark ID is stored in an index and points back to the actual row in the heap or clustered index of the table. The lookup operation occurs when an index satisfies a search and the actual row is retrieved from the table.

Yesterday's QOD How are comments denoted in an XML document?
With the markers.
Comments in an XML document are denoted just as they are in HTML with the markers.

How secure are encrypted stored procedures, triggers and views?
They are not really secure, that are only obfusticated. There are tools that you can use to get at the code.
The code itself is just obfusticated. However, you cannot edit the code, the stored procedure or view would have to be dropped and re-created. By combing this fact with a DDL trigger that executes on object creation you can tell when it has been modified.

You have created a new assembly and want to test it on SQL Server 2005. You install the developer edition with defaults on your workstation and create the assembly as a sysadmin. You then write a function to use this assembly, but it does not work. What could be the problem?
The CLR environment needs to be enabled.
By default on SQL Server 2005, the CLR runtime environment, which is what executes the .NET assemblies, is not enabled. You need to execute the following code to enable it:

The Service Broker operates on messages in which fashion?
Like a queue, first in, first out.
Messages transferred in Service Broker work in a queue fashion with the first message being sent being the first processed.

You have a user that agrees to take over some of the database administration for your SQL Server 2005. They will be in charge of granting access to one particular database used by the time card application for server logins. What security role should you assign them?
db_accessadmin
This user will not add logins, but rather grant database level access for existing logins, therefore the minimum role they need is the db_accessadmin fixed database role. This allows them the ability to add access for Windows logins, Windows group logins, or SQL Server logins.