Common Table Expression, why semicolon?

by
  • To avoid ambiguity because WITH can be used elsewhere
    ..FROM..WITH (NOLOCK)..
    RESTORE..WITH MOVE..
  • It’s optional to terminate statements with ; in SQL Server

Put together, the previous statement must be terminated before a WITH/CTE. To avoid errors, most folk use ;WITH because we don’t know what is before the CTE

So 

DECLARE @foo int;

WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

is the same as 

DECLARE @foo int

;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

The MERGE command has a similar requirement.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. T-SQL split string
  3. How can I get column names from a table in SQL Server?
  4. Conditional WHERE clause in SQL Server
  5. How can I select the first day of a month in SQL?
  6. Split function equivalent in T-SQL?
  7. Is the NOLOCK (Sql Server hint) bad practice?
  8. Multi-statement Table Valued Function vs Inline Table Valued Function
  9. Select statement to find duplicates on certain fields
  10. Difference between CTE and SubQuery?
  11. Update records in table from CTE
  12. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  13. Parse comma-separated string to make IN List of strings in the Where clause
  14. SQL query to split column data into rows
  15. When to use Common Table Expression (CTE)
  16. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  17. Compare DATETIME and DATE ignoring time portion
  18. Split function in SQL Server 2008
  19. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  20. SQL “between” not inclusive
  21. Can I set ignore_dup_key on for a primary key?
  22. Using PIVOT in SQL Server 2008
  23. Which SQL query is faster? Filter on Join criteria or Where clause?
  24. How can I group by date time column without taking time into consideration
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Cumulative Total in MS Sql server [duplicate]
  27. SQL Server SELECT INTO @variable?
  28. Using CASE Statement inside IN Clause
  29. SQL Server: +(unary) operator on non-numeric Strings
  30. Use one CTE many times

Leave a Comment