Friday, January 2, 2015

In-Memory OLTP Engine (High Level Architecture)

In-Memory OLTP Engine (High Level Architecture)
In-Memory OLTP Engine (High Level Achitecture)
In Memory OLTP Engine   (High Level Architecture)

Thursday, December 11, 2014

New and Enhanced tSQL Functions in SQL Server 2012

Enhanced and New  tSQL Functions in SQL Server 2012

1. Try_Convert 
/*Try_Convert and Try_Parse Functions to handle errors by returning NULL values*/

SELECT TRY_CONVERT(INT, 100) AS CorrrectConvert, TRY_CONVERT(INT, 'abc') AS ErrorConvert;

Result:
CorrrectConvert ErrorConvert
100 NULL


SELECT PARSE('7/17/2011' AS DATE USING 'en-US') AS USDate, PARSE('2011/7/17' AS DATE USING 'ja-JP') AS JapanDate;
Result:
USDate JapanDate
7/17/2011 7/17/2011


2. TRY_PARSE
/*If we try to parse Invalid values it will show below error like japan support 2011 but if we pass only 11
it will show error*/
SELECT PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt;

Error:
--Msg 9819, Level 16, State 1, Line 1
-- Error converting string value '7/17/11' into data type date using culture 'ja-JP'.

SELECT TRY_PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt;



3. Date and Time Functions
SELECT
DATEFROMPARTS(2014, 07, 22)    AS DATE_FROMPARTS,
DATETIME2FROMPARTS(2014, 07, 22, 14, 31, 7, 2, 3) AS DATETIME2_FROMPARTS,
DATETIMEFROMPARTS(2014, 07, 22, 14, 31, 7, 997) AS DATETIME_FROMPARTS,
DATETIMEOFFSETFROMPARTS(2014, 07, 22, 14, 31, 5, 1, -5, 0, 7) AS DATETIMEOFFSET_FROMPARTS,
SMALLDATETIMEFROMPARTS(2014, 07, 22, 14, 31) AS SMALLDATETIME_FROMPARTS,
TIMEFROMPARTS(15, 33, 6, 1, 7) AS TIME_FROMPARTS;



4. Logical/Conditional Functions
SELECT CHOOSE(3, 43466, 'Delhi', 'Agra') AS '1stPosition', CHOOSE(5, 'MUM', 'pune', 'Bangalore','allahabad','KNP') AS '5thPosition',CHOOSE(3,'a','b') 'NoPosition' ;

SELECT IIF(4 = 5, 'Ram', 'Shyam') AS iif_result;
SELECT IIF(4 = 4, 'Ram', 'Shyam') AS iif_result;




5. Concate and Format
DECLARE   @Passenger TABLE
  (ID INT,
  PassengerID VARCHAR(10),
       CityName   VARCHAR(10),
       StartDate  DATE
    )

INSERT INTO @Passenger VALUES (1,'P00001', 'Delhi', '1-Sep-2014')
INSERT INTO @Passenger VALUES (2,'P00001', 'Mathura', '1-Oct-2014')
INSERT INTO @Passenger VALUES (3,'P00001', 'Etah', '10-Oct-2014')

Query1.
SELECT *,CONCAT(PassengerID, ', ' + 'Name&Type_Desc', ', ' + CityName) AS ConcatinatedInfo
FROM  @Passenger
Result:
ID PassengerID CityName StartDate ConcatinatedInfo
1 P00001 Delhi 9/1/2014 P00001, PsgrID&CtyName, Delhi
2 P00001 Mathura 10/1/2014 P00001, PsgrID&CtyName, Mathura
3 P00001 Etah 10/10/2014 P00001, PsgrID&CtyName, Etah

Query2.
SELECT *,CONCAT(PassengerID, ', ' + 'PsgrID&CtyName', ', ' + CityName) AS ConcatinatedInfo
FROM  @Passenger

Result:
USDateFormat FormatWithZero
12/11/2014     00000001
12/11/2014     00000002
12/11/2014     00000003
Query3.
SELECT FORMAT(GETDATE(), 'd', 'en-us') AS USCulture, FORMAT(GETDATE(), 'd', 'ja-JP') AS JAPANECULTURE,FORMAT(GETDATE(), 'd', 'de-DE') AS GERMANCULTURE

Result:
USCulture JAPANECULTURE GERMANCULTURE
12/11/2014 12/11/2014 11.12.2014




6. LOG
Query4.
SELECT LOG(256, 2);
Result.
LOG
8



7. EOMONTH

Query5.
SELECT EOMONTH('12/15/2014')Endofmonth ,EOMONTH(SYSDATETIME()) AS Endofmonth1,EOMONTH(getdate()) AS endofmonth2;

Result:
Endofmonth Endofmonth1 endofmonth2
12/31/2014 12/31/2014 12/31/2014

Wednesday, December 10, 2014

Pagination in SQL Server 2012

Pagination in SQL Server 2012

/*Queries to Generate Data for pagination*/

DECLARE   @Passenger TABLE
  (
   ID INT,
       PassengerID VARCHAR(10),
       CityName   VARCHAR(10),
       StartDate  DATE
    )

INSERT INTO @Passenger VALUES (1,'P00001', 'Delhi', '1-Sep-2014')
INSERT INTO @Passenger VALUES (2,'P00001', 'Mathura', '1-Oct-2014')
INSERT INTO @Passenger VALUES (3,'P00001', 'Etah', '10-Oct-2014')
INSERT INTO @Passenger VALUES (4,'P00001', 'Kanpur', '25-Oct-2014')
INSERT INTO @Passenger VALUES (5,'P00001', 'Lucknow', '27-Oct-2014')
INSERT INTO @Passenger VALUES (6,'P00002', 'Allahabad', '1-Oct-2014')
INSERT INTO @Passenger VALUES (7,'P00002', 'Banaras', '1-Nov-2014')
INSERT INTO @Passenger VALUES (8,'P00002', 'Mumbai', '10-Nov-2014')
INSERT INTO @Passenger VALUES (9,'P00002', 'Banaras', '15-Nov-2014')
INSERT INTO @Passenger VALUES (10,'P00002', 'Pune', '25-Nov-2014')
INSERT INTO @Passenger VALUES (11,'P00002', 'Hyd', '28-Nov-2014')
INSERT INTO @Passenger VALUES (12,'P00002', 'Chennai', '30-Nov-2014')
INSERT INTO @Passenger VALUES (13,'P00003', 'Bangalore', '1-Dec-2014')


Query 1:
SELECT   * 
FROM     @Passenger 
ORDER BY Id
OFFSET 3 ROWS
FETCH NEXT 6 ROWS ONLY
Result:
IDPassengerIDCityNameStartDate
4P00001Kanpur25-Oct-2014
5P00001Lucknow27-Oct-2014
6P00002Allahabad1-Oct-2014
7P00002Banaras1-Nov-2014
8P00002Mumbai10-Nov-2014
9P00002Banaras15-Nov-2014
Query 2:
SELECT   * 
FROM     @Passenger 
ORDER BY  Id
OFFSET 9 ROWS
FETCH NEXT 3 ROWS ONLY


Result:
IDPassengerIDCityNameStartDate
10P00002Pune25-Nov-2014
11P00002Hyd28-Nov-2014
12P00002Chennai30-Nov-2014

Note: OFFSETand Next Rows count never be Negative.

SQL Server Logical/Physical Reads

SQL Server Logical/Physical Reads

Summary Info:

Logical Reads : Reading Data pages from Cache
Physical Reads : Reading Data pages from Hard Disk
Buffer Cache Hit Ratio:  (logical reads – physical reads)/logical read * 100%



Detailed Info:

Logical Reads:
Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.

Physical Reads 
Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.

Buffer Cache Hit Ratio:
Buffer hit ratio will be calculated based on these two kinds of read as the following formula: (logical reads – physical reads)/logical read * 100%. The high buffer hit ratio (if possible to near 100%) indicates good database performance on SQL Server level. So use information from physical read and buffer hit ratio to measure performance in server level and logical read to measure individual query level 


Excess of the Logical Reads tends high memory Usage, there are some ways by which we can Reduce Logical Reads:
1. Improper/Useless/Insufficient Indexes: Indexes should be build on the basis of data access or retrieval process if any of the indexes is build on the columns which are not used in a query will leads to High Logical reads and will degrade the performance while reads and writing the data....

2.Poor Fill Factor/Page Density: Page use should should not be very less. otherwise large number of page will be used for small amount of data which will also leads to High Logical Reads....

3.Wide Indexes: Indexing on the large number of columns will leads to high logical reads....

4. Index scanning: if query is leads to index scanning on the table then logical reads will be high...


Logical Reads count can be get by using following ways

Below are the ways to check logical Reads:
1. set statistics io on 

2. sys.dm_exec_query_Stats
by executing the below statement we can find detailed info about reads/writes
select * from sys.dm_exec_query_Stats

3. SQL Profiler: by executing the sql profiler on that database we can find out logical reads..

There are some other SQL DMV/F which also help us to get logical reads...

also visit:
http://www.sqlservercentral.com/Forums/Topic1250154-391-1.aspx

LEAD And LAG Analytic Functions in SQL Server 2012

LEAD And LAG Functions in SQL Server 2012

LEAD And LAG provides access to a row at a given physical offset respective to current row.
Use these analytic function in a SELECT statement to compare values in the current row with values in a Previous and following row according to given offset.
Note: Offset cannot be negative.

Let us take a passengers example: 

suppose some passengers are travelling from one city to another, they are passing
through many cities, now if we want to find out which was the previous
& city which will be next city we can achieve this using Common table expression and ranking
functions as follow

-- Let us code this in SQL Server
-- Declare @Passenger Table as below.
DECLARE   @Passenger TABLE
  (
       PassengerID VARCHAR(10),
       CityName   VARCHAR(10),
       StartDate  DATE
    )
/* Insert record in Passenger table, suppose passengers are passing different cities 
on different dates as below*/

INSERT INTO @Passenger VALUES ('P00001', 'Delhi', '1-Sep-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Mathura', '1-Oct-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Etah', '10-Oct-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Kanpur', '25-Oct-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Lucknow', '27-Oct-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Allahabad', '1-Oct-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Banaras', '1-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Mumbai', '10-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Banaras', '15-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Pune', '25-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Hyd', '28-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Chennai', '30-Nov-2014')
INSERT INTO @Passenger VALUES ('P00003', 'Bangalore', '1-Dec-2014')

/*Now if we want to find out where passengers were on next day and previous day we need to right below queries*/
--- Query 1
;
WITH   CTE as
(
        SELECT RN = ROW_NUMBER() OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC),   *
        FROM @Passenger
)
SELECT
        [Current Row].*,
        ISNULL(DATEADD(DAY, -1, [Next Row].StartDate), '31-Dec-2099') AS EndDate

,ISNULL([Previous Row].CityName,'NoPrv_Cty') PrvCity
,ISNULL([Next Row].CityName,'NoNxt_Cty') NextCity

FROM   CTE AS  [Current Row]
        LEFT JOIN CTE AS [Next Row] ON [Current Row].PassengerID   = [Next Row].PassengerID   AND  [Next Row].RN   = [Current Row].RN   + 1
LEFT JOIN CTE AS [Previous Row] ON [Current Row].PassengerID   = [Previous Row].PassengerID   AND  [Previous Row].RN = [Current Row].RN-1
ORDER BY [Current Row].PassengerID, [Current Row].RN;

/*But using LEAD and LAG Function we can achieve using below query*/
--- Query 2
SELECT *,
DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100')
        OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC))   AS EndDate,
LAG(Cityname, 1,'NoPrv_Cty')
OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS PreviousCity,
LEAD(Cityname, 1,'NoNxt_Cty')
OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS NextCity
FROM @Passenger

-- Queries will give result like below
PassengerID CityName StartDate EndDate PreviousCity NextCity
P00001 Delhi 1-Sep-2014 30-Sep-2014 NoPrv_Cty Mathura
P00001 Mathura 1-Oct-2014 9-Oct-2014 Delhi Etah
P00001 Etah 10-Oct-2014 24-Oct-2014 Mathura Kanpur
P00001 Kanpur 25-Oct-2014 26-Oct-2014 Etah Lucknow
P00001 Lucknow 27-Oct-2014 31-Dec-2099 Kanpur NoNxt_Cty
P00002 Allahabad 1-Oct-2014 31-Oct-2014 NoPrv_Cty Banaras
P00002 Banaras 1-Nov-2014 9-Nov-2014 Allahabad Mumbai
P00002 Mumbai 10-Nov-2014 14-Nov-2014 Banaras Banaras
P00002 Banaras 15-Nov-2014 24-Nov-2014 Mumbai Pune
P00002 Pune 25-Nov-2014 27-Nov-2014 Banaras Hyd
P00002 Hyd 28-Nov-2014 29-Nov-2014 Pune Chennai
P00002 Chennai 30-Nov-2014 31-Dec-2099 Hyd NoNxt_Cty
P00003 Bangalore 1-Dec-2014 31-Dec-2099 NoPrv_Cty NoNxt_Cty

/*Now if we want to find out which was the Previous to previous City and Which is Next to Next city
we need to modify our queries but in case of LEAD and LAG we just need to change off set Value
as below*/

SELECT *, 
DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100') 
        OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC))   AS EndDate,
LAG(Cityname, 2,'NoPrv_Cty') 
OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS PreviousCity,
LEAD(Cityname, 2,'NoNxt_Cty') 
OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS NextCity
FROM @Passenger

PassengerID CityName StartDate EndDate PreviousCity NextCity
P00001 Delhi 1-Sep-2014 30-Sep-2014 NoPrv_Cty Etah
P00001 Mathura 1-Oct-2014 9-Oct-2014 NoPrv_Cty Kanpur
P00001 Etah 10-Oct-2014 24-Oct-2014 Delhi Lucknow
P00001 Kanpur 25-Oct-2014 26-Oct-2014 Mathura NoNxt_Cty
P00001 Lucknow 27-Oct-2014 31-Dec-2099 Etah NoNxt_Cty
P00002 Allahabad 1-Oct-2014 31-Oct-2014 NoPrv_Cty Mumbai
P00002 Banaras 1-Nov-2014 9-Nov-2014 NoPrv_Cty Banaras
P00002 Mumbai 10-Nov-2014 14-Nov-2014 Allahabad Pune
P00002 Banaras 15-Nov-2014 24-Nov-2014 Banaras Hyd
P00002 Pune 25-Nov-2014 27-Nov-2014 Mumbai Chennai
P00002 Hyd 28-Nov-2014 29-Nov-2014 Banaras NoNxt_Cty
P00002 Chennai 30-Nov-2014 31-Dec-2099 Pune NoNxt_Cty
P00003 Bangalore 1-Dec-2014 31-Dec-2099 NoPrv_Cty NoNxt_Cty