In this post I am going to look into the new T-SQL functions which are introduced with the SQL Server 2022 version. You have to install SQL Server 2022 first in your local machine if you want to follow along. You must have a machine that has: At least 2 GB of memory An internet connection At least Windows 10 or Windows Server 2016 operating system You can download SQL Server 2022, here. You will also need to install a sample database. Please download the AdventureWorks2019 database (AdventureWorks2019.bak). Launch SSMS and connect to the SQL Server 2022 database instance. Next, you need to res ..read more

In this post I am going to create a simple ASP.Net Core API application using EF Core. I am going to use the EF Core Code First paradigm. I am going to create a simple entity e.g Doctor and perform simple CRUD operations on that entity. I am using Microsoft Visual Studio Enterprise 2019. You can also use Visual Studio Community edition 2019 or 2022 or any other latest edition of Visual Studio. You can find the relevant downloads here. I am also using MS SQL Server 2019 Express edition. You can download it here . You can use any other edition or version (e.g MS SQL Server 2017 Express edition ..read more

Many developers when building multi-tier, multi-layered application use patterns to enhance the testability and usability of their apps. They often use UoW and Repository patterns which in general are great patterns to use. For example, in the case of the repository pattern, the purpose is to abstract away the low-level database query logic. Developers used to write SQL statements in their code and the repository pattern was a way to move that SQL out of individual methods scattered throughout the code base. Developers building applications with ASP.Net Core and Entity Framework Core shou ..read more

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked several questions about temporary tables, how to create them, how to manage them, when to use them and what are the limitations of them. In this post I will try to shed light on this particular issue with lots of hands-on demos. Temporary tables and table variables make use of the system tempdb database. I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well. I am connecting to my local instance t ..read more

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked several questions about Tempdb usage and temporary objects. In this post I will try to shed light on this particular issue. Temporary tables and table variables make use of the system tempdb database. There is only one tempdb system database per SQL Server instance so if there is a huge usage of temporary objects in this database it can be the point of contention. When you create an entry in database it needs to allocate space.This is also valid for the tempdb database.There are three types of pages involv ..read more

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server stored procedures and ownership chain. Security is a big thing in SQL Server and can be implemented in various levels. Before we move on, we should define some basic terms that are very important when we talk about security in SQL Server. The principal access data. It should be authenticated by SQL Server which basically means that the principal should “prove” that it is , whom it claims to be. Principal is a user a group or an application granted ..read more

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “If there is a way to find using T-SQL tables in a database that have no clustered indexes or no indexes at all” I have answered that this is possible and I provided with some scripts. There a lot of tasks that we cannot perform through the GUI of SSMS.So we have to search through the object metadata to get our answers.We can query the catalog views and get all the information we want I have installed SQL Server 2012 Enterprise edition in my machine but you can use the ..read more

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server pages and what types of pages exist. I had to explain to them about SQL Server architecture first. Each data file is divided internally into pages.Each page has a size of 8 kbytes. In each of these pages (data,metadata,indexes) are stored. 8 continuous pages of 8kbytes make up one extent which is 64kbytes. If you make the calculations,16 extents make up 1 mbyte. Extents are divided into 2 categories, Uniform – 8 pages belong to the same ..read more

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “Does the order of the column in an index matter?” Let’s give some basic definitions first and make some analogies. A single column index is straightforward. Think of it as an index in the back of the book. Let’s say you want to learn about “DBCC FREEPROCCACHE” . You look for that command in the index of the book. The index does not have the information. It has a pointer to the page where the command is described.You turn to that page and read about it. This is a good analogy fo ..read more

I have been delivering a certified course in MS SQL Server 2012 recently and I have found something that was really impressive regarding SET options in SQL Server and index creation. I will look into the SET QUOTED_IDENTIFIER and SET ANSI_NULLS options and how their values can affect index creation. I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well. I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste) USE ..read more