SQL Server Performance

Common Table Expression in SQL Server 2005

Discussion in 'Performance-Related Article Discussions' started by shanetasker, Jan 5, 2006.

  1. shanetasker New Member

  2. JiriJ New Member

    Here is example of how to delete duplicate rows with duplicate FirstName,LastName columns from Customers
    Using Common Table Expression (CTE) in SQL Server 2005:

    -- remove duplicate rows using CTE and ROWNUMBER() in SQL2005

    WITH DUPLICATE(FirstName,LastName, RowNumber)
    AS
    (
    SELECT FirstName
    ,LastName
    ,ROW_NUMBER() OVER (PARTITION BY LastName, FirstName order by LastName, FirstName) AS RowNumber
    FROM Customers
    )
    DELETE FROM DUPLICATE WHERE RowNumber > 1


    Jiri JANECEK
    MSE, MBA, MCSD.NET
  3. gagrajbhatia New Member

    Is it possible to update values in the columns returned by the CTE... as in case of Temporary Tables...???
  4. udelojf New Member

    I have been having issues with this query.

    My table has

    Category_ID <-Reporting to a Parent ID
    Parent_ID
    Category Name

    The query I am using is a copy of yours
    ================================================

    WITH Tree(Category_ID, Parent_ID, Category_Name, Hlevel)
    AS
    (
    SELECT Category_ID, Parent_ID, Category_Name, 0 as Hlevel
    FROM Dir_Categories
    WHERE Parent_ID = 0
    UNION ALL
    SELECT e.Category_ID, e.Parent_ID, e.Category_Name, Hlevel + 1
    FROM Dir_Categories e
    INNER JOIN Tree d
    ON e.Parent_ID = d.Category_ID
    )
    SELECT Category_ID, Parent_ID, HLevel,
    Category_Name = Replicate('|__', HLevel) + Category_Name
    FROM Tree ;
    GO

    Here is the output from HELL (Sorry, it's Killing me), I abreviated the headers below so it canfit better
    It may look like a Tree View, but the subcategories are not inside their corresponding categories. HELP PLEASE I CANT SLEEP!!!
    =======================================
    CatID ParID Level Category Name
    -----------------------------------------
    100Arts & Humanities
    200Business
    300Children
    400Computers
    500Education
    600Entertainment
    700Home & Garden
    800Health & Fitness
    900News & Politics
    1000People & Society
    1100Real Estate
    1200Recreation & Sports
    1300Science & Technology
    1400Shopping
    1500Travel
    28200Weblogs
    50800Lifestyle & Relationships
    53600Home & Family
    5375361|__Food & Drink
    5385361|__Family Entertainment
    5325372|__|__Drinks and Drinking
    5335372|__|__Alcoholic Beverages
    5345372|__|__Non-Alcoholic Beverages
    5355372|__|__Home Recipes
    5095081|__Personal Development
    5105081|__Alternative Lifestyle
    5115081|__Cyber Relationships
    5125081|__Dating
    5135081|__Divorce
    5145081|__Friendship
    5155081|__Help and Advice
    5165081|__Chats and Forums
    5175081|__Love and Romance
    5185081|__Marriage
    5195081|__Weddings
    5205081|__Singles
    2832821|__Business Blogs
    2842821|__Celebrity Blogs
    2852821|__Entertainment Blogs
    2862821|__Media Blogs
    2872821|__Personal Blogs
    2882821|__Political Blogs
    2892821|__SfiFi Blogs
    2902821|__Travel Blogs
    2912821|__Sports Blogs
    2922821|__Weblog Awards
    2932821|__Weblog Directories
    2942821|__Weblog Hosting
    2952821|__Weblog Tools
    5482821|__SEO & Web Marketing Blogs
    272151|__Accommodations
    273151|__Agents and Services
    274151|__Budget Travel
    275151|__Business Travels
    276151|__Destination Guides
    277151|__Trip Planning
    278151|__Maps
    279151|__News and Media
    280151|__Transportation
    281151|__Travelogues
    238141|__Apparell
    239141|__Arts & Collectibles
    240141|__Auctions
    241141|__Babies
    242141|__Beauty
    243141|__Books and Magazines
    245141|__Business Supplies
    246141|__Beauty Supplies
    247141|__Children
    248141|__Classified Ads
    249141|__Comparison Shopping
    250141|__Computers
    252141|__Consumer Electronics
    253141|__Consumer Information
    254141|__Flowers
    255141|__Food and Drink
    256141|__Freebies
    257141|__Furniture
    258141|__Garden
    259141|__General Merchandise
    260141|__Gifts and Holidays
    261141|__Health and Fitness
    262141|__Hobbies and Crafts
    263141|__Home
    264141|__Jewelry
    265141|__Membership Waterhouse Clubs
    266141|__Outdoors
    267141|__Wedding
    269141|__Vehicles
    268141|__Religious Products
    270141|__Toys and Games
    271141|__Other
    236131|__Science
    237131|__Technology
    235121|__Athlete Information
    233121|__Recreation
    234121|__Sports
    5392342|__|__Golf
    5402342|__|__Basketball
    5412342|__|__Baseball
    5422342|__|__Sport Collectibles
    5432342|__|__Football
    5452342|__|__Soccer
    5462342|__|__Sport Fan Clubs
    5472342|__|__News & Publications
    5632342|__|__Fishing
    5712342|__|__Air Sports
    5702342|__|__Tennis
    5742342|__|__Horse Racing
    216111|__Agents
    217111|__Appraisers
    218111|__Brokers
    219111|__Commercial
    220111|__Construction
    221111|__Consultants
    222111|__Foreclosures
    223111|__For Sale by Owner
    224111|__Home Builders
    225111|__Inspectors
    226111|__Legal
    227111|__Masterplan Communities
    228111|__Property Search
    229111|__Relocation Services
    230111|__Schools and Courses
    231111|__Rentals
    232111|__Buying or Selling a Home
    204101|__Communities and Groups
    205101|__Connecting With People
    206101|__Cultures
    207101|__Genealogy
    208101|__Military
    209101|__Off The Beaten Path
    210101|__Who is Who
    211101|__Spirituality and Religion
    212101|__Society
    213101|__Men
    214101|__Women
    215101|__Youth
    19391|__Aggregators and Summaries
    19491|__News
    19591|__Politics
    19691|__Ezines
    19791|__Internet Broadcast
    19891|__Nespapers and Periodicals
    19991|__Services
    20091|__Community
    20191|__Education
    20291|__Television
    20391|__Weather
    16981|__Aging
    17081|__Alternative Medicine
    17181|__Beauty
    17281|__Child Health
    17381|__Dental Health
    17481|__Dieting and Weight Loss
    17581|__Diseases and Desorders
    17681|__Drugs and Medicines
    17781|__Health News
    17881|__Health Professions
    17981|__Hospitals
    18081|__Fitness and Exercise
    18181|__Education
    18281|__Medical Organizations
    18381|__Medical Practice
    18481|__Medical Specialists
    18581|__Public Health
    18681|__Nutrition
    18781|__Procedures and Therapies
    18981|__Women Health
    19081|__Men Health
    19181|__Injuries
    19281|__Sexual Health
    16271|__Arts and Craft
    16371|__Do It Yourself
    16471|__Gardening
    16571|__Homemaking
    16671|__Interior Design
    16771|__Pest Control
    16871|__Pets and Animals
    56671|__Home Improvement
    55071|__Home Security
    56971|__Home Safety & Prevention
    56571|__Flooring
    15061|__Entertainment Reviews
    15161|__Celebrities
    15261|__Animation
    15361|__Games
    15461|__Gossip
    15561|__Horror
    15661|__Humor
    15761|__Interactive Websites
    15861|__Movies
    15961|__Music
    16061|__Radio
    16161|__Television
    13351|__Adult Education
    13451|__Directories
    13551|__Distance Learning
    13651|__Early Education
    13751|__Educational Software
    13851|__Fields of Study
    13951|__Financial Aid
    14051|__Graduation
    14151|__Home Schooling
    14251|__Internships
    14351|__K-12_Education
    14451|__Tests and Testing
    14551|__Libraries and Resources
    14651|__Educational Material
    14751|__References
    14851|__Schools
    14951|__General Resources
    13041|__Internet
    13141|__Intranet
    13241|__Networking
    11041|__Algorithms
    11141|__Artificial Intelligence
    11241|__Artificial Life
    11341|__Computer Jobs
    11441|__CAD
    11541|__Companies
    11641|__Graphics
    11741|__Education
    11841|__Data Communications
    11941|__Hacking
    12041|__Hardware
    12141|__Software
    12241|__Consulting
    12341|__Support and Repair
    12441|__Multimedia
    12541|__Publications
    12641|__Virtual Reality
    12741|__Robotics
    12841|__Security and Protection
    12941|__Tutorials and Guides
    4711122|__|__Agents
    4721122|__|__Art
    4731122|__|__Artificial Words
    4741122|__|__Biomimicry
    4761122|__|__Conferences and Events
    4771122|__|__Departments
    4781122|__|__Distributed Projects
    4791122|__|__Genetic Programming
    4801122|__|__Iterate Prisoner Dilemma
    4811122|__|__Lindenmayer Systems
    4821122|__|__Natural Language
    4831122|__|__Particle Swarms
    4841122|__|__Programs and Applets
    4851122|__|__Research Papers
    4561112|__|__Conferences and Events
    4521112|__|__Academic Departments
    4531112|__|__Agents
    4581112|__|__Data Mining
    4591112|__|__Distributed Projects
    4601112|__|__Fuzzy
    4611112|__|__Games
    4621112|__|__Knowledge Representation
    4631112|__|__Machine Learning
    4641112|__|__Natural Language
    4651112|__|__Natural Networks
    4661112|__|__Philosophy
    4671112|__|__Programming Languages
    4681112|__|__Publications
    4691112|__|__Qualitative Physics
    4701112|__|__Vision
    4541112|__|__Applications
    4551112|__|__Belief Networks
    4751112|__|__Cellular Automata
    4571112|__|__Creativity
    4401102|__|__Animated
    4411102|__|__Compression
    4421102|__|__Computational Algebra
    4431102|__|__Cryptography
    4441102|__|__Digital Signal Processing
    4451102|__|__Genetic
    4501102|__|__Pseudorandom Numbers
    4511102|__|__Speech Recognition
    5621302|__|__Web Hosting Services
    5681302|__|__Search Engine Optimization
    5641302|__|__Web Design
    10731|__Nutrition
    10831|__Children Books
    10931|__Computer Related
    10031|__Toys
    10131|__Family Entertainment
    10231|__Health
    10331|__Pets and Animals
    10431|__Recreation
    10531|__Schools
    10631|__Sports
    56031|__Parenting
    5051022|__|__Exercise
    5061022|__|__Food and Nutrition
    5071022|__|__Safety
    4971012|__|__Arts and Crafts
    4981012|__|__Humor
    4991012|__|__Dance
    5001012|__|__Movies
    5011012|__|__Music
    5021012|__|__Theater
    5031012|__|__Toys
    5041012|__|__Magazines
    5311012|__|__Food and Drink
    4891092|__|__Computer and Video Games
    4901092|__|__Computer Science
    4911092|__|__Directories for Kids
    4921092|__|__Home Pages
    4931092|__|__Online Games
    4941092|__|__Pages for Kids
    4951092|__|__Software
    4961092|__|__Web Design
    8621|__Comodities
    8721|__Computing
    8821|__Finance
    8921|__Global Commerce and Government
    9021|__Human Resources
    9221|__Industry Specific
    9321|__Law
    9421|__Management
    9521|__News and Resources
    9621|__Non Profit
    9721|__Professional Associations and Organizations
    9821|__Sales and Marketing
    9921|__Small Business
    54921|__Insurance
    57221|__Business Services and Resources
    5735722|__|__Investigation Services
    427982|__|__Advertising
    428982|__|__Associations
    429982|__|__Consulting Services
    430982|__|__Corporate Branding
    431982|__|__Direct Marketing
    432982|__|__Online Marketing
    433982|__|__Product Development
    434982|__|__Promotional Marketing
    435982|__|__Public Relations
    436982|__|__Sales
    437982|__|__Segmentation and Targeting
    438982|__|__Specialized by Industry
    439982|__|__Strategic Planning
    416962|__|__Animal Welfare
    417962|__|__Charitable Organizations
    418962|__|__Childrens Organizations
    419962|__|__Consulting Services
    420962|__|__Environmental Organizations
    421962|__|__Fundraising
    422962|__|__Global Aid
    423962|__|__Health Organizations
    424962|__|__Religious Organizations
    425962|__|__Resources
    426962|__|__Volunteer Opportunities
    406952|__|__Business Columnists
    407952|__|__Business Newspapers
    408952|__|__Business Portals and Directories
    409952|__|__Investment News
    410952|__|__Mailing Lists
    411952|__|__Online Publications and Journals
    412952|__|__Radio and TV Programs
    413952|__|__Regional News Sources
    414952|__|__Road Conditions
    415952|__|__Sales Leads
    393942|__|__Associations
    394942|__|__Benchmarking
    395942|__|__Business Ethics
    396942|__|__Business Services and Resources
    397942|__|__Corporate Governance
    398942|__|__Information
    399942|__|__Knowledge Management
    400942|__|__Management Theory
    401942|__|__News and Publications
    402942|__|__Operations Management
    403942|__|__Organization Development
    404942|__|__Research
    405942|__|__Training and Education
    365932|__|__Associations
    366932|__|__Branches of Law
    367932|__|__International Law
    368932|__|__Legal Professionals
    369932|__|__Legal Resources
    370932|__|__Legal Services
    371932|__|__News and Publications
    3723663|__|__|__Administrative
    3733663|__|__|__Bankruptcy
    3743663|__|__|__Business
    3753663|__|__|__Communication
    3763663|__|__|__Constitutional
    3773663|__|__|__Consumer
    3783663|__|__|__Criminal Law
    3793663|__|__|__Cyberspace
    3803663|__|__|__Disabilities
    3813663|__|__|__Personal Injury
    3823663|__|__|__Employment
    3833663|__|__|__Entertainment
    3843663|__|__|__Environmental Law
    3853663|__|__|__Family Law
    3863663|__|__|__Immigration
    3873663|__|__|__Intellectual Property
    3883663|__|__|__Product Liability
    3893663|__|__|__Real Estate
    3903663|__|__|__Traffic Violations
    3913663|__|__|__Trust and Estate Law
    3923663|__|__|__Benefits
    353922|__|__Aerospace and Military
    354922|__|__Agriculture
    355922|__|__Construction
    356922|__|__Energy and Utilities
    357922|__|__Manufacturing
    358922|__|__Media and Entertainment
    359922|__|__Mining and Drilling
    360922|__|__Retail
    361922|__|__Science and Technology
    521922|__|__Automotive
    362922|__|__Service Industry
    363922|__|__Transportation and Logistics
    364922|__|__Wholesale Trade
    5225213|__|__|__Repair and Maintenance
    5235213|__|__|__Exotic and Classic Vehicles
    5245213|__|__|__Racing
    5255213|__|__|__Diesel Mechanics
    5265213|__|__|__Auto Sales
    5275213|__|__|__Road Services
    5285213|__|__|__General Enhancements
    5295213|__|__|__Car Accessories
    5305213|__|__|__High Performance
    340902|__|__Career Management
    341902|__|__Compensation
    342902|__|__Consulting Services
    343902|__|__Employee Evaluation
    344902|__|__Employee Testing
    345902|__|__Employment and Career
    346902|__|__Outsourcing
    347902|__|__Recruitment
    348902|__|__Relocation
    349902|__|__Retirement Benefits and Services
    350902|__|__Special Benefits and Services
    351902|__|__Training and Education
    352902|__|__Workplace Health and Safety
    324892|__|__B2B Markets
    325892|__|__Business Guides
    326892|__|__Chambers of Commerce
    327892|__|__Consulting Services
    328892|__|__Export Assistance
    329892|__|__Foreign Trade Zone
    330892|__|__Government
    331892|__|__Importers and Exporters
    332892|__|__International Taxation
    333892|__|__ISO
    334892|__|__Localization
    335892|__|__News and Publications
    336892|__|__Trade Development
    337892|__|__Trade Leads
    338892|__|__Translation Services
    339892|__|__World Trade Centers
    313882|__|__Banking
    314882|__|__Business Finance
    315882|__|__Business Insurance
    316882|__|__Debt Collection
    317882|__|__Consumer Finance
    318882|__|__Financial Services
    319882|__|__Investing
    320882|__|__Mortgage
    322882|__|__Taxes
    323882|__|__Venture Capital
    309872|__|__Associations
    310872|__|__Computers
    311872|__|__Information Technology
    312872|__|__Internet
    5613123|__|__|__Web Hosting Services
    296862|__|__Automated Teller Machines
    297862|__|__Binders and Laminating
    298862|__|__Communication
    299862|__|__Office Space
    300862|__|__Packaging and Containers
    301862|__|__Product Tracking
    302862|__|__Record Storage
    303862|__|__Signage
    304862|__|__Stationary Supplies
    305862|__|__Time Recording and Management
    1611|__Books and Literature
    1711|__Philosophy
    1811|__Antiques
    1911|__Arts
    2011|__Humanities
    2111|__Museums
    2211|__Organizations
    56711|__Crafts
    84222|__|__National Endowment for the Arts
    85222|__|__National Endowment for the Humanities
    78212|__|__Maritime Museums
    79212|__|__Railway Museums
    80212|__|__Aviation Museums
    81212|__|__Children Museums
    82212|__|__Science Museums
    83212|__|__Museums
    71202|__|__Books and Literature
    72202|__|__Classical Studies
    73202|__|__Cultural Studies
    74202|__|__History
    75202|__|__Languages
    76202|__|__Linguistics
    77202|__|__Philosophy
    60192|__|__Animation
    61192|__|__Arts Publications
    62192|__|__Arts Resources
    63192|__|__Art Exhibits
    64192|__|__Cartoons
    65192|__|__Design Arts
    66192|__|__Education
    67192|__|__Museums
    68192|__|__Performing Arts
    69192|__|__Visual Arts
    70192|__|__Writers Resources
    58182|__|__Antique Jewelry
    59182|__|__Restoration
    35172|__|__Academic Departments
    36172|__|__Ancient Greek Philosophy
    37172|__|__Axiology
    38172|__|__Chats and Forums
    39172|__|__Chinese Philosophy
    40172|__|__Continental Philosophy
    41172|__|__Current Movements
    42172|__|__Eastern
    43172|__|__Epistemology
    44172|__|__Ethics
    45172|__|__German Idealism
    46172|__|__History
    47172|__|__Humanism
    48172|__|__Journals
    49172|__|__Logic
    50172|__|__Metaphysics
    51172|__|__New Age
    52172|__|__Organizations
    53172|__|__Personal Pages
    54172|__|__Philosophers
    55172|__|__Philosophy of Religion
    56172|__|__Philosophy of Science
    57172|__|__Reference
    23162|__|__Art Books
    24162|__|__Authors
    25162|__|__Banned Books
    26162|__|__Best-Seller Lists
    27162|__|__Bookstores and Booksellers
    28162|__|__Book Reading Groups
    29162|__|__Downloadable Texts
    30162|__|__By Genre
    31162|__|__Magazines and e-Zines
    32162|__|__Periods and Movements
    33162|__|__Publishers
    34162|__|__Reviews
  5. Madhivanan Moderator

    quote:Originally posted by gagrajbhatia

    Is it possible to update values in the columns returned by the CTE... as in case of Temporary Tables...???
    No you cant.

    Try this

    with a
    (
    dates
    ) as

    (select getdate() as dates)
    update a set dates=null


    Madhivanan

    Failing to plan is Planning to fail
  6. Adriaan New Member

    udelojf, please start a new thread for your question.
  7. Adriaan New Member

    Madhivanan, this is an old thread!
  8. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Madhivanan, this is an old thread!<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. Old thread revisited [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />When I was reading an article about CTE, I saw this thread<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page