Archive

Archive for March, 2012

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)
%d bloggers like this: