I am sure this is a pretty common scenario in every developer’s coding journey. So I thought I would write up a little post mainly to remind me different ways of doing this as I always end up searching for it whenever I need this.

Lets say you have a stored procedure that takes input parameters and runs a query based on those parameteres. To keep things simple, I will work with only one parameter in this example. So, here is the basic stored procedure Read the rest of this entry »

Tags: SQL

I came across a nice tip while reading this article. If you want to get the number of rows in a table , you would normally do a select count(*) from tbl query, which does a table scan and can be a little time consuming query if the table has millions of rows.

This little query gives you all the counts for every table in your database, pretty quickly and instantly. Read the rest of this entry »

Tags: SQL

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. Read the rest of this entry »

Tags: SQL

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.

Tags: SQL

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.

SELECT SUBSTRING('YesNo', 4 - 3 * columnName, 3) as YesNo FROM TableName

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.

Tags: SQL

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. Read the rest of this entry »

Tags: SQL