Posts Tagged ‘SQL Server 2005’

Hi, It’s a headache for developers/DBA to write or read a complex SQL query using a number of Joins because Dealing with sub-queries often required to select a part of the data from a sub query or even join data from a query with some other many tables, for same we have an option to name our sub-queries with an alias or to use it directly and our business processes requirement may be getting more and more complex and as a resultant query would be not maintainable. Common Table Expressions(CTE) is a new way/method to define the sub-queries at once and name it using an alias and later call the same data using the alias just like what we do with a normal table. CTE is standard ANSI SQL standard.
Common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
CTE allows us to generate Tables beforehand and use it later when we actually bind the data into the output while sub-queries is parallel process in this regard.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,…n] ) ]
( CTE_query_definition )

Let see an example –

Using Sub-Query

(SELECT ED.EmpNameED.EmpDOBAD.EmpAddress From AddressDetails AD
Inner join EmployeeDetails ED on ED.EmpCode AD.EmpCodeAA

For Same, CTE implementation will as

With AA(NameAgeAddress–Provide Column names for CTE temporary Table ‘AA’
SELECT ED.EmpNameED.EmpDOBAD.EmpAddress From AddressDetails AD
  Inner join EmployeeDetails ED on ED.EmpCode AD.EmpCde)

CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don’t necessarily need a view defined for the system. CTE Create a recursive query. It’s a Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata. Using CTE we can Reference the resulting table multiple times in the same statement.

As per MSDN, The following guidelines apply to nonrecursive common table expressions

  • A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
  • Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
  • A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
  • Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
  • The following clauses cannot be used in the CTE_query_definition:
    • ORDER BY (except when a TOP clause is specified)
    • INTO
    • OPTION clause with query hints
    • FOR XML
  • When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
  • A query referencing a CTE can be used to define a cursor.
  • Tables on remote servers can be referenced in the CTE.
  • When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error.
  • When a CTE is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Ambiguous CTE references may produce unexpected join behavior and unintended query results

Guidelines for Defining and Using Recursive Common Table Expressions

The following guidelines apply to defining a recursive common table expression:

  • The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitionsare anchor members unless they reference the CTE itself.
  • Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
  • The number of columns in the anchor and recursive members must be the same.
  • The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
  • The FROM clause of a recursive member must refer only one time to the CTE expression_name.
  • The following items are not allowed in the CTE_query_definition of a recursive member:
    • GROUP BY
    • HAVING
    • Scalar aggregation
    • TOP
    • Subqueries
    • A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

    The following guidelines apply to using a recursive common table expression:

  • All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.
  • An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, MERGE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement.
  • A view that contains a recursive common table expression cannot be used to update data.
  • Cursors may be defined on queries using CTEs. The CTE is the select_statement argument that defines the result set of the cursor. Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. If another cursor type is specified in a recursive CTE, the cursor type is converted to static.
  • Tables on remote servers may be referenced in the CTE. If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. This is one way to confirm proper recursion.
  • Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE.

Hi Guys, Here, some basic tips about How to write optimized Stored Procedure.

  • Its always better to use table owner in query like

    SELECT col1,col2….coln FROM dbo.Tablename

  • Due to this, SQL query optimizer does not have to decide whether to retrieve from dbo.Tablename or other owner’s table and avoids recompilation.
  • Always Use TRUNCATE TABLE statement instead of DELETE clause if its needed to delete all rows from a table. Its more faster then DELETE statement without any conditions. TRUNCATE TABLE always releases all the space occupied by that table’s data and indexes, without logging the individual row deletes.
  • Don’t use the “sp_” prefix in a stored procedure name as the “sp_” prefix is reserved for system stored procedures. Any stored procedure that has the “sp_” prefix will cause an extra lookup in the MASTER database If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will execute but first it will find the procedure in resource database and then the user database (for SQL server 2005/2008/2012) hence causing an extra burden to the server.
  • To get number of rows from table never use “SELECT COUNT(*)” statement, There is other way for much faster to achieve this goal as

    SELECT rows FROM sysindexes WHERE id OBJECT_ID(‘Table_Name’) AND indid <2

  • Include “SET NOCOUNT ON” statement in your stored procedures to greatly reduce network traffic.
  • Try to use table variables instead of Temporary Tables – Temp tables can cause stored procedures to recompile. (From SQL 2005 onwards, using temporary table not always causing recompilations. But adding rows to temporary tables may cause recompilations). But table variables were designed specifically to guard against stored procedure recompiles during execution. If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size be careful to use them only with not very huge amount of data that you want to allocate in temp tables, otherwise you can easily get the server down.