Monday, October 30, 2017

TSQL - Create a view with a CTE

SQL Server comes with many benefits. One of the major valuable features is view in SQL Server. You know that we are not able to create temp tables in the view statements but we have another feature called Common Table Expression (CTE) to avoid this problem.


Now, we can use n number of CTEs in the view statement.
How to create View with a CTE: If you want to create a CTE in view then there is no need to use semi colon before with clause as given below-

Create View vw_WeekdaysList
AS
------ Create on the fly CTE Table
with CurrentDay as
(
select NameDay=DATENAME(DW,getdate()), WeekDay=DATEPART(dw, getdate())
union all
select NameDay=DATENAME(DW,getdate()+WeekDay),
WeekDay=DATEPART(dw, getdate()+WeekDay)
from CurrentDay
----- set weekday conditions
where WeekDay<7
)

------ Pull Data From CTE Table: CurrentDay
select Top 100 percent
NameDay, WeekDay,
---- Set flag 1 for current day
CurrentDay=IIF(WeekDay=DATEPART(dw, getdate()),1,0) 
from CurrentDay
order by WeekDay

Pull CTE based view dataset as given below-
---- Pull Week Days List
Select NameDay, WeekDay,
CurrentDay
from dbo.vw_WeekdaysList
order by WeekDay

NameDay
WeekDay
CurrentDay
Sunday
1
0
Monday
2
0
Tuesday
3
1
Wednesday
4
0
Thursday
5
0
Friday
6
0
Saturday
7
0

CTEs statements stay inside Views definitions and we can use n number of CTEs and can meet the business requirements.

To learn more, please follow us -
http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools

To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -
https://twitter.com/macxima

Thursday, October 5, 2017

TSQL - Logical OR and AND Operations in SQL Server

In SQL Server, logical operators are used to test for the truth of some condition. Based on the expressional conditions, they return a true or false value to combine one or more true or false values.

Logical Operator OR - If you are using logical operator OR that means any one or both conditions will be TRUE. In this case, if first condition is TRUE then second condition will also checked it which can be either TRUE or FALSE. 

Logical Operator AND - If you are using AND logical operator then both condition should be true for the results. We can say that if first condition is false then second condition will be checked also. 

When you are working some data queries then there are few chances to miss out simple logic order. If you are not writing your queries carefully then AND and OR statements can greatly change a data set. We can avoid common SQL bugs by either using parenthesis or organising our statements to represent the logic that should be applied. 
To understand these operators, we can take an example of customer data as given below-
CustId
CustName
CustEmail
CustAddress
CustMobile
101
Ryan Arjun
ryan.arjun@gmail.com
New Delhi, India
2541563214
102
Kimmy Wang
kwang@htc.com
New York, USA
4156321425
103
Bill Smith
bill.smith@google.com
Sydney, Australia
1562543214
104
Rose Gray
roseg@dell.com
British Columbia, Canada
5632125414
105
John Day
dayh@google.com
London, UK
4569870671
If we want to pull the data for customer where customer name like ‘Ryan%’ and Address like ‘%USA%’ or email like ‘%google%’ then how can we use these both logical operators (AND or OR) as given below-
----- Select statements
SELECT [CustId]
      ,[CustName]
      ,[CustEmail]
      ,[CustAddress]
      ,[CustMobile]
  FROM [dbo].[Tbl_Customer_Master]
  ----- customer name  start with Ryan
  where ([CustName] like 'Ryan%'
----- customer Address  like USA
  AND [CustAddress] like '%USA%')
----- customer email with google
  OR [CustEmail] like '%google%'

----- Output Result
CustId
CustName
CustEmail
CustAddress
CustMobile
103
Bill Smith
bill.smith@google.com
Sydney, Australia
1562543214
105
John Day
dayh@google.com
London, UK
4569870671
Conclusion
Logical operators compare two conditions at a time to determine whether a row can be selected for the output. They can change the result output if you are not using them carefully.

Memory Optimized Table Variable in SQL Server

This amazing feature (In-Memory Table Variables) was introduced in SQL Server 2014 to provide the better performance for storing temporary data within memory and process with in the stored Procedure or other T-SQL Scripts.
A table variable created using a memory-optimized table type is a memory-optimized table variable because SQL Server does not give us the permission to create an In-memory table variable directly with declare clause.


Memory-optimized table variables advantages or limitations:
  • The variables are only stored in memory. Data access is more efficient because memory-optimized table type use the same memory-optimized algorithm and data structures used for memory-optimized tables, especially when the variables are used in natively compiled stored procedures.
  • Another big advantages with memory-optimized table variables are that there is no tempdb utilization. Means Table variables are not stored in tempdb and do not use any resources in tempdb. 
  • Like memory-optimized tables, memory-optimized table variables do not support parallel plans and must fit in memory and do not use disk resources.


To understand this, we are going to take a dummy customer table having CustNo and CustName columns as given below:  
CustNo
CustName
20541
Lenovo Ltd.
20411
Amity University
20021
CMC Computer Ltd
20281
MRI Inc.
20151
NIIT Technologies
Based on the above table, we have to create a Memory Optimized Table Type called Customer_InMemory which is going to be used by Memory Optimized Table Variable as given below-
----- Create Memory Optimized Table Type
CREATE TYPE dbo.Customer_InMemory
AS TABLE(
     CustNo INT NOT NULL
    ,CustName NVARCHAR(50) NOT NULL
    INDEX [Ind_CustNo] HASH (CustNo)
                WITH ( BUCKET_COUNT = 2000)
)
WITH ( MEMORY_OPTIMIZED = ON )
GO
We have created Memory Optimized Table Type which is going to use by Memory Optimized Table Variable as given below-
---- Declare In-Memory Table Variable
Declare @Customers as Customer_InMemory

---- Insert values into In-Memory Table Variable
INSERT INTO @Customers (CustNo, CustName)
SELECT CustNo, CustName From [dbo].[CustMaster]

---- Pull values from In-Memory Table Variable
SELECT CustNo, CustName From @Customers
---- Output
CustNo
CustName
20541
Lenovo Ltd.
20411
Amity University
20021
CMC Computer Ltd
20281
MRI Inc.
20151
NIIT Technologies



Note: When a memory-optimized table variable is declared based on the table type, an instance of the table and index structures corresponding to the table type is created in the user session. 
With the help of the Memory Optimized Table Variables, we can increase the T-SQL queries performance. There are many typical usage scenarios where memory-optimized table variables could be more usable such as:
  1. If you want to store intermediate results then you can create a single result sets based on multiple queries in natively compiled stored procedures.
  2. Passing table-valued parameters into natively compiled stored procedures and interpreted stored procedures.
  3. Replacing disk-based table variables, and in some cases #temp tables that are local to a stored procedure. This is particularly useful if there is a lot of tempdb contention in the system.
  4. Table variables can be used to simulate cursors in natively compiled stored procedures, which can help us work around surface area limitations in natively compiled stored procedures.


Conclusion
In-memory table variable can be used in the same way as disk-based table variables. We can insert, update, and delete rows in the table variable, and use them in Transact-SQL queries. We can also pass the variables into natively compiled and interpreted stored procedures, as table-valued parameters. They do not interact with TempDb. So, Memory-optimization results in speed increases that are often 10 times faster or more.