Tag：sqlserver
-
Time：2020-7-11
Navicat for SQL Server 15It is a third-party SQL Server management tool developed by Navicat company. It can provide a comprehensive graphical solution for SQL Server database management and development. It can quickly and easily create, edit and delete all database objects, or run SQL queries and scripts, so as to help users better manage […]
-
Time：2020-6-17
0. What is a check constraint? Check constraint refers to adding additional constraints to the columns of a table. Note: check constraints cannot be defined in view. A check constraint can only define columns that must be included in the specified table. Check constraints cannot contain subqueries. Define check constraints when creating tables 1.1 syntax: […]
-
Time：2020-6-14
In this paper, we share the usage of transactions in SQL server stored procedures. The specific code is as follows create proc usp_Stock @GoodsId int, @Number int, @StockPrice money, @SupplierId int, @EmpId int, @StockUnit varchar(50), @StockDate datetime, @TotalMoney money , @ActMoney money , @baseId int, @Description nvarchar(255) as Declare @ error int = 0 — […]
-
Time：2020-6-12
Lock a table in the database Copy codeThe code is as follows: SELECT * FROM table WITH (HOLDLOCK) Note: the difference between locking a table in a database Copy codeThe code is as follows: SELECT * FROM table WITH (HOLDLOCK) Other transactions can read the table, but cannot update the deletion Copy codeThe code is […]
-
Time：2020-5-26
Copy codeThe code is as follows: @echo off Set / P Choi = start database, stop database [b, e]?if /i %choi% equ b goto beginserver if /i %choi% equ e goto endserver :beginserver net start mssqlserver goto end1 :endserver net stop mssqlserver goto end2 :end1 @echo ************************************************ @Echo * * started successfully! To reopen this […]
-
Time：2020-4-26
Sometimes there is a need for row to column conversion (that is, to use the value of the column as the column name). I usually use the case end + aggregation function to implement it. As follows: declare @t table (StudentName nvarchar(20), Subject nvarchar(20), Score int) Insert into @ t (studentname, subject, score) values (‘student […]
-
Time：2020-4-9
Phenomenon: Restore the previously backed up database to the current SQL database. Now you need to delete the user in the database. The result shows that the selected user cannot be removed because he owns the object terms of settlement: 1. Open enterprise manager, expand server, right-click local server,attribute： Open the SQL Server properties (configuration) […]
-
Time：2020-3-17
The example of this paper describes the method of SQL server to create dblink to operate remote server database. To share with you for your reference, as follows: –Configure dblink of SQLSERVER database exec sp_addlinkedserver @server=’WAS_SMS’,@srvproduct=”,@provider=’SQLOLEDB’,@datasrc=’10.131.20.100′ exec sp_addlinkedsrvlogin ‘WAS_SMS’,’false’, NULL,’CustomSMS’,’[email protected]′ select top 100 * from AS400.AS400_Extract.DBO.ORDUNA –Configure dblink from sqlserver to MySQL database exec sp_addlinkedserver […]
-
Time：2020-3-7
The problem of parameter sniff is reusing the execution plan generated by other parameters, which leads to the phenomenon that the current parameters adopt the execution plan is not optimal. Presumably, students who are familiar with data should know to generate parameter The most typical problem of sniff is that it uses parameterized SQL (or […]
-
Time：2020-2-19
The example of this paper describes the method of SQL Server batch updating two associated table data. To share with you for your reference, as follows: Mode 1: UPDATE a SET WtNo=b.NO from WT_Task a INNER JOIN WT_BasicInformation b ON a.WtId=b.ID; Mode 2: UPDATE a SET a.WtNo=b.NO FROM WT_Task a,WT_BasicInformation b WHERE a.WtId=b.ID; I hope […]
-
Time：2020-2-3
Column introduction “Dialogue CTO” is a new column of geek park. It talks about the development and growth of R & D managers from the perspective of technologists. In this column, Wang Yingqi, the founder and CEO of enterprise level R & D management tool one, is invited as an interviewer. Wang Yingqi once participated […]
-
Time：2020-2-2
Query deadlock process statement select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type=’OBJECT’ Kill deadlock process statement kill spid Next, I will share a paragraph about SQL Server deadlock detection, killing locks and processes, and viewing lock information –Detect deadlock –If a deadlock occurs, how can we detect which SQL statement or stored procedure is […]
