Data Development-Classic

Data Development-Classic

1. Sort by last name stroke:

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 ') = same as; otherwise different encrypt (' original password ')

Select Pwdencrypt (' original password ')

Select Pwdcompare (' original password ', ' encrypted password ') = same;

3. Retrieve the fields from the table:

Declare @listvarchar (+), @sql nvarchar (1000)

Select @[email protected]+ ', ' +b.name from sysobjects a,syscolumns b wherea.id=b.id and a.name= ' Table A ' Set @sql = ' SELECT ' +righ T (@list, Len (@list)-1) + ' from Table A ' exec (@sql)

4. View the hard disk partition:

EXEC Master. Xp_fixeddrives

5. Compare the equality of A/b table:

if (Selectchecksum_agg (binary_checksum (*)) from A)

=

(Select Checksum_agg (binary_checksum (*)) from B) print ' equal ' else print ' not equal '

6. Kill all the event Explorer processes:

DECLARE Hcforeach Cursorglobal for SELECT ' kill ' +rtrim (SPID)

From master.dbo.sysprocesses WHERE program_name in (' SQL Profiler ', N ' SQL Profiler ') EXEC sp_msforeach_worker '? '

7. Record Search:

Start to N Records

Select Top N * FROM table

-------------------------------

N to M record (with primary index ID)

Select Top M-n * FROM table Where ID in (Select top M idfrom table) Order by ID Desc

----------------------------------

N to end record

Select Top N * from table Order by ID Desc

Case

For example 1:

A table with more than 10,000 records, the first field of the table RecID is the self-growth field, write an SQL statement, find the table of the 31st to 40th Records.

Select Top RecId from a where recidnot in (select top of recid from a)

Parsing: If this writes a certain problem, if RecId has a logical index in the table.

Select Top Ten recid from A where ... Is looked up from the index, and the subsequent Selecttop-recid from A is looked up in the datasheet, so that the order in the index is likely to be inconsistent with the data table, which results in a query that is not the intended data.

Solution Solutions

1, with ORDER by Selecttop-RecId from A order by Ricid if the field is not self-growing, the problem occurs

2, in that subquery also add conditions:selecttop recid from A where recid>-1

Example 2: The last record in the query table does not know how much data the table has, and the table structure. Set @s = ' SELECT top 1 * from T where PID is 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 wherextype= ' 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 effect is the same in both ways

11: View views, stored procedures, 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 wherextype= ' P '

13: Querying 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. Sysdatabaseswhere Sid <> 0x01

14: Querying a table for fields and data types

Select Column_name,data_typefrom information_schema.columns WHERE table_name = ' table name '

the : Data manipulation between different server databases

--Create a linked server

exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

exec sp_addlinkedsrvlogin ' 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

--Remove linked server when no longer in use

exec sp_dropserver ' itsv ', ' droplogins '

--Connect remote/LAN data (Openrowset/openquery/opendatasource)

--1, OpenRowset

--query Example

SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Raw cost surface

SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Import local tables to remote tables

Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

Select *from Local surface

--Update the local surface

Update b

Set B. Column a=a. Column A

Fromopenrowset (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B

On A.column1=b.column1

--openquery usage requires creating a connection

--First create a connection to create a linked server

exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

--Query

SELECT *

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ')

--Import local tables to remote tables

Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')

SELECT * FROM local surface

--Update the local surface

Update b

Set B. column b=a. Column B

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ') ASA

Inner JOIN local table B on a. Column a=b. Column A

--3, Opendatasource/openrowset

SELECT *

From OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta

--Import local tables to remote tables

Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name

SELECT * FROM local surface

Data Development-Classic