• SQL Server method of manually inserting identity columns


    If we insert a value in the identity column, for example: Copy codeThe codes are as follows: insert member(id,username) values(10,’admin’)   An error message will be returned in the query analyzer:[plain] ReferencesServer: Message 544, level 16, status 1, line 1When identity_ When insert is set to off, explicit values cannot be inserted into the identity […]

  • Example of writing SQL statements like multiple conditions


    Table a no name 1 Lu, Li, Zhang 2 Zhou, Wei, Liu 3 li, ABCD table B no name sex 1 Li 12 Lu 03 ABCD 04 ABCD 1 how to copy the code is as follows: select * from a where a.name like (select b.name from b where b.sex=1) —————————————————————————————————————————-Sqlserver write replication code is […]

  • Three schemes of SQL paging statements


    Method 1: Copy codeThe codes are as follows: Select top page size*FROM table1 WHERE id NOT IN          ( Select top page size * (number of pages -1) id from table1 order by ID          ) ORDER BY id Method 2: Copy codeThe codes are as follows: Select top page size*FROM table1 WHERE id >           ( […]

  • The solution to the problem that SQLSERVER database cannot be accessed after it becomes a single user


    The solution is: Run the following SQL Copy codeThe codes are as follows: USE master;  GO  DECLARE @SQL VARCHAR(MAX);  SET @SQL=”  SELECT @[email protected]+’; KILL ‘+RTRIM(SPID)  FROM master..sysprocesses  WHERE dbid=DB_ ID (‘Database name ‘);   EXEC(@SQL); Alter database database name set multi_ USER;

  • SQL million level database optimization scheme sharing


    1. to optimize the query, try to avoid full table scanning. First, consider establishing indexes on the columns involved in where and order by. 2. try to avoid judging the null value of the field in the where clause, otherwise the engine will give up using the index and scan the whole table, such as:select […]

  • Analysis of the difference between in and exists in SQL query


    select * from A where id in (select id from B); select * from A where exists (select 1 from B where A.id=B.id); For the above two cases, in traverses and compares in memory, while exists needs to query the database. Therefore, when the B table has a large amount of data, exists is more […]

  • Simple instance of SQL conditional query statement


    Copy codeThe codes are as follows: //Create grade sheetcreate table result(        stu_id varchar2(20) not null,        china number(9,2) null,        math number(9,2) null,        english number(9,2) null); //Insert datainsert into result values(‘0001’,60,20,80); insert into result values(‘0002’,80,60,80); insert into result values(‘0003’,20,85,80); select *from result; //Condition queryselect  stu_id,(case When china>80 then ‘excellent’When china>=60 then ‘pass’When china<60 […]

  • SQL with as usage details


    1、 Meaning of with asThe phrase with as, also known as subquery factoring, allows you to do many things and define an SQL fragment that will be used by the entire SQL statement. Sometimes, it is to make the SQL statement more readable, or it may be in different parts of the union all as […]

  • SQL function merging a field together


    Recently, it is necessary to query all the fields in the associated table and recombine them into one field. At this time, the ordinary connection query can not meet the requirements, and SQL functions are required to complete it: ALTER function dbo.getResCodesByOwnerId(@OwnerId INT) returns nvarchar(2000) as begin DECLARE @codes VARCHAR(2000) SET @codes=” select @codes=stuff((select ‘,’+residence_code […]

  • A method of annotating fields with SQL statements


    Copy codeThe codes are as follows: EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @Value=n’role id’ — Comment name @level0type=N’SCHEMA’, @level0name=N’dbo’, @level1type=N’TABLE’, @Level1name=n’rolemeenuinfo’– table name @level2type=N’COLUMN’, @Level2name=n’roleid’– field name GO

  • Explanation of the location of where keyword in SQL connection query


    Because the author is naturally clumsy, and his thinking is not rigorous, and he is really not good at writing SQL statements, experts should not laugh, please skip this article directly. The background will not be introduced. First create a table and insert test data. Those fields have comments Copy codeThe code is as follows: […]

  • Two methods for SQL server to obtain the value of new record identification column


    For the newly added record, there are two ways to obtain the value of the automatic identification column of the new record:1. Use the output keyword in insertINSERT INTO table_name(column1,column2,column3)OUTPUT INSERTED. ID — returns the value of the automatically growing identity columnVALUES(”,”,”) –ADO. Net callint r = command.ExecuteScalar(); 2. Use global variable @ @ identity […]