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

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



What result would you expect from this statement?
Select * from dbo
.MyTable

All records from [dbo].[MyTable]
The answer is all records from [dbo].[MyTable]. Qualified names can include white space before and after the dots. Restrictions apply only to the names themselves that may need quoted identifiers with embedded white space or invalid characters.

You are using SQL Server 2005 and you wish to find a number of details about stored procedures created. The details include date and time, the server that created the stored procedure, the login name and the contents. How would you do this?
Create a DDL trigger on CREATE_PROCEDURE database event
The answer is Create a DDL trigger on CREATE_PROCEDURE database event You can find the answer on page 448 of Beginning SQL Server 2005 for Developers: From Novice to Professional

A .NET assembly running with UNSAFE permissions can do which of the following?
All of the above
CLR code running with UNSAFE permissions can access virtually any resource inside or outside of SQL Server without restriction. This is one reason only a sysadmin can create an assembly as UNSAFE.

What does the SQL Writer service do?
Handles Volume Shadow Service Copy functions.
The SQL Writer service was introduced to handle access to the data files in SQL Server. It allows backup programs, like VSS, to function while SQL Server is still running.

Which key provides the strongest encryption?
AES (256 bit)
The longer the key, the better the encryption, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.

You are delegating permissions on your SQL Server 2005 server to other administrators. You have local, single server jobs on one server that you would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign?
SQLAgentUserRole
SQL Server 2005 provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allows the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.

What level of permissions does a user need to create an assembly with UNSAFE permissions?
sysadmin
Only a sysadmin can create assemblies that are specified as unsafe.

Which optional SQL Server component must you install to ensure that you can create and execute maintenance plans?
SQL Server Integration Services
You must install Integration Services in order to properly create and execute maintance plans since these plans are a subset of SSIS packages.

You have tightly secured access to your SQL Server 2005 server by placing it behind a firewall and limited ports 1433 to those clients that need to directly access the SQL Server. One of your developers complains about connecting to the Integration Services esrvice on your server. What could be the problem if you know the service is working properly?
The firewall needs to allow port 135 through for Integration Services
Integration Services requires access to both DCOM and port 135. You need to open this port to allow the developer to communicate with SSIS.

In SQL Server 2005, which network protocols use endpoints?
All of the above
All communication points with SQL Server using network protocols use an endpoint. SQL Server creates defaults for each protocol that you enable.

You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?
Log and data files for your databases as well as the Quorum drive and the backup folders.
Anti-virus programs can exist on the same server as SQL Server without an issue if you exclude certain items. The database data and log files, backup files and folders, the quorum drive for clusters, temporary replication files, the SQL Server log files, and Log Shipping folders/files. You do not need to exclude that SQL Server executables and probably do not want to so that they are protected.

What port do you need to open on your server firewall to enable named pipes connections?
Port 445
Named pipes communicates across TCP port 445.

When the network service built-in account connects to a network resource, what is the context?
The computer account of the Windows installation.
The network service account connects to network resources as the computer account for the Windows installation.

A certificate is like which type of key in SQL Server 2005 encryption?
An Asymmetric Key
A certificate is like an asymmetric key and can validate the identity of the issuer.

How many connections are available in SQL Server MSDE 2000?
Unlimited, no different from the other SQL Server 2000 editions.
The answer is actually an unlimited number of connections, though this in a practical sense is limited by memory on the machine (The actual limitation is 32k connecitons). Instead there is a governer that kicks in to limit things to 8 operations, of which 3 are system operations in some cases. But these are operations, not connections.

What will be the result of the last SELECT statement if you execute the T-SQL script below as a sysadmin.
USE master
GO
IF DB_ID('test_schema') IS NOT NULL
DROP DATABASE test_schema
GO
CREATE DATABASE test_schema
GO
USE test_schema
GO
CREATE SCHEMA Schema1
CREATE TABLE Table1 (col1 int)
GO
CREATE SCHEMA Schema2
GO
CREATE TABLE Table1 (col1 int)
GO
SELECT SCHEMA_NAME(schema_id), name FROM sys.objects
WHERE name = 'Table1'

Schema1 Table1
dbo Table1
The correct answer is A. You can combine the CREATE SCHEMA with a CREATE TABLE. You can even specify GRANTs or DENYs. Search Books online for CREATE SCHEMA.