techpreparation-homepage

Home  Interview Questions  Certifications  Aptitude Questions  Tutorials  Placement Papers  Search  Resume  Soft Skills  Video  Forum  Blog

Android app on Google Play

Technical Interview Questions
Oracle Interview Questions
MySql Interview Questions
MsSql Interview Questions
JDBC Interview Questions
                              .........More

Soft Skills
Communication Skills
Leadership Skills
                              .........More

 

 

  

Microsoft SQL Server Interview Questions and Answers



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.

You are considering consolidating a number of your existing SQL Servers onto a new, large SQL Server 2005 server. You have 3 SQL Server 6.5 servers, 6 SQL Server 7.0 servers, and 16 SQL Server 2005 servers. You cannot make any application changes, but you can size a large SQL Server 2005 server to handle the disk, memory, and CPU needs of the existing servers as well as set compatibility modes. How many of these servers can you consolidate?
25
In SQL Server 2005, you can handle a large number of transactions, which makes it a good choice for consolidating servers. It includes compatibility modes for SQL Server 6, 6.5, 7 and 2000. Therefore you can consolidate all the servers, 25 of them, onto the new server.

To maintain the best performance of your SQL Server 2005 databases, how should you deploy encryption technologies?
Encrypt data with a symmetric key and secure all keys with asymmetric keys.
For performance reasons, you should encrypt data using a symmetric key and secure that key with an asymmetric key.

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.

Page Numbers :  1       2        3       4       5       6       7       8        9       10         11       12       13       14       15       16

Have a Question ? post your questions here. It will be answered as soon as possible.

Check Job Interview Questions for more Interview Questions with Answers