SQL Server

The first solution, the simplest and common method: Copy codeThe Code is as follows:Select top 30 * from article where id not in (select top 45000 id from article order by year desc, id desc) order by year desc, ID DESC Average query time: 45

StartThis is a problem last year. I found this problem only today when I am sorting out emails. It is very interesting and special. In the RelationGraph table, there are three fields (ID, Node, RelatedNode). The Node and RelatedNode fields describe

1. usage of the Case Function(1) Use the following commands: switch-case and if-else if.(2) Syntax:Case [field]When expression then display dataWhen expression then display dataElse display dataEnd(3) percent conversion of Quality Education1) We

Having usage The having clause allows us to filter various data After grouping. The having clause is slower than the aggregate statement (sum, min, max, avg, count) in the query process ). the where clause is faster than the aggregate statement (sum,

This topic describes how to create a database that supports FILESTREAM. Because FILESTREAM uses a special type of file group, you must specify the contains filestream clause for at least one file group when creating a database.Create a database with

Filter is often used to filter out some records when writing SQL statements)Copy codeThe Code is as follows:Where amount> 4000 and amount Of course, this does not mean the where clause of SQLSERVER. It means that does not use indexes for

Misunderstanding #19: operations on the Truncate table will not be loggedErrorOperations in the User table are recorded in logs. The only operation in SQL Server that is not logged is row Version Control in TempDB.The Truncate Table statement

If you use the DATENAME () function to obtain the full name of a month, you only need to display the abbreviated name of a month.The abbreviation of the 12 months is the same as that of the month in August. The abbreviation of other months is only

During SQL statement optimization, most of them will view the statement execution time. What is the statistical result displayed by SQLSERVER?The following is an example.Simple statement:Copy codeThe Code is as follows:1 SET STATISTICS TIME ON2 USE [

Truncate table name (Unrecoverable truncate identifiers will be reordered)LinqUtil. db. executeCommand ("truncate table warehousing"); // warehousing is the TABLE name. This operation is more efficient and the counter is cleared. However, tables

When executing the database creation script, the original database is often dropped first. Because SqlServer does not allow the execution of the drop database operation when detecting a data connection, the database creation script often fails to be

Statement:Copy codeThe Code is as follows:Select * from [User] order by [Password] desc, Id ascIf one condition is repeated, the second condition is used for sorting.As follows: Note the 14th and 15th records here. (Sort by Id when the Password is

There are many paging methods for SQL Server 0.1 million. Is it more efficient? It will be difficult to reach the level or above. This is not an example here. Row_number is also a major improvement over SQL server2005. For details, referCopy codeThe

FirstUse [database name];Exec sp_helpfile; You can use sp_helpfile to query the logic name of the log file, and then you can use DBCC to reduce the LOG fileBackup log [database name] WITH TRUNCATE_ONLYDbcc shrinkfile ([logical name of log file], 1)

Automatic Collection of SQLSERVER diagnostic information I believe that many people have encountered problems with SQLSERVER, and you cannot solve the problem by requiring DBA or after-sales support engineers from Microsoft, then they usually need

Misunderstanding #2: dbcc checkdb will cause blocking, because this command will lock by default This is wrong! In SQL Server 7.0 and earlier versions, the essence of dbcc checkdb commands is that the C language implements a continuously nested loop

1. Start the H2 database without opening the browser window (opened by default)2. Added support for the BigDecimal type for database creation SQL statements. By default, the h2 database does not support the bigdecimal type:SQL codeCopy codeThe Code

SQL Server Sa users are sure to have some understanding. The following describes how to modify the Authentication Mode of SQL Server 2000 and the password modification method of SQL Server Sa users for your reference.1. How to modify the

The disadvantage of this solution is that, if the data volume is large and there are many sub-categories, the number of sub-categories reaches 4 or above, this method occupies the database connection pool.It has a great impact on performance.If you

The basic algorithm for automatically updating statistics is:· If the base number of the table in the tempdb database is less than 6, each of the six modifications will be automatically updated to the table.· If the base number of a table is greater