Most Important SQL Queries

By | |

How to find highest 3 salary in sql?
Select top 1 sal from tbl where sal in(select top 3 sal from tbl order by sal desc) order by sal asc.
How to find Duplicate entries in sql?
Select distinct(sal) from tbl group by sal having count(*)>1;
How to delete Duplicate entries in sql?
Delete from tbl where id in ( select distinct(id) from tbl group by id having count(id)>1);
How to insert row number in sql query?
Select row_number() over(order by id),* from table;
How we can use try catch in SQL?
Begin try
Begin transaction
--------
--------
Commit transaction
End try
Begin catch
Rollback transaction
End catch
How to shows TCP details of SQL Server?
Select * from sys.tcp_endpoints;
How to get all tables and columns within a database?
Select * from sysobjects;
Select * from syscolumns;
Select * from systypes;
How to insert more than one record in a table?
Insert into db2.dbo.tbl(id,name) select id,name from db1.dbo.tbl;
How to add unique key constraints after create a table?
Alter table tblname add unique(colname);

Tagged . Bookmark the permalink.

2 Responses to Most Important SQL Queries

  1. Marc says:
    February 19, 2013 at 7:20 PM

    Your script to delete duplicates will delete all rows, instead of leaving behind one unique row. Try this instead:

    WITH CTE_RankedIds AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY @@SPID) as [rank],
    [id]
    FROM [dbo].[TestData]
    )
    DELETE FROM CTE_RankedIds
    WHERE [rank] > 1

    Don’t use sysobjects, syscolumns or systypes anymore. They’ve been deprecated for quite some time. Instead use [sys].[objects], [sys].[columns] and [sys].[types]

    SELECT
    [sch].[name] as [schema_name],
    [tbl].[name] as [table_name],
    [col].[name] as [column_name],
    [typ].[name] as [type_name],
    [col].[max_length] as [max_length],
    [col].[precision] as [precision],
    [col].[scale] as [scale],
    [col].[is_nullable] as [nullable]
    FROM
    [sys].[schemas] [sch]
    INNER JOIN [sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id]
    INNER JOIN [sys].[columns] [col] ON [tbl].[object_id] = [col].[object_id]
    INNER JOIN [sys].[types] [typ] ON [col].[user_type_id] = [typ].[user_type_id]
    ORDER BY
    [sch].[name],
    [tbl].[name],
    [col].[column_id]

  2. Marc says:
    February 19, 2013 at 7:17 PM

    Your script to delete duplicates will delete all rows, instead of leaving behind one unique row. Try this instead:
    [code]
    WITH CTE_RankedIds AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY @@SPID) as [rank],
    [id]
    FROM [dbo].[TestData]
    )
    DELETE FROM CTE_RankedIds
    WHERE [rank] > 1
    [/code]
    Don’t use sysobjects, syscolumns or systypes anymore. They’ve been deprecated for quite some time. Instead use [sys].[objects], [sys].[columns] and [sys].[types]
    [code]
    SELECT
    [sch].[name] as [schema_name],
    [tbl].[name] as [table_name],
    [col].[name] as [column_name],
    [typ].[name] as [type_name],
    [col].[max_length] as [max_length],
    [col].[precision] as [precision],
    [col].[scale] as [scale],
    [col].[is_nullable] as [nullable]
    FROM
    [sys].[schemas] [sch]
    INNER JOIN [sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id]
    INNER JOIN [sys].[columns] [col] ON [tbl].[object_id] = [col].[object_id]
    INNER JOIN [sys].[types] [typ] ON [col].[user_type_id] = [typ].[user_type_id]
    ORDER BY
    [sch].[name],
    [tbl].[name],
    [col].[column_id]
    [/code]

Leave a Reply