,-1, GETDATE ()),111)//111 is the style number, (100-114)Query first day of the month Date: Select DATEADD (mm, DATEDIFF (mm,0, GETDATE ()),0) asfirstday Query Last day of the month Date: Select DateAdd (MS,-3, DATEADD (mm, DATEDIFF (M,0, GETDATE ()) +1,0)) asLastday//change-The value of 3 changes accordinglyHow many days are there this month:SelectDatePart (Dd,dateadd (dd,-1, DateAdd (MM,1, Cast ((CAST (GE
Original: SQL Server 2012:SQL Server architecture--The life cycle of a query (part 3rd) (end)A simple update queryYou should now know the query life cycle of reading only the data, and next determine what happens when you need to
In the process of paging data using temporary tables, it is found that the query statement conditions are passed through the stored procedure parameters. The parameter conditions cannot be directly used after the SQL where statements are added. There is only one solution to this problem, it is to concatenate SQL statements and conditions into an
frequently (by the way, it becomes a clustered index), and can better meet most of our needs. The auto-increment ID column is omnipotent. The Int type only occupies 4 bytes to fully meet the requirements of narrow indexes. The absolute sequential storage can effectively reduce index fragmentation, which fully conforms to our table habits, it is always correct to use an auto-increment ID column as the primary key.
Here, the key columns of clustered indexes are mainly for
on sod. ProductID = P.productidwhere P.productid =897Look at the counter first, with two green peaks of 1. Is the number of compilations that occurred when the above was executed separately.--View Cache object Execution Type: adhoc (instant query) SELECT cacheobjtype,objtype,refcounts,usecounts,[sql]from sys.syscacheobjectswhere [SQL] Like '%salesorderid% ' and
row within the result set partition. The rank of a row is a number of rows before the line involved.
--like here I sort by price, take out the most expensive items in each category from top to bottom
SELECT * from
(
SELECTI.price,i.commodityname,c.classname,rank () Over(Partition byC.classidOrder byI.priceDESC) Rank2 fromCommodityinfo iINNER JOINCommodityclass C onI.commodityclass=C.classid) RWHERERank22; Under SQL
In SQL ServerEach database has a table generated by the system.The table sysobjects records all the table names in the database.We can use the following SQL syntax for query operations.
Copy codeThe Code is as follows:Select Name, id from sysobjects where xtype = 'U'
Xtype = 'U' indicates the table used. If xtype = 's' is used'The default table
There is also a
multiple fields, and the following SQL cannot be run in SQL Server and access.Select COUNT (distinct name, id) from A;To use it, use nested queries, as follows:Select COUNT (*) from (select Distinct Xing, name from B) as M;4.distinct must be placed at the beginningSelect ID, distinct name from A; --The error will be indicated because the distinct must be place
This issue is especially important for projects with a relatively large scale. If there are hundreds of stored procedures in the database,I can't find them one by one. Even if I understand the business and system very well, it will take a long time to remember.How can I use SQL statements for queries?The following describes how to query SQL statements:Copy codeTh
understand that resources such as memory are more required for concurrent processing of serial processing. Whether to use parallelism or serial travel is automatically evaluated and selected by MsSQL. A single task is divided into multiple tasks and can be run on the processor. For example, if the sort, connection, scan, and group by statements of delayed queries are executed simultaneously, SQL SERVER det
server| Page | resolution | data | statement
For example: request to select the No. 3000 page of tbllendlist records, each page 100 records.
----------Method 1:----------Select Top * from Tbllendlistwhere Fldserialno not in(Select Top 300100 fldserialno from TbllendlistORDER BY Fldserialno)ORDER BY Fldserialno
----------Method 2:----------SELECT Top 100 *From TbllendlistWHERE (Fldserialno >(SELECT MAX (Fldserialno)From (SELECT top 300100 Fldserialno
. Suggestions for using DBCC officially1. Run checkdb when the system usage is low.2. Make sure that other disk I/O operations, such as disk backup, are not performed at the same time.3. Place tempdb in a separate disk system or fast disk subsystem.4. Allow tempdb to have enough expansion space on the drive. Use DBCC with estimate onlyEstimate the amount of space required by tempdb.5. Avoid running query or
First SQL statementSelect CompanyName GSMC,ZB Zhibiao from Left Join T_companycode on T_GSNDZB.GSBH=T_companycode.companyidQuery Result:A second SQL statementSELECT min(CompanyName) GSMC,cast(round(sum(T_xstj.hsje)/10000,2) asNumeric -,2)) Ndje fromT_xstj Left JoinT_companycode onT_companycode.companyid=T_XSTJ.GSBHwhere DateDiff( YearSjgetdate())=0 Group byGsbhQuery Result:Statements that are merged int
When searching for data in a database, the SQL wildcard character can override one or more characters.
SQL wildcard characters must be used with the LIKE operator.
In SQL, you can use the following wildcard characters:
Wildcard description
% replaces one or more characters_ Replaces only one characterAny single character of the [charlist] Word columns
[^char
, high concurrency is a high level of CPU consumption caused by recompilation.C, physical connection tips (Hint)About physical connections We have analyzed in detail in the previous article that there are three physical connection methods in SQL Server: Nested loops, merges, Hash joins.You can refer to the links in my basic article:
server| Speed | optimization
MS SQL Server Query optimization method (1) There are many reasons for the slow query speed, common as follows:
1, no index or no index (this is the most common problem of query slow, is the defect of
the data is consistent, but nothing is perfect. The cost of the Begin Tran is that all the resources locked by the SQL statements cannot be released until the commit is committed before committing.It can be seen that if the BEGIN TRAN too many SQL statements, the performance of the database is poor. Before this large transaction commits, it is bound to block other statements, resulting in many blocks.The p
Many of my friends may be making mistakes in performance optimization as described below. If you have any need, please refer to the mistakes in SQL Server query performance optimization to get out of the index.
Many of my friends may be making mistakes in performance optimization as described below. If you have any need, please refer to the misunderstanding of
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.