TSQL2012 – Less frequently used features – Revision 01

I have started revising TSQL2012 book, thought of capturing few less frequently used features/clauses:

* WITH TIES

select top 10 WITH TIES orderid, orderdate, custid, empid
from Sales.Orders
order by orderdate desc 

* PERCENT

 select top 10 PERCENT orderid, orderdate, custid, empid
from Sales.Orders
order by orderdate desc 

* OFFSET-FETCH Filter — To eliminate first x rows in the result set

 select orderid, orderdate, custid, empid
from Sales.Orders
order by orderid desc 
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY 

* Window Functions – rownumber() over PARTITION

 SELECT orderid, custid, val,
  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val; 

* Few Functions

SELECT SUBSTRING('abcdefgh', 2, 3); -- 'abc'

SELECT RIGHT('abcdefg', 3); -- 'cde'
SELECT LEFT('abcdefg', 4); -- 'cde'

SELECT LEN(N'abcde'); -- 5
SELECT DATALENGTH(N'abcde'); -- 10

declare @char char(40), @nchar nchar(40), @varchar varchar(40), @nvarchar nvarchar(40)
set @char = 'abcde '
set @nchar = 'abcde '
set @varchar = 'abcde '
set @nvarchar = 'abcde '
SELECT DATALENGTH(@char) as char40, DATALENGTH(@nchar) as nchar40, DATALENGTH(@varchar) as varchar40, DATALENGTH(@nvarchar) as nvarchar40
SELECT LEN(@char) as char40, LEN(@nchar) as nchar40, LEN(@varchar) as varchar40, LEN(@nvarchar) as nvarchar40

SELECT CHARINDEX(' ','Vijaya Bhaskar'); -- 7

SELECT PATINDEX('%[0-9]%', 'abcd123efgh'); -- 5

SELECT REPLACE('1-a 2-b', '-', ':'); -- '1:a 2:b'

SELECT REPLICATE('abc', 3); -- 'abcabcabc'

SELECT supplierid,
  RIGHT(REPLICATE('0', 7) + CAST(supplierid AS VARCHAR(7)),
        8) AS strsupplierid
FROM Production.Suppliers;

SELECT STUFF('xyz', 2, 1, 'abc'); -- 'xabcz'

SELECT UPPER('Itzik Ben-Gan'); -- 'ITZIK BEN-GAN'

SELECT LOWER('Itzik Ben-Gan'); -- 'itzik ben-gan'

SELECT RTRIM(LTRIM('   abc   ')); -- 'abc'

SELECT FORMAT(1759, '0000000000'); -- '0000001759' 

* Multiple tables in Information_Schema schema are very informative

-- Information_Schema example tables 
select * from information_schema.tables 
select * from information_schema.COLUMNS
select * from information_schema.ROUTINES
select * from information_schema.VIEWS 
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to TSQL2012 – Less frequently used features – Revision 01

  1. Pingback: t-SQL to find top X toppers in each subject from marks table | Blog for reference – Vijred

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s