开发经典（Development classics）.docVIP

开发经典（Development classics） Data development - Classics 1. sort by surname strokes: 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 (the original password, encrypted password) = 1-- is the same; otherwise it is not the same as Encrypt ( original password ) Select pwdencrypt (original password) Select pwdcompare (the original password, encrypted password) = 1-- the same; otherwise not the same 3. retrieve the fields from 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 A Set @sql=select+right (@list, len (@list) -1) + from table A Exec (@sql) 4. view the hard disk partition: EXEC master..Xp_fixeddrives 5. compare the A and B tables for equality: If (select, checksum_agg (binary_checksum (*)) from, A) ??? =? ?? (select, checksum_agg (binary_checksum (*)) from, B) Print equal Else Print unequal 6. kill all the profiler process: DECLARE, hcforeach, CURSOR, GLOBAL, FOR, SELECT,Kill,+RTRIM (SPID), FROM, master.dbo.sysprocesses WHERE program_name IN (SQL, profiler, NSQL Profiler) EXEC sp_msforeach_worker ? 7. record search: Start to N record Select Top N * From table Am N to M record (to have the main index ID) 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 For example, 1: a table has more than 10000 records, the first field of the table RecID is the self growing field, and a SQL statement is written to find the thirty-first to fortieth records of the table. Select top 10, recid, from, A, where, recid, not, in (select, top,,, recid, from, A) Analysis: if you do that, you will have some problems if recid has a logical index in the tabl