My Thoughts & Views

Problem: You have a table (table1) & values as above, and it contains the duplicate records as shown, how will you delete the duplicates?

SQL Server has a SQL Profiler tool, which helps Security Administrators to have a complete view of users who have logged in or out of the server.This information can be used for legal purposes to document activity & for technical purposes to track security policy violations.

For more information please go through the documentation provided with your SQL Server.

Here is the sequence generator function written in SQL, which when passed starting and end numbers will generate the rest sequence automatically.

CREATE FUNCTION fn_p(    @exp  int)
RETURNS @result TABLE (id int)
AS
BEGIN   
      DECLARE @pow int
     SET @pow = POWER(10, @exp)
     INSERT @result        SELECT 0 id UNION ALL         SELECT 1 * @pow UNION ALL        SELECT 2 * @pow UNION ALL        SELECT 3 * @pow UNION ALL        SELECT 4 * @pow UNION ALL        SELECT 5 * @pow UNION ALL        SELECT 6 * @pow UNION ALL        SELECT 7 * @pow UNION ALL        SELECT 8 * @pow UNION ALL        SELECT 9 * @pow    RETURNENDGO

CREATE FUNCTION fn_sequence
(    
@start int,   
@end  int
)
RETURNS @result TABLE(id int)
AS
BEGIN   
IF @start > @end
RETURN       
IF @end < 10       
INSERT @result        SELECT id FROM        (            SELECT                a0.id id            FROM                fn_p(0) a0        ) d        WHERE            id BETWEEN @start AND @end    ELSE
    IF @end < 100        INSERT @result        SELECT id FROM        (            SELECT                (a1.id + a0.id) id            FROM                fn_p(1) a1 CROSS JOIN                fn_p(0) a0        ) d        WHERE id BETWEEN @start AND @end    ELSE
    IF @end < 1000        INSERT @result        SELECT id FROM        (                SELECT                (a2.id + a1.id + a0.id) id            FROM                fn_p(2) a2 CROSS JOIN                fn_p(1) a1 CROSS JOIN                fn_p(0) a0        ) d        WHERE id BETWEEN @start AND @end            ELSE
    IF @end < 10000        INSERT @result        SELECT id FROM        (            SELECT                (a3.id + a2.id + a1.id + a0.id) id            FROM                fn_p(3) a3 CROSS JOIN                fn_p(2) a2 CROSS JOIN                fn_p(1) a1 CROSS JOIN                fn_p(0) a0        ) d        WHERE id BETWEEN @start AND @end     
    RETURNENDGO

The following query gives 3rd highest records from SQL.

SELECT TOP(1) salary FROM ( SELECT TOP(3) salary FROM Emp ORDER BY salary DESC)

Keywords: getting nth highest record, nth highest record, TOP

1. Following Query displays all the table names.

Query : select name from sys.objects where type=‘u’ ;

2. Following Query displays all the columns in a table

Query : select Table_Name,Column_Name from INFORMATION_SCHEMA.COLUMNS ;

How To: Re index Entire database

Applies to : SQL Server 2000/ 2005

Recently i moved some database to new server and wanted to reindex entire Sql Server database, as people were complaining about slow response for our .NET WinForms application.

I thought it might save some body’s hours of time who is searching for the solution.
Problem :
You want to increase the performance of your database so you move Sql Server from one machine to another machine which has higher capacity than the older one. But after migration users start complaining that their application response is slow.

Soultion :
This is what i done to solve the above problem. I read somewhere that re indexing and reorganizing will help to improve some performance. So here is how to do it.

System Stored Provedure : sp_MSforeachtable

This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set.

EXEC [sp_MSforeachtable] @command1=”DBCC DBREINDEX(‘?’)”

To see progress messages while it is running, try this version:

EXEC [sp_MSforeachtable] @command1=”RAISERROR(‘DBCC DBREINDEX(”?”) …’,10,1) WITH NOWAIT DBCC DBREINDEX(‘?’)”.

You can Re index the Live Databases as well.Each table will be locked up for the duration of the reindexing. Users will be locked out if they are hitting that table.

If you know any other solution to such problems then please share it here so that it helps people to solve such problems.