- SET NOCOUNT ON should be the first statement in every stored procedure you create because it minimizes network traffic between SQL Server and client applications.
Setting NOCOUNT on disables DONE_IN_PROC messages—the messages SQL Server normally sends to the client indicating the number of rows affected by a T-SQL statement.
Because these messages are very rarely used, eliminating them conserves network bandwidth without really giving up any functionality and can speed up applications considerably. Note that you can disable DONE_IN_PROC messages for the entire server via a trace flag (3640) and for a particular user session via the sp_configure 'user options' command. (In rare circumstances, disabling DONE_IN_PROC messages can cause problems with some applications—for example, some older versions of Microsoft Access and certain ill-behaved OLEDB providers). - A procedure can contain any valid Transact-SQL command except these: CREATE DEFAULT, CREATE FUNCTION, CREATE PROC, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, SET SHOWPLAN_TEXT, and SET SHOWPLAN_ALL. These commands must reside in their own command batches, and, therefore, can't be part of a stored procedure. Procedures can create databases, tables, and indexes, but not other procedures, defaults, functions, rules, schemas, triggers, or views.You can work around this limitation—the inability to construct most other kinds of objects from within a stored procedure—by constructing a T-SQL string and executing it via sp_executesql or the EXEC() function like :
SET @sql=N'create proc dbo.test2 as select ''1'''EXEC dbo.sp_executesql @sql
- Although executing a stored procedure can be as easy as listing it on a line by itself in a T-SQL command batch, you should make a habit of prefixing all stored procedure calls with the EXEC keyword, like this: EXEC dbo.sp_who
- You should also be sure to owner-qualify procedure calls ("dbo" in the previous example). Omitting the owner from a procedure call causes SQL Server to momentarily place a compile lock on the procedure because it cannot locate it immediately in the procedure cache. This lock is released once the procedure-sans-owner is located in the cache, but can still cause problems in high-throughput environments. Owner-qualifying objects is simply a good habit to get into. It's one of those things you can do to save yourself problems down the road.
2 comments:
Subqueries Optimization
As a good rule of thumb, try to replace all subqueries with joins. The optimizer may
sometimes automatically flatten out subqueries and replace them with regular or outer
joins. But it doesn’t always do a good job at that. Explicit joins give the optimizer more
options to choose the order of tables and find the best possible plan. When you
optimize a particular query, investigate if getting rid of subqueries makes a difference.
Example
The following queries select the store names and the number of books sold in each
store. Both queries return the same result set, but the first one uses a subquery, while
the second employs an outer join. Compare the query plans produced by Microsoft
SQL Server.
Subquery Solution Join Solution
SELECT st.stor_name AS 'Store',
(SELECT SUM(bs.qty)
FROM big_sales AS bs
WHERE bs.stor_id = st.stor_id)
AS 'Books Sold'
FROM stores AS st
WHERE st.stor_id IN
(SELECT DISTINCT stor_id
FROM big_sales)
Second solution:
SELECT st.stor_name AS 'Store',
SUM(bs.qty) AS 'Books Sold'
FROM stores AS st
JOIN big_sales AS bs
ON bs.stor_id = st.stor_id
WHERE st.stor_id IN
(SELECT DISTINCT stor_id
FROM big_sales)
GROUP BY st.stor_name
-Gyan
U r write but in some cases subquries work very fine as compared to joins.
Simple example u can take of linked server where data is moved from one serer to other.
Think hard on this one....
Post a Comment