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

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.

References -Microsoft MSDN