|
|
|
|
|
1. |
What are the types of indexes available with SQL Server? |
| |
There are basically two types of indexes that we use
with the SQL Server. Clustered and the Non-Clustered.
|
|
2. |
When do we use the UPDATE_STATISTICS command? |
|
|
This command is basically used when we do a large
processing of data. If we do a large amount of deletions
any modification or Bulk Copy into the tables, we need
to basically update the indexes to take these changes
into account. UPDATE_STATISTICS updates the indexes on
these tables accordingly.
|
|
3. |
Which TCP/IP port does SQL Server run on? |
|
|
SQL Server runs on port 1433 but we can also change it
for better security.
|
|
4. |
From where can you change the default port? |
|
|
From the Network Utility TCP/IP properties –> Port
number.both on client and the server.
|
|
5. |
What is the use of DBCC commands? |
|
|
DBCC stands for database consistency checker. We use
these commands to check the consistency of the
databases, i.e., maintenance, validation task and status
checks.
|
|
6. |
Can you give me some DBCC command options? |
|
|
(Database consistency check) - DBCC CHECKDB - Ensures
that tables in the db and the indexes are correctly
linked.and DBCC CHECKALLOC - To check that all pages in
a db are correctly allocated. DBCC SQLPERF - It gives
report on current usage of transaction log in
percentage. DBCC CHECKFILEGROUP - Checks all tables file
group for any damage.
|
|
7. |
What command do we use to rename a db? |
|
|
sp_renamedb ‘oldname’ , ‘newname’
|
|
8. |
Well sometimes sp_reanmedb may not work you know because
if some one is using the db it will not accept this
command so what do you think you can do in such cases? |
|
|
In
such cases we can first bring to db to single user using
sp_dboptions and then we can rename that db and then we
can rerun the sp_dboptions command to remove the single
user mode.
|
|
9. |
What is a Join in SQL Server? |
|
|
Join actually puts data from two or more tables into a
single result set.
|
|
10. |
Can you explain the types of Joins that we can have with
Sql Server? |
|
|
There are three types of joins: Inner Join, Outer Join,
Cross Join
|
|
11. |
When do you use SQL Profiler? |
|
|
SQL Profiler utility allows us to basically track
connections to the SQL Server and also determine
activities such as which SQL Scripts are running, failed
jobs etc..
|
|
12. |
What is a Linked Server? |
|
|
Linked Servers is a concept in SQL Server by which we
can add other SQL Server to a Group and query both the
SQL Server dbs using T-SQL Statements.
|
|
13. |
Can you link only other SQL Servers or any database
servers such as Oracle? |
|
|
We
can link any server provided we have the OLE-DB provider
from Microsoft to allow a link. For Oracle we have a
OLE-DB provider for oracle that microsoft provides to
add it as a linked server to the sql server group.
|
|
14. |
Which stored procedure will you be running to add a
linked server? |
|
|
sp_addlinkedserver, sp_addlinkedsrvlogin
|
|
|
|
|
|
15. |
What are the OS services that the SQL Server
installation adds? |
|
|
MS
SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution
transac co-ordinator)
|
|
16. |
Can you explain the role of each service? |
|
|
SQL SERVER - is for running the databases
SQL AGENT - is for automation such as Jobs, DB
Maintanance, Backups
DTC - Is for linking and connecting to other SQL Servers
|
|
17. |
How do you troubleshoot SQL Server if its running very
slow? |
|
|
First check the processor and memory usage to see that
processor is not above 80% utilization and memory not
above 40-45% utilization then check the disk utilization
using Performance Monitor, Secondly, use SQL Profiler to
check for the users and current SQL activities and jobs
running which might be a problem. Third would be to run
UPDATE_STATISTICS command to update the indexes
|
|
18. |
Lets say due to N/W or Security issues client is not
able to connect to server or vice versa. How do you
troubleshoot? |
|
|
First I will look to ensure that port settings are
proper on server and client Network utility for
connections. ODBC is properly configured at client end
for connection ——Makepipe & readpipe are utilities to
check for connection. Makepipe is run on Server and
readpipe on client to check for any connection issues.
|
|
19. |
What are the authentication modes in SQL Server? |
|
|
Windows mode and mixed mode (SQL & Windows).
|
|
20. |
Where do you think the users names and passwords will be
stored in sql server? |
|
|
They get stored in master db in the sysxlogins table.
|
|
21. |
What is log shipping? Can we do logshipping with SQL
Server 7.0 |
|
|
Logshipping is a new feature of SQL Server 2000. We
should have two SQL Server - Enterprise Editions. From
Enterprise Manager we can configure the logshipping. In
logshipping the transactional log file from one server
is automatically updated into the backup database on the
other server. If one server fails, the other server will
have the same db and we can use this as the DR (disaster
recovery) plan.
|
|
22. |
Let us say the SQL Server crashed and you are rebuilding
the databases including the master database what
procedure to you follow? |
|
|
For restoring the master db we have to stop the SQL
Server first and then from command line we can type
SQLSERVER –m which will basically bring it into the
maintenance mode after which we can restore the master
db.
|
|
23. |
What is BCP? When do we use it? |
|
|
BulkCopy is a tool used to copy huge amount of data from
tables and views. But it won’t copy the structures of
the same.
|
|
24. |
What should we do to copy the tables, schema and views
from one SQL Server to another? |
|
We
have to write some DTS packages for it.
|
