Answer Posted / rathi

a stored procedure is like a subroutine. It is used to
perform tasks within the database, whether it be to INSERT,
UPDATE, DELETE, SELECT, send return values, send output
parameters, send e-mail, call command line arguments,
encapsulate business logic, enforce data integrity, or any
combination thereof. Here is a fictitious example:

CREATE PROCEDURE dbo.doStuff
@dt SMALLDATETIME
AS
BEGIN
SET NOCOUNT ON

DECLARE @un SYSNAME,
@now SMALLDATETIME

SET @un = SUSER_SNAME()
SET @now = GETDATE()

BEGIN TRANSACTION

INSERT dbo.myLog
(
ProcName,
UserName,
dt
)
SELECT
'doStuff',
@un,
@now

DELETE dbo.myLog
WHERE dt < (@now-7)

UPDATE dbo.Users
SET LastActivity = @now
WHERE UserName = @un

COMMIT TRANSACTION

SELECT TOP 3 ProcName, dt
FROM MyLog
WHERE UserName = @un
ORDER BY dt DESC

DECLARE @subject VARCHAR(255)
SET @subject = @un + ' used the doStuff procedure.'

EXEC master..xp_smtp_sendmail
@from = 'foo@bar.com',
@to = 'bar@foo.com',
@server = 'mail.myserver.com',
@subject = @subject

DECLARE @cmd VARCHAR(255)
SET @cmd = 'del c:\users\'+@un+'\archive\*.log'
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

RETURN 0
END
GO

Stored procedures accept parameters, and are the preferred
method of both manipulating data and simply returning data.
They are compiled when first run, and the query plans are
stored and cached by SQL Server's optimizer, and those
cached plans are swapped out depending on frequency of
usage. Generally, a stored procedure will perform faster
than an ad hoc query, but there are certain cases (e.g.
when a bad plan is cached) that this is not the case.

User-Defined Functions (UDFs)

In general, UDFs can be a serious source of performance
issues. Also, UDFs cannot be used for DML operations
(INSERT/UPDATE/DELETE), cannot use non-deterministic
functions ,cannot use dynamic SQL, and cannot have error-
handling (e.g. RAISERROR).

Explain what is “asynchronous” communication in sql server service broker?

586

---

Can you explain what are the restrictions applicable while creating views? : SQL Server Architecture

573

---

What is row_number () and partition by in sql server?

620

---

What do you understand by replication in sql server? Mention the different types of replication in sql server.

599

---

How to disable a login name in ms sql server?

674

---

---

what are triggers? : Sql server database administration

572

---

What are the types of subscriptions in SQL Server replication?

629

---

What is a not null constraint?

676

---

What is the data tier application?

153

---

What do you understand by intent locks?

662

---

What command would you use to add a column to a table in sql server?

858

---

What is a primary index?

620

---

What are the types of indexing?

697

---

You have to store user responses of ‘yes’ and ‘no’ what kind of data type is best suited for this task?

611

---

Does any body please help me what question's have asked for SSRS in the interview?

1757

---