Count(*) OVER()

Select Top(@PageSize) * from
(
Select
RowID=ROW_NUMBER() OVER (ORDER BY Name),
ProductID,Name,ProductNumber,ListPrice,
TotalRows=Count(*) OVER() –Count all records
from Production.Product
) A
Where A.RowId > ((@PageNumber-1)*@PageSize)

avoiding duplicate using PARTITION By

CREATE

INSERT INTO DuplicateRcordTableSELECT

1, 1UNION

ALLSELECT

1, 1 –duplicateUNION

ALLSELECT

1, 1 –duplicateUNION

ALLSELECT

1, 2UNION

ALLSELECT

1, 2 –duplicateUNION

ALLSELECT

1, 3UNION

ALLSELECT

1, 4SELECT

* FROM 

( 

SELECT ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount,* 

FROM DuplicateRcordTable)t1 WHERE DuplicateCount = 1  

TABLE DuplicateRcordTable (Col1 INT, Col2 INT)

output clause:Example

delete TempContactList

output DELETED.UTID,DELETED.LoginUnitID,DELETED.LoginUserID,DELETED.UnitID,DELETED.EventID,DELETED.InsertedDate,’TestuploadDel’

into @s_removedcontacts(UTID,TestID,LoginUserID,UnitID,EventID,TestInsertedDate,Module)

where IsHistory =1

manipulate text data SQL Server 2000 T-SQL commands

 

 

 

Solution
The following is a list of SQL Server 2000 T-SQL commands that exist to allow you to manipulate text data either from stored procedures, triggers, functions or embedded SQL code.

 

 

 

Query	Value
SELECT CHARINDEX(‘SQL’, ‘Microsoft SQL Server – SQL Server’)	11 (SQL is found in the 11 position)
SELECT CHARINDEX(‘SQL’, ‘Microsoft SQL Server – SQL Server’, 20)	24 (SQL is found in the 24 position, since we started looking in position 20)
SELECT LEFT(‘Microsoft SQL Server – SQL Server’ , 20 )	Microsoft SQL Server (left 20 characters of the string)
SELECT LEN(‘Microsoft SQL Server – SQL Server’)	33 (total length of the string)
SELECT LOWER(‘Microsoft SQL Server – SQL Server’)	microsoft sql server – sql server (string in lower case)
SELECT LTRIM( ‘ Microsoft SQL Server – SQL Server ‘)	Microsoft SQL Server – SQL Server (trimmed string removing leading spaces)
SELECT PATINDEX( ‘%SQL%’, ‘Microsoft SQL Server – SQL Server’ )	11 (SQL is found in the 11 position)
SELECT REPLACE( ‘Microsoft SQL Server – SQL Server’, ‘Server’, ‘Server 2005′ )	Microsoft SQL Server 2005 – SQL Server 2005 (string after we replace ‘Server’ with ‘Server 2005′)
SELECT REPLICATE( ‘x’ , 10 )	xxxxxxxxxx (x replicated 10 times)
SELECT REVERSE( ‘Microsoft SQL Server’ )	revreS LQS tfosorciM (string in reverse)
SELECT RTRIM( ‘ Microsoft SQL Server – SQL Server ‘)	Microsoft SQL Server – SQL Server (string after removing trailing spaces)
SELECT ‘Microsoft’ + SPACE(10) + ‘SQL Server’	Microsoft          SQL Server (string after inserting 10 spaces)
SELECT STUFF( ‘Microsoft SQL Server’, 11 , 3 , ‘2005′ )	Microsoft 2005 Server (string after replacing positions 11, 12, 13 with ‘2005′)
SELECT SUBSTRING( ‘Microsoft SQL Server’, 1, 9 )	Microsoft (substring of statement starting at position 1 for 9 characters)
SELECT UPPER(‘Microsoft SQL Server – SQL Server’)	MICROSOFT SQL SERVER – SQL SERVER (string in upper case)

In addition to using the commands by themselves you can use multiple commands at the same time to provide more meaningful results.

Query	Value
SELECT LEFT(‘Microsoft SQL Server’, CHARINDEX(‘ ‘, ‘Microsoft SQL Server – SQL Server’,13) – 1)	Microsoft SQL (find portion of string where a space is found but starting at position 13)
SELECT LEFT(‘Microsoft SQL Server’, CHARINDEX(‘ ‘, ‘Microsoft SQL Server – SQL Server’) – 1)	Microsoft (find portion of string where a space is found)
SELECT LTRIM(RTRIM(‘ Microsoft SQL Server – SQL Server ‘))	Microsoft SQL Server – SQL Server (trim leading and trailing spaces)

 

Thanks & Regards,
 
Prasanth P
Advanced Software Systems, Inc,
22866 Shaw Road,
Sterling, VA 20166
Office: 703-230-3100 Extn: 4713
Fax: 703-230-0640
Email: Prasanth@assyst-international.com
Web: www.assyst.net
 

Selecting Data from xml Col

DECLARE

@CCC VARCHAR(100)SET

@CCC=‘AAA’SELECT

* FROM

tets WHERE

UserType.exist(‘(/UserType/Type)[. = sql:variable("@CCC")]‘) = 1

incrementing counter with a single update query

DECLARE @counter int

SET @counter = 0 UPDATE #tmp_Users

SET

 @counter = counter = @counter + 1

COALESCE(), ISNULL()

Basic difference between COALESCE and their counter parts is that COALESCE returns first non null value as it can take more than 2 expressions or values as an argument while ISNULL or NVL takes only two arguments. First argument is expression or column name and second argument is expression or constant with which we want to replace first argument if it is null.

SELECT  TEST_NAME,COALESCE(TEST_COL1,TEST_COL2,9999) FROM TEST

count,exist(@xmlData)

 

declare

set

@xmlData xml @xmlData=

‘<Root>

<Main>

<Sub>4</Sub>

<Sub1></Sub1>

</Main>

</Root>’

 

SELECT

 

select @xmlData.value (‘count(/*)’, ‘int’)@xmlData.exist(‘/Root/Main[Sub>0]‘)

 

 

xml modify

declare @xml xml
set @xml=’<ContactInfo>
<LoginUserID>135</LoginUserID>
<Result>true</Result>
<MyLandLine1>
<MyPhone>
<Id>0</Id>
<Phone>AAA</Phone>
<ContactType>0</ContactType>
<PhoneType />
</MyPhone>
<MyPhone>
<Id>0</Id>
<Phone>BBB</Phone>
<ContactType>1</ContactType>
<PhoneType>Landline1</PhoneType>
</MyPhone>
</MyLandLine1>
</ContactInfo>’
 
 
SET @xml.modify(‘delete /ContactInfo/MyLandLine1/MyPhone[Phone[text() = "AAA"]]’)
select @xml

 

================

declare @xml xml

set @xml=’<ContactInfo>

<LoginUserID>135</LoginUserID>

<Result>true</Result>

<MyLandLine1>

<MyPhone>

<Id>0</Id>

<Phone>AAA</Phone>

<ContactType>0</ContactType>

<PhoneType />

</MyPhone>

<MyPhone>

<Id>0</Id>

<Phone>BBB</Phone>

<ContactType>1</ContactType>

<PhoneType>Landline1</PhoneType>

</MyPhone>

</MyLandLine1>

</ContactInfo>’

 

SET @xml.modify(‘insert <MyInformation>My Information</MyInformation> as first into (/ContactInfo)[1]‘)

select @xml

====================

SELECT 

select @XMLResult

 

@RecCount = @XMLResult.value(‘count(/AccountsList/Records/Record/RowNum[1])’,‘varchar(10)’) set @XMLResult.modify(‘replace value of (/AccountsList[1]/Count/text())[1] with sql:variable(“@RecCount”)’)

 

 

condition in COUNT using NULLIF

SELECT g.hair, ( COUNT( * ) ) AS girl_count, ( COUNT( NULLIF( did_date, 0 ) ) ) AS did_date_count, ( COUNT( NULLIF( did_date, 1 ) ) ) AS did_not_date_count FROM @girl g GROUP BY g.hair