3. Develop applications
1. sort by strokes of the Last Name:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as // From less to more
--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int, [Name] varchar (6) insert [a] select 1, 'zhang san' union allselect 2, 'Li si' union allselect 3, 'wang wu' union allselect 4, 'zhao liu' union allselect 5, 'Sun 7' --> query statement (sort by row name) select * from a Order By [Name] Collate Chinese_PRC_Stroke_ci_as/* ID Name ----------- ------ 3 Wang Wu 5 sun 7 1 Zhang 3 2 LI 4 4 Zhao 6 (5 rows affected )*/
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
EXEC master .. xp_fixeddrives/* drive MB space ----- ----------- C 168D 13020.e 57714 (3 rows affected )*/
5. Compare whether tables A and B are equal:
--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int, [Name] varchar (6) insert [a] select 1, 'zhang san' union allselect 2, 'Li si' union allselect 3, 'wang wu' union allselect 4, 'zhao liu' union allselect 5, 'Sun 7' --> test data [B] select * into [B] from [a] if (select checksum_agg (binary_checksum (*)) from A) = (select checksum_print (binary_checksum (*) from B) print 'equal to 'elasticprint' not equal to '/* equal */
6. 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
7. Obtain all user tables in the current database.
Select Name from sysobjects where xtype = 'U' and status> = 0
8. Obtain 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 ')
Select name from syscolumns where id = object_id ('A') select name from syscolumns where id in (select id from sysobjects where type = 'U' and name = 'A ') /* name ---------------------------------- IDName (2 rows affected) name ------------------------------------- IDName (2 rows affected )*/
The two methods have the same effect.
9: 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 %'
10: view all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype = 'P'
11: 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
12: query the fields and Data Types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'table name'
13. 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)
14. 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
