1 / 20

Insensitive – Not a Bad Thing for Data

Insensitive – Not a Bad Thing for Data. Noreen Redden Information Builders. Why Case Insensitivity Two Worlds. Why ? How? Problem?. Why Case Insensitivity Two Worlds: Selection. SQL SELECT T1. " LN " ,T1. " FN “ ,T1. " PAY " FROM master.dbo.EMPSQL T1

etana
Download Presentation

Insensitive – Not a Bad Thing for Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Insensitive – Not a Bad Thing for Data Noreen Redden Information Builders

  2. Why Case InsensitivityTwo Worlds Why ? How? Problem?

  3. Why Case InsensitivityTwo Worlds: Selection SQL SELECT T1."LN",T1."FN“,T1. "PAY" FROM master.dbo.EMPSQL T1 WHERE (T1. "LN" = 'deFazio‘); DEFAZIO ANTHONY 10,000 deFazio Anthony 10,000 DeFazio Anthony 10,000 TABLE FILE EMPSQL PRINT LN FN PAY WHERE LN EQ 'deFazio' END deFazio Anthony 10,000

  4. Why Case InsensitivityTwo Worlds: Selection – NOT Optimized TABLE FILE EMPSQL PRINT LN FN PAY WHERE UPCASE(15,LN, 'A15‘) EQ ‘DEFAZIO' END DEFAZIO ANTHONY 10,000 deFazio Anthony 10,000 DeFazio Anthony 10,000 SQL SELECT T1."LN",T1."FN",T1.CDEPT,T1."CDIV",T1.PAY FROM master.dbo.EMPSQL T1

  5. Why Case InsensitivityTwo Worlds: Selection TABLE FILE EMPSQL SUM PAY BY LN BY FN WHERE LN EQ 'deFazio' END SQL SELECT T1."LN",T1."FN", SUM(T1."PAY“) FROM master.dbo.EMPSQL T1 WHERE (T1. "LN" = 'deFazio‘) GROUP BY T1."LN",T1."FN“ ORDER BY T1."LN",T1."FN"; DEFAZIO ANTHONY 30,000

  6. Why Case InsensitivityTwo Worlds: Sorting TABLE FILE EMPSQL PRINT FN PAY BY LN WHERE UPCASE(15,LN, 'A15‘) EQ ‘DEFAZIO' OR ‘FAZIO' END DEFAZIO ANTHONY 10,000 DeFazio Anthony 10,000 FAZIO Anthony 9,000 Fazio Anthony 11,000 deFazio Anthony 10,000 Fazio Anthony 10,000

  7. COLLATION Why ? How? Problem? Collation is defined as a set of rules that apply to the ordering and matching of all language elements that involve comparison of two values

  8. Collation is SettableRelease 7.7 SET COLLATION = BINARY SRV_CI SRV_CS CODEPAGE • BINARY Bases collation sequence on binary values • SRV_CI Bases collation sequence on LANGUAGE • Setting, and is INSENSITIVE • SRV_CS Bases collation sequence on LANGUAGE • Setting and is SENSITIVE • CODEPAGE Bases collation on the code page in effect, and is • SENSITIVE.

  9. Collation is SettableRelease 7.7 A ? a A ? b 1 ? A

  10. Insensitive ServerSelection TABLE FILE EMPSQL PRINT LN FN PAY WHERE LN EQ 'deFazio' END SQL SELECT T1."LN",T1."FN“,T1. "PAY" FROM master.dbo.EMPSQL T1 WHERE (T1. "LN" = 'deFazio‘); DEFAZIO ANTHONY 10,000 deFazio Anthony 10,000 DeFazio Anthony 10,000

  11. Insensitive ServerSorting TABLE FILE EMPSQL PRINT LN FN PAY BY LN WHERE LN EQ ‘DeFazio' OR ‘Fazio' END DEFAZIO DEFAZIO ANTHONY 10,000 DeFazio Anthony 10,000 deFazio Anthony 10,000 Fazio Fazio Anthony 11,000 FAZIO Anthony 9,000 Fazio Anthony 10,000

  12. Selection Why ? How? Problem?

  13. Insensitive ServerTemporary Files - FIXRETRIEVE TABLE FILE EMPSQL PRINT PAY BY LN BY FN WHERE LN EQ ‘defazio’ OR ‘fazio’ OR ‘deanna’ ON TABLE HOLD AS TEMP FORMAT ALPHA END Deanna Maria 25,000 DEFAZIO ANTHONY 10,000 DEFAZIO Anthony 10,000 DEFAZIO Anthony 10,000 Fazio Anthony 11,000 Fazio Anthony 9,000 Fazio Anthony 10,000

  14. Insensitive ServerTemporary Files TABLE FILE TEMP PRINT PAY BY LN BY FN WHERE LN EQ ‘defazio’ END FIXRETRIEVE Would STOP here. Deanna Maria 25,000 DEFAZIO ANTHONY 10,000 DEFAZIO Anthony 10,000 DEFAZIO Anthony 10,000 Fazio Anthony 11,000 Fazio Anthony 9,000 Fazio Anthony 10,000 If Server is set Case INSENSITIVE WHERE test is on an alpha “key” FIXRETRIEVE is set OFF

  15. Insensitive ServerJOIN TABLE FILE EMPSQL PRINT PAY BY LN BY FN WHERE LN EQ ‘defazio’ OR ‘fazio’ OR ‘deanna’ ON TABLE HOLD AS TEMP1 FORMAT ALPHA END Deanna Maria 25,000 DEFAZIO ANTHONY 10,000 DEFAZIO Anthony 10,000 DEFAZIO Anthony 10,000 Fazio Anthony 11,000 Fazio Anthony 9,000 Fazio Anthony 10,000

  16. Insensitive ServerJOIN EMP2 file created in prior release (Case Sensitive) TABLE FILE EMP2 PRINT * END deanna Maria 25,000 fazio Anthony 11,000 samuels Joseph 15,000 DEFAZIO ANTHONY 10,000

  17. Insensitive Server JOIN LN IN EMP2 TO MULTIPLE LN IN TEMP AS AJ TABLE FILE EMP2 PRINT * END JOIN to a Sequential File: Both files Must be in ascending order on COMMON KEY Correct sequence is determined by setting of collation. TABLE FILE EMP2 PRINT * BY LN ON TABLE HOLD AS EMP3 FORMAT FOCUS INDEX LN END JOIN LN IN EMP3 TO MULTIPLE LN IN TEMP AS AJ TABLE FILE EMP3 PRINT * END

  18. Insensitive Server • JOIN to FOCUS/FIX files to be reliable must • Use numeric keys • Use Keys that follow a specific pattern: Unnn • Have been created using the same collation sequence. • JOIN to (or selection from) case sensitive files to be reliable must • Use numeric keys • Use Keys that follow a specific pattern: Unnn • Avoid indexes for selection.

  19. The Sensitive WorldCome in and Stay Set it And Forget it

  20. The Sensitive WorldCome in and Stay Why ? How? Problem? What’s Next ?

More Related