I suddenly realized one big disadvantage about having code in the database (stored procedures etc) - Restoring
Restoring a database disrupts the versioning. Code and data are not versioned together. Database structure and code are usually tied together.
Say i have a procedure i want to modify.
I edit the SP,
update it in the database
run it.
I find an [...]
Here are some clever SQL for searching strings via soundex or like, as well as finding gaps in sequences etc.
Working a lot with SQL at the moment i found a handy tool for formatting SQL-code.
SqlInform automatic SQL code formatter (http://www.sqlinform.com/)
There is an online version but also a free although time-limited JAVA-program to download. The online version handles just 100 lines of code mind you.
Another online formatter without the 100-line limit. http://www.dpriver.com/pp/sqlformat.htm
Some that i [...]
I found some articles about stored procedures and…
I agree with most of the arguments written in them
My pet peeve about SPs is that its not a good programming language for dealing with logic and control flow. Not at all!
http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
http://home.comcast.net/~eichler2/misc/AntiSP.htm
http://blog.platinumsolutions.com/node/77
Found a regex for getting SQL-code here.
“(/\*[^\*/]*\*/)|(\r\n\s*)|\t|\s{2,}”
I ran into a problem where updating a key column caused duplicate key errors. The table in question had several columns that formed the Primary Key. One of those columns was userd for a serial number making groups of keys unique.
Example
Group
GroupSerial
Apple
1
Apple
1
Pear
1
Pear
1
Here we see that each row is not unique.
So how can we fix this?
First [...]
I’m updating a system with lots of tables and i wanted to perform the same operation on more than 20 of the tables. Writing all those update statements by hand is errorprone and using copy-paste is equally worse.
I wanted to write good code with linebreaks, logmessages and comments. What to do?
Code-generation to the rescue!
1 [...]
When digging through database tables i use this little select-sctatement to find out which tables that contain a scertain column name.
1 — Show tables containing a column
2 select sysobj.name + ‘.’ + syscol.name
3 , ’sp_help ‘ + sysobj.name as sptext
4 , ’select * from ‘ + sysobj.name as selall
5 , [...]
Mats Codemix Mats Codemix på kuff
