1. What SQL statement is used to find the same record in a database? Suppose the data table is Tablea, I want to find the same record in the columna field name.
Solution:
1), Select * from Tablea a where exists (SELECT 1 from Tablea Where [PK] <> a. [Pk] and colorna = a.columna)
Note: The PK is the primary key or the field of Unique.
2), SELECT * from Tablea Where Columna in (Select Column From Tablea Group by Columna Having Count (Columna)> 1)
2. There are two two or more records in a table, how to delete them with SQL statements, only retain one
Workaround: delect Table WHERE ID NOT IN (SELECT MAX (ID) from Table Group By Column1, Column2 ...) Note: The ID is the unique field in the table. The field followed by the Group By clause is the condition you use to determine the repetition. Do the latest record with "max"
3, database recursive
Simple example:
Create Proa @ @i int as if @i> 5 Return Print @i set @ i = @ i 1 exec paoa @i ---------- Execution: Exec Proa 0
Results ---------- 0 1 2 3 4 5
4. Use the CASE statement to generate a new result (Table Name: Tablea)
Original data ID columna --------- 1 a 2 B
Workaround: Select ID, Columna, Case Columna When 'a' Ten 100 When 'b' Then 200 End As Columnb from Tablea
Results ID Columna Columnb -------------------- 1 A 100 2 B 200
5. Application of the temporary table (existing table Tablea field columna), how to add a self-increasing ID when display
Original data Columna ------ A B
Solution: Select ID = Identity (int, 1, 1), * INTO #Temp from Tablea Select * from #temp
Result ID Columna ------------ 1 A 2 B
6, the trigger uses (existing table Tablea, field [Column, Columnb] Tableb, Field [Columna, Columnb, Columnc], how to add records in Tablea, also add records in Tableb, and also delete when deleting.
Solution ----------- Add trigger ----------- Create Trigger [a_insert] on [Tablea] for INSERT AS INSERT INTO TABLEB (Columna, ColumnB) Select A. Columna, A.Columnb from Tablea a where a.columna in (Select B.Column from Inserted B)
----------- Delete trigger ----------- Create Trigger [A_DEL] ON [TABLEA] for delete as delete from Tableb Where Column In (SELECT Column from Delete) Note : Columna as the primary key
7. Distinct's application (existing table Tablea, Field Columna), how to eliminate duplicate rows
Original data Columna ------ A A B B
Workaround: 1, Select Distinct Column from Tablea 2, Select Column from Tablea Group by Columna GROUP
Results Columna ---- A B
8, the use of Convert, the table (the color "type is DataTime, the removed data is '0000-0 00:00:00', how to take out the form is '0000-00-00'
Workaround: Select Convert (Char (10), Columna, 120) AS RQ from Tablea
9, TOP application, table (Tablea), field ID, columna. How to remove some records in the middle
Original data ID columna ------------- 1 A 2 B 3 C 4 D 5 E
How to take out 3-4 records Solution: SELECT TOP 2 * from Tablea Where Id Not in (SELECT TOP 2 ID from Tablea)
Result ID Columna ------------- 3 C 4 D
10. How to use Between (Table Tablea, Field ID, Column [DateTime], Columnb [DateTime]) How to remove records.
Original data ID columna columnb ------------------------------ 1 2003-1-1 2003-2-1 2 2003-2 -2 2003-3-1 3 2003-3-2 2003-4-1 4 2003-4-2 2004-1-1
How to get the '2003-11-11' which range solution:
Declare @dt datetime set @ dt = '2003-11-1'
Select * from tablea where @dt between [columna] and [colornb] Go
Result ID Columna Columnb ------------------------------ 4 2003-4-2 2004-1-1
10, database log
1), cut off transaction log: Backup log database name with no_log
2), empty log Dump Transaction library name with no_log
Again: Enterprise Manager - Right click on the database you want to compress - All Tasks - Shrink Database - Shrink File - Select Log File - Select to shrink to XXM in the shrink mode, here will give an allowable shrinkage to The minimum number, enter this number directly, it is ok.
3) Delete LOG 1: Separate Database Enterprise Manager -> Server -> Database -> Right button -> Separate Database 2: Delete Log File 3: Additional Database Enterprise Manager -> Server -> Database -> Right -> Additional Database This method generates a new log, only 500 k only 500 k, then set this database to use code: Separate the PUBS below, and then attach one of the PUBS to the current server. EXEC SP_DETACH_DB @dbname = 'pubs' exec sp_attach_single_file_db @dbname = 'pubs', @physname = 'c: / program files / microsoft sql server / mssql / data / pubs.mdf'
4) If you want to make it grow Enterprise Manager - Server - Right-click Database - Property - Transaction Log - Limit the file growth to xm (x is your maximum data file size you allow)
--SQL statement setting mode: Alter Database Database name modify file (name = logical file name, maxsize = 20)
5), set to Automatic Continue Enterprise Manager - Server - Right click Database - Property - Option - Select "Auto Shrink"
