Monday, March 31, 2008

The COALESCE Function

A more efficient approach to creating dynamic WHERE clauses involves using the COALESCE function. This function returns the first non-null expression in its expression list. The following example shows how it works.

DECLARE @Exp1 varchar(30),
@Exp2 varchar(30)

SET @Exp1 = NULL
SET @Exp2 = 'SQL Server'

SELECT COALESCE(@Exp1,@Exp2)

--Results--

------------------------------
SQL Server

The function processes the expression list from left-to-right and returns the first non-null value. The COALESCE function can process an infinite number of expressions (e.g., COALESCE(@Exp1,@Exp2,@Exp3,@Exp4,...)), but for the example presented in this article only two are needed.

Saturday, March 29, 2008

Return Null If A Value Is A Certain Value

You need to return NULL only if the value of your data is a certain value. How do you do this?
There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'


SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

How to return random results from SQL SERVER

Sometimes you want to display 4 random articles/pics/you name it on your web page and you don’t want to write a lot of code.
SQL SERVER 2000 has a neat little function called NEWID() that can help you out.
Run the code below from Query Analyzer and keep hitting F5.
You will see that the results will be different every time.

USE pubs

SELECT top 4 * FROM dbo.authors
ORDER BY NEWID()

Date formatting in SQL Server

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select
@d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select
@d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select
@d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select
@d,convert(varchar,@d,106),106,'dd mon yy'
union all
select
@d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select
@d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select
@d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select
@d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select
@d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select
@d,convert(varchar,@d,112),112,'yymmdd'
union all
select
@d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select
@d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select
@d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select
@d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select
@d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

SQL:Format Data From Numberformat To Timeformat

Sometimes you have data that's in a number format and you need to show it in a time format. Instead of 2.25 you need to show 02:15
In that case you can use a combination of CONVERT and DATEADD
Run the examples below to see how it works

DECLARE @a DECIMAL(6,2)
SELECT @a = 3.00
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

SELECT @a = 3.15
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

SELECT @a = 3.25
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted


SELECT @a = 3.75
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

Check If Temporary Table Exists

How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL
Let's see how it works

--Create table
USE Norhtwind
GO

CREATE TABLE #temp(id INT)

--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END

--Another way to check with an undocumented optional second parameter
IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END



--Don't do this because this checks the local DB and will return does not exist
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END


--unless you do something like this
USE tempdb
GO

--Now it exists again
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END

--let's go back to Norhtwind again
USE Norhtwind
GO


--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END

now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END


It doesn't exist and that is correct since it's a local temp table not a global temp table


Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT
'##temp does not exist!'
END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT
'##temp does not exist!'
END

And yes this time it does exist since it's a global table

Sort A SQL Server Table With CASE Or CHARINDEX

Let's say you have a table with states in your database, you only have 3 values NY, ME and SC.
You want to order the result like this: first NY followed by SC and ME last.
You can do that in two different ways
One: use a case statement in your order by
Two: use Charindex in your order by
Let's see how that works

CREATE TABLE #TEST (
STATE CHAR(2))

INSERT INTO #TEST
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC'

-- order by using CASE


SELECT *
FROM #TEST
ORDER BY CASE STATE
WHEN 'NY' THEN 1
WHEN 'SC' THEN 2
ELSE 3
END


--Order by using CHARINDEX
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'NY-SC-ME')

--or without NY since CHARINDEX will return 0 for NY and it will be first
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')


--the problem is of course if you have more values and you only want to have NY and SC showing up first and second
--let's insert 2 more rows
INSERT INTO #TEST
SELECT 'IL'
UNION ALL
SELECT 'CA'

-- Now the CHARINDEX Order doesn't work
-- the trick is to make it Descending and switch the states around

SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-NY') DESC

or this way

--Order by using CHARINDEX DESC
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'ME-SC-NY') DESC

COALESCE And ISNULL Differences

Run the following block of code


-- The result is 7, integer math
SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)

--The result is 7.5, which is correct
SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)

You will see that the result is not the same ISNULL does integer math while COALESCE does not

COALESCE correctly promotes its arguments to the highest data type in the expression list.
ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int
COALESCE looks at 2.00 and 15 and then promotes the integer to decimal

Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more
Here we have 4 variables and all except for one are null

DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
DECLARE @Var3 VARCHAR(20)
DECLARE @Var4 VARCHAR(20)

SELECT @Var4 = 'ABC'


--This will return ABC
SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)

How To Split column In SQL

Suppose you have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:

245 HELIUM, COMPRESSED 2.2 UN1046


I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?
CREATE TABLE Inventory (ItemDescription VARCHAR(99))
INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )
INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )


SELECT RIGHT(ItemDescription,PATINDEX('% %',
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),
PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory

Formatting Data By Using CHARINDEX And SUBSTRING

Let's say you have names stored in the format [XYZ, ABC] but would like it to be [ABC XYZ]
How do you accomplish that in SQL?
SQL provides 2 useful functions (CHARINDEX And SUBSTRING)
Run the code below to see how they work

CREATE TABLE Names (ID INT identity not null,NameField VARCHAR(50), ProperNameField VARCHAR(50))

INSERT INTO Names
SELECT 'XYZ, ABC',NULL
UNION ALL
SELECT 'Smith, John',NULL
UNION ALL
SELECT 'Jackson, Michael',NULL
UNION ALL
SELECT 'Gates, Bill',NULL


UPDATE Names
SET ProperNameField =SUBSTRING(NameField,CHARINDEX(',',NameField) + 2,
LEN(NameField) - CHARINDEX(',',NameField))
+ ' ' + LEFT(NameField,CHARINDEX(',',NameField)-1)


SELECT * FROM Names

Pad Numbers By Using CASE, LEFT And CONVERT

Let's say you have a table with integer values below 100000 and you want them in the same format with leading zeros. For example 500 has to be 000500 and 1 has to be 000001
How do you do this in SQL server?
Below are two ways to accomplish this.

CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000

--Use CASE, LEN and CONVERT to pad the values
SELECT value,CASE LEN(value)
WHEN 1 THEN '00000' + CONVERT(VARCHAR,value)
WHEN 2 THEN '0000' + CONVERT(VARCHAR,value)
WHEN 3 THEN '000' + CONVERT(VARCHAR,value)
WHEN 4 THEN '00' + CONVERT(VARCHAR,value)
WHEN 5 THEN '0' + CONVERT(VARCHAR,value)
ELSE CONVERT(VARCHAR,value)
END AS Formattedvalue
FROM #ValueTable


--Use LEFT, LEN and CONVERT to pad the values
SELECT value,LEFT('000000',(6 -LEN(value )))
+ CONVERT(VARCHAR,value) AS Formattedvalue
FROM #ValueTable

Find all Primary and Foreign Keys In A Database

To find all your foreign and primary keys in your database run the code below.
The ouput will return the primary key, primary key table, foreign key, foreign key table. Primary keys that don't have foreign keys will have N/A in the foreign key output

USE Northwind

SELECT tc.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME

Use OBJECTPROPERTY To Find Out Table Properties

How many times did you open up a table in design view to find out if a table has
a delete trigger
a clustered index
an identity column
a primary key
a timestamp
a unique constraint
etc etc?

There is an easier way to find out all this info by using the OBJECTPROPERTY function
You will have to get the object id from the table to pass it into the OBJECTPROPERTY function.
To get the object id you can use the OBJECT_ID function.
Run the code below in the Northwind database to see how it works

USE Northwind

DECLARE @objectID INT
SELECT @objectID =OBJECT_ID('customers')

select OBJECTPROPERTY(@objectID,'TableDeleteTrigger') AS 'Table Has A Delete Trigger',
OBJECTPROPERTY(@objectID,'TableHasClustIndex') AS 'Table Has A Clustered Index',
OBJECTPROPERTY(@objectID,'TableHasIdentity') AS 'Table Has An Identity Column',
OBJECTPROPERTY(@objectID,'TableHasPrimaryKey') AS 'Table Has A Primary Key',
OBJECTPROPERTY(@objectID,'TableHasTimestamp') AS 'Table Has A Timestamp',
OBJECTPROPERTY(@objectID,'TableHasUniqueCnst') AS 'Table Has A Unique Constraint',
OBJECTPROPERTY(@objectID,'IsAnsiNullsOn') AS 'ANSI NULLS Is Set To ON'

Lookup OBJECTPROPERTY in Books On Line to find out about additional properties

SQL String Functions Tutorial

1. CHARINDEX string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.

Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6


2. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.

Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR


3. RIGHT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.

Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000


4. LEN string function takes 1 argument as string value and returns the length of entered string.

Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25


5. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.

Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000


6. STUFF string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.

Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000


7. SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length

Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

8. LOWER string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting


9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

10. REVERSE string function returns the string in reverse order. It takes 1 argument as string value.

Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA


11. LTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.


13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.

Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4


14. STR function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.

Example:
select STR(140.15, 6, 1)
Result: 140.2


15. ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.

Example:
select ASCII('A')
Result: 65

To Get The Current Identity Value From A Table

Let's first create our two simple tables
CREATE TABLE TestOne (id INT identity,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT identity,TestOneID INT,SomeDate DATETIME)

--Let's insert 4 rows into the table
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())



Here are diffrent ways to check for the current value

--1 @@IDENTITY
SELECT @@IDENTITY
--this returns 4


--2 DBCC CHECKIDENT
DBCC CHECKIDENT (TestOne, NORESEED)
after running DBCC CHECKIDENT the message returned is
Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


--3 MAX function
SELECT MAX(id)
FROM TestOne
you can also check with the MAX function but this is not recommended becuase you might get some other identity value that is not yours but from a different user

--4 TOP 1 and ORDER BY DESC
SELECT TOP 1 id
FROM TestOne
ORDER BY id DESC
--The same applies here as for the max function, this is not recommended


--5 IDENT_CURRENT
SELECT IDENT_CURRENT('TestOne')
--IDENT_CURRENT is another way to check


--6 SCOPE_IDENTITY
SELECT SCOPE_IDENTITY()
--This one is very similar to @@IDENTITY with one BIG difference (shown later)

How To Delete a null record

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(NULL)


--Check what's in the table
SELECT * FROM #TestDeleteNull

--This won't work
DELETE #TestDeleteNull
WHERE SomeDate = NULL
--(0 row(s) affected)

--This is the way
DELETE #TestDeleteNull
WHERE SomeDate IS NULL
--(1 row(s) affected)

--Check again
SELECT * FROM #TestDeleteNull

SQL Case Statements

SQL Case statement can be used in 2 forms:

  1. SQL Case statement with simple expression to compare and get results.
  2. SQL Case statement with search or comparison expression to get results.

1. SQL Case statement with simple expression:
You can use default Northwind Database to test the functionality and syntax of SQL case statement.





Syntax:

USE NORTHWIND

SELECT
'Title' =
CASE TITLEOFCOURTESY
WHEN 'MS.' THEN 'MISSUS'
WHEN 'MRS.' THEN 'MISTRESS'
WHEN 'MR.' THEN 'MISTER'
WHEN 'DR.' THEN 'DOCTOR'
ELSE ''
END,
TITLEOFCOURTESY,
FIRSTNAME,
LASTNAME
FROM EMPLOYEES



Output:

Title

TitleOfCourtsey

FirstName

LastName

Missus

Ms.

Nancy

Davolio

Doctor

Dr.

Andrew

Fuller

Missus

Ms.

Janet

Leverling

Mistress

Mrs.

Margaret

Peacock

Mister

Mr.

Steven

Buchanan

Mister

Mr.

Michael

Suyama

Mister

Mr.

Robert

King

Missus

Ms.

Laura

Callahan

Missus

Ms.

Anne

Dodsworth

2. SQL Case Statement with search comparison expressions:

Syntax:




USE NORTHWIND

SELECT
'COMMENT' =
CASE
WHEN UNITPRICE IS NULL THEN 'NO PRICE DEFINED'
WHEN UNITPRICE < 20 THEN 'LOW PRICE'
WHEN UNITPRICE > 20 AND UNITPRICE < 30 THEN 'REASONABLE PRICE'
ELSE 'EXPENSIVE ITEM!!!'
END,
PRODUCTNAME,
UNITPRICE
FROM PRODUCTS

Output:


Comment

ProductName

UnitPrice

Low Price

Chai

18.00

Low Price

Chang

19.00

Low Price

Aniseed Syrup

10.00

Reasonable Price

Chef Anton's Cajun Seasoning

22.00

Reasonable Price

Chef Anton's Gumbo Mix

21.35

Reasonable Price

Grandma's Boysenberry Spread

25.00

Expensive Item!!!

Uncle Bob's Organic Dried Pears

30.00

Expensive Item!!!

Northwoods Cranberry Sauce

40.00

SQL SubSelect-SubQueries



A sub query or sub select is a select statement that returns a single value
output
result and it can be nested inside other subquery or any SELECT, INSERT,
DELETE OR UPDATE statement.

Example:
USE NORTHWIND

SELECT P.PRODUCTNAME,
(SELECT CATEGORYNAME FROM CATEGORIES WHERE CATEGORYID = P.CATEGORYID)
FROM PRODUCTS P

Subquery used in the above example returns the category name of each product
in every tuple.



Example:

SELECT P.PRODUCTNAME, P.UNITPRICE, P.CATEGORYID
FROM
PRODUCTS P
WHERE
P.PRODUCTID = (SELECT PRODUCTID FROM PRODUCTS WHERE PRODUCTNAME='VEGIE-SPREAD')

Here subquery returns product id as single value to the main SQL query.



Example:

SELECT C.CATEGORYNAME,

(SELECT TOP 1 P.PRODUCTNAME FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC),

(SELECT TOP 1 P.UNITPRICE FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC),

(SELECT MAX(P.UNITPRICE) FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID)
/*TO VERIFY THE RESULT*/

FROM CATEGORIES C

Above example returns the maximum unit price of product, product name and category name.

SQL Count Distinct

SQL Count Statement is used to count the total number of records saved in a particular table. SQL Count Statement returns total records including duplicates.

SQL Count (*) Statement does not take any other argument such bas column name to evaluate the result. It counts all columns whether they have duplicates or nulls.

E.g.:

USE NORTHWIND

SELECT COUNT (*) FROM ORDERS

Above SQL Query will return 830.


SQL Count (ALL [Column Name]) Statement returns the number of records including duplicates but excluding null value columns

E.g.:

SELECT COUNT (ALL CUSTOMERID) FROM ORDERS


SQL Count (DISTINCT [Column Name]) Statement returns unique, non null records.

E.g.:

SELECT COUNT (DISTINCT CUSTOMERID) FROM ORDERS

Above SQL Query will return 89.

SQL Server 2005 Cast-Convert Date Time Format

Using CAST function in SQL Server 2005:

Syntax:

CAST (expression as datatype)

CAST function accepts expression name as column name or any SQL function that return expression or value and declare the datatype of the expression into which you want to cast/convert it.

Following are the SQL Server 2005 query syntax to cast DateTime into varchar:

E.g.:

USE Pubs

select cast(pubdate as varchar) from titles

or

select cast(getdate() as varchar)


Following are the MS SQL Server 2005 query Syntax to convert Time format:

SQL query to convert Time format into hh:mm:ss:

select convert(varchar, getdate(), 108)

SQL query to convert Time format into hh:mi:ss:mmm(24h):

select convert(varchar, getdate(), 114)

You can use datetime type column name instead of getdate() function.

E.g.:

USE PUBS

select convert(varchar,pubdate,108) from titles

or

select convert(varchar,pubdate,114) from titles



How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

Let's say you have a proc like this


CREATE PROC prUpdateTable
@Salesman
int = -1
AS

..........

If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do?
You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this




UPDATE table
SET
Column = COALESCE(NULLIF(@variable,-1),Column)



Here is some code to demonstrate that




CREATE TABLE #foo (id int,salesman int)
INSERT #foo VALUES(1,1)
INSERT #foo VALUES(2,1)
INSERT #foo VALUES(3,1)
INSERT #foo VALUES(4,1)



SELECT * FROM #foo
-------------
1 1
2 1
3 1
4 1

DECLARE @salesman int
SELECT
@salesman = 5

--Column value will change to 5
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =1

--Column value won't change
SELECT @salesman = -1
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =2

--Column value won't change
SELECT @salesman = NULL
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =3

--Column value will change to 3
SELECT @salesman = 3
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =4

--And here is the output, as you can see when @salesman was -1 or NULL the table did not get updated
SELECT * FROM #foo
-------------
1 5
2 1
3 1
4 3

DROP TABLE #foo





As you can see only the first and the last update statement changed the value of the salesman column
Of course you would never do this if you were to update only one column, you would skip the update instead. If you have to update multiple columns then this is something you can use instead of writing a bunch of dynamic SQL or nested IF statements.

How To Use Self Join In Sql Server 2000 2005

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

For self join in sql you can try the following example:

Create table employees:

emp_id

emp_name

emp_manager_id

1

John

Null

2

Tom

1

3

Smith

1

4

Albert

2

5

David

2

6

Murphy

5

7

Petra

5

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:

select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Result:

manager

employee

John

Tom

John

Smith

Tom

Albert

Tom

David

David

Murphy

David

Petra


Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Friday, March 28, 2008

Delete Duplicate rows from the table.

Delete Duplicate rows from the table.
Suppose there is a table called "EmployeeTable" which have some duplicate records.
There is a three way to delete the duplicate rows.

First way to delete duplicate rows :


Select distinct * into Emp_Temp_Table from EmployeeTable

In the above line we are inserting all the distinct row of the "EmployeeTable" to another table "Emp_Temp_Table" (Emp_Temp_Table will create automatically when you use the above query.)
Actuall the above query create clone of EmployeeTable and insert all the distinct row inside the Clone Table (Emp_Temp_Table).


drop table EmployeeTable

sp_rename 'Emp_Temp_Table',EmployeeTable'

Then Delete the orginal table and rename the clone table with the name of orginal table.


Second way to delete duplicate rows :

Select distinct * into Emp_Temp_Table from EmployeeTable

Truncate table EmployeeTable

insert into EmployeeTable select * from Emp_Temp_Table

drop table Emp_Temp_Table


Third way to delete duplicate rows :

Populate the new Primary Key


Alter table EmployeeTable add NewPK int NULL
Go
Declare @intCounter int
Set @intCounter = 0
Update EmployeeTable
SET @intCounter = NewPK = @intCounter + 1

Select name,RecCount=count(*), PktoKeep = max(NewPK)
Into #dupes
From EmployeeTable
Group by name
Having count(*) > 1
Order by count(*) desc


Delete dupes except one Primary key for each dup record


Delete test
from EmployeeTable a join #dupes d
a.name
where a.NewPK not in (select PKtoKeep from #dupes)



Remove the NewPK column


ALTER TABLE test DROP COLUMN NewPK
go

drop table #dupes

Thursday, March 27, 2008

How To Copy Data and Structure of One Table To Another New Tables

select * into newtable from originaltable where 1=1

Wednesday, March 26, 2008

what Is Cursor

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1
INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

What is Triggers

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.

The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.

SET NOCOUNT ON

CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')

-- Results --

Apr 28 2001 9:56AM

Using EXISTS

Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this.

Here's a simple example from the pubs database using EXISTS:
if EXISTS (select *
from authors
where au_id = '172-32-1176')
Print 'Record exits - Update'
ELSE
Print 'Record doesn''t exist - Insert'

The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false. This gives you a clean, efficient way to write a stored procedure that does either an insert or update.

Returning Complex Data from User-Defined Functions

Returning multiple values from a UDF can be done fairly easily in SQL Server, but we must return those values as a virtual table. The following very simple ParseEmail() function accepts an email address as an argument, parses it, and returns the username and domain separately:




create function EmailParse (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
declare @i int

select @i = charindex('@', @email,1);

if (@i > 1)
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
else
insert into @t values (null,null)

return
end


how to use

select username, domain
from EmailParse('test@test.com')

Thursday, March 13, 2008

What is Stored procedure?

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

  • They allow modular programming.
  • They allow faster execution.
  • They can reduce network traffic.
  • They can be used as a security mechanism.

What are the differences between UNION and JOINS?

A join selects columns from 2 or more tables. A union selects rows.

How many types of Joins?

oins can be categorized as:

  • Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
    Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.
  • Outer joins. Outer joins can be a left, a right, or full outer join.
    Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:
    • LEFT JOIN or LEFT OUTER JOIN -The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
    • RIGHT JOIN or RIGHT OUTER JOIN - A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
    • FULL JOIN or FULL OUTER JOIN - A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
  • Cross joins - Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products. (A Cartesian join will get you a Cartesian product. A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself.)

Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms

# Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

SELECT CEILING (7.1)

Write a query to convert all the letters in a word to upper case

SELECT UPPER('test')

Find top salary among two tables

SELECT TOP 1 sal
FROM (SELECT MAX(sal) AS sal
FROM sal1
UNION
SELECT MAX(sal) AS sal
FROM sal2) a
ORDER BY sal DESC

How to delete the rows which are duplicate (don’t delete both duplicate records).

SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

Find duplicate rows in a table?

  1. Find duplicate rows in a table? OR I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.
    SELECT sid, mark, COUNT(*) AS Counter
    FROM marks
    GROUP BY sid, mark
    HAVING (COUNT(*) > 1)

How to know how many tables contains empno as a column in a database?

SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno')

T-SQL Queries

There is a table which contains the names like this. a1, a2, a3, a3, a4, a1, a1, a2 and their salaries. Write a query to get grand total salary, and total salaries of individual employees in one query.




SELECT empid, SUM(salary) AS salary
FROM employee
GROUP BY empid WITH ROLLUP
ORDER BY empid

T-SQL Queries

There is a table day_temp which has three columns dayid, day and temperature. How do I write a query to get the difference of temperature among each other for seven days of a week?


SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference
FROM day_temp a INNER JOIN
day_temp b ON a.dayid = b.dayid + 1
OR
Select a.day, a.degree-b.degree from temperature a, temperature b where a.id=b.id+1

T-SQL

There are two employee tables named emp1 and emp2. Both contains same structure (salary details). But Emp2 salary details are incorrect and emp1 salary details are correct. So, write a query which corrects salary details of the table emp2

update a set a.sal=b.sal from emp1 a, emp2 b where a.empid=b.empid

T-SQL Queries

2 tables

Employee Phone
empid
empname
salary
mgrid
empid
phnumber
Select all employees who doesn't have phone?
SELECT empname
FROM Employee
WHERE (empid NOT IN
(SELECT DISTINCT empid
FROM phone))

Select the employee names who is having more than one phone numbers.
SELECT empname
FROM employee
WHERE (empid IN
(SELECT empid
FROM phone
GROUP BY empid
HAVING COUNT(empid) > 1))

Select the details of 3 max salaried employees from employee table.

SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC
Display all managers from the table. (manager id is same as emp id)

SELECT empname
FROM employee
WHERE (empid IN
(SELECT DISTINCT mgrid
FROM employee))
Write a Select statement to list the Employee Name, Manager Name under a particular manager?

SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN
Employee e2 ON e1.mgrid = e2.empid
ORDER BY e2.mgrid