Home > T-SQL > Most frequently used T-SQL Queries

Most frequently used T-SQL Queries


In this thread I would like to share some of the most frequently used SQL queries, following examples will be are demonstrated with Adventure Works and my own databases. There might be alternate sources of the available outside. All I wanted is to share it at the right place. Eventually I will be updating this separate thread with new queries which I am learning from time to time.

Query 1:

It is easy to calculate a cumulative sum or running sum of a measure object in a report but how about doing it in database side with correlated sub query,

DECLARE @table TABLE
(
date   DATE,
Amount INT
)

INSERT INTO @table
SELECT Getdate() – 6,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 5,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 4,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 3,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 2,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 1,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate(),Round(Rand(), 2) * 100

SELECT *
FROM   @table

SELECT Date,
Amount,
(SELECT Sum(Amount)
FROM   @table
WHERE  Date <= D.Date) AS Running_SUM
FROM   @table D
ORDER  BY date

output:

imagequery1

Query 2:

To get the tables that are modified after its creation,

select * from sys.tables

where

create_date > modify_date

Query 3:

To get the list of tables that are not modified (ALTER) after creating

select * from sys.tables

where

create_date = modify_date

Query 4:

To get the number of columns used in table

select name, max_column_id_used from sys.tables

Query 5:

To know how many tables contains “col1″ as a column name in a database

SELECT COUNT(*) AS NoColumns
FROM syscolumns
WHERE (name = ‘Col1′)

Query 6:

To get the filename from a network/folder path,

Example: ‘E:\blog pictures\rolling\second\img.csv’

Use the following query to extract the filename ‘img.csv’ from the respective path,

Declare @String nvarchar (100) = 'E:\blog pictures\rolling\second\img.csv'

select Right(@string,Charindex('\',REVERSE(@string))-1)
Advertisements
  1. jerimiahbaldwin
    March 26, 2012 at 10:50 pm

    Very nice. Very concise.

    Like

  2. Raghu
    September 11, 2012 at 1:59 pm

    Thank you very much for sharing these helpful information…

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: