I was recently stuck with a problem where I had to loop over a self referencing table (unlimited parent child relationships from the same table) and generate a tree structure upto n levels and because my table didnt have the levels defined in it, the tree leveles were to be defined at run time. What most people would do in this case is start from parent level and loop over until you reach the last level, this loop can be either in your scripting language (CF in my case) or a recursive stored procedure or a really complex query. I would have done the same if I didnt come across Common Table Expressions (CTE’s) which were introduced in Sql Server 2005. More »
I have always used INFORMATION_SCHEMA to some extent in some way or the other. Its quite handy to get information about database objects. This morning, I saw a nice article about this on SqlServerCentral. The author’s name is Mike and he does a pretty good job in explaining the concepts. There is also a stored procedure to search a specific value database-wide.
I recently found out that INFORMATION_SCHEMA is not only Sql Server specific (which is what I used to think). MySql supports this as well. Details here.
I was reading some SQL tips and found this small but quite handy tip. If you have a boolean column in your database which holds values like 0 and 1 , you can modify your Select queries to represent that data as a Yes or No by doing this.
The trick is if the column holds a value of 0, the SQL would read
SUBSTRING(’YesNo’, 4, 3) which returns ‘No’
And if the Column has a value of 1, it becomes SUBSTRING(’YesNo’, 1, 3) which returns ‘Yes’.
I like it. This was part of a bigger article found here.
Before I begin, this post is specific to Microsoft Sql Server, for those who care.
A little background :-
If you migrate your databases from Sql Server 7.0 to Sql Server 2005, you are going to have to deal with this at some point, I think. So, once you you have your databases migrated into Sql 2005, you will have to start worrying about backups as well and having nightly backups is a sensible thing to do. Unless you write a custom T-SQL script to do this, chances are that you will end up using the inbuilt Maintenance Plans that come with Sql Server. For people like me, who are not DBA’s but sometimes have to wear different hats to do different kinds of stuff, this is all a little known domain. More »