Database development-classic

1. sort by strokes of the Last Name:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as // From less to more

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
Case
Example 1: A table has more than 10 thousand records. The first RecID field of the table is an auto-increment field. Write an SQL statement to find the 31st to 40th records of the table.

Select top 10 recid from A where recid not in (select top 30 recid from)

Analysis: If this write method is used, some problems may occur. If the recid has a logical index in the table.

Select top 10 recid from A where ...... Select top 30 recid from A is searched from the index, while select top 30 recid from A is searched in the data table. As A result, the order in the index may be different from that in the data table, in this way, the queried data is not originally expected.

Solution

1. Use order by select top 30 recid from A order by ricid. If this field is not auto-incrementing, the problem may occur.

2. In the subquery, the following conditions are added: select top 30 recid from A where recid>-1.

Example 2: query the last record in the table and do not know how much data the table has and the table structure.
Set @ s = 'select top 1 * from T where pid not in (select top '+ str (@ count-1) + 'pid from T )'

Print @ s exec sp_executesql @ s

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 ')

Select name from syscolumns where id in (select id from sysobjects where type = 'U' and name = 'table name ')

The two methods have the same effect.

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'

15. Data operations between databases on different servers

-- Create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

Exec sp_add1_srvlogin 'itsv', 'false', null, 'username', 'Password'

-- Query example

Select * from ITSV. Database Name. dbo. Table Name

-- Import example

Select * into table from ITSV. Database Name. dbo. Table Name

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'itsv', 'droplogins'

-- Connect to remote/LAN data (openrowset/openquery/opendatasource)

-- 1. openrowset

-- Query example

Select * from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Generate a local table

Select * into table from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Import a local table to a remote table

Insert openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

Select * from local table

-- Update local table

Update B

Set B. Column A = a. Column

From openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table Name) as a inner join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

-- Query

Select *

FROM openquery (ITSV, 'select * FROM database. dbo. Table name ')

-- Import a local table to a remote table

Insert openquery (ITSV, 'select * FROM database. dbo. Table name ')

Select * from local table

-- Update local table

Update B

Set B. Column B = a. Column B

FROM openquery (ITSV, 'select * FROM database. dbo. Table name') as

Inner join local table B on a. Column A = B. Column

-- 3. opendatasource/openrowset

SELECT *

FROM opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password'). test. dbo. roy_ta

-- Import a local table to a remote table

Insert opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password'). database. dbo. Table Name

Select * from local table