\r\nuse AdventureWorks;\r\ngo\r\n--Kell az external_access-hez\r\nalter database AdventureWorks set trustworthy on\r\ngo\r\n\r\nexec sp_configure 'clr enabled', '1';\r\nreconfigure with override;\r\ngo\r\n\r\nif object_id('EventLog') is not null drop function EventLog\r\nif exists(select * from sys.assemblies where name = 'SqlClrTest') drop assembly SqlClrTest\r\n\r\ncreate assembly SqlClrTest from 'C:\\sql2008\\SqlClrTest\\bin\\SqlClrTest.dll'\r\nwith permission_set = external_access\r\ngo\r\n\r\n--L\u00e9trehozzuk a t\u00e1bla kimenet\u0171 f\u00fcggv\u00e9nyt,\r\n--ami az eventlogot publik\u00e1lja ki\r\ncreate function EventLog(@logname nvarchar(100))\r\nreturns table \r\n(\r\n Time datetime,\r\n Message nvarchar(max),\r\n Category nvarchar(max),\r\n Source nvarchar(max),\r\n EventId bigint\r\n)\r\nas external name SqlClrTest.TVF.EventLog\r\ngo\r\n\r\ncreate function EventLogWithOrder(@logname nvarchar(100))\r\nreturns table \r\n(\r\n Time datetime,\r\n Message nvarchar(max),\r\n Category nvarchar(max),\r\n Source nvarchar(max),\r\n EventId bigint\r\n)\r\norder (Time asc) --itt az \u00fajdons\u00e1g\r\nas external name SqlClrTest.TVF.EventLog\r\ngo\r\n<\/pre>\nL\u00e1that\u00f3, hogy az f\u00fcggv\u00e9ny l\u00e9trehoz\u00e1sakor meg lehet adni, az adatok milyen sorrendben fognak j\u00f6nni.
\nHa hazudunk a sorrendet illet\u0151en, akkor lek\u00e9rdez\u00e9skor \u00edgy j\u00e1runk:<\/p>\n
Msg 5332, Level 16, State 1, Line 2
\nThe order of the data in the stream does not conform to the ORDER hint specified for the CLR TVF ‘dbo.EventLog’. The order of the data must match the order specified in the ORDER hint for a CLR TVF. Update the ORDER hint to reflect the order in which the input data is ordered, or update the CLR TVF to match the order specified by the ORDER hint.<\/p>\n
\u00c9n az event loggal j\u00e1rtam \u00edgy. Elvileg az event log id\u0151 szerint sorba van rendezve. Gyakorlatilag azonban amikor az os-t telep\u00edtettem, akkor az alap\u00e9rtelmezett -8 \u00f3r\u00e1s id\u0151z\u00f3na volt megadva, amit telep\u00edt\u00e9s ut\u00e1n \u00e1ll\u00edtottam \u00e1t. Ett\u0151l az id\u0151 is vissza\u00e1llt 9 \u00f3r\u00e1val, \u00edgy az event log elej\u00e9n voltak nem id\u0151rendi bejegyz\u00e9sek.<\/p>\n
Mi\u00e9rt j\u00f3 megadni a sorrendet? Vess\u00fck \u00f6ssze ezt a k\u00e9t lek\u00e9rdez\u00e9st \u00e9s a v\u00e9grehajt\u00e1si terv\u00fcket:<\/p>\n
\r\nselect *\r\nfrom dbo.EventLog(N'Application')\r\norder by Time\r\n\r\nselect *\r\nfrom dbo.EventLogWithOrder(N'Application')\r\norder by Time\r\n<\/pre>\n
<\/a><\/p>\nA val\u00f3di k\u00f6lts\u00e9g persze nem felt\u00e9tlen \u00edgy oszlik meg, de biztos j\u00f3l m\u00e9rhet\u0151 a hat\u00e1s.
\n\u00c9szrevette mindenki az Assert oper\u00e1tort? \u0150 a hazugs\u00e1gvizsg\u00e1l\u00f3, \u0151 veszi \u00e9szre, ha m\u00e9gse j\u00f3 a be\u00edg\u00e9rt sorrend.<\/p>\n
A k\u00f6vetkez\u0151 p\u00e9ld\u00e1ban az eredm\u00e9nyek nagyon gyorsan gener\u00e1l\u00f3dnak, \u00edgy jobban l\u00e1tszik a rendez\u00e9s k\u00f6lts\u00e9ge. Egy egyszer\u0171 hatv\u00e1nyoz\u00f3, kamatos-kamat sz\u00e1m\u00edt\u00f3 f\u00fcggv\u00e9nyt mutatok:<\/p>\n
\r\n[SqlFunction(FillRowMethodName = "FillAustinPowersRow")]\r\npublic static IEnumerable AustinPowers(SqlDouble baseNumber, SqlDouble power, SqlInt32 first, SqlInt32 last)\r\n{\r\n if (baseNumber.IsNull || power.IsNull || first.IsNull || last.IsNull)\r\n {\r\n return null;\r\n }\r\n if (first < 0)\r\n {\r\n throw new ArgumentOutOfRangeException("first",\r\n "A sorozat els\u0151 eleme min. 0 kell legyen.");\r\n }\r\n if (first > last)\r\n {\r\n throw new ArgumentOutOfRangeException("first, last",\r\n "A sorozat els\u0151 elem\u00e9nek kisebb vagy egyenl\u0151 sorsz\u00e1m\u00fanak kell lenni a utols\u00f3 elemn\u00e9l.");\r\n }\r\n return AustinPowersWorker(baseNumber.Value, power.Value, first.Value, last.Value);\r\n}\r\n\r\nstatic IEnumerable AustinPowersWorker(double baseNumber, double power, int first, int last)\r\n{\r\n double curr = baseNumber * Math.Pow(power, first);\r\n yield return curr;\r\n\r\n for (int i = first; i < last; i++)\r\n {\r\n curr *= power;\r\n yield return curr;\r\n }\r\n}\r\n\r\npublic static void FillAustinPowersRow(object obj, out SqlDouble num)\r\n{\r\n num = new SqlDouble((double)obj);\r\n}\r\n[\/source]\r\n\r\nH\u00e1t nem im\u00e1dni val\u00f3 ez a yield? \r\n\r\n[source='sql']\r\ncreate function Austin(@base float, @power float, @first int, @last int)\r\nreturns table \r\n(\r\n power float\r\n)\r\nas external name SqlClrTest.TVF.AustinPowers\r\ngo\r\n\r\ncreate function Austin2(@base float, @power float, @first int, @last int)\r\nreturns table \r\n(\r\n power float\r\n)\r\norder (power asc)\r\nas external name SqlClrTest.TVF.AustinPowers\r\ngo\r\n[\/source]\r\n\r\nTesztek:\r\n[source='sql']\r\nselect power from dbo.Austin2(4000000, 1.001, 0, 100000)\r\norder by power\r\ngo\r\nselect power from dbo.Austin(4000000, 1.001, 0, 100000)\r\norder by power\r\n[\/source]\r\n\r\nV\u00e9grehajt\u00e1si id\u0151k: 26000ms vs. 200ms. Ez m\u00e1r jelent\u0151s. R\u00e1ad\u00e1sul, ha nem sz\u00e1mok j\u00f6nnek ki a TVF-b\u0151l, hanem pl. stringek, akkor a rendez\u00e9s m\u00e9g sokkal k\u00f6lts\u00e9gesebb. (A m\u00e9r\u00e9sekn\u00e9l kikapcsoltam az eredm\u00e9nyek megjelen\u00edt\u00e9s\u00e9t a Management Studioban, hogy annak az ideje lehet\u0151leg ne l\u00e1tsszon a kimenetben.)\r\n\r\nAmi viszont furcsa, hogy kb. 10 futtat\u00e1sb\u00f3l egyszer lefut p\u00e1r 100 ms alatt az els\u0151 f\u00fcggv\u00e9ny is. Nem tudom, mi lehet az oka.\r\n\r\nA k\u00f6vetkez\u0151 p\u00e9lda f\u00e1jlokat list\u00e1z egy k\u00f6nyvt\u00e1rban, amely list\u00e1t elvileg az NTFS rendezetten ad vissza:\r\n\r\n[source='csharp']\r\n[SqlFunction(FillRowMethodName = "FillDirListRow")]\r\npublic static IEnumerable DirList(SqlString startDir)\r\n{\r\n if (startDir.IsNull)\r\n {\r\n return null;\r\n }\r\n return Directory.GetFiles(startDir.Value);\r\n}\r\n\r\npublic static void FillDirListRow(object obj, out SqlString fileName)\r\n{\r\n fileName = new SqlString((string)obj);\r\n}\r\n[\/source]\r\n\r\n[source='sql']\r\ncreate function DirList(@base nvarchar(300))\r\nreturns table \r\n(\r\n FileName nvarchar(300)\r\n)\r\norder (FileName asc)\r\nas external name SqlClrTest.TVF.DirList\r\ngo\r\nselect * from dbo.DirList('c:\\windows\\system32')\r\n[\/source]\r\n\r\nNos, elk\u00fcldenek a n\u00e1th\u00e1s fr\u00e1szba, hogy m\u00e1r megint be\u00edg\u00e9rtem egy sorrendet, de nem tartottam be. No, mondom, ez \u00e9rdekes lesz, ezek szerint m\u00e9gse rendezetten adja vissza a neveket az NTFS? N\u00e9zz\u00fck a bibis v\u00e1lt\u00e1st:\r\n\r\n[source='c']\r\n11\/02\/2006 01:34 PM 6,656 blbres.dll\r\n11\/02\/2006 01:34 PM 17,408 blb_ps.dll\r\n[\/source]\r\n\r\nHopp\u00e1, igaza van, ez t\u00e9nyleg nem stimmel. WTF? N\u00e9mi gugliz\u00e1s ut\u00e1n f\u00e9ny der\u00fclt a titokra.\r\n<a href="http:\/\/blogs.msdn.com\/oldnewthing\/archive\/2005\/06\/17\/430194.aspx">Why do NTFS and Explorer disagree on filename sorting?<\/a>\r\n<a href="http:\/\/blogs.msdn.com\/michkap\/archive\/2005\/01\/16\/353873.aspx">How [case-]insensitive (apologies to Frank Sinatra)<\/a>\r\n\r\nAz els\u0151 linken Raymond Chen bar\u00e1tunk elmondja, hogy az NTFS nem a mi \u00f6r\u00f6m\u00fcnkre rendez, hanem a saj\u00e1t hat\u00e9konys\u00e1ga \u00e9rdek\u00e9ben, \u00edgy ne is v\u00e1rjuk t\u0151le, hogy majd mindenf\u00e9le nyelvi szab\u00e1lyok szerint rendezze a kimenetet, rendezi, ahogy neki j\u00f3. Amikor leform\u00e1zzuk a k\u00f6tetet, akkor kialakul egy sorrend, amit azt\u00e1n semmif\u00e9le locale v\u00e1lt\u00e1s m\u00e1r nem befoly\u00e1sol. Sz\u00f3val eset\u00fcnkben m\u00e1s az SQL Server \u00e1ltal elv\u00e1rt sorrend, mint amelyet az NTFS haszn\u00e1l rendez\u00e9shez. Ok, az NTFS fekete doboz, de milyen sorrendben gondolkodik az SQL Server ebben az esetben? M\u00e1sk\u00e9ppen fogalmazva, mi a TFV-\u00fcnk egyetlen oszlop\u00e1nak a COLLATION-je?\r\nA <a href="http:\/\/msdn2.microsoft.com\/en-us\/library\/ms179886.aspx">doksi<\/a> alapj\u00e1n Coercible-default, azaz "... If the object is declared in a batch, the object is assigned the default collation of the current database for the connection.". Ez vonatkozik r\u00e1nk. Az adatb\u00e1zis SQL_Latin1_General_CP1_CI_AS collation-\u0171, ez az AdventureWorks alap\u00e9rtelmezett elmebeteg collation-je.\r\nHogyan lehetne m\u00e1s collation-t r\u00e1mondani a TFV kimenet\u00e9re? Mi sem egyszer\u0171bb:\r\n\r\n[source='sql']\r\ncreate function DirList(@base nvarchar(300))\r\nreturns table \r\n(\r\n FileName nvarchar(300) collate Latin1_General_CI_AS\r\n)\r\norder (FileName asc)\r\nas external name SqlClrTest.TVF.DirList\r\n<\/pre>\nEtt\u0151l nem lesz j\u00f3 a f\u00fcggv\u00e9ny\u00fcnk, de l\u00e1tjuk, hogyan lehet collation-t megadni az oszlop defin\u00edci\u00f3ban. :)
\nA kis-nagybet\u0171 mappel\u00e9s az NTFS-ben a $UpCase nev\u0171 system metadata file-ban t\u00e1rol\u00f3dik, ha valaki megmutatja, hogyan kell ezt olvasni, megpr\u00f3b\u00e1lom kital\u00e1lni, van-e hozz\u00e1 ill\u0151 collation a szerverben.<\/p>\n
\u00d6sszegezve: ha a TVF \u00e1ltal publik\u00e1lt adatok elve valamilyen sorrend szerint vannak rendezve, akkor ezt a rendezetts\u00e9get kommunik\u00e1lhatjuk az SQL Server 2008 fel\u00e9, \u00edgy a rendez\u00e9seket, csoportos\u00edt\u00e1sokat vagy a distinct m\u0171veleteket jelent\u0151sen gyors\u00edthatjuk.
\nEmellett (nem trvi\u00e1lis \u00e9s kev\u00e9sb\u00e9 ismert inf\u00f3), ha egy t\u00e1bl\u00e1ba sz\u00farjuk be a TVF-b\u0151l kiszelekt\u00e1lt adatokat, \u00e9s a a c\u00e9lt\u00e1bla clu indexet tartalmaz, ami pont a TVF kimenet\u00e9nek megfelel\u0151 sorrendet \u00edr el\u0151, akkor sokkal gyorsabb lesz az insert. Err\u0151l m\u00e1r \u00edrtam egyszer<\/a>.<\/p>\nDe, hogy itt is l\u00e1ssuk a hat\u00e1st:<\/p>\n
\r\nif object_id('tempdb..#Powers') is not null drop table #Powers\r\n\r\ncreate table #Powers\r\n(\r\n id int not null identity,\r\n power float not null\r\n)\r\n\r\ncreate clustered index idx1 on #Powers(power);\r\ngo\r\n\r\ninsert #Powers\r\nselect power from dbo.Austin2(4000000, 1.001, 0, 100000)\r\noption (recompile)\r\n\r\ninsert #Powers\r\nselect power from dbo.Austin(4000000, 1.001, 0, 100000)\r\noption (recompile)\r\n<\/pre>\nA t\u00e1bla pont \u00fagy van rendezve a clu index miatt, mint a TVF kimente. Emiatt a k\u00e9t insert v\u00e9grehajt\u00e1si terve is m\u00e1s lesz:<\/p>\n