[Recent Entries][Archive][Friends][User Info]
Below are the 10 most recent journal entries recorded in the "Abu Antos'" journal:| February 6th, 2009 | |
|---|---|
| 09:55 am [Link] |
Трохзначная лёгіка наносіць удар у адказ. / Three-valued logic strikes back Аналітыкі адлавілі CREATE TABLE #temp(i int, flag bit NOT NULL) INSERT INTO #temp VALUES(1, 1) INSERT INTO #temp VALUES(2, 0) INSERT INTO #temp VALUES(NULL, 0) --returns 2 and NULL SELECT i FROM #temp WHERE flag=0 --returns 1 SELECT i FROM #temp WHERE flag=1 --you would expect this to return 1, just like the query before, but it returns nothing! SELECT i FROM #temp WHERE i not in (SELECT i FROM #temp WHERE flag=0) --this corrected query returns 1, as it should SELECT i FROM #temp WHERE i not in (SELECT i FROM #temp WHERE flag=0 AND i IS NOT NULL) |
| December 9th, 2008 | |
| 01:34 pm [Link] |
SQL Server question Has anybody ever had any use for @@PROCID other than object_name(@@PROCID) ? Current Music: Die Krupps - Metalmorphosis of Die Krupps |
| April 7th, 2007 | |
| 03:22 pm [Link] |
Граблі Наступілі на граблі, бо трэба чытаць весь TFM, а не толькі першы сказ UPDATE (column) Tests for an INSERT or UPDATE action to a specified column (ага, падумалі суворыя тэхаскія парні, вось гэтае UPDATE() нам і падкажа, ці ўстаўляюць значэньне ў поле) and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. Aле фіг вам, сказалі мелкасофтаўцы, бо IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted. Imported event Original Current Mood: working |
| April 13th, 2006 | |
| 11:01 am [Link] |
SQL Server stinks! SELECT Round(59999.0 * 0.155,2) 9299.8500 - correct SELECT Round(Cast(59999.0 as money) * Cast(0.155 as float),2) 9299.8400000000001 - wrong! Fix: SELECT Round(Cast(Cast(59999.0 as money) * Cast(0.155 as float) as money) ,2) |
| December 21st, 2005 | |
| 03:56 pm [Link] |
CircumCHAR(1)ion Толькі што змарнаваў амаль паўгадзіны, спрабаваў зразумець, чаму мая функцыя ў SQL Server выдавала лабуду. Тый-жа самы запыт, калі яго пусьціць напрасткі, працаваў як трэба. Пікантнасьць дадавала тая абставіна, што функцыі Transact-SQL Debugger дебагіць нельга, толькі працэдуры. Ў рэзультаце знайшоў - памылкова апісаў параметр як CHAR(1) замест CHAR(10) (дрыгнула рука маладога хірурга), а сэрвер, замест таго каб пакрыўдзіцца, што-ж ты, гад, апранаеш дзіцячыя гольфікі на здаровага бугая, паслухмяна абразаў значэньне. І гэта пры тым, што яшчэ 8 год таму ў InterBase магчыма было аб'яўляць свае дамэны! Just wasted almost half an hour trying to understand why my SQL Server function was returning garbage. The same query, when ran directly, was working as expected. Bonus insult points to MSFT for making it impossible to debug functions with Transact-SQL Debugger (stored procedures only). Finally found the gotcha - mistakenly declared an input parameter as CHAR(1) instead of CHAR(10). Instead of screaming in my face, SQL Server was obediently circumcising the value. And boy, back in 1998 InterBase allowed me to create problem-specific domains! |
| October 19th, 2005 | |
| 10:52 am [Link] |
[programmism] DateDiff You would think that the T-SQL function that is called DateDiff would return the difference between the two dates. For example, if your two dates are 10:53 and 10:54 (the difference is 1 minute), and you want to get the difference in hours, you would expect that 1 minute difference would be rounded to 0 hours. It does happen with 10:53 and 10:54, however, you'll be surprised when you calculate the difference between 10:59 and 11:00. A quick check shows that the difference between '1/31/2005 23:59:59' and '2/1/2005' (1 second time interval) is equal to 1 minute, 1 hour, 1 day and 1 month depending on the first argument to DateDiff! SELECT DateDiff(ss, '1/1/2005 1:00:00 AM', '1/1/2005 1:00:01 AM')Of course, it is mentioned in the help - "DATEDIFF Returns the number of date and time boundaries crossed between two specified dates", but who in their right mind decided to implement it like that??? Note to self: only use DateDiff for seconds (ss), as others are not helpful at all.. Current Music: Reverend Glasseye And His Wooden Legs - Black River Falls |
| August 31st, 2005 | |
| 11:49 am [Link] |
AVG - integer division http://community.livejournal.com/ru_pro |
| July 28th, 2005 | |
| 01:26 pm [Link] |
The automatic DBA Why are the RDBMSs so stupid? Why do we need a human DBA to tell the server to create the indices? After all, the server knows what types of queries are being run against the data. So after seeing thousands of queries like "... WHERE A=? AND B=? AND ? BETWEEN C AND D" it should be smart enough to ask itself a question "Given the previous query history, wouldn't it make sence to create non-unique indices on A, B, C and D columns, paying by using extra storage and slowing inserts, but winning in lookup time?" Чаму РСКБД* такія тупыя? Чаму дзеля таго, каб стварыць новыя індэксы, патрэбны чалавек-DBA? Сэрвер-жа мае статыстыку па запытах, чаму-ж пасьля тысяч запытаў кшталту "... WHERE A=? AND B=? AND ? BETWEEN C AND D" ён ня можа аўтаматычна праверыць "А можа, трэба зрабіць неўнікальныя індэксы па палёх A, B, C і D columns - хай на тое спатрэбіцца больш памяці й даданьне новых дадзеных трохі замедліцца, але-ж запыты-чытачы паскорацца?" *Рэляцыйныя сыстэмы кіраваньня базамі дадзеных Tags: sql |
| January 14th, 2005 | |
| 06:45 am [Link] |
Access SQL <> SQL Server SQL -2 Раз пайшла такая п'янка, распавяду пра баг, што мне найболей запомніўся. ( непраграмістым не чытаць ) |
| January 13th, 2005 | |
| 10:00 am [Link] |
Access SQL <> SQL Server SQL А вы ведалі, што вось такі запыт: SELECT A.Field, B.Field, C.Field
FROM A LEFT JOIN B ON A.Key=B.Key
LEFT JOIN C ON A.Key=C.Keyпрацуе ў SQL Server'ы, але выдае памылку ў Access'ы? Дык ведайце. Лечыцца проста:FROM (A LEFT JOIN B ON A.Key=B.Key)
LEFT JOIN C ON A.Key=C.KeyCurrent mood: recovering from a botched attempt to downgrade an SQL Server-based app to an Access-based one.
|