from :

Before that 2 I've seen it in this book with as A simple example of clause , I didn't find any information on the Internet .

Today I want to sum up , The main explanation is as follows :

【 Large tables are updated in batches 】

【 Large tables are deleted in batches 】

【 Only one line remains in a completely duplicate line 】

  1. -- Create test table
  2. --  DROP TABLE [tabName]
  3. SELECT * INTO [tabName] FROM sys.objects
  4. SELECT * FROM [databaseName].[dbo].[tabName] ORDER BY name desc

    1. ---------------------------------------------------------------
    2. ---------------------------------------------------------------
    3. 【 Large tables are updated in batches 】
    4. When a web page needs to be upgraded , The database needs to add fields or update field values , For large meters, it will be blocked for a long time .
    5. Generally, fields are added first, which are allowed to be null value , Update the default values in the table , Add constraints again
    6. For example, the test table should be principal_id Updated to 0, Here's the easiest way to update :
    7. ;WITH TAB AS(
    8. SELECT TOP 10 principal_id FROM [dbo].[tabName] where principal_id is null
    9. )update TAB set principal_id = 0
    10. To update fields principal_id Just take one . Before each selection 10 Row update null by 0, You can create scheduled job updates .
    11. ---------------------------------------------------------------
    12. ---------------------------------------------------------------
    13. 【 Large tables are deleted in batches 】
    14. For some data maintenance, you need to delete more data , The table is large and many users are still using it .
    15. Generally, create a job and delete it at night , Or delete by a field segment .
    16. More convenient method can also choose to meet the conditions before deleting N That's ok
    17. ;WITH TAB AS(
    18. SELECT TOP 10 principal_id FROM [dbo].[tabName] where principal_id is null
    20. ---------------------------------------------------------------
    21. ---------------------------------------------------------------
    22. 【 Only one line remains in a completely duplicate line 】
    23. -- Insert to produce duplicate lines
    24. INSERT INTO [tabName]
    25. SELECT TOP 50 PERCENT * FROM [databaseName].[dbo].[tabName]
    26. SELECT * FROM [databaseName].[dbo].[tabName] ORDER BY name desc
    27. The most used cases on the Internet , All specify that a column must be unique , In order to use a statement to delete other duplicate .
    28. For exactly the same line , Most represent creating a temporary table to transition operations .
    29. The following uses “with  Clause   and  ROW_NUMBER() function ” To delete completely duplicate rows
    30. ,partition When grouping, you can select a column ( Or all columns ) Group sort
    31. ;WITH TAB AS(
    32. SELECT ROW_NUMBER()over(partition by object_id order by (select 0)) id
    33. FROM [dbo].[tabName]

CTE More related articles on the magical use of

  1. Ingenious use CTE, Implementation of a statement sql recursive query ,SQLServer recursive

    In database design, we often encounter the relationship structure of parent-child nodes , You often need to find the root of a node , Or all the children of a node , Generally speaking, it is implemented recursively in the business layer , Or database stored procedure implementation . But in fact SQLServer Provided CTE It can be very good ...

  2. 【CSS Advanced 】 The magic of pseudo elements -- The beauty of single label

    I am studying recently  <CSS SECRET>(CSS reveal ) This masterpiece , Yes CSS With a deeper understanding , The following project : CSS3 ideas  -- Demo ( Please use Chrome Browser open , Extraordinary value ...

  3. angular2 Series of tutorials ( Ten ) Two ways to start 、 Two routing services 、 The magic of reference types and singleton patterns

    Today we are going to talk about ng2 Routing system based on Network . Example

  4. JavaScript Wonderful and happy ( One ) And Function optimization

    JavaScript The series of wonderful and happy articles mainly show some JavaScript The above is more interesting features and some useful skills , A lot of the content in it is the content that I used in the project ( Of course , Not all skills are used very often ^_^) ...

  5. Promise The past and the present, and the magical skills

    Browser event model and callback mechanism JavaScript Running in the browser as a single thread , This is every book JavaScript It will be mentioned in textbooks . At the same time out of the right UI Thread operation security considerations ,JavaScript and UI Threads are in the same ...

  6. 【CSS Advanced 】 The magic of pseudo elements 2 - Multi column uniform layout and title Attribute effect

    Recently, both work and self-learning are very busy , In the face of lengthy blog articles, the heart is always more than the strength , But also constantly contact and learn fragmentary but meaningful knowledge , I would like to share with you , So this article may be very short . This article follows me to another one CSS The text of pseudo elements ...

  7. Not really mentioned %%lockres%% The magic effect of

    %%lockres%% This value seems to be rarely mentioned , Even Microsoft is in the official document . It returns to be a Hash Value, It's useless to see the value . Later, in practice, it also has its magical function , For example, when there are performance problems such as LOCK when , Usually go through first ...

  8. sql server Simple application of common expressions (cte)

    One . Preface Now when you do project data access, you will basically choose one orm frame , It shields the underlying data access form in the form of object-oriented , Let developers focus more on business processing , Instead of interacting with the database , Help us improve development efficiency : For example, some simple insert ...

  9. recursive CTE

    Common table expression (CTE) It has an important advantage , That is to be able to quote itself , This creates recursion CTE. recursive CTE It's a repeat execution CTE To return a subset of data until a common table expression for the complete result set is obtained . When a query references recursion ...

Random recommendation

  1. Wechat menu php The array format

    // spare $menu['button']['0']['name'] = ' integral '; $menu['button']['0']['sub_button']['0']['type'] = 'view'; ...

  2. chrome The browser closes the tab page

    chrome The browser closes the tab prompt :Scripts may close only the windows that were opened by it. terms of settlement : adopt open Method to close . open( ...

  3. Java Medium Swing Keyboard binding case

    package ch12; import javax.swing.*; import java.awt.*; import java.awt.event.*; /** * Created by Jiq ...

  4. ImageMaigck Chinese path is not supported

    I don't know what's going on . In retrospect : char* pTest1 = " test .txt"; wchar_t* pTest2 = L" test .txt"; Above is pTest1 Pointing to the inside ...

  5. OSCLI

  6. Java colony -- How to solve the problem of multi-user high concurrent access for large websites

    Time past quickly. , Log in to the blog Park again to blog , It's been a month since I last blogged , Although it's because I found an internship , But it also shows that their ability to control time is not so strong , ha-ha , It seems that continuous efforts are needed !( Here's a special illustration of Ben ...

  7. Linux command Quick command synthesis

    FTP Boot up Start to make FTP It starts automatically every time it is turned on , Run the command :  chkconfig --level 35 vsftpd on linux To turn it off 1.halt linux Cancellation 1.logout linu ...

  8. WPF Customize TabControl Control style

    One . Preface It's often used in programs TabControl Control , The default control style is very common . And the style or function may not meet our requirements . such as : We need to TabControl The title can be centered . Or average distribution : Or we hope TabContr ...

  9. Python 3+selenium+unittest+HTMLTestRunner Generate test reports

    One . download, decompression , Put it in python Of the installation path site-packages Under the table of contents ...

  10. It's getting cold , Be sure to keep warm , Okay? ( The requirements specification is placed in github 了 )

    Team project ——AI gobang ( Applet ) One . Team show : Team name : Where is the future tomorrow waiting for you team team member : Long TIANYAO ( captain )(3116005190), Lin Yuzhi (3116005188), Huang Huichao (3116005178) ...