Assorted Questions from SQL Server Forums

1. Writing query without using DISTINCT keyword:

Below is my table data.I want the result as 3.

col1
------
4
4
66
66
88

Result as
count
----
3

How to write a Query without using Distinct. Because I am running this query in SQL Mobile. In Sql Mobile edition there is no "distinct" keyword support.

The solution is as follows:

CREATE TABLE Table1(Col1 int);

INSERT INTO Table1
SELECT (4)
  UNION ALL SELECT (4)
  UNION ALL SELECT (66)
  UNION ALL SELECT (66)
  UNION ALL SELECT (88);

SELECT COUNT(*) FROM 
              (SELECT 0 AS c1
               FROM Table1 GROUP BY Col1) AS t1;
2. Which is better SP or Views?

Asking which is faster or better is like comparing the speed of a car with that of a boat - the speed difference is irrelevant, since you'll always prefer the boat if you travel over water, and always the car for travels over land.


VIEW: A view is a "virtual" table consisting of a SELECT statement, by means of "virtual"
I mean no physical data has been stored by the view -- only the definition of the view is stored inside the database; unless you materialize the view by putting an index on it.

1. By definition you can not pass parameters to the view.
2. NO DML operations (e.g. INSERT, UPDATE, and DELETE) are allowed inside the view; ONLY SELECT statements.

Most of the time, view encapsulates complex joins so it can be reusable in the queries or stored procedures. It can also provide level of isolation and security by hiding sensitive columns from the underlying tables.

Stored Procedure: A stored procedure is a group of Transact-SQL statements compiled into a single execution plan or in other words saved collection of Transact-SQL statements.

Here is a good summary from SQL MVP Hugo Kornelis (was posted in a newsgroup few years ago)
　
A stored procedure:

• accepts parameters
• can NOT be used as building block in a larger query
• can contain several statements, loops, IF ELSE, etc.
• can perform modifications to one or several tables
• can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

　
A view:

• does NOT accept parameters
• can be used as building block in a larger query
• can contain only one single SELECT query
• can NOT perform modifications to any table
• but can (sometimes) be used as the target of an INSERT, UPDATE or DELETE statement.

　

>>Also is view plans are in plan cache or not? where are SPs plans are stored in cache and are always >>best for performance boost?

>>where are SPs plans are stored in cache and are always best for performance boost?

Yes execution plan for sps are stored in "plan cache" and in general it can boosts performance..

3. Stored Procedure - Standards and Best practices checking

Looking for an utility which can review stored .procedures for Standards, best practices etc. Env is SQL Server 2005 I can offer some manual hints:

4. Return only a single result set - unlike sp_monitor

http://www.sqlusa.com/sqlformat/http://www.sqlusa.com/bestpractices/training/scripts/parametersniffing/

1. Naming: prefix with "usp" or "sproc" - for example, uspInventoryUpdate

2. Add sproc comment block after "AS"

3. Make it safe from parameter sniffing:

5. Avoid nesting stored procedures because error control is difficult

6. Comment where needed

7. Format code for readability - auto formatter:

Simple Questions on Stored Procedures

The following mind map diagrammatically briefs about stored procedures. For an in-depth learning about the concepts, do search books on-line topics for stored procedures.

Questions:

1. Which of the following commands, a stored procedure can execute?

(a) USE
(b) Set Showplan_Text on
(c) Set Showplan_All on
(d) Both (b) and (C)
(e) None of the above.


2. You have noticed that a stored procedure is recompiled on each execution. The cause of recompilation is a simple query statement. How will you optimize the performance of your stored procedure with minimum effort?

(a) Create an additional stored procedure, and include the query that causes the recompilation. Call the new stored procedure from the new one.
(b) Add the RECOMPILE query hint to the query statement that causes the recompilation.
(c) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(d) Use the sp_recompile system stored procedure to force the recompilation of your stored procedure the next time it runs.


3. Which one of the following option regenerates the query plan each time a stored procedure is executed?

(a) Recompile
(b) Encryption
(c) Execute As
(d) Varying


4. Which of the following can be used to recompile a stored procedure everytime it is running? (Choose all that apply.)

(a) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(b) Add the RECOMPILE query hint to one of the stored procedure statements.
(c) Use the sp_recompile system stored procedure.
(d) Specify the WITH RECOMPILE option when you execute the stored procedure.


5. Your RTSdev database alerts you that the transaction log file is almost full. You suspect that one of the stored procedures has left a transaction open. Which one of the following can be used to ensure it?

(a) Execute DBCC TLOGFULL against the RTSdev database.
(b) Execute DBCC OPENTRAN against the temp database.
(c) Execute DBCC TLOGFULL against the temp database.
(d) Execute DBCC OPENTRAN against the RTSdev database.


6. The following script is used to create a stored procedure:

DECLARE @UserID int
BEGIN TRY
 BEGIN TRANSACTION
  DELETE From dbo.Users
  WHERE UserID = @UserID
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 DECLARE @errMsg nvarchar(300)
 SELECT @errMsg = ERROR_MESSAGE()
END CATCH

When execute the SP, you observe that it leaves open transactions. How will you modify the stored procedure?

(a) Add a ROLLBACK TRANSACTION to the CATCH block.
(b) Add a ROLLBACK TRANSACTION to the TRY block.
(c) Add a COMMIT TRANSACTION to the CATCH block.
(d) Add a COMMIT TRANSACTION to the TRY block.


7. If you need to create a stored procedure to perform some calculation-intensive operations and then return the results as quickly and efficiently as possible. How will you achieve that?

(a) Use parameters within the stored procedures.
(b) Use a TRY CATCH block within the stored procedure.
(c) Use a CLR-integrated stored procedure.
(d) Create the stored procedure so it recompiles each time it is run.


Show Answers:

1. (a)
2. (b)
3. (a)
4. (a), (d)
5. (d)
6. (a)
7. (c)


Hide Answers (X).