No of Records using Select Query

-- No of Total Records using Select Query
Select count(*) from Purchasing.PurchaseOrderDetail

-- No of Records for each group using Select Query
Select PurchaseOrderID, count(*) NoOfRec from Purchasing.PurchaseOrderDetail Group By PurchaseOrderID

Identify Column in All Tables

use AdventureWorks
Select * From sys.columns
select * from sys.tables

-- Select Columns From All Tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'EmployeeID'

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'PurchaseOrderID'

Link Triggers & Tables

Triggers and thier corresponding Tables

-- Sybase
select * from systrigger

-- SQL Server
select * from sys.triggers

-- Triggers and Tables
If you want to know the triggers for each table or trigger for a
corresponding table use the following:

-- Sybase
Select stab.table_name, strig.trigger_name from systable stab, systrigger strig
where stab.table_id = strig.table_id

-- SQL Server
Select stab.name, strig.name from sys.tables stab, sys.triggers strig
where stab.object_id = strig.parent_id

Get COMPUTER NAME from SQL Query

Get the HOST NAME / COMPUTER NAME

Get the Computer Name using SQL Query

Select Host_Name() as Computer_Name

Get Computer Name, Get HOst Name

Here is the Way you can get the same in Visual Basic : http://vbadud.blogspot.com/2007/05/get-computer-name.html

Here is the Way you can get the same in .Net :
http://dotnetdud.blogspot.com/2007/06/get-computer-name-in-net.html

Coalesce Function

Coalesce Function

Displays the first nonnull expression among its arguments

-- returns Iam Not a Null
Select Coalesce (Null, Null, 'Iam Not a Null', Null) as NotNull

This function will be used to skip the NULL columns and get the ones that have value. This function can be maximum utilised in situations where you need to get one value from a group of columns that could contain null.

For example, Let us assume that you have a contact table with office_phno, mobile_nos, home_phnos in it and you need to send them greetings. You can use Coalesce Function to get the non null number according to priority

Select Coalesce (Office_phno, mobile_nos, home_phnos, 'No Ph No Available) as ContactNo

Maximum Level of Nesting in SQL Server Stored Procedure

Maximum Level of Nesting in SQL Server Stored Procedure

A Maximum of 32 levels of Nesting is allowed in SQL Server Stored Procedure. When the maximum of 32 is exceeded, the transaction is terminated.

Select @@NestLevel

Returns the nesting level of the current stored procedure execution (initially 0) on the local server

Keywords: @@NestLevel, Maximum Level of Nesting

DatePart - Get Part of the Date

-- Selecting the Current Year/Get Current Year/Separate Year Part from Date
Select DatePart(YY, GetDate()) as Current_Year

-- Selecting the Current Quarter/Get Current Quarter/Separate Quarter Part from Date
Select DatePart(QQ, GetDate()) as Current_Quarter

-- Selecting the Current Month/Get Current Month/Separate Month Part from Date
Select DatePart(MM, GetDate()) as Current_Month

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(month, GetDate()) as Current_Month_Name

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(day, GetDate()) as Current_Day

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(wk, GetDate()) as Current_Week

First day of the week - @@datefirst

The first day of the week will be 7 - Sunday (default for US English)

Select @@datefirst as FirstDay

You can set it by :
Set datefirst 1;

Current Database - SQL Server

What is the Current Database?

Select DB_NAME() as Curr_DB

Tie in Max or Min Function - WITH TIES

If you have two employees with the same birth date - the youngest/oldest employee has a tie. Then use of WITH TIES clause will solve it.

If WITH TIES is also specified, all rows that contain the last value returned by the ORDER BY clause are returned, even if doing this exceeds the number specified by expression.

Top 1 could return two or even more rows in case of a Tie

Select TOP 1 WITH TIES BirthDate from HumanResources.Employee Order By 1 Desc

WITH TIES can be used to get the matching records of the last/first even if it is more than the given number or percent

WITH TIES requires an ORDER BY clause.

Youngest Employee - SQL TOP

********************Youngest & Oldest Employee********************

To get the youngest / olderst employee, two different methods are shown here.

1. Using Max and Min Functions
2. Using Top in Select Statement

'' Youngest Employee (Reference : AdventureWorks)


Select Min(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Asc
Select TOP 1 * from HumanResources.Employee Order By BirthDate Asc

Select * from Person.Contact where ContactID = (Select TOP 1 EmployeeID from HumanResources.Employee Order By BirthDate Asc)

'' Oldest Employee (Reference : AdventureWorks)


Select Max(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Desc










Free Search Engine Submission






AddMe - Search Engine Optimization


Homerweb Search