Which command using Query Analyzer will give you the version of
SQL server and operating system?
SELECT
SERVERPROPERTY
(
'productversion'
),
SERVERPROPERTY
(
'productlevel'
), SERVERPROPERTY
(
'edition'
)
What is SQL Server Agent?
SQL Server agent plays an important role in the day-to-day tasks
of a database administrator (DBA). It is often overlooked as one of the main
tools for SQL Server management. Its purpose is to ease the implementation of
tasks for the DBA, with its full-function scheduling engine, which allows you
to schedule your own jobs and scripts.
Can a stored procedure call itself or recursive stored
procedure? How much level SP nesting is possible?
Yes. Because
Transact-SQL supports recursion, you can write stored procedures that call
themselves. Recursion can be defined as a method of problem solving wherein the
solution is arrived at by repetitively applying it to subsets of the problem. A
common application of recursive logic is to perform numeric computations that
lend themselves to repetitive evaluation by the same processing steps. Stored
procedures are nested when one stored procedure calls another or executes
managed code by referencing a CLR routine, type, or aggregate. You can nest
stored procedures and managed code references up to 32 levels.
What is Log Shipping?
Log shipping is the
process of automating the backup of database and transaction log files on a
production SQL server, and then restoring them onto a standby server.
Enterprise Editions only supports log shipping. In log shipping 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 can be used this as the Disaster Recovery plan. The key feature
of log shipping is that it will automatically backup transaction logs throughout
the day and automatically restore them on the standby server at defined
interval.
Name 3 ways to get an accurate count of the number of records in
a table?
SELECT
*
FROM
table1
SELECT
COUNT
(*)
FROM
table1
SELECT
rows
FROM
sysindexes
WHERE
id
=
OBJECT_ID
(
table1
) AND
indid
<
2
What does it mean to have QUOTED_IDENTIFIER ON? What are the
implications of having it OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited
by double quotation marks, and literals must be delimited by single quotation
marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must
follow all Transact-SQL rules for identifiers.
What is the difference between a Local and a Global temporary
table?
A local temporary table exists only
for the duration of a connection or, if defined inside a compound statement,
for the duration of the compound statement.
A global temporary table remains in
the database permanently, but the rows exist only within a given connection.
When connection is closed, the data in the global temporary table disappears.
However, the table definition remains with the database for access when
database is opened next time.
What is the STUFF function and how does it differ from the
REPLACE function?
STUFF function is used
to overwrite existing characters. Using this syntax, STUFF (string_expression,
start, length, replacement_characters), string_expression is the string that
will have characters substituted, start is the starting position, length is the
number of characters in the string that are substituted, and
replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurrences. Using the
syntax REPLACE (string_expression, search_string, replacement_string), where every
incidence of search_string found in the string_expression will be replaced with
replacement_string.
0 comments
Post a Comment