Special SQL statements and Optimization Principles

1. sort by strokes of the Last Name:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2. database encryption:
Select encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different
3. Retrieve the fields in the table:
Declare @ list varchar (1000), @ SQL nvarchar (1000)
Select @ list = @ list + ',' + B. name from sysobjects a, syscolumns B where a. id = B. id and a. name = 'table'
Set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table'
Exec (@ SQL)
4. View hard disk partitions:
EXEC master .. xp_fixeddrives
5. Compare whether tables A and B are equal:
If (select checksum_agg (binary_checksum (*) from)
=
(Select checksum_sum( binary_checksum (*) from B)
Print 'Equality'
Else
Print 'unequal'
6. Kill all event Inspector processes:
DECLARE hcforeach cursor global for select 'Kill '+ RTRIM (spid) FROM master. dbo. sysprocesses
WHERE program_name IN ('SQL filer', n' SQL event profiler ')
EXEC sp_msforeach_worker '? '
7. Record Search:
Starting with N records
Select Top N * From table
-------------------------------
N to M records (primary index ID required)
Select Top M-N * From table Where ID in (Select Top m id From Table) Order by ID Desc
----------------------------------
N to the end record
Select Top N * From Table Order by ID Desc
8. How to modify the Database Name:
Sp_renamedb 'old _ name', 'new _ name'
9: Get all user tables in the current database
Select Name from sysobjects where xtype = 'U' and status> = 0
10: Get all fields of a table
Select name from syscolumns where id = object_id ('table name ')
11. View views, stored procedures, and functions related to a table
Select a. * from sysobjects a, syscomments B where a. id = B. id and B. text like '% table name %'
12: view all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype = 'P'
13: Query all databases created by the user
Select * from master .. sysdatabases D where sid not in (select sid from master .. syslogins where name = 'sa ')
Or
Select dbid, name AS DB_NAME from master .. sysdatabases where sid <> 0x01
14: query the fields and Data Types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'table name'
[N]. [title]:
Select * From TableName Order By CustomerName
[N]. [title]:
Select * From TableName Order By CustomerName
Bytes --------------------------------------------------------------------------------------
Bytes --------------------------------------------------------------------------------------
SQL optimization is a complex task. The following basic principles are recorded when I read a book. They are clear and have no nonsense:
1. Use of indexes:
(1) When the inserted data is more than 10% of the records in the data table, you must first Delete the index of the table to improve the efficiency of data insertion. After the data is inserted, an index is created.
(2) Avoid using functions or calculations in the index column. In the where clause, if the index is a part of the function, the optimizer will not use the index and use full table scanning. For example:
Inefficient: select * from dept where sal * 12> 2500;
Efficient: select * from dept where sal> 2500/12;
(3) Avoid using not and "! = ", The index can only tell what exists in the table, but cannot tell what does not exist in the table, when the database encounters not and"! = ", The full table scan will stop using the index.
(4). Index column> =>
Inefficient: select * from emp where deptno> 3
Efficient: select * from emp where deptno> = 4
The difference between the two lies in that the former dbms will directly jump to the first record with deptno equal to 4, while the latter will first locate the record with deptno equal to 3 and scan forward to the first record with deptno greater than 3.
(5). You have to enable indexing for a column using a function. function-based indexing is a good solution.
2. Use of cursors:
When you delete, update, and insert data in a massive data table, the efficiency of using a cursor is the slowest, but it is essential to use a cursor, therefore, it is very important to use the cursor correctly:
(1 ). use a timestamp in the source table for data extraction. In this way, daily maintenance of dimension table data only applies to data with the latest update date, greatly reducing the number of data records to be maintained.
(2) Add a condition to insert and update dimension tables to filter existing records in the dimension table. For example:
Insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)
Ods_customer is the data source table. Dim_customer is a dimension table.
(3 ). an explicit cursor is used because an implicit cursor performs two operations. The first record is retrieved, and the second exception too rows is checked. The explicit cursor does not perform the second operation.
3. SQL Optimization for data extraction and uploading:
(1). Connection sequence in the Where clause:
Oracle uses the bottom-up sequence to parse the where clause. According to this principle, the join between tables must be written before other where conditions. The conditions that can filter out a large number of records must be written at the end of the where clause. For example:
Inefficient: select * from emp e where sal> 5000 and job = 'manager' and 25 <(select count (*) from emp where mgr = e. empno );
Efficient: select * from emp e where 25 <(select count (*) from emp where mgr = e. empno) and sal> 5000 and job = 'manager ';
(2) When deleting a full table, use truncate instead of delete. Note that truncate is only applicable when deleting a full table, because truncate is ddl rather than dml.
(3) Try to use commit as much as possible
As long as it is possible to use commit as much as possible for each delete, insert, and update operation in the program, the system performance will be greatly improved because of the resources released by commit.
(4). Replacing in with exists can improve the query efficiency.
(5). replace not in with not exists
(6). Optimize group
To improve the efficiency of group by statements, you can filter out unnecessary records before group by statements. For example:
Inefficient: select job, avg (sal) from emp group by job having job = 'President 'or job = 'manager ';
Efficient: select job, avg (sal) from emp having job = 'President 'or job = 'manager' group by job;
(7) conditional use of union-all to replace union: This sorting is unnecessary and the efficiency will be increased by 3 to 5 times.
(8). Separate tables and Indexes
Always create your tables and indexes in different tablespaces, and never store objects that are not in the oracle internal system in the system tablespace. At the same time, ensure that the data table space and index tablespace are placed on disks controlled by Different Hard Disk control cards.

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/21aspnet/archive/2007/03/23/1539227.aspx