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: