FAQ on Tools in SQL Server
133. Have you ever used DBCC command? Give an example for it.
The Transact-SQL programming language provides DBCC statements that
act as Database Console Commands for Microsoft® SQL Serve 2000. These
statements check the physical and logical consistency of a database.
Many DBCC statements can fix detected problems. Database Console
Command statements are grouped into these categories.
Statement category Perform
Maintenance statements Maintenance tasks on a database, index, or
filegroup.
Miscellaneous statements Miscellaneous tasks such as enabling
row-level locking or removing a dynamic-link library (DLL) from memory.
Status statements Status checks.
Validation statements Validation operations on a database, table,
index, catalog, filegroup, system tables, or allocation of database pages.
DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc.
134. How do you use DBCC statements to monitor various aspects of a
SQL server installation?
135. What is the output of DBCC Showcontig statement?
Displays fragmentation information for the data and indexes of the
specified table.
136. How do I reset the identity column?
You can use the DBCC CHECKIDENT statement, if you want to reset or
reseed the identity column. For example, if you need to force the
current identity value in the jobs table to a value of 100, you can
use the following:
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO
137. About SQL Command line executables
Utilities
bcp
console
isql
sqlagent
sqldiag
sqlmaint
sqlservr
vswitch
dtsrun
dtswiz
isqlw
itwiz
odbccmpt
osql
rebuildm
sqlftwiz
distrib
logread
replmerg
snapshot
scm
regxmlss
138. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a
transaction manager that allows client applications to include several
different sources of data in one transaction. MS DTC coordinates
committing the distributed transaction across all the servers enlisted
in the transaction.
139. What is DTS? Any drawbacks in using DTS?
Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a
set of graphical tools and programmable objects that lets you extract,
transform, and consolidate data from disparate sources into single or
multiple destinations.
140. What is BCP?
The bcp utility copies data between an instance of Microsoft® SQL
Server™ 2000 and a data file in a user-specified format.
C:\Documents and Settings\sthomas>bcp
usage: bcp {dbtable query} {in out queryout format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
141. How can I create a plain-text flat file from SQL Server as input
to another application?
One of the purposes of Extensible Markup Language (XML) is to solve
challenges like this, but until all applications become XML-enabled,
consider using our faithful standby, the bulk copy program (bcp)
utility. This utility can do more than just dump a table; bcp also can
take its input from a view instead of from a table. After you specify
a view as the input source, you can limit the output to a subset of
columns or to a subset of rows by selecting appropriate filtering
(WHERE and HAVING) clauses.
More important, by using a view, you can export data from multiple
joined tables. The only thing you cannot do is specify the sequence in
which the rows are written to the flat file, because a view does not
let you include an ORDER BY clause in it unless you also use the TOP
keyword.
If you want to generate the data in a particular sequence or if you
cannot predict the content of the data you want to export, be aware
that in addition to a view, bcp also supports using an actual query.
The only "gotcha" about using a query instead of a table or view is
that you must specify queryout in place of out in the bcp command line.
For example, you can use bcp to generate from the pubs database a list
of authors who reside in
bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout
c:\CAauthors.txt -c -T -S
142. What are the different ways of moving data/databases between
servers and databases in SQL Server?
There are lots of options available, you have to choose your option
depending upon your requirements. Some of the options you have are:
BACKUP/RESTORE, detaching and attaching databases, replication, DTS,
BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT
scripts to generate data.
143. How will I export database?
Through DTS - Import/Export wizard
Backup - through Complete/Differential/Transaction Log
144. How to export database at a particular time, every week?
Backup - Schedule
DTS - Schedule
Jobs - create a new job
145. How do you load large data to the SQL server database?
bcp
146. How do you transfer data from text file to database (other than DTS)?
bcp
147. What is OSQL and ISQL utility?
The osql utility allows you to enter Transact-SQL statements, system
procedures, and script files. This utility uses ODBC to communicate
with the server.
The isql utility allows you to enter Transact-SQL statements, system
procedures, and script files; and uses DB-Library to communicate with
Microsoft® SQL Server™ 2000.
All DB-Library applications, such as isql, work as SQL Server
6.5–level clients when connected to SQL Server 2000. They do not
support some SQL Server 2000 features.
The osql utility is based on ODBC and does support all SQL Server 2000
features. Use osql to run scripts that isql cannot run.
148. What Tool you have used for checking Query Optimization? What is
the use of profiler in sql server? What is the first thing u look at
in a SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to
monitor events in an instance of Microsoft® SQL Server™. You can
capture and save data about each event to a file or SQL Server table
to analyze later. For example, you can monitor a production
environment to see which stored procedures is hampering performance by
executing too slowly.
Use SQL Profiler to:
• Monitor the performance of an instance of SQL Server.
• Debug Transact-SQL statements and stored procedures.
• Identify slow-executing queries.
• Test SQL statements and stored procedures in the development phase
of a project by single-stepping through statements to confirm that the
code works as expected.
• Troubleshoot problems in SQL Server by capturing events on a
production system and replaying them on a test system. This is useful
for testing or debugging purposes and allows users to continue using
the production system without interference.
Audit and review activity that occurred on an instance of SQL Server.
This allows a security administrator to review any of the auditing
events, including the success and failure of a login attempt and the
success and failure of permissions in accessing statements and objects
For More SQL SERVER Frequently Asked Interview Questions
No comments:
Post a Comment