SQL Server

It is convenient to delete all data tables in the database and clear the database. There are some constraints that cannot be deleted directly. You need to delete the constraints in the database first. The Code is as follows:Copy codeThe Code is as

Misunderstanding #27: Using BACKUP... with checksum can replace DBCC CheckDBErrorAt first glance, because backup with checksum will detect the CHECKSUM values of all allocated pages, this misunderstanding seems to be the case, but in fact it is not

The default datetime format of the Chinese version of SQL Server is yyyy-mm-dd Thh: mm: ss. mmm.For example:Select getdate ()I sorted out the date format conversion methods that may be frequently used in SQL Server:Example:Select CONVERT (varchar,

Here we use excel Data for the simplest test.1) Save the excel data as a t.txt file. Note that the file suffix is. txt. 1 jhchen 11/07/2005 20:04:00 2005-11-7 20:042 jhchen 11/07/2005 20:04:00 2005-11-7 20:043 jhchen 11/07/2005 20:04:00 2005-11-7 20:

Take SQL Server 2012 as an Example Is part of the database, we executeCopy codeThe Code is as follows:Deny view any DATABASE to PUBLIC;Then run the command for the Best user of the Best database.Copy codeThe Code is as follows:Alter authorization

Create a stored procedureYou can store table names and comparison fields as parameters.Create PROCEDURE sp_getTeacherInfo@ TblName nvarchar (30), -- table name@ CmpStr nvarchar (30), -- value used for comparison@ Tblasd nvarchar (30) -- fields

Question: retrieve data between 100 and 200 entries in a tableMethod 1: temporary tableCopy codeThe Code is as follows:Select top 200 * into # aa from table order by time -- Insert the top m pen into the temporary tableSet rowcount 100Select * from #

1. Search for duplicate records① If you just don't want repeated records in the query results, you can add DistinctSelect distinct * from TestTable② If You Want to query repeated records and their quantity Select UserID, UserName, count (*) as

Note: The delete statement is a delete statement without the where clause.SimilaritiesTruncate, delete without where clause, and drop both delete table data.Differences:1. truncate and delete: delete only data. Do not delete the table structure

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

/*-- 1 the UNION operator combines two or more query results into a single result set.There are two basic rules for query result sets using UNION:1. The columns and columns in all queries must be in the same order.2. Data type must be compatibleA.

1. xml. existIf the input is an XQuery expression, 0, 1 or Null is returned. 0 indicates no, 1 indicates yes, and Null indicates that the input is Null.2. xml. valueThe input is an XQuery expression and returns an SQL Server scalar value.3. xml.

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [f_ SQL] ') and xtype in (n'fn', n'if', n'tf '))Drop function [dbo]. [f_ SQL]GOIf exists (select * from dbo. sysobjects where id = object_id (n' [ordinal table] ') and

Table operationsExample 1 Table STUDENTS in the table teaching management database can be defined as follows: Copy codeThe Code is as follows:Create table students (sno numeric (6, 0) NOT NULLSname char (8) NOT NULLAge numeric (3, 0)Sex char

Note: copy a table (only copy structure, source table name: a new table name: B)Select * into B from a where 1 <> 1 Description: copy a table (copy data, source table name: a target table name: B)Insert into B (a, B, c) select d, e, f from B;

The connection can be established in the FROM clause or Where clause of the Select statement. It is similar that it helps to distinguish the connection operation FROM the search conditions in the Where clause when the clause points out the

Run the following code in the SQL query analyzer.Copy codeThe Code is as follows:Declare @ t varchar (255), @ c varchar (255)Declare table_cursor cursor for select a. name, B. nameFrom sysobjects a, syscolumns B, policypes cWhere a. id = B. id and a.

In SQL server, the Replace keyword is mainly used to Replace a character in a string with another character. Today, we need to reverse thinking. It can also be used to query whether a specific string is contained, for example, given the following

First, place the file to be restored in the DATA file in ms SQL, and enter the ms SQL master database server.1. We use the default method to create a database for recovery (such as MHDYF2005 ). It can be created in SQL Server.2. Stop the database

Syntax: Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ]{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]