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
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
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) |
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
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)
@xmlData xml @xmlData=
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
================
====================
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
-
Archives
- December 2009 (8)
- June 2009 (2)
- April 2009 (2)
- August 2008 (2)
- July 2008 (9)
- June 2008 (10)
- May 2008 (1)
- March 2008 (1)
- December 2007 (2)
-
Categories
-
RSS
Entries RSS
Comments RSS