Dot Guru

SPs are Databse subroutine 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. 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. View: • To hide the complexity of the underlying database schema, or customize the data and schema for a set of users. • To control access to rows and columns of data. • To aggregate data for performance. UDFS: Set of statements which must return an object considered as function. 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)....

Multiple Active Result Sets - MARS In a nutshell, it is the ability to have more than one pending request under a given SQL Server connection. For most cases this will directly translate to the ability to have more than one default result set (firehose cursor) outstanding while other operations can execute within the same session. It is probably as important to delimit what MARS is not: Parallel execution: Though MARS enables more than one request to be submitted under the same connection, this does not imply that they will be executed in parallel inside the server. MARS will multiplex execution threads between outstanding requests in the connection, interleaving at well defined points. Cursor replacement: As described earlier, there are some scenarios where cursors represented a suitable workaround for a lack of MARS; it may be valid to migrate those scenarios to use MARS. However, this does not imply that all current usages of cursors should be moved to MARS. By default, all o...

SELECT * FROM temp1 t1 WHERE n - 1 = ( SELECT COUNT(DISTICNT ( sal )) FROM temp1 t2 WHERE t2.sal < t1.sal ) SELECT TOP 1 * FROM ( SELECT DISTINT TOP n sal FROM temp1 ORDER BY sal DESC) AS t ORDER BY t.sal ASC In sql server 2005 WITH MyCTE AS(  SELECT t_id,   sal,   DENSE_RANK() OVER(ORDER BY sal DESC) as rank1  FROM temp1  ) SELECT TOP 1 t_id, sal, rank1 FROM MyCTE WHERE rank1 = n Any updates/comments are appreciated.