Question { Merrill Lynch, 14676 }

What is one of the first things you would do to increase
performance of a query? For example, a boss tells you that
?a query that ran yesterday took 30 seconds, but today it
takes 6 minutes?

Answer

Can u check the folliwing points are used in the procedure
or a Query.

Table should have primary key
Table should have minimum of one clustered index
Table should have appropriate amount of non-clustered index
Non-clustered index should be created on columns of table
based on query which is running
Following priority order should be followed when any index
is created a) WHERE clause, b) JOIN clause, c) ORDER BY
clause, d) SELECT clause
Do not to use Views or replace views with original source
table
Triggers should not be used if possible, incorporate the
logic of trigger in stored procedure
Remove any adhoc queries and use Stored Procedure instead
Check if there is atleast 30% HHD is empty - it improves
the performance a bit
If possible move the logic of UDF to SP as well
Remove * from SELECT and use columns which are only
necessary in code
Remove any unnecessary joins from table
If there is cursor used in query, see if there is any other
way to avoid the usage of this (either by SELECT … INTO or
INSERT … INTO, etc)
There are few hardware upgrades can be considered as well
like separating index on different disk drive or moving
tempdb to another drive. However, I am not suggesting them
here as they are not quick way to improve the performance
of query.

Question { 11427 }

Explain sp_configure commands, set commands?

Answer

sp_configure :
The xp_cmdshell option is a server configuration option
that enables system administrators to control whether the
xp_cmdshell extended stored procedure can be executed on a
system.
—- To allow advanced options to be changed.

EXEC sp_configure ’show advanced options’, 1

GO
—- To update the currently configured value for advanced
options.

RECONFIGURE

GO

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

—- To update the currently configured value for this
feature.

RECONFIGURE
GO
SET:
This command is used to set the values to the variables
ex:
declare @id int
set @id=2

Question { NIIT, 49937 }

Difference between sql server 2000 and sql server 2005?

Answer

Sql server 2005 New features:
1.Datatype varchar(max),XML datatype
2.OUTPUT Clause
3.APPLY OPERATOR
4. PIVOT and UNPIVOT?
A Pivot Table can automatically sort, count, and total the
data stored in one table or spreadsheet and create a second
table displaying the summarized data. The PIVOT operator
turns the values of a specified column into column names,
effectively rotating a table.

5.Tools and utilities
Data types
T-SQL enhancements
Programmability enhancements
XML support
Native XML web services
SQL Management Objects
SQL Server Integration Services
SQL Server Reporting Services
SQL Server Notification Services
SQL Server Service Broker
Replication Management Objects
SQL Server Agent
SQL Server Mobile Edition
The code used in the book is available

In sql server2000 Extended Stored procedures , and it
modified it into CLR Stored procedures

Question { Satyam, 4487 }

how do u do Performance tunning ?

Answer

• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-
clustered index
• Non-clustered index should be created on columns of
table based on query which is running
• Following priority order should be followed when
any index is created a) WHERE clause, b) JOIN clause, c)
ORDER BY clause, d) SELECT clause
• Do not to use Views or replace views with original
source table
• Triggers should not be used if possible,
incorporate the logic of trigger in stored procedure
• Remove any adhoc queries and use Stored Procedure
instead
• Check if there is atleast 30% HHD is empty - it
improves the performance a bit
• If possible move the logic of UDF to SP as well


• Remove * from SELECT and use columns which are only
necessary in code
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is
any other way to avoid the usage of this (either by SELECT
… INTO or INSERT … INTO, etc)
There are few hardware upgrades can be considered as well
like separating index on different disk drive or moving
tempdb to another drive. However, I am not suggesting them
here as they are not quick way to improve the performance
of query.

Question { HCL, 29864 }

What is cursor ? And what is difference between Trigger ?

Answer

Cursor is an Database object and retrive the rows from
database row by row ,
And it is maily use for ot reduce the network traffic
it contains 5 features
1.DECLARE the Cursor
2.OPEN the cusrsor
3.FETCH the cursor
4.CLOSE the cursor
5.DEALLOCATE the cursor

TRIGGER : IT is also an database object and You can
perform an anction Trigger will fire automatacallly.
like (DML,DDL)
They can cascade changes through related tables in the
database; however, these changes can be executed more
efficiently using cascading referential integrity
constraints.

They can guard against malicious or incorrect INSERT,
UPDATE, and DELETE operations and enforce other
restrictions that are more complex than those defined with
CHECK constraints.

Multiple DML triggers of the same type (INSERT, UPDATE, or
DELETE) on a table allow multiple, different actions to
take place in response to the same modification statement.

Question { 4803 }

How To Make password Protected SQL Server 2005 Database
i.e when i open SQL Server database then ask for password.

Answer

To some extent what you can/should do to restrict direct
access depends on you application and deployment
requirements, and what type of direct access you are trying
to prevent. For instance, if you are sole administrator of
all machines that the app will be deployed on, you may be
able to restrict access by locking down the SA password and
dissallowing access to the DB files via the filesystem.
If, on the other hand, you are not the admin, it will be
pretty much impossible to completely block direct access
against a determined admin. The databases can always be
copied via the filesystem to another machine with a useable
SA account.

Even if you are not the admin of the machine, there are
some things you can do to avoid casual access outside of
the app itself. Here is an article that talks about
securing your connection string:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnnetsec/html/THCMCh14.asp

You can also use an application role for server-side
permissions. See:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_security_89ir.asp

Question { 7966 }

How to find out name of all employees who has salary less
than 200 Rs.?

Answer

select Emp_Name from emptable where sal < 200

Question { 3017 }

Can I know,how to Execute Funcion and Trigger through
command(Manualy Execution) in MS SQL/SERVER 2005,give me
answer with Example.

Answer

Function can excute by the SELECT statement
ex SELECT Functionname(parameters), select *from
functionname

select s_func(Parameters)

We cant execute the trigger ,
When we perform a DML(DELETE,INSERT,UPDATE) operation
Trigger will fire automatically

Question { Geo Research Centre, 7114 }

what is the difference between Delete and Truncate

Answer

What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate
will actually remove all the rows from a table and there
will be no data in the table after we run the truncate
command.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction
log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages
used to store the table’s data, and only the page
deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table
structure and its columns, constraints, indexes and so on
remain. The counter used by an identity for new rows is
reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a
FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a
trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.



DELETE
DELETE removes rows one at a time and records an entry in
the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE
instead. If you want to remove table definition and its
data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.