• Principle analysis and example sharing of log mechanism of tempdb in sqlserver


    test case We create similar objects T1, # T1 in the user database (testpage) and tempdb, and create non temporary tables in tempdb, then execute the corresponding insert script (to generate logs), and record the execution time for comparison, which shows that tempdb is “fast” Code User database testpage use testpage go create table t1 […]

  • Solution to the problem of “tempdb” log full in SQLSERVER database


    Execute SQL statement without using temporary table Prompt server: Message 9002, level 17, status 2, line 1 The log for database ‘tempdb’ is full. Please back up the transaction log of the database to free up some log space. I found a solution on the Internet, which is to expand the size of the log […]

  • On the important role of tempdb in SQL Server System


    Introduction: Tempdb is the system database of SQL server. It is always an important part of SQL server. It is used to store temporary objects. It is easy to understand that tempdb is the sketchpad of SQL server. Both applications and databases can use tempdb as a temporary data store. All users of an instance […]

  • Sqlserver prompt “column prefix tempdb. Invalid: no table name specified” solution


    How to solve the error prompt “column prefix tempdb. Invalid: no table name specified” when querying and approving vouchers? Reason: because SQL2000 can’t recognize the special characters such as “,”, “and Chinese characters in the computer name, it can only use English letters and numbers. Solution: right click the “computer name” in my computer’s “properties” […]

  • Talking about the difference between SQL server user defined function and stored procedure


    1、 Custom function: 1. Table variables can be returned2. There are many restrictions, includingThe output parameter cannot be used;Temporary tables cannot be used;The operation inside the function cannot affect the external environment;The result set cannot be returned by select;Cannot update, delete, database table;3. Must return a scalar value or table variableUser defined functions are generally […]

  • In depth analysis of SQL server query plan


    For SQL Server optimization, query optimization may be a very common thing. As the optimization of the database itself is also a wide range of topics, so this paper only talks about how to understand the SQL server query plan when optimizing the query. After all, my knowledge of SQL server is limited. If there […]

  • SQL Server add, delete, modify and check some uncommon tips


    An insert statement 1. Only the default value is inserted into the data insert into [DBTrain].[dbo].[log_info] default values 2. In the nullable field, if you want to set null, you can press [Ctrl + 0]. Note that zero is not o. if you want to restore the value before modification, press [ESC] Two update statements […]

  • Summarize the usage of SQL server group by


    Today, let’s summarize the usage of group by with examples. To sum up: group by: all, cube, rollup, compute, compute by Create data script Create Table SalesInfo (Ctiy nvarchar(50), OrderDate datetime, OrderID int ) insert into SalesInfoSelect n ‘Beijing’, ‘2014-06-09’, 1001 union allSelect n ‘Beijing’, ‘2014-08-09’, 1002 union allSelect n ‘Beijing’, ‘2013-10-09’, 1009 union allSelect […]

  • Ignore index prompt in SQL Server


    When we want a query statement to use an index, we usually add an index prompt in the query statement, like this Copy codeThe code is as follows: SELECT id,name from TB with (index(IX_xttrace_bal)) where bal<100 In the production environment, because of this index prompt, the optimizer will not consider other indexes. Sometimes this index […]

  • Partition backup method of SQL server data backup file


    When the database is fully backed up, we may sometimes encounter an extreme situation. For example, there is only 5g space left for C, D and E on the server But if the business library needs 12g space to be fully backed up, what should we do at this time? Use filegroup backup? But the […]

  • Asp.net development SQL server into Oracle method detailed explanation


    preface Some time ago, our company’s project was to transfer SQL server to Oracle, and we found that there was less information on this aspect on the Internet. So let’s share our experience and record the problems. Let’s take a look at the detailed introduction At first I studied it for a while and then […]