<thecfguy blog="sharing thought">

In some cases we may need to rebuild indexes for wholeover the database. There are many ways to rebuild indexes on table but one I like is using DBCC DBREINDEX statement. This one statement we reindex single or all indexes on table. But again this will be very time consuming when you want to rendex for all tables in database.

Hmm... Not to worry we can appy reindexing to all tables by below SQL code.

DECLARE @tablename varchar(200)
DECLARE table_cursor CURSOR FOR 
select [name] from sysobjects where type='U'

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC DBREINDEX (@tablename, '', 80);
    FETCH NEXT FROM table_cursor INTO @tablename
END 
CLOSE table_cursor
DEALLOCATE table_cursor

SQL statement 

select [name] from sysobjects where type='U' 

Keep passion while running this query as it will take time to reindix all tabes.

I really love database diagram feature of SQL server. Recently I have installed Sql server 2008 edition and it diagram feature stop work for me. Showing below annoying error although I have dbowner rights.

"Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."

After doing googling I found solution and it is really simple. Do following steps to enable database diagram.

1. Highlight database for which you want to enable database diagram.
2. Select Property from context menu.
3. Highlight Options and choose SQL Server 2005 (90) from compatibility level dropdown.
4. Highlight Files options and set owner SA or any other user who has dbowner rights. You can click on button next to text field to search available user.
5. Click on OK to save changes.
6. Try to access "Database Diagrams" option again (You may need to refresh the database).

SQL Server 2005 comes with magical WITH clause which make getting hierarchical (recursive) data very easily (just within one query). Let me explain with example. Suppose I like to get manager hierarchy from employee table. 

Employee Table:

employeeId  empName                                            managerId
----------- -------------------------------------------------- -----------
1           R1                                                 NULL
2           R2                                                 1
3           R3                                                 1
4           R4                                                 2
5           R5                                                 2
6           R6                                                 3
7           R7                                                 2
8           R8                                                 3
9           R9                                                 4


Let's say I want to get hierarchical manager list for employee R9 (Id 9) and just try below query.

WITH Managers(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM Employees where employeeId = 9
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM Employees e
        INNER JOIN Managers m
        ON e.EmployeeID  = m.ManagerID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM Managers

And here is magical result

ManagerID   EmployeeID  EmployeeLevel
----------- ----------- -------------
4           9           0
2           4           1
1           2           2
NULL        1           3

 With clause provide recursive execution over the table row which gives this magical result. Hope you may like it.

I was just working around to get list zip code within 25 miles of radius for given address. I have database table for Zip Codes with below columns.

• "zip" - Store zip code
• "zip_latitude" - Store latitude
• "zip_longitude" - Store longitude

I have created MS SQL function which returns sql table object with list of all zip codes within specified radius in which you might interested.

Read more...

Hi All,

SQL Injection attach is biggest problem for web developer. I will always suggest to user <cfqueryparam> in condition which give better performance and top of that it will stop sql injection. While buiding sql query runtime it is not really possible to use <cfqueryparam> tag in such case we require to escape special sql characters.

Single quotes ( ' ) is most dangerous character from where sql Injection normally start and solution is really simple, just replace single quotes with two times single quotes ( '' ) and you are preety safe. I used to create one function (let's say sqlSafe) in Application.cfc file or any class file which is extended to Application.cfc so it is easily accessible to everywhere.

<cffunction name="sqlSafe" access="public" returntype="string" output="false">
    <cfargument name="strVal" required="true">
    <cfscript>
        var sqlList = "',%";
        var replacementList = "'',\%";
        return trim(replaceList( strVal , sqlList , replacementList ));
    </cfscript>
    <cfreturn retStr>
</cffunction>

We just need to call this function before attaching any user input string in sql query. I really appreciate any suggestion which make this function much stronger.