SQL Server

Copy codeThe Code is as follows:Create trigger TgNameOn tbFor updateAsIf update (recommend)BeginUpdate tb set commenddate = (getdate () from tb inner join inserted on tb. vlistid = Inserted. vlistidEnd Recommend indicates the updated field.The key

Copy codeThe Code is as follows:If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [f_WorkDay] ') and xtype in (n'fn', n'if', n'tf '))Drop function [dbo]. [f_WorkDay]GO-- Calculate the working days of two different datesCreate

Copy codeThe Code is as follows:Create proc commonPagination@ Columns varchar (500), -- Name of the column to be displayed, separated by commas@ TableName varchar (100), -- Name of the table to be queried@ OrderColumnName varchar (100), -- Name of

For example, we created a table T1 and a T1 view V1, then changed T1, and then looked at the V1 results:Create Table T1 first: Copy codeThe Code is as follows:IF OBJECT_ID ('T1 ') IS NOT NULLDrop table T1Create table T1 (col1 INT, col2 INT)Insert

The most common numeric type is int, but it is not necessarily the best choice. Bigint, smallint, and tinyint can be used in special scenarios. Their features are shown in the following table: Data type Range Storage Bigint

USE MASTERGOIf exists (SELECT * FROM dbo. sysdatabases WHEREName = 'my _ test_database ')Drop database [my_test_database]GOCreate database [my_test_database]GOUSE [my_test_database]GOCreate table [my_table] ([my_id] VARCHAR (16 ))GO-- The stored

1. For example, there are two tables A (Field 1, Field 2, Field 3) and B (Field 2, Field 3). the stored data is as follows:Table Field 1 Field 2 Field 3 1 Location content 1 1 Location content 1

Recently, a friend asked me that it was very slow for him to delete millions or tens of millions of data from SQLSERVER. He analyzed the problem and gave some comments as follows, it may be useful to many people.If your hard disk space is small and

Displays information about all tables or views in a database of an SQL Server.The difference between SQL server 2000 and 2005 lies in the red part.The following statement is used to obtain information about all tables. Replace "U" with "V" with "U"

SQL Server date calculationGenerally, you need to obtain the current date and calculate some other dates. For example, your program may need to determine the first or last day of a month. Most of you probably know how to divide a date (year, month,

Sp_rename N 'table name. Field name', 'new field name', 'column'Sp_renameChange the name of the user-created object (such as a table, column, or user-defined data type) in the current database.SyntaxSp_rename [@ objname =] 'object _ name ',[@

There are many reasons for slow query speed. The following are common causes:1. No index or no index is used (this is the most common problem of slow query and is a defect in programming)2. Low I/O throughput leads to a bottleneck effect.3. the

1. SQL Recursion In SQL Server, we can use table expressions to implement recursive algorithms, which are generally used to prevent organizations from loading and processing their correlations. --> Implementation: Assume that the three main fields

SQL server does not have the bool type, but uses the bit type to represent the bool value, which is estimated to save storage space.However, it may be difficult to implement the reverse operation. For example, the following statement cannot be used:

1. Prepare test data ---------------------------------------------------------------------------------Create table t1 (C1 integer,C2 integer,C3 integer); Insert into t1 values (1, 2, 3) Insert into t1 values (1, 8, 4)Insert into t1 values (1,

1. view the database versionSelect @ version2. view the operating system parameters of the machine where the database is locatedExec master .. xp_msver3. view database startup parametersSp_configure4. view the database startup timeSelect convert

Set SQL Server to use ROW_NUMBER without sorting-- 1. We can see that nhib.pdf is a page written in this way, and it feels easier to write (should it be less efficient ?)-- With is just an alias?[SQL]With query as (select ROW_NUMBER () over (order

Select * from table name where Convert (varchar (100), Date Field, 23) = '2017-12-15'Here is an application of the Convert function:Copy codeThe Code is as follows:Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 AMSelect CONVERT (varchar (1

SQLServer has two extended stored procedures to implement Scanf and Printf functions. They can be used properly to simplify the SQL code while extracting and splicing strings.1. xp_sscanf can be used to break down strings with relatively fixed

At work, I encountered a problem like this: to clear and set the permissions of the database account, one of the users Test, you can only have the DML (Update, insert, delete, etc.) operation permissions of the database MyAssistant, and the